Backup & PITR

How to perform base backup & PITR with pgBackRest?

In the case of a hardware failure, a physical replica failover could be the best choice. whereas for data corruption scenarios (whether machine or human in origin), Point-in-Time Recovery (PITR) is often more appropriate.

Backup

Use the following command to perform backup:

1. # stanza name = {{ pg_cluster }} by default
2. pgbackrest --stanza=${stanza} --type=full|diff|incr backup

4. # you can also use the following command in pigsty (/pg/bin/pg-backup)
5. pg-backup       # make a backup, incr, or full backup if necessary
6. pg-backup full  # make a full backup
7. pg-backup diff  # make a differential backup
8. pg-backup incr  # make a incremental backup

Use the following command to print backup info:

1. pb info  # print backup info

Backup Info Example

1. $ pb info
2. stanza: pg-meta
3. status: ok
4. cipher: none

6. db (current)
7. wal archive min/max (14): 000000010000000000000001/000000010000000000000023

9. full backup: 20221108-105325F
10. timestamp start/stop: 2022-11-08 10:53:25 / 2022-11-08 10:53:29
11. wal start/stop: 000000010000000000000004 / 000000010000000000000004
12. database size: 96.6MB, database backup size: 96.6MB
13. repo1: backup set size: 18.9MB, backup size: 18.9MB

15. incr backup: 20221108-105325F_20221108-105938I
16. timestamp start/stop: 2022-11-08 10:59:38 / 2022-11-08 10:59:41
17. wal start/stop: 00000001000000000000000F / 00000001000000000000000F
18. database size: 246.7MB, database backup size: 167.3MB
19. repo1: backup set size: 35.4MB, backup size: 20.4MB
20. backup reference list: 20221108-105325F

Restore

Use the following command to perform restore

1. pg-pitr                                 # restore to wal archive stream end (e.g. used in case of entire DC failure)
2. pg-pitr -i                              # restore to the time of latest backup complete (not often used)
3. pg-pitr --time="2022-12-30 14:44:44+08" # restore to specific time point (in case of drop db, drop table)
4. pg-pitr --name="my-restore-point"       # restore TO a named restore point create by pg_create_restore_point
5. pg-pitr --lsn="0/7C82CB8" -X            # restore right BEFORE a LSN
6. pg-pitr --xid="1234567" -X -P           # restore right BEFORE a specific transaction id, then promote
7. pg-pitr --backup=latest                 # restore to latest backup set
8. pg-pitr --backup=20221108-105325        # restore to a specific backup set, which can be checked with pgbackrest info

10. pg-pitr                                 # pgbackrest --stanza=pg-meta restore
11. pg-pitr -i                              # pgbackrest --stanza=pg-meta --type=immediate restore
12. pg-pitr -t "2022-12-30 14:44:44+08"     # pgbackrest --stanza=pg-meta --type=time --target="2022-12-30 14:44:44+08" restore
13. pg-pitr -n "my-restore-point"           # pgbackrest --stanza=pg-meta --type=name --target=my-restore-point restore
14. pg-pitr -b 20221108-105325F             # pgbackrest --stanza=pg-meta --type=name --set=20221230-120101F restore
15. pg-pitr -l "0/7C82CB8" -X               # pgbackrest --stanza=pg-meta --type=lsn --target="0/7C82CB8" --target-exclusive restore
16. pg-pitr -x 1234567 -X -P                # pgbackrest --stanza=pg-meta --type=xid --target="0/7C82CB8" --target-exclusive --target-action=promote restore

1. # restore to the latest available point (e.g. hardware failure)
2. pgbackrest --stanza=pg-meta restore

4. # PITR to specific time point (e.g. drop table by accident)
5. pgbackrest --stanza=pg-meta --type=time --target="2022-11-08 10:58:48" \
6. --target-action=promote restore

8. # restore specific backup point and then promote (or pause|shutdown)
9. pgbackrest --stanza=pg-meta --type=immediate --target-action=promote \
10. --set=20221108-105325F_20221108-105938I restore

Note: you have to stop postgres & patroni before restore, and start them after restore.

1. pg-dw      # sudo systemctl stop patroni
2. pg-stop    # pg_ctl -D /pg/data stop
3. pg-s       # print postgres status
4. <restore>  # perform pgbackrest restore
5. pg-start   # pg_ctl -D /pg/data start
6. pg-up      # sudo systemctl start patroni

There is an util script /pg/bin/pg-pitr which will wrap pgbackrest restore and generate PITR manaul:

Policy

You can customize your backup policy with node_crontab and pgbackrest_repo

local repo

For example, the default pg-meta will take a full backup every day 1 am

1. node_crontab:  # make a full backup 1 am everyday
2. - '00 01 * * * postgres /pg/bin/pg-backup full'

With the default local repo retention policy, it will keep at most 2 full backups, and temporary allow 3 during backup.

1. pgbackrest_repo:                  # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository
2. local:                          # default pgbackrest repo with local posix fs
3. path: /pg/backup              # local backup directory, `/pg/backup` by default
4. retention_full_type: count    # retention full backups by count
5. retention_full: 2             # keep 2, at most 3 full backup when using local fs repo

Beware, this means your backup disk storage should be at least 3 times than the database file size.

minio repo

When using minio, storage capacity is usually not a problem, you can keep backups as long as you want.

For example, the default pg-test will take a full backup on monday, and incr backup on other weekdays

1. node_crontab:  # make a full backup 1 am everyday
2. - '00 01 * * 1 postgres /pg/bin/pg-backup full'
3. - '00 01 * * 2,3,4,5,6,7 postgres /pg/bin/pg-backup'

And with a 14 day time retention policy, backup in last two weeks will be kept. but beware this guarteen a week’s PITR period.

1. pgbackrest_repo:                  # pgbackrest repo: https://pgbackrest.org/configuration.html#section-repository=
2. minio:                          # optional minio repo for pgbackrest
3. type: s3                      # minio is s3-compatible, so s3 is used
4. s3_endpoint: sss.pigsty       # minio endpoint domain name, `sss.pigsty` by default
5. s3_region: us-east-1          # minio region, us-east-1 by default, useless for minio
6. s3_bucket: pgsql              # minio bucket name, `pgsql` by default
7. s3_key: pgbackrest            # minio user access key for pgbackrest
8. s3_key_secret: S3User.Backup  # minio user secret key for pgbackrest
9. s3_uri_style: path            # use path style uri for minio rather than host style
10. path: /pgbackrest             # minio backup path, default is `/pgbackrest`
11. storage_port: 9000            # minio port, 9000 by default
12. storage_ca_file: /etc/pki/ca.crt  # minio ca file path, `/etc/pki/ca.crt` by default
13. bundle: y                     # bundle small files into a single file
14. cipher_type: aes-256-cbc      # enable AES encryption for remote backup repo
15. cipher_pass: pgBackRest       # AES encryption password, default is 'pgBackRest'
16. retention_full_type: time     # retention full backup by time on minio repo
17. retention_full: 14            # keep full backup for last 14 days

Last modified 2023-02-27: refresh en docs to v2.0 (e82b371)