The upcoming version of PostgreSQL, v17, is set to include an exciting new feature – incremental backups. Read on for a quick teaser on what working with incremental backups will look like.
Disclaimer: Note that as of this writing (May 2024), a beta release of PostgreSQL 17 is expected soon. The details of this feature and its usage might change between now and the final release of PostgreSQL 17.
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.
WAL Summarization
Postgres 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:
psql (17devel)
Type "help" for help.
postgres=# show summarize_wal;
summarize_wal
---------------
off
(1 row)
postgres=# alter system set summarize_wal = 'on';
ALTER SYSTEM
postgres=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
postgres=# show summarize_wal;
summarize_wal
---------------
on
(1 row)
postgres=#
Changing this setting does not need a Postgres restart to take effect, only a reload. After this, you can see the new process running:
$ ps xf
PID TTY STAT TIME COMMAND
[...]
1170 ? Ss 0:00 /opt/pg17/bin/postgres -D .
1171 ? Ss 0:01 \_ postgres: checkpointer
1172 ? Ss 0:00 \_ postgres: background writer
1174 ? Ss 0:00 \_ postgres: walwriter
1175 ? Ss 0:00 \_ postgres: walsummarizer
1176 ? Ss 0:00 \_ postgres: autovacuum launcher
1177 ? Ss 0:00 \_ postgres: logical replication launcher
[...]
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 backup1:
$ pg_basebackup -D backup1 -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 backup2_full), so we can compare against the incremental backup:
$ pg_basebackup -D backup2_full -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 backup1, so we use the manifest backup1/backup_manifest and create the backup in backup2_incr:
$ pg_basebackup -D backup2_incr -i backup1/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 backup1
189M backup2_full
51M backup2_incr
The second full backup backup2_full has the same size as the initial full backup backup1. The incremental backup backup2_incr is only a bit more than a quarter of the size of the full backup at the same point in time.
The contents of backup2_full and backup2_incr look similar, but by looking at the backup label you can see if the backup is an incremental one or not:
$ cat backup2_full/backup_label
START WAL LOCATION: 0/15000028 (file 000000010000000000000015)
CHECKPOINT LOCATION: 0/15000080
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2024-05-22 10:13:37 IST
LABEL: pg_basebackup base backup
START TIMELINE: 1
$ cat backup2_incr/backup_label
START WAL LOCATION: 0/17000028 (file 000000010000000000000017)
CHECKPOINT LOCATION: 0/17000080
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2024-05-22 10:13:56 IST
LABEL: pg_basebackup base backup
START TIMELINE: 1
INCREMENTAL FROM LSN: 0/10000028
INCREMENTAL FROM TLI: 1
Restoring Incremental Backups
To restore an incremental backup, use the new utility pg_combinebackup. The following command takes the first full backup backup1 and the incremental backup at a later time backup2_incr, combines them, and writes the output into the directory restore2:
$ pg_combinebackup -o restore2 backup1 backup2_incr
We can start a postmaster with the data directory in restore2 and see that it is starting successfully after a backup recovery:
2024-05-22 10:26:28.269 IST [1443] LOG: starting PostgreSQL 17devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2024-05-22 10:26:28.272 IST [1443] LOG: listening on IPv6 address "::1", port 7001
2024-05-22 10:26:28.273 IST [1443] LOG: listening on IPv4 address "127.0.0.1", port 7001
2024-05-22 10:26:28.277 IST [1443] LOG: listening on Unix socket "/tmp/.s.PGSQL.7001"
2024-05-22 10:26:28.291 IST [1446] LOG: database system was interrupted; last known up at 2024-05-22 10:13:56 IST
2024-05-22 10:26:28.500 IST [1446] LOG: starting backup recovery with redo LSN 0/17000028, checkpoint LSN 0/17000080, on timeline ID 1
2024-05-22 10:26:28.511 IST [1446] LOG: redo starts at 0/17000028
2024-05-22 10:26:28.515 IST [1446] LOG: completed backup recovery with redo LSN 0/17000028 and end LSN 0/17000120
2024-05-22 10:26:28.515 IST [1446] LOG: consistent recovery state reached at 0/17000120
2024-05-22 10:26:28.515 IST [1446] LOG: redo done at 0/17000120 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2024-05-22 10:26:28.613 IST [1444] LOG: checkpoint starting: end-of-recovery immediate wait
2024-05-22 10:26:28.641 IST [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-05-22 10:26:28.652 IST [1443] LOG: database system is ready to accept connections
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.
About pgDash
pgDash is a modern, in-depth monitoring solution designed specifically for PostgreSQL deployments. pgDash shows you information and metrics about every aspect of your PostgreSQL database server, collected using the open-source tool pgmetrics.
pgDash provides core reporting and visualization functionality, including collecting and displaying PostgreSQL information and providing time-series graphs, detailed reports, diagnostics, alerting, teams and more. Checkout the features here or signup today for a free trial.