PIGSTY

Restore

Restore PostgreSQL from Backup

You can use the pre-configured pgbackrest to perform Point-in-Time Recovery (PITR) in Pigsty.

  • Manually: PITR with the pg-pitr hint script, do it manually, more flexible with more complexity.
  • Playbook: PITR with the pgsql-pitr.yml playbook, automatic, but less flexible and more error-prone.

If you are very convenient with your configuration, you can use the fully automatic playbook, otherwise, consider do it step by step manually

Quick Start

If you want to roll back the pg-meta cluster to the previous timepoint, adding the pg_pitr:

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta2
    pg_pitr: { time: '2025-07-13 10:00:00+00' }  # restore from the latest backup

Then run the pgsql-pitr.yml playbook, it will roll back the pg-meta cluster to the specified timepoint.

./pgsql-pitr.yml -l pg-meta

Restore PITR

The archive_mode will be disabled on recovered cluster to prevent unwanted WAL writes. If the recovered database status is ok, you can enable the archive_mode and make a full backup.

postgres @ pg-meta $
psql -c 'ALTER SYSTEM RESET archive_mode; SELECT pg_reload_conf();'
pg-backup full    # take a new full backup

Recovery Target

You can specify different types of recovery targets in pg_pitr, but they are mutually exclusive:

  • time: which time point to restore?
  • name: restore to a named restore point (created by pg_create_restore_point)
  • xid: restore to a specific transaction ID (TXID/XID)
  • lsn: restore to a specific LSN (Log Sequence Number) point

The recovery type will be set accordingly if any of the above parameters is specified, otherwise it will be set to latest (the end of the WAL archive stream). The special immediate type can be used to instruct pgbackrest to minimize the recovery time by stop at the first consistent point.

Target Type

pg_pitr: { }  # restore to the latest status (wal archive stream end)
pg_pitr: { time: "2025-07-13 10:00:00+00" }
pg_pitr: { lsn: "0/4001C80" }
pg_pitr: { xid: "250000" }
pg_pitr: { name: "some_restore_point" }
pg_pitr: { type: "immediate" }

By Time

The most frequently used target is the time point; you can specify the time point to restore to:

restore to a timepoint
./pgsql-pitr.yml -e '{"pg_pitr": { "time": "2025-07-13 10:00:00+00" }}'

Time should be a valid PostgreSQL TIMESTAMP, YYYY-MM-DD HH:MM:SS+TZ is recommended.

By Name

You can create a named restore point with pg_create_restore_point:

SELECT pg_create_restore_point('shit_incoming');

And use that named restore point in PITR:

./pgsql-pitr.yml -e '{"pg_pitr": { "name": "shit_incoming" }}'

By XID

If you have a transaction that accidentally deleted some data, the best way to recover is to restore the database to the state before that transaction.

restore right before a transaction
./pgsql-pitr.yml -e '{"pg_pitr": { "xid": "250000", exclusive: true }}'

You can find the exact transaction id from monitoring dashboard, or find it from TXID from the CSVLOG.

Inclusive vs Exclusive

The target parameter is "inclusive" by default, which means the recovery will include the target point. The exclusive flag will exclude that exact target, like the xid 24999 will be the last transaction being replayed

This only applies to time, xid, lsn recovery targets, check recovery_target_inclusive for details.

By LSN

PostgreSQL uses the LSN (Log Sequence Number) to identify the position of a WAL record. You can find it everywhere, like the PG LSN panel from Pigsty dashboards.

restore to a LSN
./pgsql-pitr.yml -e '{"pg_pitr": { "lsn": "0/4001C80", timeline: "1" }}'

To restore to an exact point in the WAL stream, you may also specify the timeline parameter (default to latest)

Recovery Source

  • cluster: which cluster to restore? the current pg_cluster will be used by default, you can use any other cluster in the same pgbackrest repo
  • repo: overwrite the backup repo, use the same format in pgbackrest_repo
  • set: the latest backup set is used by default, but you can specify a specific pgbackrest backup by label

Pigsty will recover from the pgbackrest backup repository, if you are using a centralized backup repo (like MinIO/S3), you can specify another "stanza" (another cluster's backup directory) to restore from.

pg-meta2:
  hosts: { 10.10.10.11: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta2
    pg_pitr: { cluster: pg-meta }  # restore from the pg-meta cluster backup

The above configuration will mark the PITR procedure to use the pg-meta stanza. You can also pass the pg_pitr parameter via CLI args:

pitr pg-meta2 with pg-meta backup
./pgsql-pitr.yml -l pg-meta2 -e '{"pg_pitr": { "cluster": "pg-meta" }}'

You can also use these targets when pitr from another cluster:

./pgsql-pitr.yml -l pg-meta2 -e '{"pg_pitr": { "cluster": "pg-meta", "time": "2025-07-14 08:00:00+00" }}'

Break Down

This approach is semi-automatic, you will participate in the PITR process to make key decisions.

For example, this configuration will restore the pg-meta cluster itself to the specified timepoint

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta2
    pg_pitr: { time: '2025-07-13 10:00:00+00' }  # restore from the latest backup

Let's do this one step by step:

./pgsql-pitr.yml -l pg-meta -t down     # pause patroni HA
./pgsql-pitr.yml -l pg-meta -t pitr     # run the pitr procedure
./pgsql-pitr.yml -l pg-meta -t up       # generate pgbackrest config and restore script
# down                 : # stop ha and shutdown patroni and postgres
#   - pause            : # pause patroni auto failover
#   - stop             : # stop patroni and postgres service
#     - stop_patroni   : # stop patroni service
#     - stop_postgres  : # stop postgres service
# pitr                 : # perform the PITR procedure
#   - config           : # generate pgbackrest config and restore script
#   - restore          : # run the pgbackrest restore command
#   - recovery         : # start postgres and complete recovery
#   - verify           : # verify the recovered cluster control data
# up:                  : # start postgres / patroni and resume ha
#   - etcd             : # clean up etcd metadata before launching
#   - start            : # start patroni and postgres service
#     - start_postgres : # start postgres service
#     - start_patroni  : # start patroni service
#   - resume           : # resume patroni auto failover

PITR Definition

There are more options available in the pg_pitr parameter:

pg_pitr:                        # define a PITR task
    cluster: "some_pg_cls_name"   # Source cluster name
    type: latest                  # Recovery target type: time, xid, name, lsn, immediate, latest
    time: "2025-01-01 10:00:00+00" # Recovery target: time, exclusive with xid, name, lsn
    name: "some_restore_point"    # Recovery target: named restore point, exclusive with time, xid, lsn
    xid:  "100000"                # Recovery target: transaction ID, exclusive with time, name, lsn
    lsn:  "0/3000000"             # Recovery target: log sequence number, exclusive with time, name, xid
    timeline: latest              # Target timeline, can be an integer, latest by default,
    exclusive: false              # Exclude the target point, default false?
    action: pause                 # Post-recovery action: pause, promote, shutdown
    archive: false                # Preserve archive settings? false by default
    db_exclude: [ template0, template1 ]
    db_include: []
    link_map:
      pg_wal: '/data/wal'
      pg_xact: '/data/pg_xact'
    process: 4                    # Parallel restore processes
    repo: {}                      # Repository to restore from
    data: /pg/data                # where to restore the data
    port: 5432                    # listen port of the recovered instance

On this page

Quick StartRestore PITRRecovery TargetTarget TypeBy TimeBy NameBy XIDBy LSNRecovery SourceBreak DownPITR Definition