crontab

#m h   dom mon dow   command
30 06  *   *   0     pgbackrest --type=full --stanza=staging backup
30 06  *   *   1-6   pgbackrest --type=diff --stanza=staging backup

pgbackrest.local.conf

[staging]
pg1-path=/var/lib/pgsql/9.6/data
[global]
repo1-cipher-pass=< openssl rand -base64 48 >
repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-diff=1 // two differentials will need to be performed before one is expired.
repo1-retention-full=2
start-fast=y
stop-auto=y
[global:archive-push]
compress-level=3

pgbackrest.md

Installation

sudo yum install pgbackrest

Setup envdir

sudo mkdir -p /etc/wal-e.d/env
sudo chmod 750 /etc/pgbackrest.d
sudo su
echo "<secret-key-content>" > /etc/pgbackrest.d/env/PGBACKREST_REPO1_S3_KEY
echo "<access-key>" > /etc/pgbackrest.d/env/PGBACKREST_REPO1_S3_KEY_SECRET
echo 's3://integra-postgres-backups-<environment>' > /etc/pgbackrest.d/env/PGBACKREST_REPO1_S3_BUCKET
chown -R root:postgres /etc/pgbackrest.d
chmod -R o-rwx /etc/pgbackrest.d/env
exit

pgbackrest createstanza

sudo -u postgres pgbackrest --stanza=staging --log-level-console=info stanza-create

pgbackrest push

sudo -u postgres pgbackrest --stanza=staging --log-level-console=info backup

pgbackrest full backup

sudo -u postgres pgbackrest --stanza=staging --type=full --log-level-console=detail backup'

pgbackrest diff backup

sudo -u postgres pgbackrest --stanza=staging --type=diff --log-level-console=info backup

Get info on backups

sudo -u postgres pgbackrest info

Restore

Stop cluster and remove data directory

sudo pg_ctlcluster 9.4 demo stop
sudo -u postgres rm /var/lib/postgresql/9.6/staging/ -mindepth 1 -delete

Restore backup and start postgres

sudo -u postgres pgbackrest --stanza=staging restore
sudo pg_ctlcluster 9.6 staging start

pgbackrest.s3.conf

[staging]
pg1-path=/var/lib/pgsql/9.6/data
[global]
process-max=4 // File creation time in S3 is relatively slow so commands benefit by increasing process-max to parallelize file creation
repo1-cipher-type=none
repo1-path=/staging-repo
repo1-retention-diff=1 // two differentials will need to be performed before one is expired.
repo1-retention-full=2
repo1-s3-bucket=staging-backup
repo1-s3-endpoint=s3.amazonaws.com
repo1-s3-key=$PGBACKREST_REPO1_S3_KEY
repo1-s3-key-secret=$PGBACKREST_REPO1_S3_KEY_SECRET
repo1-s3-region=us-east-1
repo1-type=s3
start-fast=y
stop-auto=y
[global:archive-push]
compress-level=3

pgbackrest_s3.json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::demo-bucket"
            ],
            "Condition": {
                "StringEquals": {
                    "s3:prefix": [
                        "",
                        "demo-repo"
                    ],
                    "s3:delimiter": [
                        "/"
                    ]
                }
            }
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::demo-bucket"
            ],
            "Condition": {
                "StringLike": {
                    "s3:prefix": [
                        "demo-repo/*"
                    ]
                }
            }
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::demo-bucket/demo-repo/*"
            ]
        }
    ]
}

postgresql.conf

archive_command = 'pgbackrest --stanza=staging archive-push %p'
archive_mode = on 
listen_addresses = '*'
log_line_prefix = 
max_wal_senders = 3
wal_level = hot_standby