PostgreSQL New Feature is a series.
- Key features, parameters, and system catalog changes in PostgreSQL 17
- New and improved features in PostgreSQL 17
- Vacuum performance improvements in PostgreSQL 17
- Incremental Backup in PostgreSQL 17
This post explains how to take and restore incremental backups, and then describes the WAL Summary feature that incremental backup relies on.
Incremental Backup
Up to PostgreSQL 16, which did not support incremental backup, backups were based on full database backups. Every backup produced backup files as large as the database. To recover data changed after the backup, you had to archive WAL files so they were not deleted, and then use them for recovery. This process took a long time and needed a lot of storage space.
Starting with PostgreSQL 17, incremental backup is supported, which saves backup time and storage space. Incremental backup needs 1 full database backup, but after that each backup captures only what changed since the last backup. That is, it captures only the changes made since the last backup, whether that was a full backup or another incremental backup. Regardless of the changes, this can greatly reduce backup time and storage consumption compared to a full backup.
Features added for incremental backup
New option for the pg_basebackup utility
The -i option was added to support incremental backup. You run it by specifying the manifest file of the last backup.
New pg_combinebackup utility
A new utility in PostgreSQL 17 for merging multiple incremental backups into a single backup.
Key option | Description |
-d, --debug | Print debug logs |
-n, --dry-run | Show what would be done without actually performing the merge |
-o, --output=DIRECTORY | Specify the path where the reconstructed data directory is stored |
backup_directory | Specify one or more paths of the incremental backups to merge. |
New system parameters
Parameter | Default | Description |
summarize_wal | off | Whether the WAL Summary feature is enabled. Must be set to on for incremental backup |
wal_summary_keep_time | 14400 (10d) | Retention period for WAL Summary files |
Taking an incremental backup
To use incremental backup, you need to enable the
summarize_wal system parameter.Before taking an incremental backup, first create a base (full) backup.
After the base (full) backup, generate some data and then take an incremental backup. When taking an incremental backup, make sure to use the backup_manifest of the most recent backup. At this step, the most recent backup is the base (full) backup, so specify the backup_manifest of base_backup.
Add more data and take another incremental backup. Again, use the backup_manifest of the most recent backup. At this step, the most recent backup is the first incremental backup, so specify the backup_manifest of incremental_backup01.
Restoring incremental backups
In the backup step, we took one base (full) backup and two incremental backups. Now we restore these backups, start the database on the restored data directory, and check that the test data we inserted earlier is there.
With the pg_combinebackup utility, new in PostgreSQL 17, you can combine all incremental backups and restore them. Combine the incremental backups created earlier with pg_combinebackup and restore them into the /home/postgres/pg17.combine_backup directory.
The postgresql.conf file in the restored directory is the same as the postgresql.conf of the backed-up server. To start a database that uses the restored directory as its data directory, change the port in postgresql.conf and then start the database.
Connect to the database and check the data. The test data is a total of 2,000,000 rows in the test01 table: 1,000,000 rows with c1=1 and 1,000,000 rows with c1=2.
Trouble Shooting
When the summarize_wal parameter is not enabled
If the summarize_wal parameter is not enabled, a base backup is possible but an incremental backup is not.
If you set
summarize_wal=on and run the incremental backup again, you get the error message below.The incremental backup fails because summarize_wal was enabled after the base backup was taken. So after enabling summarize_wal, you need to take a new base backup and then take the incremental backup for it to work.
When the backup directories are not listed in order for pg_combinebackup
When running pg_combinebackup, the backup directories must be listed in backup order. If they are not, pg_combinebackup fails with an error and the incremental backups cannot be restored.
When the base backup is not listed first (listed as incremental backup 1 → incremental backup 2 → base backup)
When the incremental backups are not listed in order (listed as base backup → incremental backup 2 → incremental backup 1)
WAL Summary feature
The walsummarizer process, new in PostgreSQL 17, is a new WAL-related background process that efficiently summarizes WAL and collects statistics.
WAL Summary parameters
Parameter | Category | Default | Description |
summarize_wal | Write-Ahead Log > Summarization | off | Whether the WAL Summary feature is enabled |
wal_summary_keep_time | Write-Ahead Log > Summarization | 14400 (10d) | Retention period for WAL Summary files |
To enable the WAL Summary feature, the
wal_level parameter must be set to replica or logical, and the summarize_wal parameter, new in PostgreSQL 17, must be turned on (default: off).When the WAL Summary feature is enabled, the walsummarizer process runs and stores WAL summaries in the pg_wal/summaries directory.
WAL Summary files are retained according to the
wal_summary_keep_time parameter, new in PostgreSQL 17. The default for wal_summary_keep_time is 14400 minutes (10 days), so WAL Summary files are removed automatically after 10 days.For testing, we create a table, insert data, force a WAL Summary file to be created, and then check its contents.
You can see that a WAL Summary file was created by issuing a CHECKPOINT. The contents of a WAL Summary file cannot be read with an editor; you read them with the pg_walsummary utility, new in PostgreSQL 17.
In the pg_walsummary output,
REL 16384 is the test01 table, and blocks 0.. 8 shows that 9 blocks were changed. You can also see this in the WAL Summary function examples below.The following functions are provided to inspect the contents of WAL Summary files.
Function | Description |
pg_available_wal_summaries() | Returns the start LSN and end LSN of each WAL Summary file |
pg_wal_summary_contents( tli , start_lsn , end_lsn ) | Returns update information between the specified LSNs |
pg_get_wal_summarizer_state() | Returns the WAL summarization status |
pg_available_wal_summaries()
You can infer the start LSN and end LSN of a WAL Summary file from its file name without calling the function.
pg_wal_summary_contents( tli , start_lsn , end_lsn )
Column | Description |
relfilenode | Relation file number (the relation name can be found through the relfilenode column of pg_class) |
reltablespace | OID of the relation's tablespace (the tablespace name can be found through the oid column of pg_tablespace) |
reldatabase | OID of the relation's database (the database name can be found through the oid column of pg_database) |
relforknumber | Fork number in the relation |
relblocknumber | Page number in the relation (in the example above, this means 9 blocks were modified in the relation with relfilenode = 16384) |
is_limit_block | f : Indicates a modification by a WAL recordt and relblocknumber > 0 : Indicates that the relation fork was truncated to the length given by relblocknumber within the range of WAL recordst and relblocknumber = 0 : Indicates that the relation fork was created or dropped within the range of WAL records |
Check the relation name (relfilenode)
Check the tablespace name (reltablespace)
Check the database name (reldatabase)
Check the number of modified blocks (relblocknumber)
pg_get_wal_summarizer_state()
Column | Description |
summarized_tli | If the walsummarizer process has never run: 0If the walsummarizer process has run: shows the TLI(Timeline) of the last WAL Summary file written to disk |
summarized_lsn | If the walsummarizer process has never run: 0/0If the walsummarizer process has run: shows the end LSN of the last WAL Summary file written to disk |
pending_lsn | If the walsummarizer process is not running: 0/0If the walsummarizer process is running: shows the end LSN of the last record (greater than or equal to summarized_lsn) |
summarizer_pid | If the walsummarizer process is not running: NULLIf the walsummarizer process is running: the PID of the walsummarizer process |
Related articles
