Skip to content

Backup Policy

Design backup policies according to your needs

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.

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:

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

Was this page helpful?

Last Modified 2026-10-12 · update extensions (4d4b1d96)