Full and Incremental Backup using pg_basebackup and pg_combinebackup in PostgreSQL

PostgreSQL is a robust open-source database that supports online physical backups, advanced backup techniques, and point-in-time recovery for enterprise-grade reliability. In the world of data, we should always be prepared for all kinds of scenarios like hardware failure, data corruption, user negligence, or software bugs. Starting from PostgreSQL 17, we see the support for Incremental backups using pg_basebackup. In this article, we will discuss the steps to implement Full and Incremental Backup using pg_basebackup and pg_combinebackup for PostgreSQL, for a robust backup strategy.

The need for a good backup strategy

A strong backup strategy for most of the databases including PostgreSQL, is required to support the following key functionalities.

• Disaster Recovery

  • Ability to recover a database after a total system failure (hardware crash, fire, natural disaster, Data Center Outage and other failures leading to the loss of a database system).

• Point-in-Time Recovery (PITR)

  • Ability to rolling back the database to a specific point-in-time. This helps in scenarios such as -
    • accidental DROP TABLE
    • data corruption
    • deployment failures yielding to data loss or other accidental changes

  PITR help us take the database back to a healthy state, as long as we have a good backup strategy.

• Replication for High Availability

  • Backups are the primary steps to be performed for setting up replication. In order to setup a replica or a standby, we must be able to take a consistent backup of the database, that can be restored on the standby, before replicating changes from primary.

Natively supported tools in PostgreSQL for a strong backup strategy

Let us understand the natively supported PostgreSQL tools and utilities, that support the three requirements discussed above.

• pg_basebackup
  pg_basebackup is a lightweight, native PostgreSQL tool that supports online physical backups without downtime. It enables both full and incremental backups, making it ideal for reliable disaster recovery and point-in-time-recovery.

  • Full Backup

    • A full backup (or base backup) is a complete, consistent snapshot of the entire PostgreSQL data directory at a specific point in time. It serves as the foundation for any recovery operation. When using pg_basebackup, the full backup is the starting point for all subsequent incremental backups.

  • Incremental Backup

    • An Incremental backup captures only the blocks or files that have changed since the last backup (which could be the full backup or a previous incremental backup), this reduces backup time, I/O load on the server, and storage space compared to running full backups daily.

• pg_combinebackup

  • An incremental backup on its own is not immediately usable for recovery. To restore, we must first apply all incremental backups, in order, to the original full backup. For this reason, PostgreSQL supports pg_combinebackup, a tool that merges the full and incremental backups as a combined backup.

• WAL Archiving

  • PostgreSQL continuously generates Write-Ahead Log (WAL) records to ensure that every change made to the database is securely captured for recovery and replication. Since WAL segments are recycled once certain thresholds are reached, enabling WAL Archiving aka Continuous Archiving is a must to preserve historical transaction logs.

  • How to enable continuous archiving ?

    • By turning on archive_mode and configuring an appropriate archive_command, PostgreSQL automatically copies completed WAL segments to external or remote storage. This ensures that even after hardware failures or data corruption, the database can be fully restored to a consistent state using the archived WAL files.

Steps to setup Full and Incremental backup using pg_basebackup and pg_combinebackup

In this section, we will see the steps involved in setting up Full and Incremental backups for PostgreSQL.

Prerequisites

The following parameters are considered as mandatory when taking a full and an incremental backup with pg_basebackup.

• wal_level : We must at least set wal_level to replica, this is the default. Also supported when this is set to logical. We may use minimal instead of replica for full backups, but it won't support WAL archiving for point-in-time-recovery (PITR).

• summarize_wal: Must be set to ON. WAL sumarizer tracks changes at the block level to support Incremental backups. This is applicable for PostgreSQL 17, and 18 releases only.

• archive_mode: Must be set to on. To support WAL archiving and PITR.

• archive_command: a shell command or a script to archive a WAL. For example: 'cp %p /var/lib/postgresql/wal_archive/%f'

We can use ALTER SYSTEM to modify the above parameters. Depending on the parameters being modified, you may need a restart.

ALTER SYSTEM SET wal_level TO 'replica';
ALTER SYSTEM SET summarize_wal TO 'on';
ALTER SYSTEM SET archive_mode TO 'on';
ALTER SYSTEM SET archive_command TO 'cp %p /var/lib/postgresql/wal_archive/%f';

$ pg_ctl -D $PGDATA reload -mf

Steps to take Full and Incremental backups.

Step 1: Use the below command to take Full backup of the PostgreSQL database cluster.

$ pg_basebackup -h localhost -p 5432 -U postgres -D /tmp/backup_full -Fp -P -v -Xs  

You may change the hostname and port and the location of the backup as per your instance configuration.

Note: The backup should be taken with a superuser or a user with Replication role.

This basebackup by default creates a manifest file unless --no-manifestis supplied. This manifest file consists the list of every file present in the backup with the exception of any WAL files that may be included. This is needed as a reference for the incremental backups, to maintain a relationship for later backups.

Step 2: We will now use pgbench to generate some changes on the database and generate a few WAL segments. This step is included for the demo purpose only. You need not perform this step.

pgbench -c 16 -j 4 -T 300 -h localhost -p 5432 -d postgres

Now we have some WAL's generated and let us proceed with the first Incremental backup.

Step 3: Taking an Incremental Backup with--incremental option

pg_basebackup -h localhost -p 5432 -U postgres -D /tmp/backup_incremental -Fp -P -v -Xs --incremental=/tmp/backup_full/backup_manifest

Postgres community now added--incrementaloption to take incremental backups which is not present in versions before PostgreSQL 17. The incremental backup needs a backup_manifest to create a incremental backup so that it identifies which blocks have changed since last full back.

Merging Full and Incremental Backup for Restore test using pg_combinebackup

The pg_combinebackup expects the first argument to be a Full backup path, followed by the path to N incremental backups. It is to be noted that pg_basebackup doesn't register each incremental backup with a numbered subsequent backup. it is our responsibility to note subsequent incremental backups in a chronoligical order and pass them to pg_combinebackup as an argument.

Using the below command, we merge the full and incremental backups that were taken in the previous steps.

pg_combinebackup /tmp/backup_full/ /tmp/backup_incremental/ -o ./backup_merged_18

Now that we have merged all the backups, we will now copy the necessary configuration files to merged backup folder path, make the necessary edits to port and data_directory entries and start the server.

cp -r conf.d/ /var/lib/postgresql/backup_merged_18/
cp pg_hba.conf pg_ident.conf postgresql.conf start.conf /var/lib/postgresql/backup_merged_18/

We still need to do some modifications to recover the database when using WAL Archiving for a consistent recovery point.

We need to create arecovery.signal file in recovered data_directory to tell PostgreSQL to recover WALs from the Wal Archive.

Additionally we have to add the parameter restore_command in the PostgreSQL configuration file like postgresql.conf or postgresql.auto.conf.

touch /var/lib/postgresql/backup_merged_17/recovery.signal

The recovery.signal file will be removed once the database reaches a consistent state.

In postgresql.auto.conf, add the appropriate shell command that can fetch the WAL segments from archive directory.

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'

Start the Restored PostgreSQL Instance

$ pg_ctl -D /var/lib/postgresql/backup_merged_18 start -l logfile

We should now have a successfully restored and recovered Full plus Incremental backup.

Conclusion

The arrival of native incremental backup support in PostgreSQL 17 via pg_basebackup and with the pg_combinebackup utility marks a major milestone in PostgreSQL. PostgreSQL users can now leverage the Full and Incremental backup capabilities without relying on external tools. If you are looking to migrate from Oracle to PostgreSQL or SQL Server to PostgreSQL or moving your databases being cloud platforms, do not hesitate to reach us at: connect@hexacluster.ai

Subscribe to our Newsletters and Stay tuned for more interesting topics.