pgBackRest

There are a variety of methods for creating a copy of a database:

Logical dumps
VM snapshots
Standby with delayed replication
File system snapshots
Rsync

For specialized caes all of these methods may be useful, but pgBackRest is capable enough to be deployed nearly any kind of PostgreSQL installation.

Configuration

First we need a minimal configuration

#!/bin/sh
pgver=16

cat > /etc/pgbackrest.conf << EOF
[global]
repo1-path=/pgbackup/$(hostname -s)
repo1-retention-full=2
process-max=4
compress-type=lz4

[main]
pg1-path=/pg_data/${pgver}
EOF

Initialization

To automatically initialize or upgrade after a release

backup_path=/pgbackup/$(hostname -s)
[ -d $backup_path ] || mkdir $backup_path

pgbackrest --stanza=main check
case $? in
    28)
        pgbackrest --stanza=main --log-level-console=info stanza-upgrade
        ;;
    55)
        pgbackrest --stanza=main --log-level-console=info stanza-create
        ;;
esac
pgbackrest --stanza=main check

Verifying Archive Status

SELECT pg_walfile_name(pg_switch_wal());

postgres=# select last_archived_wal,last_archived_time from pg_stat_archiver;
    last_archived_wal     |      last_archived_time
--------------------------+-------------------------------
 000000050000683C00000023 | 2019-10-22 10:13:50.134754-04
(1 row)

This view will also report the number of failed archives attempts

postgres=# SELECT * FROM pg_stat_archiver;
-[ RECORD 1 ]------+------------------------------
archived_count     | 923
last_archived_wal  | 000000050000683F000000BC
last_archived_time | 2019-10-23 13:33:42.543887-04
failed_count       | 0
last_failed_wal    |
last_failed_time   |
stats_reset        | 2019-10-22 10:13:21.440249-04

Reset these stats with

postgres=# SELECT * FROM pg_stat_reset_shared('archiver');

Testing the Restore

rsync -va --delete /pgbackup/db1/ /pgbackup/db2.restore

ssh db2
rcctl stop postgresql
cd /pg_data
mv 16 16.old && mkdir 16
pgbackrest --stanza=main --repo1-path=/pgbackup/db2.restore --log-level-console=warn restore

High Write Loads

If a database is under high write load over an extended period of time, single-process WAL archiving may not be able to keep up. pgBackRest also supports a work queue which uses a staging directory.

  archive-async=y
  spool-path=/pg_data/pgbackrest_spool

With this in place multiple workers will be used to compress and copy WAL files.

Use a spool directory on the same partition to ensure that rename(2) does not result in copying data across volumes.

Restore Options

To specify a specific point in time

--target-action=promote
--type=time --target='2023-03-31 12:00:00'

Adjust target parameters without copying all data

--delta