Postgres backups under the hood — PlanetScale📹 The future of AI infrastructure: optimize and shard your database with agents.Watch the talk
Navigation<br>Blog|Engineering<br>Table of contents «Close »Table of contents<br>The types of Postgres backups<br>Logical backupsLogical backups and transaction wraparound
File system backup<br>Continuous archivingThe WAL<br>Archiving data<br>Point in time recovery
Backups at scale<br>PlanetScale Postgres is the fastest way to run Postgres in the cloud. Plans start at just $5 per month.<br>Learn more
Get the RSS feed
Postgres backups under the hood<br>Josh Brown [@imjosh] | July 24, 2026<br>Data integrity is one of the most important pillars for a database system. If your database goes down, backups are how you stay safe and recover in case of disaster.<br>Backups should be taken often, and recovery needs to stay fast. With backups being such a vital part of keeping your data safe, you should know how they work.<br>The types of Postgres backups<br>There are three different ways to back up a Postgres database: Logical backups (pg_dump), file system backups, and continuous archiving.<br>Logical backups<br>pg_dump is a purely logical backup that exports a consistent snapshot of the database, and is the simplest way to back up a Postgres database. At its core, pg_dump reads the current state of the database and writes the data to a given output.<br>pg_dump can create dumps in either plain text as SQL statements (with SQL queries such as COPY or INSERT), or custom pg_dump formats designed for use with pg_restore.<br>Regardless of the format used, the data pg_dump outputs is instructions on how to rebuild the data in the database.<br>-- An abbreviated snippet of output from pg_dump in plain format<br>CREATE TABLE public.customers (<br>id bigint NOT NULL,<br>name text NOT NULL,<br>email text NOT NULL,<br>country text NOT NULL,<br>created_at timestamp with time zone NOT NULL<br>);
ALTER TABLE public.customers OWNER TO postgres;
COPY public.customers (id, name, email, country, created_at) FROM stdin;<br>1 Customer 1 user1@example.com UK 2020-01-02 00:00:00+00<br>2 Customer 2 user2@example.com DE 2020-01-03 00:00:00+00<br>...
Reading, formatting, and compressing a few gigabytes is trivial, but in the terabytes+ this becomes increasingly unfeasible. Even if your system can take a successful pg_dump backup at 30TB, it consumes a lot of resources. User queries must contend with pg_dump for CPU and IO, and in worst case scenarios this brings the database to a crawl.<br>Note<br>Logical backups lack the ability to do point in time recovery (PITR) since the backup is a static snapshot of data at a given time. However, they are particularly useful for facilitating migrations.
pg_dump connects to the database through a standard SQL connection, only saving the rows and schema inside the database. It does not save physical page layouts, dead tuples, stored index bytes, or cluster-wide roles/tablespaces. This generally keeps outputs from pg_dump smaller compared to other backup methods.<br>Logical backups and transaction wraparound<br>Logical backups leverage Postgres's MVCC support in order to take a consistent backup while the database is online. If you're not familiar with MVCC, read our blog on Postgres MVCC. The section on transaction horizons is particularly important here. When pg_dump starts, it opens a transaction in one of two ways:<br>-- pg_dump with the default settings<br>BEGIN;<br>SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY;<br>...
-- pg_dump --serializable-deferrable<br>-- Ensures transactions are serializable while the backup is being taken.<br>-- Generally not recommended for backups intended for disaster recovery.<br>-- see the --serializable-deferrable flag for pg_dump<br>-- https://www.postgresql.org/docs/current/app-pgdump.html<br>BEGIN;<br>SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE;<br>...
Every transaction that modifies data gets assigned an internal id, referred to as its xid. Rows in the database contain a hidden variable named xmin. xmin informs Postgres of the xid for the transaction that modified that row, while xmax is which transaction deleted, updated or locked that row.<br>Although pg_dump may not get its own xid, it pins a transaction snapshot of the active xids at that moment, giving the dump a consistent view of the database for its entire duration.<br>Note<br>txid is the 8 byte ID used for tracking transactions that modify data. xid is the lower 4 bytes of txid and is stored in tuples on disk as xmin, xmax, or in other variables.
Remember that xids are 32-bit integers used to track the historical version of rows (or tuples, technically) across transactions. Tuples are the underlying representation of a row on disk. 32 bits can only represent a maximum of ~4 billion numbers, so what happens after we have more than 4 billion transactions?<br>Postgres treats xids as a circular range. Quoting from depesz's article on transaction wraparound:<br>Let's explain this bit on smaller numbers. Let's assume that we have the whole range of 0 to 10. Only 11 xids....