Backup & Restore

• 1: Backup Mechanism
• 2: Backup Policy
• 3: Backup Repository
• 4: Admin Commands
• 5: Restore Operations
• 6: Clone a PG Cluster

pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ]

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

1 - Backup Mechanism

Pigsty’s backup and restore operations execute pgBackRest commands. For safe operation, understand the pgBackRest model and how Pigsty maps its orchestration parameters 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 uses pg_cluster as the stanza name. The pg-meta stanza stores data under backup/pg-meta/ and archive/pg-meta/. Thus, several clusters can share one repository.

The stanza records the source system identifier and major version. pgBackRest checks these values before a backup. Because of this check, 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 generates these settings from pgbackrest_repo. See Backup Repository.

Backup Chains and Labels

Labels identify the backup chain. 20250715-013657F is a full backup. 20250715-013657F_20250715-013724D and ..._20250715-013730I depend on the full backup identified before the underscore. Use --set to select 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) determine when full backup chains expire. Dependent differential/incremental backups and WAL needed only by that chain expire with the full backup. Pigsty enables expire-auto. Use pig pb expire --plan to preview a manual run.

Time retention defines a minimum window. It does not remove every full backup older than N days. An old full backup expires only when another retained full backup has reached that age. With weekly full backups, a 14-day setting therefore commonly retains three full chains and approximately 14–21 days of history.

WAL Archiving

PostgreSQL calls 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. Thus, 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. --target-timeline selects the recovery branch and defaults to latest. 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 files that differ. It then writes the recovery state (recovery.signal, restore_command, and recovery_target_*). WAL replay starts after PostgreSQL starts.

A successful restore command does not complete 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 a physical restore. Excluded databases receive sparse zeroed files so PostgreSQL can complete recovery. These databases are inaccessible. You must remove them explicitly afterward. This is not a logical subset as with pg_dump.

How Configuration Is Rendered

pgbackrest_method selects the entry that Pigsty writes to /etc/pgbackrest/pgbackrest.conf. Pigsty converts underscores to hyphens and adds the repo1- prefix to keys.

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 generates a separate temporary configuration at /pg/conf/pitr.conf. PostgreSQL recovery output goes to /pg/tmp/recovery.log.

Scheduled Backups

Pigsty installs pg_crontab entries for the postgres OS user on every cluster node. pg-backup checks the current role, so only the primary makes backups. After promotion, the new primary runs future scheduled backups.

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 and configures pgBackRest, then creates the stanza. If pgbackrest_init_backup is enabled, it attempts an initial full backup. It writes /etc/pgbackrest/initial.done only after that backup succeeds.

pgbackrest_exporter exports backup metrics and listens on pgbackrest_exporter_port, which defaults to 9854. To disable it, use pgbackrest_exporter_enabled. To customize it, use pgbackrest_exporter_options.

2 - Backup Policy

The chart below shows the “Recovery Window” and “Backup Storage Usage” on one timeline (0~108h). Use it to compare the two metrics.

The 30-day example uses a database size of 100GB and daily writes of 10GB. It shows both metrics with weekly full backups, daily incremental backups, and 14-day full-backup retention.

• When: Backup schedule
• Where: Backup repository
• How: Backup method

When to Backup

First, choose when to back up the database. Backup frequency affects recovery time. Recovery replays WAL from the last backup to the target point. More frequent backups reduce the amount of WAL to replay and shorten recovery.

Daily Full Backup

For production databases, we recommend a daily full backup as the simplest initial policy. This is also Pigsty’s default backup policy. Pigsty implements it through crontab.

pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ]
pgbackrest_method: local          # Choose backup repository method: `local`, `minio`, or other custom repository
pgbackrest_repo:                  # pgbackrest repository configuration: https://pgbackrest.org/configuration.html#section-repository
  local:                          # Default pgbackrest repository using local POSIX filesystem
    path: /pg/backup              # Local backup directory, defaults to `/pg/backup`
    retention_full_type: count    # Retain full backups by count
    retention_full: 2             # Keep 2, up to 3 full backups when using local filesystem repository

Assume a database size of 100GB, daily writes of 10GB, and a duration of 1 hour for each full backup. The chart below shows the recovery window and storage usage for this daily full backup policy with a local repository.

The recovery window cycles between 25-49 hours, and storage usage is roughly 2 full backups plus around 2 days of WAL archives. In practice, prepare at least 3~5 times the base database size as backup disk capacity for the default policy.

tooltip: { trigger: axis, formatter: $fn:tipMerged, axisPointer: { type: line, snap: true, label: { show: false } } }
axisPointer: { link: [ { xAxisIndex: [0, 1] } ] }
legend: { show: false, bottom: 10, itemGap: 18, data: ["Primary Backup", "Secondary Backup", "WAL Archive", "Transient Backup"] }
grid:
  - { left: 82, right: "10%", top: 42, height: 218, containLabel: false }
  - { left: 82, right: "10%", top: 286, height: 218, containLabel: false }
xAxis:
  - type: category
    gridIndex: 0
    position: bottom
    boundaryGap: false
    data: [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108]
    name: Time h
    nameLocation: end
    nameGap: 10
    nameTextStyle: { align: left, verticalAlign: top, padding: [8, 0, 0, 0] }
    axisLabel: { interval: 11, formatter: $fn:fmtHour }
    axisLine: { show: true, symbol: [none, arrow], symbolSize: [10, 14], lineStyle: { width: 1.6, color: "#4b5563" } }
    axisTick: { show: true, length: 6 }
    splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.28, color: "#9ca3af" } }
    minorTick: { show: true, splitNumber: 12, length: 3 }
    minorSplitLine: { show: true, lineStyle: { type: dotted, width: 1, opacity: 0.14, color: "#9ca3af" } }
  - type: category
    gridIndex: 1
    position: top
    boundaryGap: true
    data: [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108]
    axisLabel: { show: false }
    axisLine: { show: true, lineStyle: { width: 1.6, color: "#4b5563" } }
    axisTick: { show: true, length: 6 }
    splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.22, color: "#9ca3af" } }
yAxis:
  - type: value
    gridIndex: 0
    min: 0
    max: 52
    interval: 5
    name: Recovery Window h
    nameLocation: end
    nameRotate: 0
    nameGap: 8
    nameTextStyle: { align: left, verticalAlign: bottom, padding: [0, 0, 8, 4] }
    axisLabel: { formatter: $fn:fmtWin }
    axisLine: { show: true, symbol: [none, arrow], symbolSize: [10, 14], lineStyle: { width: 1.6, color: "#4b5563" } }
    axisTick: { show: true, length: 6 }
    splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.35, color: "#9ca3af" } }
    minorTick: { show: true, splitNumber: 5, length: 3 }
    minorSplitLine: { show: true, lineStyle: { type: dotted, width: 1, opacity: 0.18, color: "#9ca3af" } }
  - type: value
    gridIndex: 1
    min: 0
    max: 350
    interval: 50
    inverse: true
    name: Backup Storage GB
    nameLocation: end
    nameRotate: 0
    nameGap: 8
    nameTextStyle: { align: left, verticalAlign: top, padding: [10, 0, 0, 4] }
    axisLabel: { formatter: $fn:fmtGbTick }
    axisLine: { show: true, symbol: [arrow, none], symbolSize: [10, 14], lineStyle: { width: 1.6, color: "#4b5563" } }
    axisTick: { show: true, length: 6 }
    splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.32, color: "#9ca3af" } }
series: [ { name: Recovery Window, type: line, smooth: false, symbol: none, showSymbol: false, xAxisIndex: 0, yAxisIndex: 0, lineStyle: { width: 3, color: "#f2a000" }, itemStyle: { color: "#f2a000" }, data: [[0,0],[1,0],[1,1],[2,2],[3,3],[4,4],[5,5],[6,6],[7,7],[8,8],[9,9],[10,10],[11,11],[12,12],[13,13],[14,14],[15,15],[16,16],[17,17],[18,18],[19,19],[20,20],[21,21],[22,22],[23,23],[24,24],[25,25],[26,26],[27,27],[28,28],[29,29],[30,30],[31,31],[32,32],[33,33],[34,34],[35,35],[36,36],[37,37],[38,38],[39,39],[40,40],[41,41],[42,42],[43,43],[44,44],[45,45],[46,46],[47,47],[48,48],[49,49],[49,25],[50,26],[51,27],[52,28],[53,29],[54,30],[55,31],[56,32],[57,33],[58,34],[59,35],[60,36],[61,37],[62,38],[63,39],[64,40],[65,41],[66,42],[67,43],[68,44],[69,45],[70,46],[71,47],[72,48],[73,49],[73,25],[74,26],[75,27],[76,28],[77,29],[78,30],[79,31],[80,32],[81,33],[82,34],[83,35],[84,36],[85,37],[86,38],[87,39],[88,40],[89,41],[90,42],[91,43],[92,44],[93,45],[94,46],[95,47],[96,48],[97,49],[97,25],[98,26],[99,27],[100,28],[101,29],[102,30],[103,31],[104,32],[105,33],[106,34],[107,35],[108,36]], markLine: { symbol: none, label: { show: false }, data: [ { xAxis: 0, lineStyle: { color: "#59a14f", type: "solid", width: 1.4, opacity: 0.75 } }, { xAxis: 24, lineStyle: { color: "#59a14f", type: "solid", width: 1.4, opacity: 0.75 } }, { xAxis: 48, lineStyle: { color: "#59a14f", type: "solid", width: 1.4, opacity: 0.75 } }, { xAxis: 72, lineStyle: { color: "#59a14f", type: "solid", width: 1.4, opacity: 0.75 } }, { xAxis: 96, lineStyle: { color: "#59a14f", type: "solid", width: 1.4, opacity: 0.75 } }, { xAxis: 1, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.8 } }, { xAxis: 25, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.8 } }, { xAxis: 49, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.8 } }, { xAxis: 73, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.8 } }, { xAxis: 97, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.8 } }, { yAxis: 25, label: { show: true, formatter: "lower 25h", position: "end", distance: 12, color: "#2563eb" }, lineStyle: { color: "#2563eb", type: "dashdot", width: 1.4, opacity: 0.75 } }, { yAxis: 49, label: { show: true, formatter: "upper 49h", position: "end", distance: 12, color: "#7c3aed" }, lineStyle: { color: "#7c3aed", type: "dashdot", width: 1.4, opacity: 0.75 } } ] } }, { name: Primary Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 5, itemStyle: { color: "#59a14f" }, data: [0,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100] }, { name: Secondary Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 5, itemStyle: { color: "#4e79a7" }, data: [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100] }, { name: WAL Archive, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 5, itemStyle: { color: "#edc949" }, data: [0,0,0.42,0.83,1.25,1.67,2.08,2.5,2.92,3.33,3.75,4.17,4.58,5,5.42,5.83,6.25,6.67,7.08,7.5,7.92,8.33,8.75,9.17,9.58,10,10.42,10.83,11.25,11.67,12.08,12.5,12.92,13.33,13.75,14.17,14.58,15,15.42,15.83,16.25,16.67,17.08,17.5,17.92,18.33,18.75,19.17,19.58,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20] }, { name: Transient Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 5, itemStyle: { color: "#9ca3af", opacity: 0.75 }, data: [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100,0,0,0,0,0,0,0,0,0,0,0,0] } ]

Full + Incremental Backup

You can optimize backup space usage by adjusting these parameters.

If using Silo / S3 as a centralized backup repository, storage is no longer limited by the local disk. In this case, consider using full + incremental backup with a 2-week retention policy:

pg_crontab:  # Full backup at 1 AM on Monday, incremental backups on weekdays
  - '00 01 * * 1           /pg/bin/pg-backup full'
  - '00 01 * * 2,3,4,5,6,7 /pg/bin/pg-backup'
pgbackrest_method: minio
pgbackrest_repo:                  # pgbackrest repository configuration: https://pgbackrest.org/configuration.html#section-repository
  minio:                          # Optional S3-compatible repository preset
    type: s3                      # Silo uses the S3-compatible repository type
    s3_endpoint: sss.pigsty       # object-storage endpoint, `sss.pigsty` by default
    s3_region: us-east-1          # compatibility region, `us-east-1` by default
    s3_bucket: pgsql              # backup bucket, `pgsql` by default
    s3_key: pgbackrest            # pgBackRest access key
    s3_key_secret: S3User.Backup  # object-storage user secret
    s3_uri_style: path            # use path-style rather than host-style URIs
    path: /pgbackrest             # backup path, `/pgbackrest` by default
    storage_port: 9000            # Silo service port, 9000 by default
    storage_ca_file: /etc/pki/ca.crt  # Pigsty CA path, `/etc/pki/ca.crt` by default
    block: y                      # Enable block-level incremental backup
    bundle: y                     # Bundle small files into a single file
    bundle_limit: 20MiB           # Bundle size limit, recommended 20MiB for object storage
    bundle_size: 128MiB           # Bundle target size, recommended 128MiB for object storage
    cipher_type: aes-256-cbc      # Enable AES encryption for remote backup repository
    cipher_pass: pgBackRest       # AES encryption password, defaults to 'pgBackRest'
    retention_full_type: time     # Retain full backups by time
    retention_full: 14            # Keep full backups from the last 14 days

With weekly full backups and 14-day time-based retention, the steady-state recovery window is approximately 14–21 days. The exact window depends on successful backups and WAL archiving.

For a database of 100GB with daily writes of 10GB, the following chart shows backup size:

tooltip: { trigger: axis, formatter: $fn:tipMerged30, axisPointer: { type: line, snap: true, label: { show: false } } }
axisPointer: { link: [ { xAxisIndex: [0, 1] } ] }
legend: { show: false, bottom: 10, itemGap: 18, data: ["Primary Backup", "Secondary Backup", "Incremental Backup", "WAL Archive", "Transient Backup"] }
grid:
  - { left: 82, right: "10%", top: 42, height: 218, containLabel: false }
  - { left: 82, right: "10%", top: 302, height: 218, containLabel: false }
xAxis:
  - type: value
    gridIndex: 0
    position: bottom
    boundaryGap: false
    min: 0
    max: 31
    interval: 1
    name: Time
    nameLocation: end
    nameGap: 10
    nameTextStyle: { align: left, verticalAlign: top, padding: [8, 0, 0, 0] }
    axisLabel: { formatter: $fn:fmtDay30 }
    axisLine: { show: true, symbol: [none, arrow], symbolSize: [10, 14], lineStyle: { width: 1.6, color: "#4b5563" } }
    axisTick: { show: true, length: 6 }
    splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.28, color: "#9ca3af" } }
    minorTick: { show: true, splitNumber: 4, length: 3 }
    minorSplitLine: { show: true, lineStyle: { type: dotted, width: 1, opacity: 0.14, color: "#9ca3af" } }
  - type: category
    gridIndex: 1
    position: top
    boundaryGap: true
    z: 10
    data: [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30]
    axisLabel: { show: false }
    axisLine: { show: true, lineStyle: { width: 1.6, color: "#4b5563" } }
    axisTick: { show: true, alignWithLabel: true, length: 6 }
    splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.22, color: "#9ca3af" } }
yAxis:
  - type: value
    gridIndex: 0
    min: 0
    max: 360
    interval: 48
    name: Recovery Window h
    nameLocation: end
    nameRotate: 0
    nameGap: 8
    nameTextStyle: { align: left, verticalAlign: bottom, padding: [0, 0, 8, 4] }
    axisLabel: { formatter: $fn:fmtWin30 }
    axisLine: { show: true, symbol: [none, arrow], symbolSize: [10, 14], lineStyle: { width: 1.6, color: "#4b5563" } }
    axisTick: { show: true, length: 6 }
    splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.35, color: "#9ca3af" } }
    minorTick: { show: true, splitNumber: 4, length: 3 }
    minorSplitLine: { show: true, lineStyle: { type: dotted, width: 1, opacity: 0.18, color: "#9ca3af" } }
  - type: value
    gridIndex: 1
    min: 0
    max: 600
    interval: 50
    inverse: true
    z: 10
    name: Storage GB
    nameLocation: end
    nameRotate: 0
    nameGap: 8
    nameTextStyle: { align: left, verticalAlign: top, padding: [10, 0, 0, 4] }
    axisLabel: { formatter: $fn:fmtGbTick30 }
    axisLine: { show: true, symbol: [arrow, none], symbolSize: [10, 14], lineStyle: { width: 1.6, color: "#4b5563" } }
    axisTick: { show: true, length: 6 }
    splitLine: { show: true, lineStyle: { type: dashed, width: 1, opacity: 0.32, color: "#9ca3af" } }
series:
  - { name: Recovery Window, type: line, smooth: false, symbol: none, showSymbol: false, xAxisIndex: 0, yAxisIndex: 0, lineStyle: { width: 3, color: "#f28e2c" }, itemStyle: { color: "#f28e2c" }, data: [[1,24],[2,48],[3,72],[4,96],[5,120],[6,144],[7,168],[8,192],[9,216],[10,240],[11,264],[12,288],[13,312],[14,336],[14,168],[15,192],[16,216],[17,240],[18,264],[19,288],[20,312],[21,336],[21,168],[22,192],[23,216],[24,240],[25,264],[26,288],[27,312],[28,336],[28,168],[29,192],[30,216]], markLine: { symbol: none, label: { show: false }, data: [ { xAxis: 7, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.65 } }, { xAxis: 14, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.65 } }, { xAxis: 21, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.65 } }, { xAxis: 28, lineStyle: { color: "#336791", type: "solid", width: 1.4, opacity: 0.65 } }, { yAxis: 168, label: { show: true, formatter: "lower 7d", position: "end", distance: 12, color: "#2563eb" }, lineStyle: { color: "#2563eb", type: "dashdot", width: 1.4, opacity: 0.72 } }, { yAxis: 336, label: { show: true, formatter: "upper 14d", position: "end", distance: 12, color: "#7c3aed" }, lineStyle: { color: "#7c3aed", type: "dashdot", width: 1.4, opacity: 0.72 } } ] } }
  - { name: Primary Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 16, itemStyle: { color: "#59a14f" }, data: [100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100] }
  - { name: Secondary Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 16, itemStyle: { color: "#4e79a7" }, data: [0,0,0,0,0,0,0,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100] }
  - { name: Incremental Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 16, itemStyle: { color: "#76b7b2" }, data: [0,10,20,30,40,50,60,70,80,90,100,110,120,130,70,80,90,100,110,120,130,70,80,90,100,110,120,130,70,80] }
  - { name: WAL Archive, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 16, itemStyle: { color: "#edc949" }, data: [10,20,30,40,50,60,70,80,90,100,110,120,130,140,80,90,100,110,120,130,140,80,90,100,110,120,130,140,80,90] }
  - { name: Transient Backup, type: bar, stack: used, xAxisIndex: 1, yAxisIndex: 1, barWidth: 16, itemStyle: { color: "#9ca3af", opacity: 0.75 }, data: [0,0,0,0,0,0,0,0,0,0,0,0,0,110,0,0,0,0,0,0,110,0,0,0,0,0,0,110,0,0] }

Backup Location

Pigsty provides two default backup repository definitions: local and minio.

• local: Default option, uses local /pg/backup directory (symlink to pg_fs_backup: /data/backups)
• minio: Uses SNSD single-node MinIO cluster (supported by Pigsty, but not enabled by default)

pgbackrest_method: local          # Choose backup repository method: `local`, `minio`, or other custom repository
pgbackrest_repo:                  # pgbackrest repository configuration: https://pgbackrest.org/configuration.html#section-repository
  local:                          # Default pgbackrest repository using local POSIX filesystem
    path: /pg/backup              # Local backup directory, defaults to `/pg/backup`
    retention_full_type: count    # Retain full backups by count
    retention_full: 2             # Keep 2, up to 3 full backups when using local filesystem repository
  minio:                          # Optional minio repository
    type: s3                      # minio is S3 compatible
    s3_endpoint: sss.pigsty       # minio endpoint domain, defaults to `sss.pigsty`
    s3_region: us-east-1          # minio region, defaults to us-east-1, meaningless for minio
    s3_bucket: pgsql              # minio bucket name, defaults to `pgsql`
    s3_key: pgbackrest            # minio user access key for pgbackrest
    s3_key_secret: S3User.Backup  # minio user secret for pgbackrest
    s3_uri_style: path            # minio uses path-style URIs instead of host-style
    path: /pgbackrest             # minio backup path, defaults to `/pgbackrest`
    storage_port: 9000            # minio port, defaults to 9000
    storage_ca_file: /etc/pki/ca.crt  # minio CA certificate path, defaults to `/etc/pki/ca.crt`
    block: y                      # Enable block-level incremental backup
    bundle: y                     # Bundle small files into a single file
    bundle_limit: 20MiB           # Bundle size limit, recommended 20MiB for object storage
    bundle_size: 128MiB           # Bundle target size, recommended 128MiB for object storage
    cipher_type: aes-256-cbc      # Enable AES encryption for remote backup repository
    cipher_pass: pgBackRest       # AES encryption password, defaults to 'pgBackRest'
    retention_full_type: time     # Retain full backups by time
    retention_full: 14            # Keep full backups from the last 14 days

3 - Backup Repository

Two parameters select the backup location. pgbackrest_repo defines candidate repositories. pgbackrest_method selects one repository. Pigsty converts repository keys to pgBackRest repo1-* options, so you can use any supported pgBackRest repository option directly. Pigsty v4.5.0 writes only the entry selected by pgbackrest_method as repo1. Multiple candidate keys do not enable multi-repository backup.

Default Repositories

Pigsty supplies two definitions: local and minio.

• local is the default. /pg/backup points at pg_fs_backup, /data/backups by default.
• minio uses Silo from the MINIO module or another compatible S3 service. It is supported but not selected by default.

pgbackrest_method: local
pgbackrest_repo:
  local:
    path: /pg/backup
    retention_full_type: count
    retention_full: 2
  minio:
    type: s3
    s3_endpoint: sss.pigsty
    s3_region: us-east-1
    s3_bucket: pgsql
    s3_key: pgbackrest
    s3_key_secret: S3User.Backup
    s3_uri_style: path
    path: /pgbackrest
    storage_port: 9000
    storage_ca_file: /etc/pki/ca.crt
    block: y
    bundle: y
    bundle_limit: 20MiB
    bundle_size: 128MiB
    cipher_type: aes-256-cbc
    cipher_pass: pgBackRest
    retention_full_type: time
    retention_full: 14

The presets use different settings. local provides simple, fast restores with count-based retention and no encryption or bundling. minio enables AES-256-CBC encryption, bundles small files, uses block incremental backup, and retains full backups by time.

Retention Policy

pgBackRest applies retention after each backup (expire-auto). When a full backup expires, its dependent differential/incremental backups expire with it. WAL needed only by that chain also expires.

• retention_full_type: count with retention_full: 2 keeps the two newest full chains. A third chain can exist briefly while a new full backup completes.
• retention_full_type: time with retention_full: 14 defines a minimum time window. An old full backup expires only when another full backup is at least 14 days old. With weekly full backups, this usually retains three chains and approximately 14–21 days of recovery history.

See Backup Policy for recovery-window and space calculations, and Admin Commands for a safe expiration preview.

Use a Silo Repository

The MINIO module currently deploys Silo, an S3-compatible object store. It provides an independent disaster-recovery copy only if it is outside the database host or site failure domain. Deploy it, then select the minio preset:

all:
  vars:
    pgbackrest_method: minio
  children:
    minio: { hosts: { 10.10.10.10: { minio_seq: 1 }} ,vars: { minio_cluster: minio, minio_type: silo }}

By default, the preset uses the HTTPS endpoint sss.pigsty and validates it with /etc/pki/ca.crt. MINIO initialization creates the default pgsql bucket and pgbackrest user.

For production, deploy and test a fault-tolerant object store with multiple nodes and drives. See MINIO Configuration. The minio preset also supports servers that Pigsty does not manage, including independent MinIO, RustFS, and other compatible implementations. For these servers, installation, upgrades, certificates, and lifecycle management remain outside the MINIO role support scope.

Use S3 or Cloud Object Storage

For a single database node, an off-host cloud object store is often the most useful repository. Define a new entry and select it:

pgbackrest_method: s3
pgbackrest_repo:
  s3:
    type: s3
    s3_endpoint: oss-cn-beijing-internal.aliyuncs.com
    s3_region: oss-cn-beijing
    s3_bucket: <your_bucket_name>
    s3_key: <your_access_key>
    s3_key_secret: <your_secret_key>
    s3_uri_style: host
    path: /pgbackrest
    bundle: y
    bundle_limit: 20MiB
    bundle_size: 128MiB
    cipher_type: aes-256-cbc
    cipher_pass: <separate-strong-passphrase>
    retention_full_type: time
    retention_full: 14
  local:
    path: /pg/backup
    retention_full_type: count
    retention_full: 2

pgBackRest also supports Azure, GCS, and SFTP repositories.

Share a Repository Across Clusters

A centralized repository can serve several PostgreSQL clusters. Each stanza takes its name from pg_cluster and isolates one cluster’s backup and archive history. This also enables cross-cluster restore.

Cluster names must therefore be globally unique within a shared repository, even across otherwise separate deployment environments.

Repository Versioning

Object-store versioning can preserve earlier versions after an overwrite or deletion. These versions share the same storage system and control plane. Thus, versioning does not replace an independent off-site or offline copy. Enable versioning when you create a bucket:

minio_buckets:
  - { name: pgsql, versioning: true }
  - { name: meta, versioning: true }
  - { name: data }

pgBackRest’s repo-target-time option can read the repository as it existed at an earlier time when the backend retains those versions.

Repository Locking

Some S3-compatible services support Object Lock/WORM. You cannot change or permanently delete a retained object version until its retention period ends. A normal delete can still create a delete marker that hides the current object. Historical versions remain and continue to consume capacity.

• MinIO Object Lock
• AWS S3 Object Lock

The lock flag enables versioning and lock capability only when Pigsty creates the bucket:

minio_buckets:
  - { name: pgsql, lock: true }
  - { name: meta, versioning: true }
  - { name: data }

The flag does not set a default retention period. Configure GOVERNANCE or COMPLIANCE retention with mcli retention set or the storage console. Then verify it with mcli retention info. A sufficiently privileged principal can bypass GOVERNANCE. Even root cannot shorten COMPLIANCE retention.

Locking changes expiration and stanza deletion. pgBackRest may expire objects logically while retained historical versions remain until their deadline. Before you enable locking, test backup, expiration, delete-marker cleanup, and version recovery on a non-production bucket.

Switch Repositories

After you change a repository definition or pgbackrest_method, generate the configuration again and initialize the stanza. Promptly create a recovery point in the new repository:

./pgsql.yml -t pg_backup -l pg-meta           # Apply the repository definition and create the stanza
sudo -iu postgres pg-backup full              # Run on the current primary to establish the first recovery point

Pigsty does not migrate existing backups automatically. While you retain the old repository, you can select it as a restore source through pg_pitr.repo.

4 - Admin Commands

On a database node, run backup commands as the database superuser (pg_dbsu, which defaults to postgres. Use one of these interfaces:

• pig pb: the PIG CLI wrapper, with automatic stanza detection, DBSU switching, and safety checks; this is the recommended interface
• pb: a login-shell function that supplies --stanza and forwards arguments to pgBackRest
• pgbackrest: the native command; see the pgBackRest command reference

Command Overview

Enable Backup

If pgbackrest_enabled is true at cluster creation, Pigsty enables backup automatically. If backup was disabled at creation, or repository settings have changed, run the pg_backup subtask:

./pgsql.yml -t pg_backup -l pg-meta   # Configure pgBackRest and create the stanza

After cluster initialization, Pigsty attempts an initial full backup. It writes /etc/pgbackrest/initial.done only after the backup command succeeds. If the attempt fails, the playbook ignores the failure and leaves no marker. This file only prevents the initialization task from repeating. Always verify repository state with pig pb info or pgbackrest info. Define scheduled backups with pg_crontab; see Backup Policy.

Remove Backup

To remove only a backup stanza, use pig pb delete as the preferred interface. It asks for interactive confirmation. With a multi-stanza configuration, you must also specify the target explicitly. Verify the exact target first:

pig pb info -s pg-meta           # Verify the backup chain and recovery window
pig pb delete -s pg-meta         # Executes only after typing the exact stanza name

By default, pgsql-rm.yml also tries to delete the cluster’s backup stanza when it removes a primary instance (pg_role = primary). Each command below changes or deletes state. Do not run a command only because it appears in an example:

./pgsql-rm.yml -l pg-meta                          # Remove the cluster and its backups
./pgsql-rm.yml -l pg-meta -e pg_rm_backup=false    # Remove the cluster while preserving backups
./pgsql-rm.yml -l pg-meta -t pg_backup             # Remove backup-related state only

Before execution, verify a recent usable backup and record the recovery requirement. Have the operator re-enter the exact cluster/stanza name. To preserve backups when you remove the cluster, set pg_rm_backup to false.

pgsql-rm.yml -t pg_backup forcibly runs pgbackrest stanza-delete on the primary. In local mode, it removes the local repository directory. It then removes the pgBackRest configuration and initial-backup marker. The task ignores some deletion errors. Thus, a successful playbook result does not prove that it physically removed the repository objects. To delete only a stanza, prefer pig pb delete, which provides a plan and requires confirmation.

With object versioning and object-lock retention, deletion may create a delete marker. Locked historical versions continue to consume storage until their retention period expires.

Manual Backup

You can start a backup outside the crontab schedule. Both pg-backup and pig pb backup check that the current instance is primary. They exit on a replica:

pg-backup            # Incremental; pgBackRest promotes it to full if no full backup exists
pg-backup full       # Full backup
pg-backup diff       # Differential backup relative to the latest full
pg-backup incr       # Incremental backup relative to the latest backup

pig pb backup full   # Equivalent PIG wrapper with stanza and DBSU detection

Backup consumes disk I/O and network bandwidth. Pigsty limits parallelism to a small number of processes. Still, you should schedule production backups for periods with low traffic.

Inspect Backups

pb info shows backups and WAL archive status for the current stanza:

pb info          # pgbackrest --stanza=pg-meta info
pig pb info      # Equivalent PIG wrapper
pig pb list      # List backup sets; use "list stanza" for all stanzas

Backup labels that end in F, D, and I identify full, differential, and incremental backups. The portion before an underscore identifies the full backup at the start of that chain. The WAL archive range and oldest usable full backup define the recovery window.

The pgbackrest_exporter service on port 9854 continuously exports metrics such as the latest backup time, type, size, and error status.

Expire Old Backups

pgBackRest applies the retention policy automatically after backups (expire-auto). To preview or run expiration manually, use:

pig pb expire --plan    # Show what would be removed; no deletion
pig pb expire           # Apply the configured retention policy

Stanza Management

A stanza records a cluster’s backup identity, including its system identifier and major version. You may need to manage it manually:

pig pb create                    # Create a stanza; cluster initialization normally does this
pig pb upgrade                   # Update after a major-version upgrade or clone recovery
pig pb delete -s pg-meta         # Delete all backups and archives after confirmation; destructive

The usual manual upgrade case is post-clone cleanup. After you restore another cluster’s backup into a new cluster, update the stanza identity before you write new backups.

Check and Control

pig pb check     # End-to-end check of configuration, archive push, and repository access
pig pb stop      # Stop new pgBackRest operations for a maintenance window
pig pb start     # Re-enable pgBackRest operations

check verifies that WAL can reach the repository through the archive path. It is not a purely local or read-only operation.

Logs

pig pb log              # Show the latest log snapshot
pig pb log list         # List log files
pig pb log tail         # Follow the latest log
ls /pg/log/pgbackrest/  # Directory containing backup, archive, and restore logs

For pgsql-pitr.yml, PostgreSQL writes recovery output to /pg/tmp/recovery.log.

Alternative Backup Tools

pg-basebackup

The legacy /pg/bin/pg-basebackup script creates a single-file physical backup with native pg_basebackup. It uses an lz4-compressed tar stream and writes to /pg/backup by default. Use it only for a simple local copy when a pgBackRest repository is unavailable:

pg-basebackup                                      # /pg/backup/backup_<tag>_<date>.tar.lz4
pg-basebackup --dst /tmp --file backup.tar.lz4     # Explicit destination and filename

mkdir -p /tmp/data
cat /pg/backup/backup_pg-meta_20250713.tar.lz4 | unlz4 -d -c | tar -xC /tmp/data

Logical Backup

Logical backups made with pg_dump cannot provide PITR. They are suitable for cross-major-version migration, partial exports, and long-term logical snapshots. Production recovery plans commonly use logical and physical backups together. See the PostgreSQL documentation.

5 - Restore Operations

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

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

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" }}'

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.

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

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" }

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" }}'

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

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:

1. Verify the recovery state and the smallest authorized set of application checks.

2. After a cross-cluster clone, complete stanza cleanup. Create a full backup on the new timeline as soon as practical:

   pg-backup full

3. 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

6 - Clone a PG Cluster

Cloning restores production history into another cluster and leaves the production cluster unchanged. This makes cloning a useful recovery method with less risk to production. You can recover accidentally deleted data from the clone, validate backups in a drill, or inspect a historical state. You can also reset a test environment to a production snapshot.

The target must have access to the source backup repository and use a compatible PostgreSQL major version. Recovery may overwrite the target. In a shared Silo/S3 repository, a stanza isolates each cluster’s backups. Targets with the required credentials can access these backups.

Clone an Existing Cluster

Assume the four-node sandbox contains pg-meta and pg-test, sharing a Silo repository. To reset pg-test to the latest state of pg-meta, point pg_pitr to the pg-meta stanza:

pig pg list pg-test
pig pb info
./pgsql-pitr.yml -l pg-test -e '{"pg_pitr": { "cluster": "pg-meta", "archive": false, "action": "promote" }}'

Add a recovery target to clone any state inside the recovery window. For example, reset to 15:30 on December 26, 2025:

./pgsql-pitr.yml -l pg-test -e '{"pg_pitr": { "cluster": "pg-meta", "time": "2025-12-26 15:30:00+08", "archive": false, "action": "promote" }}'

These cross-cluster examples set archive: false to keep the exploratory recovery from archiving under the target stanza. After Patroni takes control, complete the stanza and archive cleanup below.

The target may also be a newly initialized empty cluster, such as pg-meta2. Use the normal cluster creation procedure to create it. Then perform cross-cluster PITR.

pgBackRest restore uses delta mode and rewrites only files that differ from the backup. Repeated drills, or a target already synchronized through a standby cluster, can therefore restore much faster than a first full restore.

For accidental deletion, validate the clone and use pg_dump to export only the affected tables or database back into production. An in-place rollback of the entire production cluster should be the last resort, not the first response.

Post-Clone Cleanup

The clone contains the source cluster’s data, while the target stanza may still record the target’s old PostgreSQL system identifier. pgBackRest refuses a backup when the identifiers do not match, preventing the new cluster from contaminating the source history.

After you validate the clone, complete these steps. A cluster restart changes the running service. First inspect the primary and replication state, schedule the maintenance window, and obtain explicit approval.

pb stanza-upgrade                           # accept the new system-id; cross-cluster clone only
psql -c 'ALTER SYSTEM RESET archive_mode;' # undo archive: false
pg restart pg-test                         # archive_mode requires a restart
pg-backup full                             # establish a recovery point on the new timeline

Until you complete these steps, scheduled backups can fail the identity check. A clone restored with archive: false produces no new WAL archive:

postgres@pg-test-1:~$ pb backup
INFO: backup command begin 2.57.0: --annotation=pg_cluster=pg-test ... --stanza=pg-test --start-fast
ERROR: [051]: PostgreSQL version 18, system-id 7588470953413201282 do not match stanza version 18, system-id 7588470974940466058
       HINT: is this the correct stanza?
INFO: backup command end: aborted with exception [051]

Rebuild Backup Identity

stanza-upgrade lets the new cluster continue writing under its existing stanza. If the clone needs a completely independent backup history, rebuild the stanza instead.

Declarative workflow:

pig pb info -s pg-test
pig pb delete -s pg-test                 # type the exact stanza when prompted
./pgsql.yml -t pg_backup -l pg-test      # create a fresh stanza
pg-backup full

Equivalent low-level workflow:

pig pb stop
pig pb delete -s pg-test                 # confirm the exact stanza
pig pb start
pig pb create -s pg-test
pig pb backup full -s pg-test

Online Copies: Standby Clusters

A PITR clone is a static snapshot. Use a streaming-replication standby cluster for an online copy that follows the source continuously. Use a delayed cluster for a fixed rollback window, such as one hour.

The three methods complement each other. Standby clusters provide a live copy. Delayed clusters preserve a fixed delay. PITR clones provide any historical state inside the recovery window and do not require an existing online replica.

Restore Drills

A clone provides an end-to-end restore drill without changes to production. It overwrites the designated drill target. Run one quarterly and after major backup changes:

1. Select a point inside the production recovery window.
2. Restore it into the drill cluster and record elapsed time as the measured PITR RTO.
3. Validate integrity with authorized row-count checks, critical-table checks, and application connectivity.
4. Complete post-clone cleanup and verify that the drill cluster can create a new backup.
5. Record timing, failures, and any difference between the runbook and reality.

See Manual Recovery for a sandbox exercise using pgBackRest primitives, or Fork an Instance for an XFS snapshot-based local test copy.