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 postgresql12
s3cmd cp postgresql1 --no-check-certificate s3://pgbackrest
s3cmd ls --no-check-certificate s3://pgbackrest/postgresql12
                       DIR   s3://pgbackrest/postgresql12/

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=/postgresql12
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

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

Configure PostgreSQL on postgresql12 server

nano /var/lib/pgsql/12/data/postgresql.conf
...
archive_mode = on
archive_command = 'pgbackrest --stanza=postgresql12 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=postgresql12 stanza-create
sudo -iu postgres pgbackrest --stanza=postgresql12 check

sudo -iu postgres pgbackrest --stanza=postgresql12 --type=full backup

Show backup information

$ sudo -iu postgres pgbackrest info
stanza: postgresql12
    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 postgresql host.

sudo systemctl stop postgresql12

sudo -iu postgres pgbackrest --stanza=postgresql12 restore

Restore only test database:

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