Incremental Backup Using pg_basebackup in PostgreSQL 18

By -

 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.

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

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.

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.

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.

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

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.

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.

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

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.


Comments

Post a Comment

Popular posts from this blog

Oracle 21c Enhancements for TTS Export/Import

By -
  In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail. Improvement 1: Parallel Execution of TTS Export/Import In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following: Connected to: Oracle Database 19c Enterprise Edition Release 19.0 .0 .0 .0 – Production ORA-39002: invalid operation ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams. Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import: [oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check= y parallel= 2 Export: Release 21.0 . 0 . 0 . 0 – Production on Tue Oct 12 06 : 48 : 54 2021 Version 21.3 . 0 . 0 . 0 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPOR...
Read more

Oracle 23ai — error_message_details Parameter for Displaying Error Details

By -
  error_message_details   is another new parameter introduced in Oracle version 23ai, which allows displaying detailed information about data value-related errors. By setting this parameter to   ON , we can see the exact value that caused errors such as   ORA-00001: unique constraint violated   within the error message itself. For example, in the following scenario, enabling the  error_message_details  parameter helps identify the value that triggered the error: SQL > create table tbl1(id number primary key); Table created. SQL > variable B number; SQL > exec :B: = 2547 ; PL / SQL procedure successfully completed. SQL > insert into tbl1 values (:B); 1 row created. SQL > insert into tbl1 values (:B); ERROR at line 1 : 'ORA-00001: unique constraint (USEF.SYS_C008328) violated on table USEF.TBL1 columns (ID)' 'ORA-03301: (ORA-00001 details) row with column values (ID:2547) already exists' Help: https: / / docs.oracle.com ...
Read more

Buffer Busy Wait and Read by Other Session in Oracle

By -
Image
Assume a block is accessed and pinned in the buffer cache by Session 1 (each buffer can be pinned by only one session at a time). While this block is pinned, if another session attempts to access the same block, it must wait. This type of wait is called  Buffer Busy Wait . What distinguishes Buffer Busy Wait from some other wait events is that the contention occurs at the  block level . Even if the two sessions intend to access different rows within the same block, they are still competing at the block level. As you know, Oracle blocks can contain different types of data. For example, one block may hold user data, while another may store control information. Therefore, contention for blocks can be divided into several categories: Data Block Data Segment Header Undo Header Undo Block Let’s briefly review the role of each block type in this wait event. 1) Data Block This refers to the blocks that store object data. Buffer Busy Waits at this level can have several causes: Poorly ...
Read more