DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

Incremental Backup Using pg_basebackup in PostgreSQL 18

#postgres #database #linux #ai

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Remove the contents of the existing data directory:

[postgres@OL8 ~]$ rm -rf  /var/lib/pgsql/18/data/*
Enter fullscreen mode Exit fullscreen mode

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/
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

The table and its data are available after restoring the full backup together with the incremental backup.

Top comments (0)