Restore Operations
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.
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:
Before recovery, verify the same target with these checks, then run the playbook:
You can pass the same object temporarily on the command line:
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 until WAL replay reaches the recovery target: promoted, paused, or shut down there.
It fails if PostgreSQL exits or pauses before the target, for example when the target lies beyond the end of the archive.
It then prints control data, removes old etcd metadata, and starts the cluster under Patroni again.
Only the primary is restored. Replicas are left untouched until up.
Then each one is wiped and cloned from the new primary by Patroni, one at a time, after the new primary is writable.
Before Patroni starts, the primary is promoted at the target if it is still paused or shut down there, because Patroni discards the recovery target.
The playbook first prints the source, target, and restore command, but does not ask for interactive approval.
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.
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:
Recover by Name
Create an unambiguous marker before a risky change with pg_create_restore_point:
Recover by Transaction ID
If monitoring or the CSVLOG TXID field identifies the offending transaction, use exclusive to stop before that transaction:
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.
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 repositoryrepo: a temporary repository definition in the same format as apgbackrest_repoentryset: a specific backup label; otherwise pgBackRest selects a suitable set
For example, recover pg-meta2 from pg-meta:
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:
After down, confirm that the processes have stopped.
The pitr stage returns only after WAL replay reaches the target.
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.
With action: pause, validate the data, then run up.
up promotes the primary at the target before it starts Patroni, so a manual promotion is optional.
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.
With backup: true, the playbook moves the primary’s current data directory to /pg/data-backup.
Replicas do not keep a copy: up wipes them and clones them from the new primary.
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:
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.
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.
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:
If exploratory recovery used
archive: false, restore archiving. Thearchive_modesetting requires a restart. First confirm the maintenance window, current primary, and replication state. Then obtain explicit approval for the restart: