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:

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:

mkdir -m 770 /var/log/pgbackrest
chown postgres:postgres /var/log/pgbackrest
mkdir /etc/pgbackrest

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

[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 recoveryopen in new window, 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

Full backup copies all files in a database cluster.

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.

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.

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

Backup restore

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

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.

See also

To monitor Bun performance, you can use OpenTelemetry instrumentation that comes with Bun and Uptrace.

Uptrace is a source-available APMopen in new window powered by OpenTelemetry and ClickHouse. It allows you to identify and fix bugs in production faster knowing what conditions lead to which errors.

You can get startedopen in new window with Uptrace by downloading a DEB/RPM package or a pre-compiled Go binary.