As pre-requisite for incremental backup feature the below two parameters should be set first:
postgres=# show summarize_wal;
summarize_wal
on
postgres=# show wal_level;
wal_level
replica
if summarize_wal parameter not set then set it and you will need to restart postgres instance to take affect.
postgres=# ALTER SYSTEM SET summarize_wal = on;
To perform a simulation i will create a dummy database called “testdb” and will create a table t1 and insert dummy values:
postgres=# create database testdb;
CREATE DATABASE
postgres=# \c testdb
You are now connected to database “testdb” as user “postgres”.
testdb=#
testdb=# create table t1 (val text);
CREATE TABLE
testdb=# \dt
List of relations
Schema | Name | Type | Owner
——–+——+——-+———-
public | t1 | table | postgres
(1 row)
testdb=# insert into t1 values (1);
INSERT 0 1
testdb=# insert into t1 values (2);
INSERT 0 1
testdb=# insert into t1 values (3);
INSERT 0 1
testdb=# select * from t1;
val
1
2
3
(3 rows)
testdb=# \q
I will now take a special FULL backup:
/usr/pgsql-17/bin/pg_basebackup -D /var/lib/pgsql/backup
I will then add two extra rows in the table:
postgres=# \c testdb
You are now connected to database “testdb” as user “postgres”.
testdb=#
testdb=#
testdb=# select * from t1;
val
1
2
3
(3 rows)
testdb=# insert into t1 values (4);
INSERT 0 1
testdb=# insert into t1 values (5);
INSERT 0 1
testdb=#
testdb=# select * from t1;
val
1
2
3
4
5
(5 rows)
I will then create special directory for the incremental backup:
mkdir -p /var/lib/pgsql/inc_backup
Then, I will create an incremental backup
/usr/pgsql-17/bin/pg_basebackup –incremental=/var/lib/pgsql/backup/backup_manifest -D /var/lib/pgsql/inc_backup
You will see summarize WAL files generated under this directory:
/var/lib/pgsql/17/data/pg_wal/summaries
I will then create a combine backup directory that will merge the full+incremental backup to have one piece of backup used for restore:
mkdir -p /var/lib/pgsql/comb_backup
/usr/pgsql-17/bin/pg_combinebackup /var/lib/pgsql/backup /var/lib/pgsql/inc_backup -o /var/lib/pgsql/comb_backup
change the permission of the directory to 700:
chmod 700 /var/lib/pgsql/comb_backup
I will edit vi postgresql.conf under combined backup directory /var/lib/pgsql/comb_backup and change the port to 5400, then I will start the cluster
/usr/pgsql-17/bin/pg_ctl -D /var/lib/pgsql/comb_backup start
[postgres@localhost ~]$ psql -p 5400
psql (17.6)
Type “help” for help.
postgres=# SHOW data_directory;
data_directory
/var/lib/pgsql/comb_backup
(1 row)
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
testdb | postgres | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 | | |
(4 rows)
postgres=# \c testdb
You are now connected to database “testdb” as user “postgres”.
testdb=# select * from t1;
val
1
2
3
4
5
(5 rows)
in terms of configuration files such as pg_hba.conf , PostgreSQL.conf,….etc , the latest copy taken from the incremental backup will be reflected in the combined backup piece file.
References:
https://www.postgresql.org/docs/17/continuous-archiving.html#BACKUP-INCREMENTAL-BACKUP
PostgreSQL Master 