pgBackRest: PostgreSQL S3 backups

This tutorial explains how to backup PostgreSQL database using pgBackRestopen in new window and S3.

Introduction

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.
• Encryption.
• And many moreopen in new window.

Installation

Ubuntu provides pre-compiled packages for pgbackrest:

1. sudo apt install pgbackrest

Terms

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.

Configuration

Let’s create a basic directory structure for configs and logs:

1. mkdir -m 770 /var/log/pgbackrest
2. chown postgres:postgres /var/log/pgbackrest
3. mkdir /etc/pgbackrest

And save the following config in /etc/pgbackrest/pgbackrest.conf:

1. [demo]
2. pg1-path=/var/lib/postgresql/14/main

4. [global]
5. repo1-retention-full=3 # keep last 3 backups
6. repo1-type=s3
7. repo1-path=/s3-path
8. repo1-s3-region=us-east-1
9. repo1-s3-endpoint=s3.amazonaws.com
10. repo1-s3-bucket=s3_bucket_name
11. repo1-s3-key=$AWS_ACCESS_KEY
12. repo1-s3-key-secret=$AWS_SECRET_KEY

14. # Force a checkpoint to start backup immediately.
15. start-fast=y
16. # Use delta restore.
17. delta=y

19. # Enable ZSTD compression.
20. compress-type=zst
21. compress-level=6

23. log-level-console=info
24. log-level-file=debug

For point-in-time recoveryopen in new window, you also need to configure PostgreSQL to upload WAL files to S3:

1. archive_mode = on
2. archive_command = 'pgbackrest --stanza=demo archive-push %p'
3. archive_timeout = 300

Full backup

Full backup copies all files in a database cluster.

1. sudo -u postgres pgbackrest --type=full --stanza=demo backup

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

1. sudo -u postgres pgbackrest --type=diff --stanza=demo backup

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

1. sudo -u postgres pgbackrest --type=incr --stanza=demo backup

Backup restore

To restore the cluster from the last backup:

1. sudo -u postgres pgbackrest --stanza=demo --delta restore

To view all available backups:

1. sudo -u postgres pgbackrest --stanza=demo info

Conclusion

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.