November 29, 2024
Summary: in this tutorial, you will learn how to perform incremental backups in PostgreSQL.
Table of Contents
Why Incremental Backups?
PostgreSQL provides a method to take a full, consistent backup from a running PostgreSQL server without disrupting existing client connections. For large deployments, this may take hours or days to complete and can eat up a lot of disk I/O bandwidth during that time. To work around this, typically a full backup is taken occasionally and WAL archiving is done continuously. WAL archiving is thus used as a fine-grained incremental backup.
Having a genuine incremental backup solution, however, can result in much lower disk space requirements for backup, as well as reducing the overall complexity of backup and restore operations. It is easier, and less error-prone, to work with one full backup and one incremental backup, rather than one full backup and a thousand WAL files.
To perform incremental backups and restore in PostgreSQL 17, follow these steps:
WAL Summarization
PostgreSQL 17 comes with a new background worker process called a WAL summarizer process, which creates “summaries” of WAL files in a directory called pg_wal/summaries. By default this process is not turned on, but it needs to be for incremental backup to work. To turn it on, use the new configuration setting summarize_wal:
ALTER SYSTEM SET summarize_wal = 'on';
SELECT pg_reload_conf();
Changing this setting does not need a PostgreSQL restart to take effect, only a reload.
Full Backup
The usual way of taking backups, using pg_basebackup, results in a full backup. Let’s take a full backup into a directory /backup/full1:
$ pg_basebackup -D /backup/full1 -c fast -p 7000 -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/10000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_1182"
pg_basebackup: write-ahead log end point: 0/10000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
After a while and some changes, let’s take another full backup (into /backup/full2), so we can compare against the incremental backup:
$ pg_basebackup -D /backup/full2 -c fast -p 7000
Incremental Backup
Let’s also take an incremental backup at this point. To do this, use pg_basebackup with the new -i, --incremental=OLDMANIFEST option. We want to take the increment from the full backup in /backup/full1, so we use the manifest /backup/full1/backup_manifest and create the backup in /backup/incr2:
$ pg_basebackup -D /backup/incr2 --incremental /backup/full1/backup_manifest -c fast -p 7000 -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/17000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_1223"
pg_basebackup: write-ahead log end point: 0/17000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
Let’s compare the sizes of all three backups:
$ du -sh /backup/*
189M /backup/full1
189M /backup/full2
51M /backup/incr2
The second full backup /backup/full2 has the same size as the initial full backup /backup/full1. The incremental backup /backup/incr2 is only a bit more than a quarter of the size of the full backup at the same point in time.
The contents of /backup/full2 and /backup/incr2 look similar, but by looking at the backup label you can see if the backup is an incremental one or not:
$ cat /backup/full2/backup_label
START WAL LOCATION: 0/15000028 (file 000000010000000000000015)
CHECKPOINT LOCATION: 0/15000080
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2024-11-22 10:13:37 IST
LABEL: pg_basebackup base backup
START TIMELINE: 1
$ cat /backup/incr2/backup_label
START WAL LOCATION: 0/17000028 (file 000000010000000000000017)
CHECKPOINT LOCATION: 0/17000080
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2024-11-22 10:13:56 IST
LABEL: pg_basebackup base backup
START TIMELINE: 1
INCREMENTAL FROM LSN: 0/10000028
INCREMENTAL FROM TLI: 1
Second Incremental Backup
When more changes occur, create the second incremental backup using the backup_manifest from the first incremental backup:
$ pg_basebackup -D /backup/incr3 --incremental /backup/incr2/backup_manifest -c fast -p 7000
Restoring Incremental Backups
To restore an incremental backup, use the new utility pg_combinebackup. The following command takes the first full backup /backup/full1 and the incremental backups at a later time, combines them, and writes the output into the directory /backup/restore3:
$ pg_combinebackup -o /backup/restore3 /backup/full1 /backup/incr2 /backup/incr3
We can start a postmaster with the data directory in /backup/restore3 and see that it is starting successfully after a backup recovery:
2024-11-22 10:26:28.269 UTC [1443] LOG: starting PostgreSQL 17 on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2024-11-22 10:26:28.272 UTC [1443] LOG: listening on IPv6 address "::1", port 7001
2024-11-22 10:26:28.273 UTC [1443] LOG: listening on IPv4 address "127.0.0.1", port 7001
2024-11-22 10:26:28.277 UTC [1443] LOG: listening on Unix socket "/tmp/.s.PGSQL.7001"
2024-11-22 10:26:28.291 UTC [1446] LOG: database system was interrupted; last known up at 2024-11-22 10:13:56 IST
2024-11-22 10:26:28.500 UTC [1446] LOG: starting backup recovery with redo LSN 0/17000028, checkpoint LSN 0/17000080, on timeline ID 1
2024-11-22 10:26:28.511 UTC [1446] LOG: redo starts at 0/17000028
2024-11-22 10:26:28.515 UTC [1446] LOG: completed backup recovery with redo LSN 0/17000028 and end LSN 0/17000120
2024-11-22 10:26:28.515 UTC [1446] LOG: consistent recovery state reached at 0/17000120
2024-11-22 10:26:28.515 UTC [1446] LOG: redo done at 0/17000120 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2024-11-22 10:26:28.613 UTC [1444] LOG: checkpoint starting: end-of-recovery immediate wait
2024-11-22 10:26:28.641 UTC [1444] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.009 s, sync=0.003 s, total=0.032 s; sync files=2, longest=0.002 s, average=0.002 s; distance=16384 kB, estimate=16384 kB; lsn=0/18000028, redo lsn=0/18000028
2024-11-22 10:26:28.652 UTC [1443] LOG: database system is ready to accept connections
Considerations
- Backup Directory: Ensure each backup, whether full or incremental, is stored in a separate directory to prevent overwriting files.
- Backup Monitoring: Regularly monitor the backups to ensure they complete successfully and the
backup_manifestfiles are correctly maintained. - Restore Procedure: Familiarize yourself with the restoration process, especially if you need to combine multiple incremental backups using tools like
pg_combinebackupin PostgreSQL 17. This tool helps combine incremental backups into a usable state.
By following these steps, you can maintain a series of incremental backups that efficiently capture changes to your PostgreSQL database.
Incremental Backup vs PITR
In the current PostgreSQL version, it is possible to first take a full backup, then archive each WAL file generated since that point. This allows you to restore the backup, then replay each WAL file up to a specific point in time, thus recreating the data directory as it was at that point. This is usually called Point-In-Time-Recovery (PITR).
Logically, the result of a PITR up to an incremental backup point should be the same as restoring that incremental backup at that point. The difference however, is that the size of the incremental backups can be a little, or a lot, less than the combined WAL file size. If a row is updated by 100 transactions for example, WAL files will have to record all those 100 changes, but the incremental backup only records the difference between the initial and final states of the data directory contents.
If you were using WAL archiving for purely incremental backup purposes and not PITR, it is now possible to simply use the new incremental backup feature instead. If you need PITR, WAL archiving and replay is still the way to go.