In this article, I will show how to create a full backup and an incremental backup using pg_basebackup in PostgreSQL 18. I will then use pg_combinebackup to combine the backups and restore the database.
Preparing PostgreSQL
First, we need to enable the required parameters in postgresql.conf.
Add or modify the following parameters:
[postgres@OL8 ~]$ vi /var/lib/pgsql/18/data/postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp "%p" "/archive/%f"'
summarize_wal = on
The summarize_wal parameter enables WAL summarization, which is required for incremental backups.
After changing these parameters, restart PostgreSQL:
[postgres@OL8 ~]$ /usr/pgsql-18/bin/pg_ctl -D /var/lib/pgsql/18/data -l logfile restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
Creating Backup Directories
Next, create separate directories for the full and incremental backups:
[root@OL8 ~]# mkdir /PGbackup/Full /PGbackup/Incremental -p
[root@OL8 ~]# chown -R postgres.postgres /PGbackup
Now the environment is ready to create the full backup.
Creating the Full Backup
Run pg_basebackup as the postgres user:
[postgres@OL8 ~]$ pg_basebackup -D /PGbackup/Full/ -P
23648/23648 kB (100%), 1/1 tablespace
The backup directory contains a complete PostgreSQL data directory:
[postgres@OL8 ~]$ ls -l /PGbackup/Full/
-rw-------. 1 postgres postgres 229 Aug 17 20:55 backup_label
-rw-r-----. 1 postgres postgres 227 Aug 17 20:55 backup_label.old
-rw-------. 1 postgres postgres 138089 Aug 17 20:55 backup_manifest
drwxr-x---. 5 postgres postgres 33 Aug 17 20:55 base
-rw-------. 1 postgres postgres 30 Aug 17 20:55 current_logfiles
drwxr-x---. 2 postgres postgres 4096 Aug 17 20:55 global
drwxr-x---. 2 postgres postgres 32 Aug 17 20:55 log
drwxr-x---. 2 postgres postgres 6 Aug 17 20:55 pg_commit_ts
drwxr-x---. 2 postgres postgres 6 Aug 17 20:55 pg_dynshmem
-rw-r-----. 1 postgres postgres 5721 Aug 17 20:55 pg_hba.conf
-rw-r-----. 1 postgres postgres 2681 Aug 17 20:55 pg_ident.conf
drwxr-x---. 4 postgres postgres 68 Aug 17 20:55 pg_logical
drwxr-x---. 4 postgres postgres 36 Aug 17 20:55 pg_multixact
drwxr-x---. 2 postgres postgres 6 Aug 17 20:55 pg_notify
drwxr-x---. 2 postgres postgres 6 Aug 17 20:55 pg_replslot
drwxr-x---. 2 postgres postgres 6 Aug 17 20:55 pg_serial
drwxr-x---. 2 postgres postgres 6 Aug 17 20:55 pg_snapshots
drwxr-x---. 2 postgres postgres 6 Aug 17 20:55 pg_stat
drwxr-x---. 2 postgres postgres 6 Aug 17 20:55 pg_stat_tmp
drwxr-x---. 2 postgres postgres 6 Aug 17 20:55 pg_subtrans
drwxr-x---. 2 postgres postgres 6 Aug 17 20:55 pg_tblspc
drwxr-x---. 2 postgres postgres 6 Aug 17 20:55 pg_twophase
-rw-r-----. 1 postgres postgres 3 Aug 17 20:55 PG_VERSION
drwxr-x---. 4 postgres postgres 77 Aug 17 20:55 pg_wal
drwxr-x---. 2 postgres postgres 18 Aug 17 20:55 pg_xact
-rw-r-----. 1 postgres postgres 88 Aug 17 20:55 postgresql.auto.conf
-rw-r-----. 1 postgres postgres 32453 Aug 17 20:55 postgresql.conf
The backup_manifest file is particularly important because it is used to identify the contents of the backup when creating an incremental backup.
Making Changes to the Database
Now, let’s make some changes to the database after the full backup has been created.
First, create a new database:
postgres=# create database vahid;
CREATE DATABASE
postgres=# \c vahid
You are now connected to database "vahid" as user "postgres".
vahid=# create table tb(id int,name varchar(100));
CREATE TABLE
vahid=# insert into tb values(1,'Vahid Yousefzadeh');
INSERT 0 1
At this point, the full backup does not contain these changes because they were made after the full backup was created.
Creating the Incremental Backup
Now we can create an incremental backup based on the full backup.
Use the backup_manifest from the full backup as the reference:
[postgres@OL8 ~]$ pg_basebackup -D /PGbackup/Incremental/ --incremental /PGbackup/Full/backup_manifest -P
11477/31360 kB (36%), 1/1 tablespace
The incremental backup is much smaller than the full backup because it contains only the data required to update the referenced backup.
The resulting directory contains another backup manifest and the required PostgreSQL files:
[postgres@OL8 ~]$ ls -lh /PGbackup/Incremental/
total 252K
-rw-------. 1 postgres postgres 286 Aug 17 20:59 backup_label
-rw-r-----. 1 postgres postgres 227 Aug 17 20:59 backup_label.old
-rw-------. 1 postgres postgres 184K Aug 17 20:59 backup_manifest
drwxr-x---. 6 postgres postgres 46 Aug 17 20:59 base
-rw-------. 1 postgres postgres 30 Aug 17 20:59 current_logfiles
drwxr-x---. 2 postgres postgres 4.0K Aug 17 20:59 global
drwxr-x---. 2 postgres postgres 32 Aug 17 20:59 log
drwxr-x---. 2 postgres postgres 6 Aug 17 20:59 pg_commit_ts
drwxr-x---. 2 postgres postgres 6 Aug 17 20:59 pg_dynshmem
-rw-r-----. 1 postgres postgres 5.6K Aug 17 20:59 pg_hba.conf
-rw-r-----. 1 postgres postgres 2.7K Aug 17 20:59 pg_ident.conf
drwxr-x---. 4 postgres postgres 68 Aug 17 20:59 pg_logical
drwxr-x---. 4 postgres postgres 36 Aug 17 20:59 pg_multixact
drwxr-x---. 2 postgres postgres 6 Aug 17 20:59 pg_notify
drwxr-x---. 2 postgres postgres 6 Aug 17 20:59 pg_replslot
drwxr-x---. 2 postgres postgres 6 Aug 17 20:59 pg_serial
drwxr-x---. 2 postgres postgres 6 Aug 17 20:59 pg_snapshots
drwxr-x---. 2 postgres postgres 6 Aug 17 20:59 pg_stat
drwxr-x---. 2 postgres postgres 6 Aug 17 20:59 pg_stat_tmp
drwxr-x---. 2 postgres postgres 6 Aug 17 20:59 pg_subtrans
drwxr-x---. 2 postgres postgres 6 Aug 17 20:59 pg_tblspc
drwxr-x---. 2 postgres postgres 6 Aug 17 20:59 pg_twophase
-rw-r-----. 1 postgres postgres 3 Aug 17 20:59 PG_VERSION
drwxr-x---. 4 postgres postgres 77 Aug 17 20:59 pg_wal
drwxr-x---. 2 postgres postgres 18 Aug 17 20:59 pg_xact
-rw-r-----. 1 postgres postgres 88 Aug 17 20:59 postgresql.auto.conf
-rw-r-----. 1 postgres postgres 32K Aug 17 20:59 postgresql.conf
Restoring the Full and Incremental Backups
Now let’s test the backup by completely removing the existing PostgreSQL data directory and rebuilding it from the full and incremental backups.
First, stop PostgreSQL:
[postgres@OL8 ~]$ /usr/pgsql-18/bin/pg_ctl -D /var/lib/pgsql/18/data -l logfile stop
waiting for server to shut down.... done
server stopped
Remove the contents of the existing data directory:
[postgres@OL8 ~]$ rm -rf /var/lib/pgsql/18/data/*
At this point, the original data directory is empty.
Combining the Backups with pg_combinebackup
PostgreSQL provides the pg_combinebackup utility, which can combine a base backup and one or more incremental backups into a new PostgreSQL data directory.
[postgres@OL8 ~]$ pg_combinebackup -o /var/lib/pgsql/18/data/ /PGbackup/Full/ /PGbackup/Incremental/
The command creates the restored data directory:
[postgres@OL8 data]$ ll
total 248
-rw-r-----. 1 postgres postgres 229 Aug 17 21:03 backup_label
-rw-r-----. 1 postgres postgres 227 Aug 17 21:03 backup_label.old
-rw-r-----. 1 postgres postgres 182110 Aug 17 21:03 backup_manifest
drwxr-x---. 6 postgres postgres 46 Aug 17 21:03 base
-rw-r-----. 1 postgres postgres 30 Aug 17 21:03 current_logfiles
drwxr-x---. 2 postgres postgres 4096 Aug 17 21:03 global
drwxr-x---. 2 postgres postgres 32 Aug 17 21:03 log
drwxr-x---. 2 postgres postgres 6 Aug 17 21:03 pg_commit_ts
drwxr-x---. 2 postgres postgres 6 Aug 17 21:03 pg_dynshmem
-rw-r-----. 1 postgres postgres 5721 Aug 17 21:03 pg_hba.conf
-rw-r-----. 1 postgres postgres 2681 Aug 17 21:03 pg_ident.conf
drwxr-x---. 4 postgres postgres 68 Aug 17 21:03 pg_logical
drwxr-x---. 4 postgres postgres 36 Aug 17 21:03 pg_multixact
drwxr-x---. 2 postgres postgres 6 Aug 17 21:03 pg_notify
drwxr-x---. 2 postgres postgres 6 Aug 17 21:03 pg_replslot
drwxr-x---. 2 postgres postgres 6 Aug 17 21:03 pg_serial
drwxr-x---. 2 postgres postgres 6 Aug 17 21:03 pg_snapshots
drwxr-x---. 2 postgres postgres 6 Aug 17 21:03 pg_stat
drwxr-x---. 2 postgres postgres 6 Aug 17 21:03 pg_stat_tmp
drwxr-x---. 2 postgres postgres 6 Aug 17 21:03 pg_subtrans
drwxr-x---. 2 postgres postgres 6 Aug 17 21:03 pg_tblspc
drwxr-x---. 2 postgres postgres 6 Aug 17 21:03 pg_twophase
-rw-r-----. 1 postgres postgres 3 Aug 17 21:03 PG_VERSION
drwxr-x---. 4 postgres postgres 77 Aug 17 21:03 pg_wal
drwxr-x---. 2 postgres postgres 18 Aug 17 21:03 pg_xact
-rw-r-----. 1 postgres postgres 88 Aug 17 21:03 postgresql.auto.conf
-rw-r-----. 1 postgres postgres 32453 Aug 17 21:03 postgresql.conf
The output directory now contains the combined contents of the full and incremental backups.
Starting PostgreSQL
The data directory has been restored, so we can start PostgreSQL:
[postgres@OL8 ~]$ /usr/pgsql-18/bin/pg_ctl -D /var/lib/pgsql/18/data -l logfile start
waiting for server to start.... done
server started
Verifying the Restore
Finally, let’s verify that the database and table created after the full backup are available.
Connect to PostgreSQL:
[postgres@OL8 ~]$ psql
psql (18.0)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+-------------+-------------+--------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
template0 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
vahid | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
(4 rows)
postgres=# \c vahid
You are now connected to database "vahid" as user "postgres".
vahid=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | tb | table | postgres
(1 row)
vahid=# select * from tb;
id | name
----+-------------------
1 | Vahid Yousefzadeh
(1 row)
The table and its data are available after restoring the full backup together with the incremental backup.
