Restore Operations

Perform PITR with pgsql-pitr.yml, pig pitr, or pig pb restore; select targets, run stages, and verify the result.

Pigsty provides three restore interfaces. They use the same parameter semantics for different scopes:

Entry point	Use case	What it controls
`pgsql-pitr.yml`	Production cluster recovery	HA pause, multiple nodes, etcd cleanup, restore, and restart
`pig pitr`	A local database node	Single-instance orchestration without the admin node
`pig pb restore`	An instance not managed by Patroni	A direct pgBackRest restore wrapper

For a sandbox drill, see Manual Recovery. To recover into another cluster without changes to production, see Clone a PG Cluster.

PITR overwrites the target cluster

pgsql-pitr.yml pauses HA and stops Patroni/PostgreSQL. It overwrites the target data directory with pgbackrest --force restore. It then deletes the target cluster’s etcd prefix and rebuilds HA. The playbook prints a plan but does not wait for confirmation. Before a real restore, inspect the topology with pig pg list <target-cluster>. Verify a recent usable backup and recovery window with pig pb info. Have the operator state and confirm the exact target cluster and recovery point. For production recovery, schedule a maintenance window and retain an independently verified backup.

Quick Start

To roll pg-meta back to an earlier time, declare pg_pitr:

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_pitr: { time: '2025-07-13 10:00:00+00', action: promote }

Before recovery, verify the same target with these checks, then run the playbook:

pig pg list pg-meta
pig pb info
./pgsql-pitr.yml -l pg-meta

You can pass the same object temporarily on the command line:

./pgsql-pitr.yml -l pg-meta -e '{"pg_pitr": { "time": "2025-07-13 10:00:00+00", "action": "promote" }}'

Use valid JSON for command-line variables

The -e value must be valid JSON: quote keys and string values, for example {"pg_pitr": {"time": "...", "archive": true}}. Booleans are not quoted. Invalid quoting can fail parsing or silently produce the wrong value.

The playbook pauses Patroni HA, stops the cluster, and performs a delta pgBackRest restore. It starts PostgreSQL and waits for a consistent recovery state. It then prints control data, removes old etcd metadata, and starts the cluster under Patroni again. The playbook first prints the source, target, and restore command, but does not ask for interactive approval. For a one-shot targeted recovery, explicitly set action: promote. To inspect data at the target, use step-by-step execution with action: pause.

Recovery Targets

pg_pitr supports six recovery target forms. The four target values are mutually exclusive.

default/latest

pg_pitr: { }  # Replay to the end of the WAL archive stream

time

pg_pitr: { time: "2025-07-13 10:00:00+00" }

lsn

pg_pitr: { lsn: "0/4001C80" }

xid

pg_pitr: { xid: "250000" }

name

pg_pitr: { name: "some_restore_point" }

immediate

pg_pitr: { type: "immediate" }

With no target, recovery replays all archived WAL to the latest available state. Pigsty uses the internal type default for this mode. immediate stops at the first consistent point. Use it to get a usable instance as quickly as possible or to test a backup.

Recover by Time

Use a valid PostgreSQL TIMESTAMP. We strongly recommend an explicit time zone:

./pgsql-pitr.yml -l pg-meta -e '{"pg_pitr": { "time": "2025-07-13 10:00:00+00", "action": "promote" }}'

Recover by Name

Create an unambiguous marker before a risky change with pg_create_restore_point:

SELECT pg_create_restore_point('before_migration');

./pgsql-pitr.yml -l pg-meta -e '{"pg_pitr": { "name": "before_migration", "action": "promote" }}'

Recover by Transaction ID

If monitoring or the CSVLOG TXID field identifies the offending transaction, use exclusive to stop before that transaction:

./pgsql-pitr.yml -l pg-meta -e '{"pg_pitr": { "xid": "250000", "exclusive": true, "action": "promote" }}'

Recover by LSN

An LSN identifies a position in the WAL stream. Pigsty’s PG LSN dashboard panel also shows it. If the required position is on a particular timeline, set timeline. The default is latest.

./pgsql-pitr.yml -l pg-meta -e '{"pg_pitr": { "lsn": "0/4001C80", "timeline": "1", "action": "promote" }}'

Inclusive and exclusive targets

Targets are inclusive by default, so recovery replays the target transaction. exclusive: true excludes the exact target. It applies only to time, xid, and lsn. It maps to PostgreSQL’s recovery_target_inclusive.

Recovery Source

By default, recovery uses the target cluster’s own backup. Use these three fields to select another source:

cluster: the source stanza, including another cluster in a shared repository
repo: a temporary repository definition in the same format as a pgbackrest_repo entry
set: a specific backup label; otherwise pgBackRest selects a suitable set

For example, recover pg-meta2 from pg-meta:

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

Step-by-Step Execution

During an incident, use tags to require a human decision between stages. Confirm the backup, recovery point, and exact target. Then run the stages in order:

./pgsql-pitr.yml -l pg-meta -t down  # Pause HA; stop Patroni and PostgreSQL
./pgsql-pitr.yml -l pg-meta -t pitr  # Restore, replay WAL, and print control information
./pgsql-pitr.yml -l pg-meta -t up    # Remove etcd metadata; start the cluster and resume HA

# down                 : # stop HA and PostgreSQL
#   - pause            : # pause Patroni automatic failover
#   - stop             : # stop Patroni and PostgreSQL
# pitr                 : # perform PITR
#   - config           : # render pgBackRest config and restore script
#   - backup           : # optionally move PGDATA to /pg/data-backup
#   - restore          : # run pgBackRest restore
#   - recovery         : # start PostgreSQL and replay WAL
#   - verify           : # print recovered control data
# up                   : # rebuild HA
#   - etcd             : # remove old cluster metadata
#   - start            : # start Patroni/PostgreSQL
#   - resume           : # resume Patroni automatic failover

After down, confirm that the processes have stopped. After pitr, inspect /pg/tmp/recovery.log and query the recovery state. Then perform the narrowly authorized checks of business data. pg_controldata /pg/data reports checkpoint and timeline metadata. This information alone does not prove that recovery reached a time, XID, or LSN target.

SELECT pg_is_in_recovery(), pg_is_wal_replay_paused(),
       pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();

With action: pause, validate the data before promotion, then run up. If the target is wrong, adjust pg_pitr and repeat pitr before up. pause or shutdown provides an effective human decision point only in this staged procedure. For a one-shot targeted recovery, explicitly set action: promote.

pg_ctl -D /pg/data promote            # only after validating an action: pause recovery
./pgsql-pitr.yml -l pg-meta -t up     # rebuild Patroni HA

Repeating the pitr stage

With backup: true, the playbook moves the current data directory to /pg/data-backup. It deletes any existing /pg/data-backup first. The staged procedure is supported. A restore with backup: true is not generally idempotent.

PITR Parameter Definition

Declare the target, action, and treatment of existing data explicitly:

pg_pitr:
  cluster: pg-meta                 # source cluster/stanza; defaults to pg_cluster
  type: default                    # default | time | xid | name | lsn | immediate
  time: "2025-07-13 10:00:00+00"  # mutually exclusive with xid, name, and lsn
  name: "some_restore_point"       # mutually exclusive with time, xid, and lsn
  xid: "250000"                    # mutually exclusive with time, name, and lsn
  lsn: "0/4001C80"                 # mutually exclusive with time, xid, and name
  exclusive: false                 # exclude the exact target; time/xid/lsn only
  timeline: latest                 # target timeline; integer or latest
  set: latest                      # backup label; auto-select by default
  action: pause                    # pause | promote | shutdown
                                   # a specified target defaults to pause
  archive: true                    # preserve archiving; false sets archive-mode=off
  backup: false                    # move old PGDATA to /pg/data-backup before restore
  db_exclude: []                   # databases to exclude
  db_include: []                   # databases to include
  link_map:                        # tablespace/WAL link remapping
    pg_wal: '/data/wal'
    pg_xact: '/data/pg_xact'
  process: 4                       # restore workers; defaults to node_cpu
  repo: {}                        # temporary source repository definition
  data: /pg/data                  # target data directory
  port: 5432                      # recovery instance port

See Parameter Mapping for the corresponding pgBackRest options.

Single Instance: `pig pitr`

pig pitr performs a local-node procedure without Ansible. It validates the target, stanza, and backup, then stops Patroni/PostgreSQL and restores the data. It can optionally start PostgreSQL. It then prints instructions for the next steps.

pig pitr -t "2025-07-13 10:00:00+00"    # Recover to a point in time
pig pitr --xid 250000 -X                # Recover to before transaction 250000
pig pitr --name before_migration        # Recover to a named restore point
pig pitr -d                             # Recover to the end of the WAL archive stream
pig pitr -I --no-restart                # Restore for immediate recovery and leave PostgreSQL stopped
pig pitr -t "..." --plan                # Print the execution plan without making changes

Use -b/--set for a backup set, -T/--target-timeline for a timeline, and --target-action for the action after recovery reaches the target. For a side restore, use -D/--data with --no-restart. The command normally attempts a fast stop and aborts if that fails. Only explicit --force-stop permits immediate shutdown and a kill fallback.

For managed PGDATA, the command leaves Patroni stopped. Validate the instance before pig pt start. The command does not remove etcd metadata, rebuild replicas, or rejoin the instance to an HA cluster.

Primitive: `pig pb restore`

Use pig pb restore for an instance that Patroni does not manage, or that you deliberately removed from management. The command directly wraps pgbackrest restore. It validates the environment and requires PostgreSQL to be stopped. It then shows the plan and asks for confirmation.

pig pb restore --time "2025-07-13 10:00"
pig pb restore --set 20250715-013657F
pig pb restore -d

It rejects a live Patroni-managed target because Patroni could restart a partially restored instance. It also rejects any running PostgreSQL target. Arguments after --, such as --tablespace-map or --link-all, pass through to pgBackRest. You cannot override wrapped options, such as target, stanza, and repository, after --.

Post-Recovery

After restore:

Verify the recovery state and the smallest authorized set of application checks.
After a cross-cluster clone, complete stanza cleanup. Create a full backup on the new timeline as soon as practical:
```
pg-backup full
```
If exploratory recovery used archive: false, restore archiving. The archive_mode setting requires a restart. First confirm the maintenance window, current primary, and replication state. Then obtain explicit approval for the restart:
```
psql -c 'ALTER SYSTEM RESET archive_mode;'
pg restart pg-meta
pg-backup full
```