pgBackRest: PostgreSQL S3 backups
This tutorial explains how to backup PostgreSQL database using pgBackRest and S3.
pgBackRest is a modern PostgreSQL Backup & Restore solution that has all the features you may ever need:
- Parallel backup and restore.
- Full, differential, and incremental backups.
- Delta restore.
- ZSTD compression.
- And many more.
Ubuntu provides pre-compiled packages for pgbackrest:
sudo apt install pgbackrest
Stanza is a pgBackRest configuration for a PostgreSQL database cluster. Most db servers only have one db cluster and therefore one stanza.
Repository is where pgBackRest stores backups and archives WAL segments.
Let's create a basic directory structure for configs and logs:
mkdir -m 770 /var/log/pgbackrest chown postgres:postgres /var/log/pgbackrest mkdir /etc/pgbackrest
And save the following config in
[demo] pg1-path=/var/lib/postgresql/14/main [global] repo1-retention-full=3 # keep last 3 backups repo1-type=s3 repo1-path=/s3-path repo1-s3-region=us-east-1 repo1-s3-endpoint=s3.amazonaws.com repo1-s3-bucket=s3_bucket_name repo1-s3-key=$AWS_ACCESS_KEY repo1-s3-key-secret=$AWS_SECRET_KEY # Force a checkpoint to start backup immediately. start-fast=y # Use delta restore. delta=y # Enable ZSTD compression. compress-type=zst compress-level=6 log-level-console=info log-level-file=debug
For point-in-time recovery, you also need to configure PostgreSQL to upload WAL files to S3:
archive_mode = on archive_command = 'pgbackrest --stanza=demo archive-push %p' archive_timeout = 300
Full backup copies all files in a database cluster.
sudo -u postgres pgbackrest --type=full --stanza=demo backup
Differential backup only copies files that have changed since the last full backup. It is smaller than a full backup, but to restore it you will need the base full backup.
sudo -u postgres pgbackrest --type=diff --stanza=demo backup
Incremental backup only copies files that have changed since the last backup (full, differential, or incremental). It is smaller than a full or differential backup, but to restore it you will need all dependant backups.
sudo -u postgres pgbackrest --type=incr --stanza=demo backup
To restore the cluster from the last backup:
sudo -u postgres pgbackrest --stanza=demo --delta restore
To view all available backups:
sudo -u postgres pgbackrest --stanza=demo info
pgBackRest is a reliable backup tool that requires miminum configuration. To achieve a good balance between backup size and restoration time, you can create a full backup weekly and a differential/incremental backup daily.