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!