Physical Backup using pgBackRest in PostgreSQL

Jamsher Khan 23 mins2
()

Physical Backup using pgBackRest in PostgreSQL

In the previous blog, we have demonstrated Configure pgbackrest on Backup Host – Remote in Backup Server Click here to read more. In this blog, we will use the pgbackrest tool to take different types of database backup. pgBackRest is an open-source backup tool for PostgreSQL which offers easy configuration and reliable backups. pgBackRest is a reliable, easy-to-use backup and restore solution that can seamlessly scale up to the largest databases and workloads by utilizing algorithms optimized for database-specific requirements. Follow the below options to set up it. 


Help Command: Use option help to get all available options for pgbackrest.

-bash-4.2$ pgbackrest help
pgBackRest 2.34 - General help

Usage:
    pgbackrest [options] [command]

Commands:
    archive-get     Get a WAL segment from the archive.
    archive-push    Push a WAL segment to the archive.
    backup          Backup a database cluster.
    check           Check the configuration.
    expire          Expire backups that exceed retention.
    help            Get help.
    info            Retrieve information about backups.


Help for Specific Command: Use help <command> to get full available options for that specific command.

-bash-4.2$ pgbackrest help backup
2021-06-30 14:28:26.848 P00   INFO: backup command begin 2.34: --exec-id=5666-38b20719 --log-level-console=info --log-level-file=debug --process-max=2 --repo1-path=/u01/pg_backup --repo1-retention-full=2 --start-fast --stop-auto
pgBackRest 2.34 - 'backup' command help

Backup a database cluster.

When multiple repositories are configured, pgBackRest will backup to the
highest priority repository (e.g. repo1) unless the --repo option is specified.

pgBackRest does not have a built-in scheduler so it's best to run it from cron
or some other scheduling mechanism.

See Perform a Backup for more details and examples.

Command Options:

  --archive-check                check that WAL segments are in the archive
                                 before backup completes [default=y]


Full Database Backup: Use command pgbackrest backup to take full database backup of specific stanza. Default PgBackRest will try to do an incremental backup. But as there is no full backup yet, a full backup will be done i.e executing the first time. Once a full backup is done, all future backups will be incremental unless we specify the type of backup.

-bash-4.2$ pgbackrest --stanza=prod_backup backup
2021-06-30 14:25:19.986 P00   INFO: backup command begin 2.34: --exec-id=5446-9e8c46dd --log-level-console=info --log-level-file=debug --pg1-host=test-machine01 --pg1-host-user=postgres --pg1-path=/var/lib/pgsql/13/data --process-max=2 --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup --start-fast --stop-auto
WARN: no prior backup exists, incr backup has been changed to full
2021-06-30 14:25:21.305 P00   INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2021-06-30 14:25:22.022 P00   INFO: backup start archive = 00000001000000000000000D, lsn = 0/D000060
2021-06-30 14:25:23.952 P02   INFO: backup file test-machine01:/var/lib/pgsql/13/data/pg_tblspc/16713/PG_13_202007201/16716/17178 (1.2MB, 1%) checksum c9d663bccee764ef657c9678060b69df6925b313
2021-06-30 14:25:47.309 P00   INFO: full backup size = 71.5MB
2021-06-30 14:25:47.309 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-06-30 14:25:47.821 P00   INFO: backup stop archive = 00000001000000000000000D, lsn = 0/D000170
2021-06-30 14:25:47.828 P00   INFO: check archive for segment(s) 00000001000000000000000D:00000001000000000000000D
2021-06-30 14:25:48.164 P00   INFO: new backup label = 20210630-142521F
2021-06-30 14:25:48.204 P00   INFO: backup command end: completed successfully (28225ms)
2021-06-30 14:25:48.205 P00   INFO: expire command begin 2.34: --exec-id=5446-9e8c46dd --log-level-console=info --log-level-file=debug --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup
2021-06-30 14:25:48.207 P00   INFO: expire command end: completed successfully (3ms)
-bash-4.2$


Full database backup: If we want to perform another full backup we can specify the option –type=full

-bash-4.2$ pgbackrest --stanza=prod_backup backup --type=full
2021-06-30 14:26:52.569 P00   INFO: backup command begin 2.34: --exec-id=5549-41fc0853 --log-level-console=info --log-level-file=debug --pg1-host=test-machine01 --pg1-host-user=postgres --pg1-path=/var/lib/pgsql/13/data --process-max=2 --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup --start-fast --stop-auto --type=full
2021-06-30 14:26:53.504 P00   INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2021-06-30 14:26:54.022 P00   INFO: backup start archive = 00000001000000000000000F, lsn = 0/F000028
2021-06-30 14:27:00.418 P02   INFO: backup file test-machine01:/var/lib/pgsql/13/data/base/1/14027 (0B, 100%)
2021-06-30 14:27:00.422 P00   INFO: full backup size = 71.5MB
2021-06-30 14:27:00.422 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-06-30 14:27:00.937 P00   INFO: backup stop archive = 00000001000000000000000F, lsn = 0/F000138
2021-06-30 14:27:00.944 P00   INFO: check archive for segment(s) 00000001000000000000000F:00000001000000000000000F
2021-06-30 14:27:01.182 P00   INFO: new backup label = 20210630-142653F
2021-06-30 14:27:01.230 P00   INFO: backup command end: completed successfully (8663ms)
2021-06-30 14:27:01.230 P00   INFO: expire command begin 2.34: --exec-id=5549-41fc0853 --log-level-console=info --log-level-file=debug --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup
2021-06-30 14:27:01.233 P00   INFO: repo1: 13-1 remove archive, start = 00000001000000000000000A, stop = 00000001000000000000000C


Incremental database backup: After the full backup is taken, Every backup will be an incremental backup. If you didn’t mention explicitly –type=full.

-bash-4.2$ pgbackrest --stanza=prod_backup backup
2021-06-30 14:31:54.336 P00   INFO: backup command begin 2.34: --exec-id=5921-c47ad3bc --log-level-console=info --pg1-host=test-machine01 --pg1-host-user=postgres --pg1-path=/var/lib/pgsql/13/data --process-max=2 --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup --start-fast --stop-auto
2021-06-30 14:31:55.326 P00   INFO: last backup label = 20210630-142653F, version = 2.34
2021-06-30 14:31:55.326 P00   INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2021-06-30 14:31:55.740 P00   INFO: backup start archive = 000000010000000000000011, lsn = 0/11000028
2021-06-30 14:31:56.547 P01   INFO: backup file test-machine01:/var/lib/pgsql/13/data/global/pg_control (8KB, 63%) checksum 4c257e76c6af0af9fce17a9ad14d99d25c98f7a0
2021-06-30 14:31:56.751 P02   INFO: backup file test-machine01:/var/lib/pgsql/13/data/log/postgresql-Wed.log (4.7KB, 101%) checksum 2539cab8666db2d6ae1a256be52ed6e475a6a955
2021-06-30 14:31:56.956 P01   INFO: backup file test-machine01:/var/lib/pgsql/13/data/pg_logical/replorigin_checkpoint (8B, 101%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532
2021-06-30 14:31:56.957 P00   INFO: incr backup size = 12.5KB
2021-06-30 14:31:56.957 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-06-30 14:31:57.163 P00   INFO: backup stop archive = 000000010000000000000011, lsn = 0/11000100
2021-06-30 14:31:57.168 P00   INFO: check archive for segment(s) 000000010000000000000011:000000010000000000000011
2021-06-30 14:31:57.498 P00   INFO: new backup label = 20210630-142653F_20210630-143155I
2021-06-30 14:31:57.554 P00   INFO: backup command end: completed successfully (3219ms)
2021-06-30 14:31:57.554 P00   INFO: expire command begin 2.34: --exec-id=5921-c47ad3bc --log-level-console=info --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup
2021-06-30 14:31:57.556 P00   INFO: repo1: 13-1 no archive to remove
2021-06-30 14:31:57.556 P00   INFO: expire command end: completed successfully (2ms)


Incremental database backup: If we want to perform incremental backup we can specify the option –type=incr.

-bash-4.2$ pgbackrest --stanza=prod_backup --type=incr backup
2021-06-30 14:35:36.039 P00   INFO: backup command begin 2.34: --exec-id=6160-2c1b7668 --log-level-console=info --pg1-host=test-machine01 --pg1-host-user=postgres --pg1-path=/var/lib/pgsql/13/data --process-max=2 --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup --start-fast --stop-auto --type=incr
2021-06-30 14:35:36.986 P00   INFO: last backup label = 20210630-142653F_20210630-143524D, version = 2.34
2021-06-30 14:35:36.986 P00   INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2021-06-30 14:35:37.391 P00   INFO: backup start archive = 000000010000000000000015, lsn = 0/15000028
2021-06-30 14:35:38.695 P01   INFO: backup file test-machine01:/var/lib/pgsql/13/data/global/pg_control (8KB, 51%) checksum 1ccab8031b5cc831012b31121cf1b186cc9ae543
2021-06-30 14:35:38.695 P02   INFO: backup file test-machine01:/var/lib/pgsql/13/data/log/postgresql-Wed.log (7.6KB, 100%) checksum e7de7a4ee29d168e24b7f42d8f50e64cdf8f281f
2021-06-30 14:35:38.898 P01   INFO: backup file test-machine01:/var/lib/pgsql/13/data/pg_logical/replorigin_checkpoint (8B, 100%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532
2021-06-30 14:35:38.899 P00   INFO: incr backup size = 15.5KB
2021-06-30 14:35:38.899 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-06-30 14:35:39.105 P00   INFO: backup stop archive = 000000010000000000000015, lsn = 0/15000100
2021-06-30 14:35:39.109 P00   INFO: check archive for segment(s) 000000010000000000000015:000000010000000000000015
2021-06-30 14:35:39.442 P00   INFO: new backup label = 20210630-142653F_20210630-143536I
2021-06-30 14:35:39.501 P00   INFO: backup command end: completed successfully (3463ms)
2021-06-30 14:35:39.501 P00   INFO: expire command begin 2.34: --exec-id=6160-2c1b7668 --log-level-console=info --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup
2021-06-30 14:35:39.504 P00   INFO: repo1: 13-1 no archive to remove
2021-06-30 14:35:39.504 P00   INFO: expire command end: completed successfully (3ms)
-bash-4.2$


Differential database backup: If we want to perform incremental backup we can specify the option –type=diff.

-bash-4.2$
-bash-4.2$ pgbackrest --stanza=prod_backup --type=diff backup
2021-06-30 14:35:23.799 P00   INFO: backup command begin 2.34: --exec-id=6122-9963f54b --log-level-console=info --pg1-host=test-machine01 --pg1-host-user=postgres --pg1-path=/var/lib/pgsql/13/data --process-max=2 --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup --start-fast --stop-auto --type=diff
2021-06-30 14:35:24.798 P00   INFO: last backup label = 20210630-142653F, version = 2.34
2021-06-30 14:35:24.798 P00   INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2021-06-30 14:35:25.212 P00   INFO: backup start archive = 000000010000000000000013, lsn = 0/13000028
2021-06-30 14:35:26.466 P02   INFO: backup file test-machine01:/var/lib/pgsql/13/data/log/postgresql-Wed.log (6.2KB, 44%) checksum fce6fd6bbb93954aa49110605ef58ea140f0003d
2021-06-30 14:35:26.670 P01   INFO: backup file test-machine01:/var/lib/pgsql/13/data/global/pg_control (8KB, 101%) checksum c3f45ac1886e1a75e44275fc0df25e8b2581eb53
2021-06-30 14:35:26.873 P02   INFO: backup file test-machine01:/var/lib/pgsql/13/data/pg_logical/replorigin_checkpoint (8B, 101%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532
2021-06-30 14:35:26.874 P00   INFO: diff backup size = 14.0KB
2021-06-30 14:35:26.875 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-06-30 14:35:27.078 P00   INFO: backup stop archive = 000000010000000000000013, lsn = 0/13000100
2021-06-30 14:35:27.086 P00   INFO: check archive for segment(s) 000000010000000000000013:000000010000000000000013
2021-06-30 14:35:27.413 P00   INFO: new backup label = 20210630-142653F_20210630-143524D
2021-06-30 14:35:27.475 P00   INFO: backup command end: completed successfully (3676ms)
2021-06-30 14:35:27.475 P00   INFO: expire command begin 2.34: --exec-id=6122-9963f54b --log-level-console=info --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup
2021-06-30 14:35:27.478 P00   INFO: repo1: 13-1 no archive to remove
2021-06-30 14:35:27.478 P00   INFO: expire command end: completed successfully (3ms)
-bash-4.2$


List available backup: Use command barman list-backup to list all available backups of the specific stanza.

-bash-4.2$ pgbackrest info --stanza=prod_backup
stanza: prod_backup
    status: ok
    cipher: none

    db (current)
        wal archive min/max (13): 00000001000000000000000D/000000010000000000000015

        full backup: 20210630-142521F
            timestamp start/stop: 2021-06-30 14:25:21 / 2021-06-30 14:25:47
            wal start/stop: 00000001000000000000000D / 00000001000000000000000D
            database size: 71.5MB, database backup size: 71.5MB
            repo1: backup set size: 11.7MB, backup size: 11.7MB

        full backup: 20210630-142653F
            timestamp start/stop: 2021-06-30 14:26:53 / 2021-06-30 14:27:00
            wal start/stop: 00000001000000000000000F / 00000001000000000000000F
            database size: 71.5MB, database backup size: 71.5MB
            repo1: backup set size: 11.7MB, backup size: 11.7MB

        incr backup: 20210630-142653F_20210630-143155I
            timestamp start/stop: 2021-06-30 14:31:55 / 2021-06-30 14:31:57
            wal start/stop: 000000010000000000000011 / 000000010000000000000011
            database size: 71.5MB, database backup size: 13KB
            repo1: backup set size: 11.7MB, backup size: 1.5KB
            backup reference list: 20210630-142653F

        diff backup: 20210630-142653F_20210630-143524D
            timestamp start/stop: 2021-06-30 14:35:24 / 2021-06-30 14:35:26
            wal start/stop: 000000010000000000000013 / 000000010000000000000013
            database size: 71.5MB, database backup size: 14.5KB
            repo1: backup set size: 11.7MB, backup size: 1.6KB
            backup reference list: 20210630-142653F

        incr backup: 20210630-142653F_20210630-143536I
            timestamp start/stop: 2021-06-30 14:35:36 / 2021-06-30 14:35:39
            wal start/stop: 000000010000000000000015 / 000000010000000000000015
            database size: 71.5MB, database backup size: 15.9KB
            repo1: backup set size: 11.7MB, backup size: 1.8KB
            backup reference list: 20210630-142653F
-bash-4.2$
-bash-4.2$
-bash-4.2$ pwd
/u01/pg_backup/backup/prod_backup
-bash-4.2$
-bash-4.2$ ls -ltr
total 8
drwxr-x---. 3 postgres postgres   18 Jun 30 14:25 backup.history
drwxr-x---. 4 postgres postgres   89 Jun 30 14:25 20210630-142521F
drwxr-x---. 4 postgres postgres   89 Jun 30 14:27 20210630-142653F
drwxr-x---. 3 postgres postgres   72 Jun 30 14:31 20210630-142653F_20210630-143155I
drwxr-x---. 3 postgres postgres   72 Jun 30 14:35 20210630-142653F_20210630-143524D
drwxr-x---. 3 postgres postgres   72 Jun 30 14:35 20210630-142653F_20210630-143536I
lrwxrwxrwx. 1 postgres postgres   33 Jun 30 14:35 latest -> 20210630-142653F_20210630-143536I
-rw-r-----. 1 postgres postgres 3572 Jun 30 14:35 backup.info
-rw-r-----. 1 postgres postgres 3572 Jun 30 14:35 backup.info.copy
-bash-4.2$


Backup Auto Deletion: As we have defined in the configuration file repo1-retention-full=2, so pgbackrest will maintain only 2 copies of the full backup and If we take 3rd full backup the oldest full backup will be deleted.

-bash-4.2$ pgbackrest --stanza=prod_backup backup --type=full
2021-06-30 15:00:36.525 P00   INFO: backup command begin 2.34: --exec-id=7567-f8de261b --log-level-console=info --pg1-host=test-machine01 --pg1-host-user=postgres --pg1-path=/var/lib/pgsql/13/data --process-max=2 --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup --start-fast --stop-auto --type=full
2021-06-30 15:00:37.469 P00   INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2021-06-30 15:00:37.884 P00   INFO: backup start archive = 000000010000000000000017, lsn = 0/17000028
2021-06-30 15:00:39.524 P02   INFO: backup file test-machine01:/var/lib/pgsql/13/data/pg_tblspc/16713/PG_13_202007201/16716/17178 (1.2MB,
2021-06-30 15:00:44.306 P02   INFO: backup file test-machine01:/var/lib/pgsql/13/data/base/1/14032 (0B, 100%)
2021-06-30 15:00:44.508 P01   INFO: backup file test-machine01:/var/lib/pgsql/13/data/base/1/14027 (0B, 100%)
2021-06-30 15:00:44.511 P00   INFO: full backup size = 71.5MB
2021-06-30 15:00:44.511 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-06-30 15:00:44.714 P00   INFO: backup stop archive = 000000010000000000000017, lsn = 0/17000138
2021-06-30 15:00:44.721 P00   INFO: check archive for segment(s) 000000010000000000000017:000000010000000000000017
2021-06-30 15:00:45.277 P00   INFO: new backup label = 20210630-150037F
2021-06-30 15:00:45.346 P00   INFO: backup command end: completed successfully (8822ms)
2021-06-30 15:00:45.346 P00   INFO: expire command begin 2.34: --exec-id=7567-f8de261b --log-level-console=info --repo1-path=/u01/pg_backup --repo1-retention-full=2 --stanza=prod_backup
2021-06-30 15:00:45.347 P00   INFO: repo1: expire full backup 20210630-142521F
2021-06-30 15:00:45.349 P00   INFO: repo1: remove expired backup 20210630-142521F
2021-06-30 15:00:45.403 P00   INFO: repo1: 13-1 remove archive, start = 00000001000000000000000D, stop = 00000001000000000000000E
2021-06-30 15:00:45.403 P00   INFO: expire command end: completed successfully (57ms)

This document is just for learning purposes and always validates in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

Related Articles

, , ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

2 thoughts on “Physical Backup using pgBackRest in PostgreSQL”

Comments are closed.

Related Articles