Theory
- A stanza is the configuration for a PostgreSQL database cluster that defines where it is located, how it will be backed up, archiving options, etc. Most db servers will only have one PostgreSQL database cluster and therefore one stanza, whereas backup servers will have a stanza for every database cluster that needs to be backed up.
- The stanza-delete command removes data in the repository associated with a stanza. Use this command with caution — it will permanently remove all backups and archives from the pgBackRest repository for the specified stanza.
A. CONFIGURATION
1. Create backup location
[root@edb-nhatrang /]# mkdir -p /backup/pgbackrest
[root@edb-nhatrang /]# chown -R enterprisedb: /backup/
2. Database Cluster Configuration
- Enable archive mode ON
- Set archive_command = ‘pgbackrest --stanza=employees archive-push %p’
- Get data_directory = ‘/u01/edb/as16/data’
edb=# show archive_mode ;
archive_mode
--------------
on
(1 row)
edb=# show archive_command ;
archive_command
-----------------------------------------------
pgbackrest --stanza=employees archive-push %p
(1 row)
edb=# show data_directory ;
data_directory
--------------------
/u01/edb/as16/data
(1 row)
edb=# show wal_level ;
wal_level
-----------
replica
(1 row)
edb=# show max_wal_senders ;
max_wal_senders
-----------------
10
(1 row)
edb=# show log_filename ;
log_filename
----------------------------------
enterprisedb-%Y-%m-%d_%H%M%S.log
(1 row)
3. Pgbackrest Repository Configuration
[root@edb-nhatrang ~]# vi /etc/pgbackrest.conf
[global]
repo1-path=/backup/pgbackrest
repo1-retention-full=2
archive-async=y
log-level-console=info
log-level-file=debug
start-fast=y
[employees]
pg1-path=/u01/edb/as16/data
pg1-database=edb
pg1-port=5444
pg-version-force=16
4. Create the Stanza
[enterprisedb@edb-nhatrang ~]$ pgbackrest --stanza=employees stanza-create
2024-06-24 11:21:43.740 P00 INFO: stanza-create command begin 2.52: --exec-id=9141-2cc3bdde --log-level-console=info --log-level-file=debug --pg1-database=edb --pg1-path=/u01/edb/as16/data --pg1-port=5444 --pg-version-force=16 --repo1-path=/backup/pgbackrest --stanza=employees
2024-06-24 11:21:44.577 P00 INFO: stanza-create for stanza 'employees' on repo1
2024-06-24 11:21:44.605 P00 INFO: stanza-create command end: completed successfully (872ms)
[enterprisedb@edb-nhatrang ~]$ pgbackrest --stanza=employees check
2024-06-24 11:23:48.398 P00 INFO: check command begin 2.52: --exec-id=9200-b7adf37f --log-level-console=info --log-level-file=debug --pg1-database=edb --pg1-path=/u01/edb/as16/data --pg1-port=5444 --pg-version-force=16 --repo1-path=/backup/pgbackrest --stanza=employees
2024-06-24 11:23:49.047 P00 INFO: check repo1 configuration (primary)
2024-06-24 11:23:49.259 P00 INFO: check repo1 archive for WAL (primary)
2024-06-24 11:23:49.883 P00 INFO: WAL segment 000000010000000000000012 successfully archived to '/backup/pgbackrest/archive/employees/16-1/0000000100000000/000000010000000000000012-7bc3126200cf29f093e00b0b134b474ad6fcbbef.gz' on repo1
2024-06-24 11:23:49.883 P00 INFO: check command end: completed successfully (1489ms)
[enterprisedb@edb-nhatrang ~]$ ls -l /backup/pgbackrest/
total 0
drwxr-x---. 3 enterprisedb enterprisedb 23 Jun 24 11:21 archive
drwxr-x---. 3 enterprisedb enterprisedb 23 Jun 24 11:21 backup
B. BACKUP
Notes:
- By default, pgBackRest will attempt to perform an incremental backup. However, an incremental backup must be based on a full backup and since no full backup existed pgBackRest ran a full backup instead. We can control type backup with option --type=full/incr/diff
- Backing up large database with huge number of datafiles, we should use File Bundling (repo-bundle) feature and Block Incremental (repo-block) feature.
- WAL log, Archive log will deleted automatically based on backup retention.
[enterprisedb@edb-nhatrang ~]$ pgbackrest --stanza=employees --log-level-console=info --process-max=2 backup
2024-06-24 12:00:50.738 P00 INFO: backup command begin 2.52: --exec-id=11687-3338c01a --log-level-console=info --log-level-file=debug --pg1-database=edb --pg1-path=/u01/edb/as16/data --pg1-port=5444 --pg-version-force=16 --process-max=2 --repo1-path=/backup/pgbackrest --repo1-retention-full=2 --stanza=employees --start-fast
WARN: no prior backup exists, incr backup has been changed to full
2024-06-24 12:00:51.495 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2024-06-24 12:00:52.019 P00 INFO: backup start archive = 000000010000000000000014, lsn = 0/14000028
2024-06-24 12:00:52.019 P00 INFO: check archive for prior segment 000000010000000000000013
2024-06-24 12:01:05.221 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2024-06-24 12:01:05.427 P00 INFO: backup stop archive = 000000010000000000000014, lsn = 0/14000138
2024-06-24 12:01:05.438 P00 INFO: check archive for segment(s) 000000010000000000000014:000000010000000000000014
2024-06-24 12:01:05.557 P00 INFO: new backup label = 20240624-120051F
2024-06-24 12:01:05.661 P00 INFO: full backup size = 397.7MB, file total = 2185
2024-06-24 12:01:05.661 P00 INFO: backup command end: completed successfully (14930ms)
2024-06-24 12:01:05.661 P00 INFO: expire command begin 2.52: --exec-id=11687-3338c01a --log-level-console=info --log-level-file=debug --repo1-path=/backup/pgbackrest --repo1-retention-full=2 --stanza=employees
2024-06-24 12:01:05.673 P00 INFO: expire command end: completed successfully (12ms)
==> This is a Full backup "20240624-120051F"
Change data
employees=> create table temp_01 as select * from department;
[enterprisedb@edb-nhatrang ~]$ pgbackrest --stanza=employees --log-level-console=info --process-max=2 backup
2024-06-24 12:02:40.496 P00 INFO: backup command begin 2.52: --exec-id=11769-3921e26a --log-level-console=info --log-level-file=debug --pg1-database=edb --pg1-path=/u01/edb/as16/data --pg1-port=5444 --pg-version-force=16 --process-max=2 --repo1-path=/backup/pgbackrest --repo1-retention-full=2 --stanza=employees --start-fast
2024-06-24 12:02:41.274 P00 INFO: last backup label = 20240624-120051F, version = 2.52
2024-06-24 12:02:41.274 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2024-06-24 12:02:42.004 P00 INFO: backup start archive = 000000010000000000000016, lsn = 0/16000028
2024-06-24 12:02:42.004 P00 INFO: check archive for prior segment 000000010000000000000015
2024-06-24 12:02:43.791 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2024-06-24 12:02:43.999 P00 INFO: backup stop archive = 000000010000000000000016, lsn = 0/16000100
2024-06-24 12:02:44.011 P00 INFO: check archive for segment(s) 000000010000000000000016:000000010000000000000016
2024-06-24 12:02:44.028 P00 INFO: new backup label = 20240624-120051F_20240624-120241I
2024-06-24 12:02:44.109 P00 INFO: incr backup size = 3.8MB, file total = 2186
2024-06-24 12:02:44.110 P00 INFO: backup command end: completed successfully (3621ms)
2024-06-24 12:02:44.110 P00 INFO: expire command begin 2.52: --exec-id=11769-3921e26a --log-level-console=info --log-level-file=debug --repo1-path=/backup/pgbackrest --repo1-retention-full=2 --stanza=employees
2024-06-24 12:02:44.130 P00 INFO: expire command end: completed successfully (20ms)
==> This is an incr backup “20240624-120051F_20240624-120241I”, based on Full backup “20240624-120051F”
Change data
employees=> create table temp_02 as select * from department;
[enterprisedb@edb-nhatrang ~]$ pgbackrest --stanza=employees --log-level-console=info --process-max=2 backup
2024-06-24 12:03:52.729 P00 INFO: backup command begin 2.52: --exec-id=11807-29860e34 --log-level-console=info --log-level-file=debug --pg1-database=edb --pg1-path=/u01/edb/as16/data --pg1-port=5444 --pg-version-force=16 --process-max=2 --repo1-path=/backup/pgbackrest --repo1-retention-full=2 --stanza=employees --start-fast
2024-06-24 12:03:53.543 P00 INFO: last backup label = 20240624-120051F_20240624-120241I, version = 2.52
2024-06-24 12:03:53.543 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2024-06-24 12:03:54.100 P00 INFO: backup start archive = 000000010000000000000018, lsn = 0/18000028
2024-06-24 12:03:54.100 P00 INFO: check archive for prior segment 000000010000000000000017
2024-06-24 12:03:55.919 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2024-06-24 12:03:56.125 P00 INFO: backup stop archive = 000000010000000000000018, lsn = 0/18000100
2024-06-24 12:03:56.137 P00 INFO: check archive for segment(s) 000000010000000000000018:000000010000000000000018
2024-06-24 12:03:56.153 P00 INFO: new backup label = 20240624-120051F_20240624-120353I
2024-06-24 12:03:56.233 P00 INFO: incr backup size = 3.8MB, file total = 2187
2024-06-24 12:03:56.233 P00 INFO: backup command end: completed successfully (3509ms)
2024-06-24 12:03:56.233 P00 INFO: expire command begin 2.52: --exec-id=11807-29860e34 --log-level-console=info --log-level-file=debug --repo1-path=/backup/pgbackrest --repo1-retention-full=2 --stanza=employees
2024-06-24 12:03:56.242 P00 INFO: expire command end: completed successfully (9ms)
==> This is incr backup “20240624-120051F_20240624-120353I”, based on Full Backup “20240624-120051F” and previous incr backup “20240624-120051F_20240624-120241I”
[enterprisedb@edb-nhatrang ~]$ pgbackrest --stanza=employees --log-level-console=info --process-max=2 --type=diff backup
2024-06-24 12:05:01.858 P00 INFO: backup command begin 2.52: --exec-id=11840-1c9fa05e --log-level-console=info --log-level-file=debug --pg1-database=edb --pg1-path=/u01/edb/as16/data --pg1-port=5444 --pg-version-force=16 --process-max=2 --repo1-path=/backup/pgbackrest --repo1-retention-full=2 --stanza=employees --start-fast --type=diff
2024-06-24 12:05:02.631 P00 INFO: last backup label = 20240624-120051F, version = 2.52
2024-06-24 12:05:02.631 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2024-06-24 12:05:02.941 P00 INFO: backup start archive = 00000001000000000000001A, lsn = 0/1A000028
2024-06-24 12:05:02.941 P00 INFO: check archive for prior segment 000000010000000000000019
2024-06-24 12:05:04.871 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2024-06-24 12:05:05.077 P00 INFO: backup stop archive = 00000001000000000000001A, lsn = 0/1A000100
2024-06-24 12:05:05.098 P00 INFO: check archive for segment(s) 00000001000000000000001A:00000001000000000000001A
2024-06-24 12:05:05.117 P00 INFO: new backup label = 20240624-120051F_20240624-120502D
2024-06-24 12:05:05.197 P00 INFO: diff backup size = 3.9MB, file total = 2187
2024-06-24 12:05:05.197 P00 INFO: backup command end: completed successfully (3343ms)
2024-06-24 12:05:05.197 P00 INFO: expire command begin 2.52: --exec-id=11840-1c9fa05e --log-level-console=info --log-level-file=debug --repo1-path=/backup/pgbackrest --repo1-retention-full=2 --stanza=employees
2024-06-24 12:05:05.207 P00 INFO: expire command end: completed successfully (10ms)
==> This is a diff backup “20240624-120051F_20240624-120502D”, based on Full backup “20240624-120051F”
Verify backup infomartion
[enterprisedb@edb-nhatrang employees]$ ls -ltr
total 8
drwxr-x---. 4 enterprisedb enterprisedb 89 Jun 24 12:01 20240624-120051F
drwxr-x---. 3 enterprisedb enterprisedb 18 Jun 24 12:01 backup.history
drwxr-x---. 4 enterprisedb enterprisedb 89 Jun 24 12:02 20240624-120051F_20240624-120241I
drwxr-x---. 4 enterprisedb enterprisedb 89 Jun 24 12:03 20240624-120051F_20240624-120353I
drwxr-x---. 4 enterprisedb enterprisedb 89 Jun 24 12:05 20240624-120051F_20240624-120502D
lrwxrwxrwx. 1 enterprisedb enterprisedb 33 Jun 24 12:05 latest -> 20240624-120051F_20240624-120502D
-rw-r-----. 1 enterprisedb enterprisedb 3378 Jun 24 12:05 backup.info
-rw-r-----. 1 enterprisedb enterprisedb 3378 Jun 24 12:05 backup.info.copy
[enterprisedb@edb-nhatrang employees]$ du -hs *
94M 20240624-120051F
1.4M 20240624-120051F_20240624-120241I
1.4M 20240624-120051F_20240624-120353I
1.5M 20240624-120051F_20240624-120502D
436K backup.history
4.0K backup.info
4.0K backup.info.copy
0 latest
[enterprisedb@edb-nhatrang employees]$ pgbackrest info
stanza: employees
status: ok
cipher: none
db (current)
wal archive min/max (16): 000000010000000000000012/00000001000000000000001A
full backup: 20240624-120051F
timestamp start/stop: 2024-06-24 12:00:51+07 / 2024-06-24 12:01:05+07
wal start/stop: 000000010000000000000014 / 000000010000000000000014
database size: 397.7MB, database backup size: 397.7MB
repo1: backup set size: 85.5MB, backup size: 85.5MB
incr backup: 20240624-120051F_20240624-120241I
timestamp start/stop: 2024-06-24 12:02:41+07 / 2024-06-24 12:02:43+07
wal start/stop: 000000010000000000000016 / 000000010000000000000016
database size: 397.7MB, database backup size: 3.8MB
repo1: backup set size: 85.5MB, backup size: 476.9KB
backup reference list: 20240624-120051F
incr backup: 20240624-120051F_20240624-120353I
timestamp start/stop: 2024-06-24 12:03:53+07 / 2024-06-24 12:03:56+07
wal start/stop: 000000010000000000000018 / 000000010000000000000018
database size: 397.7MB, database backup size: 3.8MB
repo1: backup set size: 85.5MB, backup size: 476.7KB
backup reference list: 20240624-120051F, 20240624-120051F_20240624-120241I
diff backup: 20240624-120051F_20240624-120502D
timestamp start/stop: 2024-06-24 12:05:02+07 / 2024-06-24 12:05:04+07
wal start/stop: 00000001000000000000001A / 00000001000000000000001A
database size: 397.7MB, database backup size: 3.9MB
repo1: backup set size: 85.5MB, backup size: 477.7KB
backup reference list: 20240624-120051F
C. COPY BACKUP SET TO TAPE & RESTORE EXPIRED BACKUP SET
Because of organization's policy, we must keep backup sets a long time, maybe 5 years. While pgbackrest configures "repo-retention-full" parameter a limit number (2 or 3 ...). So we must copy the oldest backup set to tape or staging area before it is expired.
Configuration:
- repo1-path=/backup/pgbackrest
- repo1-retention-full=2
[enterprisedb@edb-nhatrang ~]$ tree -L 3 /backup/pgbackrest/
/backup/pgbackrest/
├── archive
│ └── employees
│ ├── 16-1
│ ├── archive.info
│ └── archive.info.copy
└── backup
└── employees
├── 20240627-224531F ==> the oldest backup set
├── 20240628-085104F
├── backup.history
├── backup.info
├── backup.info.copy
└── latest -> 20240628-085104F
Because of retaining only 2 full backup sets, so we need copy the oldest backup set (20240627-224531F) plus archive, metadata (backup.history, backup.info, backup.info.copy) to tape before the next full backup.
When restoring the expired backup set to new server, we must configure right path of backup location in /etc/pgbackrest.conf.
- Retrieve the backup files (typically the entire backup directory) from tape to a local directory.
- Ensure the backup directory structure ('backup', 'archive', etc.) is intact.
- Use pgBackRest’s restore command to restore the backup to the new server.
- pgbackrest restore --stanza=<stanza-name> --set=<backup-name> --target-action=promote
Ref: