Backup & PITR

    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.

    Use the following command to perform :

    Use the following command to 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
    14.         incr backup: 20221108-105325F_20221108-105938I
    16.             wal start/stop: 00000001000000000000000F / 00000001000000000000000F
    17.             database size: 246.7MB, database backup size: 167.3MB
    18.             repo1: backup set size: 35.4MB, backup size: 20.4MB
    19.             backup reference list: 20221108-105325F

    Use the following command to perform 

    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 \
    1. pg-dw      # sudo systemctl stop patroni
    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:

    You can customize your backup policy with and pgbackrest_repo

    • schedule full or incr backup with
    • setup backup retension policy with pgbackrest_repo

    local repo

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

    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

    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'

    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.