pgBackRest Backup to S3

In this article I will show you how to use pgBackRest to backup PostgreSQL servers to S3 buckets.

For the purpose of this demo setup, we’ll use MinIO server, which is an Amazon S3 Compatible Object Storage.

s3cmd

yum install -y epel-release
yum --enablerepo epel-testing install -y s3cmd
nano ~/.s3cfg
host_base = minio.mydomain.intra:9000
host_bucket = minio.mydomain.intra:9000
bucket_location = us-east-1
use_https = false
access_key = <minop_access_key>
secret_key = <minio_secret_key>
signature_v2 = False

Create bucket and folders

s3cmd mb --no-check-certificate s3://pgbackrest
mkdir postgresql1
s3cmd cp postgresql1 --no-check-certificate s3://pgbackrest
s3cmd ls --no-check-certificate s3://pgbackrest/postgresql1
                       DIR   s3://pgbackrest/postgresql1/

Installation

yum install epel-release -y
yum update -y
rpm -Uvh https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
yum install -y pgbackrest

Configure pgBackRest

nano /etc/pgbackrest.conf

[global]
repo1-path=/postgresql1
repo1-type=s3
repo1-s3-endpoint=minio.mydomain.intra
repo1-s3-port=9000
repo1-s3-bucket=pgbackrest
repo1-s3-verify-tls=n
repo1-s3-key=<minop_access_key>
repo1-s3-key-secret=<minio_secret_key>
repo1-s3-region=us-east-1

repo1-retention-full=1
process-max=2
log-level-console=info
log-level-file=debug
start-fast=y
delta=y

[postgresql1]
pg1-path=/var/lib/pgsql/12/data

Configure PostgreSQL on postgresql1

nano /var/lib/pgsql/12/data/postgresql.conf
...
archive_mode = on
archive_command = 'pgbackrest --stanza=postgresql1 archive-push %p'
...

systemctl restart postgresql12

Perform a backup

Before we can start a backup we need to initialize the backup repository.

sudo -iu postgres pgbackrest --stanza=postgresql1 stanza-create
sudo -iu postgres pgbackrest --stanza=postgresql1 check
sudo -iu postgres pgbackrest --stanza=postgresql1 --type=full backup

Show backup information

postgresql1 -iu postgres pgbackrest info
stanza: postgresql1
    status: ok
    cipher: none

    db (current)
        wal archive min/max (11-1): 000000010000000000000005/000000010000000000000005

        full backup: 20200219-091209F
            timestamp start/stop: 2020-02-19 09:12:09 / 2020-02-19 09:12:21
            wal start/stop: 000000010000000000000005 / 000000010000000000000005
            database size: 23.5MB, backup size: 23.5MB
            repository size: 2.8MB, repository backup size: 2.8MB

Restore a backup

The restore command can then be used on the postgresql1 host.

sudo systemctl stop postgresql12

sudo -iu postgres pgbackrest --stanza=postgresql1 restore

Restore only test database:

sudo -iu postgres pgbackrest --stanza=postgresql1 --delta --db-include=test restore