Backup & Restore

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

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 Policy

The chart below combines the “Recovery Window” and “Backup Storage Usage” on a single timeline (0~108h) so they can be inspected together.

Under the same assumptions (database size 100GB, daily writes 10GB), it shows how both metrics evolve over 30 days with “weekly full + daily incremental” backups and 14-day full-backup retention.

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

When to Backup

The first question is when to backup your database - this is a tradeoff between backup frequency and recovery time. Since you need to replay WAL logs from the last backup to the recovery target point, the more frequent the backups, the less WAL logs need to be replayed, and the faster the recovery.

Daily Full Backup

For production databases, it’s recommended to start with the simplest daily full backup strategy. This is also Pigsty’s default backup strategy, implemented via 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 your database size is 100GB, daily writes are 10GB, and each full backup takes 1 hour. Under this daily-full local-repo strategy, recovery window and backup storage evolve as shown below:

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 time-based retention of 14 days, the steady-state recovery window is roughly 14–21 days. The exact window still depends on successful backup and WAL archival runs.

Assuming your database size is 100GB and writes 10GB of data per day, the backup size is as follows:

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

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

• 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

2 - Restore Operations

Pigsty provides three restore entry points. They share the same parameter semantics, but serve different scopes:

For a hands-on sandbox drill, see Manual Recovery. To recover into another cluster without changing 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 }

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

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

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

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

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:

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

3 - Clone a PG Cluster

Cloning is one of the safest and most useful applications of recovery: leave production untouched and restore its historical state into another cluster. You can recover accidentally deleted data from the clone, validate backups in a drill, inspect a historical state, or reset a test environment to a production snapshot.

The target must be able to access the source backup repository, may be overwritten, and must use a compatible PostgreSQL major version. With a shared Silo/S3 repository, each cluster’s backups are isolated by a stanza and visible to targets holding the required credentials.

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. Create it through the normal cluster creation workflow, 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 validating the clone, complete these steps. Restarting the cluster is a service change: 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 this is complete, scheduled backups can fail the identity check, and 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 should start a completely independent backup history, rebuild that 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 a continuously following online copy, or 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, and PITR clones expose any historical state inside the recovery window without requiring a pre-existing online replica.

Restore Drills

A clone is an end-to-end restore drill that does not touch production, although it does overwrite 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.

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

5 - Backup Repository

Two parameters decide where backups are stored: pgbackrest_repo defines candidate repositories, while pgbackrest_method selects one. Repository keys are rendered deterministically as pgBackRest repo1-* options, so any supported pgBackRest repository option can be used directly. Pigsty v4.5.0 renders only the entry selected by pgbackrest_method as repo1; keeping several candidate keys does 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 deliberately differ. local favors 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, dependent differential/incremental backups and the WAL needed only by that chain expire with it.

• retention_full_type: count plus retention_full: 2 keeps the two newest full chains; a third can exist briefly while a new full completes.
• retention_full_type: time plus retention_full: 14 establishes a minimum time window. An old full does not expire until another full is at least 14 days old; with weekly full backups this usually leaves three chains and roughly 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 when deployed 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 }}

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

For serious production use, deploy and test a fault-tolerant multi-node/multi-drive object store; see MINIO Configuration. The preset name minio does not require the server to be managed by Pigsty: independently operated MinIO, RustFS, or another compatible implementation can use it, but that service’s installation, upgrades, certificates, and lifecycle remain outside the MINIO role’s support boundary.

Use S3 or Cloud Object Storage

For a single database node, an off-host cloud object store is often the most valuable 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, mapped from pg_cluster, 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. It still shares the same storage system and control plane, so it does not replace an independent off-site or offline copy. Enable it for a bucket when it is created:

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. A retained object version cannot be changed or permanently deleted until its retention period ends. A normal delete can still create a delete marker that hides the current object while historical versions remain and continue consuming 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 }

It does not set a default retention period. Configure GOVERNANCE or COMPLIANCE retention with mcli retention set or the storage console, then verify 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. Test backup, expiration, delete-marker cleanup, and version recovery on a non-production bucket before enabling it.

Switch Repositories

After changing a repository definition or pgbackrest_method, rerender configuration, initialize the stanza, and create a recovery point in the new repository promptly:

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

Existing backups are not migrated automatically. While retained, the old repository can still be selected as a restore source through pg_pitr.repo.

6 - Admin Commands

Run backup commands as the database superuser (pg_dbsu, postgres by default) on a database node. You can use any of these entry points:

• 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 when the cluster is created, backup is enabled automatically. If it was disabled at creation time, 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; the playbook ignores a failed attempt and leaves no marker. This file only prevents the initialization task from repeating, so always verify actual repository state with pig pb info or pgbackrest info. Define scheduled backups with pg_crontab; see Backup Policy.

Remove Backup

pig pb delete is the preferred interface when only a backup stanza must be removed. It asks for interactive confirmation; with a multi-stanza configuration, the target must also be explicit. 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

When a primary instance (pg_role = primary) is removed, pgsql-rm.yml also tries to delete the cluster’s backup stanza by default. Every command below changes or deletes state; never execute one merely by copying the 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, record the recovery requirement, and have the operator re-enter the exact cluster/stanza name. Set pg_rm_backup to false to preserve backups while removing the cluster.

pgsql-rm.yml -t pg_backup forcibly runs pgbackrest stanza-delete on the primary, removes the local repository directory in local mode, then removes the pgBackRest configuration and initial-backup marker. The task ignores some deletion errors, so a successful playbook result does not prove that repository objects were physically removed. Prefer pig pb delete when only the stanza needs deletion because it supplies a plan and confirmation guard.

With object versioning and object-lock retention, deletion may create a delete marker while locked historical versions continue consuming storage until their retention period expires.

Manual Backup

You can trigger a backup outside the crontab schedule. Both pg-backup and pig pb backup check that the current instance is primary and 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, but production runs should still be scheduled for low-traffic periods.

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 ending in F, D, and I identify full, differential, and incremental backups. The portion before an underscore identifies the full backup anchoring that chain. The WAL archive range and the oldest usable full backup together bound 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

The configured retention policy is applied automatically after backups (expire-auto). Preview or run expiration manually with:

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. Manual management is occasionally required:

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 restoring another cluster’s backup into a new cluster, update the stanza identity before new backups can be written.

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 performs an archive-path check rather than being purely local or read-only; it verifies that WAL can reach the repository.

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 recovery output is written to /pg/tmp/recovery.log.

Alternative Backup Tools

pg-basebackup

The legacy /pg/bin/pg-basebackup script creates a single-file physical backup using native pg_basebackup, an lz4-compressed tar stream, and /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, but they are appropriate 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.