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.
Full + Incremental Backup
You can optimize backup space usage by adjusting these parameters.
If using MinIO / S3 as a centralized backup repository, you can use storage space beyond local disk limitations. 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 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
When used with the built-in minio backup repository, this provides a guaranteed 1-week PITR recovery window.
Assuming your database size is 100GB and writes 10GB of data per day, the backup size is as follows:
Backup Location
By default, Pigsty provides two default backup repository definitions: local and minio backup repositories.
local: Default option, uses local/pg/backupdirectory (symlink topg_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
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.