Restore Operations

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

Task

Pigsty provides three restore entry points. They share the same parameter semantics, but serve 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 hands-on sandbox drill, see Manual Recovery. To recover into another cluster without changing production, see Clone a PG Cluster.

PITR overwrites the target cluster

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

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 }

Run the same target through the safety gate before executing it:

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, performs a delta pgBackRest restore, starts PostgreSQL and waits for a consistent recovery state, prints control data, removes old etcd metadata, and starts the cluster under Patroni again. It prints the source, target, and restore command first, but has no interactive approval gate. A one-shot targeted recovery should therefore declare action: promote explicitly. 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.

Recovery target types

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’s internal type is default). immediate stops at the first consistent point, which is useful for obtaining a usable instance as quickly as possible or testing a backup.

Recover by Time

Use a valid PostgreSQL TIMESTAMP; an explicit time zone is strongly recommended:

./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 the offending transaction ID is known from monitoring or CSVLOG’s TXID field, 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. It is also visible in Pigsty’s PG LSN dashboard panel. Set timeline when the desired position is on a particular 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 the target transaction is replayed. exclusive: true excludes the exact target. It applies only to time, xid, and lsn, and maps to PostgreSQL’s recovery_target_inclusive.

Recovery Source

Recovery uses the target cluster’s own backup by default. Three fields can 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

In an incident, use tags to retain an explicit human gate between stages. After confirming the backup, recovery point, and exact target, 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 the processes are stopped. After pitr, inspect /pg/tmp/recovery.log and query the recovery state before checking narrowly authorized business data. pg_controldata /pg/data reports checkpoint and timeline metadata; it does not by itself prove that a time, XID, or LSN target was reached.

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

With action: pause, promote only after validation, then run up. If the target is wrong, adjust pg_pitr and repeat pitr before up. pause or shutdown creates a meaningful human gate only in this staged workflow; use action: promote explicitly for one-shot targeted recovery.

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, but deletes any existing /pg/data-backup before doing so. The staged workflow is supported; a restore using 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 workflow without Ansible: validate the target, stanza, and backup; stop Patroni/PostgreSQL; restore; optionally start PostgreSQL; and print follow-up guidance.

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, --target-action for the post-target action, and -D/--data with --no-restart for a side restore. 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 it leaves Patroni stopped. Validate the instance before pig pt start. It does not remove etcd metadata, rebuild replicas, or rejoin the instance to an HA cluster.

Primitive: `pig pb restore`

For an instance not managed by Patroni (or one deliberately taken out of management), pig pb restore directly wraps pgbackrest restore. It validates the environment, requires PostgreSQL to be stopped, displays 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 half-restored instance, and it rejects any running PostgreSQL target. Arguments after --, such as --tablespace-map or --link-all, pass through to pgBackRest, but wrapped options such as target, stanza, and repository cannot be overridden there.

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. Because archive_mode is a postmaster setting, 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
```