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)