Backup Mechanism

Pigsty’s backup and restore operations ultimately execute pgBackRest commands. Using them safely requires both pgBackRest’s model and the mapping from Pigsty’s orchestration layers to native options.

Core pgBackRest Concepts

Stanza: the Cluster’s Backup Identity

A stanza names one PostgreSQL backup configuration and isolates that cluster inside a repository. Pigsty maps it directly from pg_cluster: the pg-meta stanza stores data under backup/pg-meta/ and archive/pg-meta/, so several clusters can share one repository.

The stanza records the source system identifier and major version and checks them before a backup. That identity check is why a cross-cluster clone needs stanza-upgrade afterward. Pigsty creates the stanza during cluster initialization; stanza-upgrade updates it after a major-version change or clone.

Repository: Where Backups Live

A repository stores backup files and WAL archives. repo1-type selects POSIX, S3, Azure, GCS, or SFTP; repo1-path, repo1-cipher-*, and repo1-retention-* define location, encryption, and retention. Pigsty renders these from pgbackrest_repo; see Backup Repository.

Backup Chains and Labels

Labels encode the chain. 20250715-013657F is a full backup; 20250715-013657F_20250715-013724D and ..._20250715-013730I depend on the full identified before the underscore. --set chooses the starting backup explicitly; otherwise pgBackRest selects the newest usable set before the target.

Retention: Keeping the Repository Bounded

repo1-retention-full and repo1-retention-full-type (count or time) decide when full chains expire. Dependent differential/incremental backups and WAL needed only by that chain expire with the full. Pigsty enables expire-auto, and pig pb expire --plan previews a manual run.

Time retention is a minimum window, not “keep only fulls newer than N days.” An old full expires only when another retained full has reached that age. A 14-day setting with weekly fulls therefore commonly retains three full chains and roughly 14–21 days of history.

WAL Archiving

PostgreSQL invokes archive-push when a WAL segment fills or archive_timeout elapses. During recovery, restore_command calls archive-get. Pigsty enables asynchronous archiving through /pg/spool so a temporary repository delay does not block the primary directly.

Timelines

Each promotion after recovery or failover creates a new timeline. Older timeline history remains in the repository, and --target-timeline chooses the recovery branch (latest by default). See the conceptual PITR mechanism.

What restore Actually Does

restore first reconstructs the data directory. Pigsty enables --delta, so pgBackRest validates existing files and rewrites only mismatches. It then writes recovery state (recovery.signal, restore_command, and recovery_target_*). Actual WAL replay happens after PostgreSQL starts.

Consequently, a successful restore command is only half of PITR. --target-action controls what happens when replay reaches the target: pause, promote, or shutdown.

pgbackrest --stanza=pg-meta restore
pgbackrest --stanza=pg-meta --type=immediate restore
pgbackrest --stanza=pg-meta --type=time --target='2025-07-13 10:00:00+00' restore
pgbackrest --stanza=pg-meta --type=xid --target='250000' --target-exclusive restore
pgbackrest --stanza=pg-meta --type=name --target='my-restore-point' restore
pgbackrest --stanza=pg-meta --type=lsn --target='0/4001C80' --target-action=promote restore

Observe a Backup Chain

Run read-only info after full, differential, and incremental backups to inspect labels, size, WAL bounds, and references:

pig pb info

A representative sequence looks like:

full backup: 20250715-013657F
diff backup: 20250715-013657F_20250715-013724D
    backup reference total: 1 full
incr backup: 20250715-013657F_20250715-013730I
    backup reference total: 1 full, 1 diff

Pigsty’s Wrapper Layers

Command Primitives

pb is a login-shell function that reads the first stanza from the local configuration and forwards arguments:

pb() (
    stanza=$(grep -o '\[[^][]*]' /etc/pgbackrest/pgbackrest.conf | head -n1 | sed 's/.*\[\([^]]*\)].*/\1/')
    pgbackrest --stanza="${stanza}" "$@"
)
pb info     # pgbackrest --stanza=pg-meta info
pb backup   # pgbackrest --stanza=pg-meta backup

pg-backup adds a primary-role check for scheduled use:

pg-backup full
pg-backup diff
pg-backup incr   # the default; pgBackRest promotes it when no full exists

pig pb adds stanza detection, DBSU privilege handling, primary checks for backup, and plan/confirmation guards for destructive primitives. See Admin Commands.

Parameter Mapping

A selective restore is still physical. Excluded databases receive sparse zeroed files so PostgreSQL can complete recovery, but those databases are inaccessible and must be removed explicitly afterward; this is not a logical subset like pg_dump.

How Configuration Is Rendered

The entry selected by pgbackrest_method is rendered to /etc/pgbackrest/pgbackrest.conf: underscores become hyphens and keys receive the repo1- prefix.

pgbackrest_repo:
  minio:
    type: s3                  # repo1-type=s3
    s3_endpoint: sss.pigsty   # repo1-s3-endpoint=sss.pigsty
    cipher_type: aes-256-cbc  # repo1-cipher-type=aes-256-cbc
    retention_full: 14        # repo1-retention-full=14

pgsql-pitr.yml renders a separate temporary /pg/conf/pitr.conf; PostgreSQL recovery output goes to /pg/tmp/recovery.log.

Scheduled Backups

pg_crontab entries are installed for the postgres OS user on every cluster node. Because pg-backup checks the current role, only the primary backs up, and a promoted primary takes over future schedules.

pg_crontab:
  - '00 01 * * 1 /pg/bin/pg-backup full'
  - '00 01 * * 2,3,4,5,6,7 /pg/bin/pg-backup'

./pgsql.yml -t pg_crontab -l pg-meta

See Backup Policy for frequency and retention design.

Deployment Details

The pg_backup subtask installs/configures pgBackRest, creates the stanza, and—when pgbackrest_init_backup is enabled—attempts an initial full backup. /etc/pgbackrest/initial.done is written only after that backup succeeds.

pgbackrest_exporter listens on pgbackrest_exporter_port, 9854 by default, and exports backup metrics. Disable it with pgbackrest_exporter_enabled or customize it with pgbackrest_exporter_options.