In the last post about the introduction of incremental backups for PostgreSQL 17 we’ve looked at the basic concepts of that feature and how you can use it. In the meantime Robert Haas has written a blog post describing some uses cases for the feature. This blog post is the inspiration for this one, and this is all about backups chains and how you can use them.
For configuring PostgreSQL for incremental backups please check the previous post, I am not going to repeat this here.
Let’s assume today is Sunday and you are taking a full backup every Sunday like this:
postgres@debian12-pg:/home/postgres/ [pgdev] mkdir -p /var/tmp/backups/sunday
postgres@debian12-pg:/home/postgres/ [pgdev] pg_basebackup -D /var/tmp/backups/sunday/
For Monday to Saturday you’re doing incremental backups:
postgres@debian12-pg:/home/postgres/ [pgdev] weekdays=('mon' 'tue' 'wed' 'thu' 'fri' 'sat')
postgres@debian12-pg:/home/postgres/ [pgdev] for i in "${weekdays[@]}"
do
mkdir -p /var/tmp/backups/$i
pg_basebackup --incremental=/var/tmp/backups/sunday/backup_manifest --pgdata=/var/tmp/backups/$i
done
postgres@debian12-pg:/home/postgres/ [pgdev] ls -la /var/tmp/backups/
total 36
drwxr-xr-x 9 postgres postgres 4096 Jan 12 17:13 .
drwxrwxrwt 7 root root 4096 Jan 12 17:13 ..
drwxr-xr-x 20 postgres postgres 4096 Jan 12 17:10 fri
drwxr-xr-x 20 postgres postgres 4096 Jan 12 17:10 mon
drwxr-xr-x 20 postgres postgres 4096 Jan 12 17:10 sat
drwxr-xr-x 20 postgres postgres 4096 Jan 12 16:57 sunday
drwxr-xr-x 20 postgres postgres 4096 Jan 12 17:10 thu
drwxr-xr-x 20 postgres postgres 4096 Jan 12 17:10 tue
drwxr-xr-x 20 postgres postgres 4096 Jan 12 17:10 wed
This gives you an incremental backup every day except Sunday and all of them use the full backup form Sunday as a starting point. You might also use one of the previous incremental backups as the starting point for new incremental backups like this:
postgres@debian12-pg:/home/postgres/ [pgdev] mkdir /var/tmp/backups/xx
postgres@debian12-pg:/home/postgres/ [pgdev] pg_basebackup --incremental=/var/tmp/backups/wed/backup_manifest --pgdata=/var/tmp/backups/xx
postgres@debian12-pg:/home/postgres/ [pgdev] ls /var/tmp/backups/xx
backup_label pg_dynshmem pg_notify pg_subtrans postgresql.auto.conf
backup_manifest pg_hba.conf pg_replslot pg_tblspc postgresql.conf
base pg_ident.conf pg_serial pg_twophase
current_logfiles pg_log pg_snapshots PG_VERSION
global pg_logical pg_stat pg_wal
pg_commit_ts pg_multixact pg_stat_tmp pg_xact
In this case the new incremental backups is based on a previous incremental backup, it does not need to reference a previous full backup.
What we have now is the following chain:
sun - full backup
-- mon - incremental, based on sun
-- tue - incremental, based on sun
-- wed - incremental, based on sun
-- thu - incremental, based on sun
-- fri - incremental, based on sun
-- sat - incremental, based on sun
What you can do to reduce the chain of backups is to combine some of them using pg_combinebackup. Let’s assume we want to introduce another full backup on Wednesday. Instead of doing a full backup we can also combine the full backup from Sunday with the incremental backup we did on Wednesday:
postgres@debian12-pg:/home/postgres/ [pgdev] pg_combinebackup /var/tmp/backups/sunday/ /var/tmp/backups/wed/ -o /var/tmp/backups/wed_full
postgres@debian12-pg:/home/postgres/ [pgdev] ls /var/tmp/backups/wed_full
backup_label pg_dynshmem pg_notify pg_subtrans postgresql.auto.conf
backup_manifest pg_hba.conf pg_replslot pg_tblspc postgresql.conf
base pg_ident.conf pg_serial pg_twophase
current_logfiles pg_log pg_snapshots PG_VERSION
global pg_logical pg_stat pg_wal
pg_commit_ts pg_multixact pg_stat_tmp pg_xact
This gives us a new consistent full backup, and this one can be used as a starting point for further incremental backups:
postgres@debian12-pg:/home/postgres/ [pgdev] mkdir /var/tmp/backups/oo
postgres@debian12-pg:/home/postgres/ [pgdev] pg_basebackup --incremental=/var/tmp/backups/wed_full/backup_manifest --pgdata=/var/tmp/backups/oo
postgres@debian12-pg:/home/postgres/ [pgdev] ls /var/tmp/backups/oo
backup_label pg_dynshmem pg_notify pg_subtrans postgresql.auto.conf
backup_manifest pg_hba.conf pg_replslot pg_tblspc postgresql.conf
base pg_ident.conf pg_serial pg_twophase
current_logfiles pg_log pg_snapshots PG_VERSION
global pg_logical pg_stat pg_wal
pg_commit_ts pg_multixact pg_stat_tmp pg_xact
To create a new incremental backup based on this new full backup just follow the same procedure and give the backup manifest of that backup for the new incremental one:
postgres@debian12-pg:/home/postgres/ [pgdev] mkdir /var/tmp/backups/pp
postgres@debian12-pg:/home/postgres/ [pgdev] pg_basebackup --incremental=/var/tmp/backups/oo/backup_manifest --pgdata=/var/tmp/backups/pp
postgres@debian12-pg:/home/postgres/ [pgdev] ls /var/tmp/backups/pp
backup_label pg_dynshmem pg_notify pg_subtrans postgresql.auto.conf
backup_manifest pg_hba.conf pg_replslot pg_tblspc postgresql.conf
base pg_ident.conf pg_serial pg_twophase
current_logfiles pg_log pg_snapshots PG_VERSION
global pg_logical pg_stat pg_wal
pg_commit_ts pg_multixact pg_stat_tmp pg_xact
Combine the full backup and the two incrementals, and you have again a consistent full backup which you can just start up:
postgres@debian12-pg:/home/postgres/ [pgdev] pg_combinebackup /var/tmp/backups/wed_full /var/tmp/backups/oo /var/tmp/backups/pp -o /var/tmp/backups/kk
postgres@debian12-pg:/home/postgres/ [pgdev] echo "port=8888" >> /var/tmp/backups/kk/postgresql.auto.conf
postgres@debian12-pg:/home/postgres/ [pgdev] chmod 700 /var/tmp/backups/kk
postgres@debian12-pg:/home/postgres/ [pgdev] pg_ctl -D /var/tmp/backups/kk start
postgres@debian12-pg:/home/postgres/ [pgdev] psql -p 8888 -c "select version()"
version
--------------------------------------------------------------------
PostgreSQL 17devel on x86_64-linux, compiled by gcc-12.2.0, 64-bit
(1 row)
Another use case: You do a full backup on Sunday:
postgres@debian12-pg:/home/postgres/ [pgdev] rm -rf /var/tmp/backups/
postgres@debian12-pg:/home/postgres/ [pgdev] pg_basebackup -D /var/tmp/backups/sun
Then you do incremental backups on Monday and Tuesday, but this time the incremental backup from Tuesday is based on the incremental backup from Monday:
postgres@debian12-pg:/home/postgres/ [pgdev] pg_basebackup --incremental=/var/tmp/backups/sun/backup_manifest --pgdata=/var/tmp/backups/mon
postgres@debian12-pg:/home/postgres/ [pgdev] pg_basebackup --incremental=/var/tmp/backups/mon/backup_manifest --pgdata=/var/tmp/backups/tue
On Wednesday you combine Sunday, Monday and Tuesday and create a new incremental backup based on this:
postgres@debian12-pg:/home/postgres/ [pgdev] pg_combinebackup /var/tmp/backups/sun /var/tmp/backups/mon /var/tmp/backups/tue -o /var/tmp/backups/wed_full
postgres@debian12-pg:/home/postgres/ [pgdev] pg_basebackup --incremental=/var/tmp/backups/wed_full/backup_manifest --pgdata=/var/tmp/backups/wed_incr
What this is supposed to show you: It is totally up to you how you manage the chain of backups. Each backup can be the starting point for another one and combining several incremental backups (including the last full) into a new full backup helps you in reducing the chain of backups. I really like this concept as it gives you a lot of choice in how you are going to implement your backup strategy.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)