Full database backups are a great thing, but creating them frequently can be inconvenient. If the database is large, the backup will also be large, and creating it can put a significant load on the server and the network when transferring the backup to storage.

To avoid doing full backups all the time and to implement point-in-time recovery, PostgreSQL administrators typically archive WAL files.

However, starting with PostgreSQL 17, full support for incremental backups has been introduced. In this article, we will explain what incremental backups are, how they differ from WAL file archiving, and provide a simple guide on how to set up incremental backups.

What are incremental backups?

Incremental backups involve first creating a full backup of a file (in this case, a database) and then archiving only the changes made afterward. For example, if a database is 100 GB and a single byte changes, the incremental backup will record only that byte (and its position in the file). To restore, the full backup is unpacked, and then the necessary changes are applied to the relevant bytes using the incremental backups.

Simplified, at a low level of abstraction, a database consists of fixed-size blocks. When a block is updated, it also records the value of the global transaction number that performed the change known as the log sequence number (LSN). When an incremental backup is created, it copies only the blocks whose LSN values are higher than those recorded at the time of the previous backup (this information is taken from a special file called the backup_manifest).

How is it different from write-ahead log (WAL) archiving?

In the PostgreSQL community, the process of WAL archiving is often referred to as incremental backups. However, with the introduction of official support for incremental backups, it’s important to clearly distinguish between the two concepts.

The key differences between WAL archiving and incremental backups are as follows:

1. Implementation level
   • WAL archiving involves copying an existing WAL file already present on the disk.
   • Incremental backup entails scanning and copying only the database blocks that have changed.
2. Usage level
   • WAL archiving: you specify a command in postgresql.conf that describes how to copy the WAL file “externally.” PostgreSQL executes this command automatically when a WAL file is filled and ready for archiving.
   • Incremental backup: you run the pg_basebackup command with the --incremental parameter, passing the backup_manifest from the previous backup (either a base or incremental backup).
3. Supported versions
   • WAL archiving: supported starting from PostgreSQL version 8.0
   • Incremental backup: supported starting from PostgreSQL version 17.0
4. Administrative considerations
   • WAL archiving: one significant drawback is that if WAL files stop being archived (e.g., due to storage running out of space), PostgreSQL will stop deleting old WAL files. This can eventually lead to the database filling up entirely.
   • Incremental backups: these depend on previous incremental backups. For each backup, you must provide the backup_manifest from the previous backup. During restoration, you need to arrange backups in the correct order. It’s essential to establish a good naming convention to avoid confusion.

nano /etc/postgresql/17/main/postgresql.conf

Find and set (or simply add at the end) the following parameters:

summarize_wal = on
wal_level=replica

These settings enable more detailed logging for WAL files. After editing, you need to restart PostgreSQL:

sudo systemctl restart postgresql

Step-by-step guide to taking incremental backups in PostgreSQL

Let’s first define the order for performing incremental backups in PostgreSQL:

1. Take a full (base) backup
2. Take an incremental backup, providing the manifest file from the full backup
3. Take another incremental backup, using the manifest file from the previous incremental backup
4. Repeat step 3

You can perform all these steps using the pg_basebackup utility, which is installed alongside the PostgreSQL server.

Base (full) backup in PostgreSQL

The pg_basebackup utility is included with the PostgreSQL server. Essentially, this command safely copies the PostgreSQL data directory. It serves as the foundation for subsequent incremental backups.

pg_basebackup --host=localhost --user=postgres --format=tar --pgdata=/backup/pg_base/

Parameters:

• --host : the server address
• --user: the username
• --format: the archive format. The tar option allows everything to be packed into a TAR archive. In this article, we will use the TAR format since it provides a clearer demonstration of the backup components. If the database contains additional tablespaces, they will also be packed into archives.
• --pgdata: the destination path where the backup will be created. If the path does not exist, it will be created automatically.

If the database does not have additional tablespaces, the directory specified in --pgdata will contain three files:

$:~# ls /backup/pg_base/
backup_manifest base.tar pg_wal.tar

All these files are necessary. Let’s review them in detail:

• backup_manifest: this file is required for the next incremental backup. It determines the point from which data changes should be captured for the incremental backup.
• base.tar: a TAR archive of the directory (e.g., ls /var/lib/postgresql/17/main). During restore, this is the data you will copy back to the database directory.
• pg_wal.tar: a TAR archive containing the WAL files needed for recovery 

Incremental backup in PostgreSQL

To create an incremental backup, use the pg_basebackup utility with the --incremental  parameter, specifying the backup_manifest  from the previous backup.

The first incremental backup

For the first incremental backup, provide the backup_manifest from the full (base) backup:

pg_basebackup --user=postgres --format=tar --host=localhost --incremental=/backup/pg_base_backup/backup_manifest --pgdata=/backup/pg_inc1/ --incremental=/backup/pg_base/backup_manifest

This command will create files with the same names as the base backup: backup_manifest, base.tar, and pg_wal.tar. However, the base.tar file will be significantly smaller because it contains only the incremental data.

Subsequent incremental backups

For each subsequent incremental backup, you need to provide the backup_manifest from the previous incremental backup:

pg_basebackup --user=postgres --format=tar --host=localhost  --pgdata=/backup/pg_inc2/ --incremental=/backup/pg_inc1/backup_manifest

And so on. For each subsequent incremental backup, you use the backup_manifest from the previous one. To avoid confusion, it’s best to write a script to automate this process.

Step-by-Step: how to restore a PostgreSQL Incremental backup

Recovery plan

Here’s the plan for restoring the database:

1. Extract all TAR archives
2. Move all WAL files into a separate directory
3. Use the pg_combinebackup utility to merge all backups
4. Replace the contents of the PostgreSQL data directory
5. Configure the PostgreSQL server for the restore
6. Start the PostgreSQL server
7. Check the logs for any errors

For demonstration purposes, try to restore from the backup created earlier in this article.

Extracting archives

Unpack the TAR archives containing the database files into separate directories, and copy the backup_manifest into each directory:

mkdir -p /pg_prepare_restopre/pg_base/
tar -xvf /backup/pg_base/base.tar -C /pg_prepare_restopre/pg_base/
cp /backup/pg_base/backup_manifest /pg_prepare_restopre/pg_base/backup_manifest
mkdir -p /pg_prepare_restopre/pg_inc1/
tar -xvf /backup/pg_inc1/base.tar -C /pg_prepare_restopre/pg_inc1/
cp /backup/pg_inc1/backup_manifest /pg_prepare_restopre/pg_inc1/backup_manifest
mkdir -p /pg_prepare_restopre/pg_inc2/
tar -xvf /backup/pg_inc2/base.tar -C /pg_prepare_restopre/pg_inc2/
cp /backup/pg_inc2/backup_manifest /pg_prepare_restopre/pg_inc2/backup_manifest

Extract the archives with WAL files into a single directory:

mkdir -p /pg_prepare_restopre/all_wal/
tar -xvf /backup/pg_base/pg_wal.tar -C /pg_prepare_restopre/all_wal/
tar -xvf /backup/pg_inc1/pg_wal.tar -C /pg_prepare_restopre/all_wal/
tar -xvf /backup/pg_inc2/pg_wal.tar -C /pg_prepare_restopre/all_wal/

Use pg_combinebackup to prepare for restore

The pg_combinebackup utility is included in the PostgreSQL server binaries but is not automatically added to the PATH environment variable during installation. Therefore, you need to specify the full path to the utility when running the command:

/usr/lib/postgresql/17/bin/pg_combinebackup --output=/pg_prepare_restopre/pgdata/ /pg_prepare_restopre/pg_base/ /pg_prepare_restopre/pg_inc1/ /pg_prepare_restopre/pg_inc2/

In the --output parameter, specify the path where the prepared database files will be stored. After this parameter, list the backups in the correct order: the base backup followed by all subsequent incremental backups.

Stop PostgreSQL

Before replacing the contents of the PostgreSQL data directory, you need to stop the PostgreSQL server.

systemctl stop postgresql

Delete or Move the PostgreSQL Data Directory

Move the current PostgreSQL data directory to a different location:

mv /var/lib/postgresql/17/main/ /var/lib/postgresql/17/main_b/

or simply delete the data directory:

rm -rf /var/lib/postgresql/17/main/

Replace the PostgreSQL data directory

Recreate the PostgreSQL data directory:

mkdir -p /var/lib/postgresql/17/main/

Move the files prepared by the pg_combinebackup utility into the newly created PostgreSQL data directory:

cp -R /pg_prepare_restopre/pgdata/* /var/lib/postgresql/17/main/

Configure PostgreSQL server for restore

Create an empty file named recovery.signal, which signals the PostgreSQL server to perform a restore upon startup:

touch /var/lib/postgresql/17/main/recovery.signal

Set the directory containing the WAL files in the restore_command parameter. Open the postgresql.conf file for editing:

nano /etc/postgresql/17/main/postgresql.conf

and replace or append the command template for copying WAL files to the PostgreSQL server at the end:

restore_command = 'cp /pg_prepare_restopre/all_wal/%f %p'

The PostgreSQL server will automatically substitute the values: %f  will be replaced with the name of the WAL file, and %p will be replaced with the target directory:

Set permissions

When recreating the PostgreSQL data directory, permissions were likely reset. Additionally, the PostgreSQL server must have access to the directory from which it will copy the WAL files. Set the necessary permissions:

chmod -R 700 /var/lib/postgresql/17/main/
chown -R postgres /var/lib/postgresql/17/main/
chgrp -R postgres /var/lib/postgresql/17/main/

chmod -R 700 /pg_prepare_restopre/all_wal/
chown -R postgres /pg_prepare_restopre/all_wal/
chgrp -R postgres /pg_prepare_restopre/all_wal/

Start the PostgreSQL server

systemctl start postgresql

Check the Logs

If your server fails to start or you notice that the data has not been restored, you should check the logs:

tail -n 100 /var/log/postgresql/postgresql-17-main.log

2025-01-11 04:18:10.158 UTC [152309] LOG: database system was interrupted; last known up at 2025-01-11 04:00:42 UTC
cp: cannot stat '/pg_prepare_restopre/all_wal/00000003.history': No such file or directory
2025-01-11 04:18:10.205 UTC [152309] LOG: starting backup recovery with redo LSN 2/90000028, checkpoint LSN 2/900000B8, on timeline ID 2
2025-01-11 04:18:10.207 UTC [152309] LOG: restored log file "00000002.history" from archive
2025-01-11 04:18:10.236 UTC [152309] LOG: restored log file "000000020000000200000090" from archive
2025-01-11 04:18:10.248 UTC [152309] LOG: starting archive recovery
2025-01-11 04:18:10.250 UTC [152309] LOG: redo starts at 2/90000028
cp: cannot stat '/pg_prepare_restopre/all_wal/000000020000000200000091': No such file or directory
2025-01-11 04:18:10.254 UTC [152309] LOG: completed backup recovery with redo LSN 2/90000028 and end LSN 2/90000158
2025-01-11 04:18:10.254 UTC [152309] LOG: consistent recovery state reached at 2/90000158
2025-01-11 04:18:10.254 UTC [152306] LOG: database system is ready to accept read-only connections
cp: cannot stat '/pg_prepare_restopre/all_wal/000000020000000200000091': No such file or directory
2025-01-11 04:18:10.256 UTC [152309] LOG: redo done at 2/90000158 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2025-01-11 04:18:10.272 UTC [152309] LOG: restored log file "000000020000000200000090" from archive
cp: cannot stat '/pg_prepare_restopre/all_wal/00000003.history': No such file or directory
2025-01-11 04:18:10.288 UTC [152309] LOG: selected new timeline ID: 3
2025-01-11 04:18:10.316 UTC [152309] LOG: restored log file "00000002.history" from archive
2025-01-11 04:18:10.317 UTC [152309] LOG: archive recovery complete
2025-01-11 04:18:10.318 UTC [152307] LOG: checkpoint starting: end-of-recovery immediate wait
2025-01-11 04:18:10.322 UTC [152307] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.001 s, total=0.005 s; sync files=2, longest=0.001 s, average=0.001 s; distance=16384 kB, estimate=16384 kB; lsn=2/91000028, redo lsn=2/91000028
2025-01-11 04:18:10.328 UTC [152306] LOG: database system is ready to accept connections

The last line indicates that the database has started successfully.

cp: cannot stat '/pg_prepare_restopre/all_wal/000000020000000200000091': No such file or directory

The presence of “errors” such as this is no cause for alarm. This is explicitly mentioned in the PostgreSQL documentation:

Normally, recovery will proceed through all available WAL segments, thereby restoring the database to the current point in time (or as close as possible given the available WAL segments). Therefore, a normal recovery will end with a “file not found” message, the exact text of the error message depending upon your choice of restore_command. You may also see an error message at the start of recovery for a file named something like 00000001.history. This is also normal and does not indicate a problem in simple recovery situations. Source.

If the database fails to start, there will be an error message, which usually indicates the root cause (most likely, you either mixed up the order of steps or didn’t set the necessary permissions).

Notes

• Even if no changes were made to the database, the backup will still take up several megabytes. With default settings, this is around 50 MB, largely due to WAL files.
• Both base and incremental backups are created after a checkpoint in the database. During heavy write operations, new checkpoints may not appear for up to 5 minutes (this is governed by the server parameter checkpoint_timeout). You can use the --checkpoint=fast parameter to start the backup earlier. However, this is not recommended, as creating a checkpoint puts significant load on the disk. It’s better to let the database determine the optimal time for this.
• If your database uses additional tablespaces, you need to unpack them to the same locations as they were on the source server. The base.tar archive includes a file called tablespace_map that indicates where everything should be located.

Recommendations

1. Test your backups. Always test the restoration process for your backups — at least on a test server. If you don’t have one, you can easily set up a temporary server on an hourly-billed hosting provider.
2. Use compression. It’s useful to enable compression when creating backups. You can enable compression either on the server side or the client side (but not both simultaneously).
   • --compress=client compresses the output archives and adds .gz to their filenames automatically.
   • --compress=servercompresses the backups on the server side, reducing network load but increasing CPU usage on the server.
     Compressed backups take up less space, so they can be created more frequently or retained for longer.
3. Verbose mode. Use the -v parameter for the pg_basebackup utility to display additional information about what it’s doing. This can help identify the stage at which the utility is stuck, should it hang during execution.