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.
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.
The types of Postgres backups
There are three different ways to back up a Postgres database: Logical backups (pg_dump
), file system backups, and continuous archiving.
Logical backups
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.
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
.
Regardless of the format used, the data pg_dump
outputs is instructions on how to rebuild the data in the database.
-- An abbreviated snippet of output from pg_dump in plain format
CREATE TABLE public.customers (
id bigint NOT NULL,
name text NOT NULL,
email text NOT NULL,
country text NOT NULL,
created_at timestamp with time zone NOT NULL
);
ALTER TABLE public.customers OWNER TO postgres;
COPY public.customers (id, name, email, country, created_at) FROM stdin;
1 Customer 1 user1@example.com UK 2020-01-02 00:00:00+00
2 Customer 2 user2@example.com DE 2020-01-03 00:00:00+00
...
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.
Note
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.
Logical backups and transaction wraparound
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:
-- pg_dump with the default settings
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY;
...
-- pg_dump --serializable-deferrable
-- Ensures transactions are serializable while the backup is being taken.
-- Generally not recommended for backups intended for disaster recovery.
-- see the --serializable-deferrable flag for pg_dump
-- https://www.postgresql.org/docs/current/app-pgdump.html
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY, DEFERRABLE;
...
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.
Although pg_dump
may not get its own xid
, it pins a transaction snapshot of the active xid
s at that moment, giving the dump a consistent view of the database for its entire duration.
Note
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 xid
s 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?
Postgres treats xid
s as a circular range. Quoting from depesz's article on transaction wraparound:
Let's explain this bit on smaller numbers. Let's assume that we have the whole range of 0 to 10. Only 11 xids. If my current xid is 5, then xids 0..4 are in the past, and 6..10 are in the future. And if current xid is 3? Future is simple: 4..8. But past now contains two ranges: 0..2, and 9..10. Similarly, if current xid is 9, then past xids will be 4..8, and future would be 10, 0..3.
To keep the range circular and xid
s reusable, Postgres uses a freeze bit. This bit marks a tuple as older than any running transaction or snapshot, and should always be read as an "old" tuple.
When autovacuum
runs, it internally calls an internal Postgres function GetOldestNonRemovableTransactionId()
. This finds the oldest xid
that is assigned to an open transaction, otherwise known as the transaction horizon, and autovacuum
will go and freeze tuples "older" than that given xid.
A unique situation arises when the following criteria are met:
- A long running open transaction pins the transaction horizon
- More than ~2 billion new transactions have started after the long running transaction
If both of these points are met, new transactions start seeing old unfrozen tuples as if they were written by future transactions. This is called a transaction wraparound. At this point, Postgres will refuse new commands that would create xid
s, triggering read-only mode to ensure no data loss actually occurs.
Since pg_dump
holds its snapshot open for the entire duration of the backup, it prevents the transaction horizon from advancing until the backup finishes. On a large database with sufficiently high throughput, pg_dump
may inadvertently trigger this phenomenon bringing the cluster into read-only mode.
Above, we can see this in action. Each transaction has its own "future" and "past" based off its own xid
. Every time autovacuum
runs, it advances the oldest unfrozen xid
. The blue section represents active xid
s that have not yet been frozen.
When the cluster goes into read-only mode in our visual, new transactions would potentially cause data loss. A new transaction would see unfrozen tuples created by the first transaction after pg_dump
ran as if they were created by a future transaction that has not yet run.
On very large clusters (upwards of 30TB), this is a valid concern depending on the write throughput to the database. This isn't to say pg_dump
is bad by any means. pg_dump
is an extremely useful tool. The point here is demonstrating that for taking scheduled backups, pg_dump
is most likely not the correct tool.
File system backup
Another approach is to perform a full file system backup. This is substantially faster than pg_dump
for saving and restoring a backup.
A file system backup bypasses the database and directly copies the underlying file system.
In order for file system backups to be usable however, the database must be completely shut down. If the database is online during a backup, rows could be changed after the backup process has started, but before they have been copied, leaving the database in an inconsistent state.
Note
It is possible to take a file system backup online, only if the underlying file system supports atomic file system snapshots. See the Postgres docs on file system backups for more details.
This is explained in more detail in the next section, as this is what continuous archiving solves.
The other drawback to this type of backup (and logical backups) is that changes to data after a backup has been taken are not included in the backup. If data from between "now" and the most recent backup needs to be recovered, it isn't there.
The solution to this is to save the WAL, along with the file system, which is exactly what continuous archiving does.
Offline file system backups are generally a non-starter for production workloads, as no one wants to take down their database for hours to perform a daily backup.
Continuous archiving
The most powerful (and complicated) backup method is continuous archiving. Continuous archiving combines a file system backup with the write-ahead log (WAL).
This process can be done without downtime and generally lower impact on the CPU of the current system. This is what PlanetScale uses to facilitate Postgres and Neki backups.
The first step of taking a continuous archive backup has already started long before you take your first backup: archiving the WAL.
The WAL
The WAL is like your database's logbook. Every change, addition, or deletion of the data in your database is written down in the WAL.
Before Postgres makes any changes to data on disk, it first records the changes in the WAL. This ensures that upon a system crash, Postgres can restore to a consistent state by replaying the data mutations saved in its WAL. See the ARIES Wikipedia for more information on the algorithm and reasons behind WAL.
The WAL is by default saved in 16MB segments as binary files, however the size is configurable per cluster. These can be fed to tools such as pg_waldump
yielding human readable output:
rmgr: Heap2 len (rec/tot): 60/ 7136, tx: 0, lsn: 0/01C12FF8, prev 0/01C12F80, desc: PRUNE snapshotConflictHorizon: 771, nredirected: 0, ndead: 15
blkref #0: rel 1663/16384/16387 fork main blk 0 (FPW); hole: offset: 452, length: 1116
rmgr: Heap len (rec/tot): 119/ 6443, tx: 778, lsn: 0/01C14BF0, prev 0/01C12FF8, desc: HOT_UPDATE old_xmax: 778, old_off: 44, old_infobits: [], flags: 0x10, new_xmax: 0, new_off: 85
blkref #0: rel 1663/16384/16387 fork main blk 1 (FPW); hole: offset: 364, length: 1868
rmgr: Heap len (rec/tot): 114/ 114, tx: 778, lsn: 0/01C16538, prev 0/01C14BF0, desc: HOT_UPDATE old_xmax: 778, old_off: 45, old_infobits: [], flags: 0x10, new_xmax: 0, new_off: 86
blkref #0: rel 1663/16384/16387 fork main blk 1
...
...
...
rmgr: Heap len (rec/tot): 106/ 106, tx: 778, lsn: 0/01C16F78, prev 0/01C16F08, desc: HOT_UPDATE old_xmax: 778, old_off: 82, old_infobits: [], flags: 0x10, new_xmax: 0, new_off: 108
blkref #0: rel 1663/16384/16387 fork main blk 1
rmgr: Transaction len (rec/tot): 46/ 46, tx: 778, lsn: 0/01C16FE8, prev 0/01C16F78, desc: COMMIT 2026-07-21 02:22:15.866163 UTC
rmgr: Heap len (rec/tot): 107/ 807, tx: 779, lsn: 0/01C17018, prev 0/01C16FE8, desc: HOT_UPDATE old_xmax: 779, old_off: 1, old_infobits: [], flags: 0x10, new_xmax: 0, new_off: 2
blkref #0: rel 1663/16384/16399 fork main blk 0 (FPW); hole: offset: 508, length: 7492
rmgr: Heap len (rec/tot): 107/ 911, tx: 779, lsn: 0/01C17340, prev 0/01C17018, desc: HOT_UPDATE old_xmax: 779, old_off: 2, old_infobits: [], flags: 0x10, new_xmax: 0, new_off: 3
blkref #0: rel 1663/16384/16399 fork main blk 8 (FPW); hole: offset: 500, length: 7388
There is a lot of information here, and remember this is only what pg_waldump
is showing us. For understanding how continuous archiving works, there are a few parts of WAL we need to understand.
len (rec/tot)
:rec
is the size in bytes of the specific record data, whiletot
is the total bytes saved in WAL for this entry.lsn
: The log sequence number (LSN) is the byte address of the given WAL line.FPW
: Full page write (FPW) marks where a full page was written to the WAL. This is what causes rec != tot in thelen
field.rel
: Tablespace / db / filenode, or more simply put, the location of a table file in a specific database. Tables are represented as filenodes, which are made up of pages, and saved as files on disk. See our MVCC blog for more detail.blk
: The specific page of the filenode that is being updated or saved.desc
: What action is happening.HOT_UPDATE
, for example, is when an updated tuple with an associated index (in this case from anUPDATE
SQL query) is saved to the same page it was originally on.
By default, Postgres enables a setting called full_page_writes
. This setting is necessary for continuous archiving, and is forced on during a file system backup (also known as a base backup). This instructs Postgres to save an entire page to WAL the first time the page is updated after a checkpoint.
If the base backup is taken from a replica or standby node, then the operator must ensure full_page_writes
is turned on for the primary. When the backup is taken directly on the primary, pg_basebackup
(the common CLI tool for taking continuous archives) will force it on.
Note
Pages are chunks of data within table or index files on disk, usually 8KB per page. Pages contain the underlying tuples and pointers that materialize into rows.
Every WAL line above containing FPW
marks a point where Postgres saved the entire page (blk
) within a given filenode (rel
) to the WAL.
If you look closely at the rel
, blk
, and FPW
on each line, you can see each first update to a given page (rel
/blk
) after a checkpoint triggers a new full page write. This is the mechanism that makes continuous archiving possible, as we will see later on.
Archiving data
Saving WAL segments often means streaming them to external storage systems such as S3. This can be done via tools such as wal-g
, which are built for this exact purpose.
For any Postgres cluster (including Neki shards) running on PlanetScale, every primary has a wal-g
sidecar streaming its Postgres WAL to S3.
When we start a backup, we begin by taking an online file system backup. Interestingly, we don't care if writes or mutations cause the file system to be in an inconsistent state, or even corrupted.
The data files we copy over can be thought of as "smeared" data on disk since they have potentially been "smeared" by queries that happened during our backup. We will take a look at exactly why we don't need to worry about smeared data with an example.
The start time is based on a LSN in the log, known as the backup's start_lsn
, while the end_lsn
is the point in the WAL at which the backup completed.
Say we start taking a backup at some time stamp. As the backup is copying over files, the database receives a SQL statement:
UPDATE users SET email_confirmed=true WHERE id = 314159
where email_confirmed
was previously false. At this point, any one of three things could happen after the query executes.
- The backup process has already copied the file containing the page for that row where
email_confirmed
was set to false. This is the best case; our backup is unaffected, and still in a consistent state. We have already copied the file, so we do not care if it is modified after the fact. - The backup has not yet read the page where that row lives, and our backup is now out of sync. The backup started at a time where
email_confirmed
was false, but it will save that data page while it is true! - The page is backed up at the same instant it was written to, and is now corrupted. The page now contains mangled bits, and later on we will be unable to read its contents properly.
How does Postgres know how to un-smear these data files?
This is where the full_page_writes
capability of the WAL comes into play. After we have copied over the smeared file system backup from S3 to a new container for restoring, Postgres looks over the WAL reading every change between start_lsn
and end_lsn
.
Since full_page_writes
was on when Postgres received the UPDATE users ...
query mentioned above, it created a new heap tuple on disk as usual, and additionally logged a full image of the entire 8KB page where that row lives to the WAL.
Regardless of the state of that page from the file system backup, whether it is corrupted, in an inconsistent state, or perfectly healthy, we have enough information within the WAL to reconstruct it. The full page image in the WAL can replace the same page that was originally backed up if the ordinary WAL records are not enough to repair it.
Notice that the actual restored backup is not a snapshot of the database when the backup was started (like how pg_dump
works), but a consistent version of the database when it finished backing up.
If we tried to restore the database to a point in-between start_lsn
and end_lsn
we would potentially be left with smeared data. The restore process must read through the full WAL from start to end before the restore can be marked as healthy.
Point in time recovery
The backups system described above is also what facilitates Postgres' ability to do point-in-time restores.
This is where the "Continuous" part of "Continuous Archiving" comes into play. Since the WAL is continuously saved to S3, we can restore any backup and continuously replay the WAL past our end_lsn
up to any arbitrary point in time.
Replaying the WAL is generally more expensive than copying over the file system, however. This is why backups need to happen often, and is why at PlanetScale we back up every cluster every 12 hours by default. This keeps the WAL replay within a reasonable bound (half of a day) and keeps restores fast.
Continuously saving WAL is what also powers cluster resizes. When you resize your cluster on PlanetScale, the following is happening under the hood:
- We bring up a new node with the desired specs
- Copy over the file system backup (or mount an EBS volume if we are on an EBS backed node)
- Replay WAL as close to the current time as possible
- Stream remaining changes from the primary
- Cutover to new node
In fact, this is the same process we use to take backups in the first place! We use the latest backup taken (usually around 12 hours ago), bring it online, catch it up to the primary, and finally save its file system as a new backup. This is all done on a throwaway node only used to take a new backup, meaning the entire process never contends with the primary for CPU, IO, or network bandwidth.
Backups at scale
Even with these techniques in place, extremely large databases can end up with backups that take 12+ hours to complete, resulting in ever growing windows between restore points.
When restore is needed, it could take days to get the new node back online. You can attempt a PITR from your most recent backup, but that could mean waiting substantially longer for WAL to not only stream over, but replay onto a new node.
We wrote previously on how Vitess handles backups at this scale, but what about Postgres?
Find out next week where we discuss distributed Postgres, and how splitting your cluster into shards with Neki can scale your backup process to petabytes and beyond.