Remote PostgreSQL backups with `pgbackrest`

Adapted from pgbackrest Quick Start guide. There is too much text that doesn't need to be re-read, so I have compiled the necessary parts for quick scanning and quick setup for those who are familiar but have forgetten.

Vocabulary

Cluster stanza: configuration for a PostgreSQL database cluster that defines where it is located, how it will be backed up, archiving options, etc.
Repository: where backup files will live

Environment

+-----------------+         +-----------------+
| Backup server   |         | DB host         |
| pgbackrest 2.58 |---ssh---| pgbackrest 2.58 |
| FreeBSD 15      |         | OpenBSD 7.9     |
+-----------------+         +-----------------+

Backup server has a pgbackrest user with rw permissions on the backup repository /backups/db.

Installation

Backup server

pkg install pgbackrest

DB host

pkg_add pgbackrest

Setup ssh connection from Backup to DB host and vice verse

Backup server

ssh-keygen -t ed25519 -C "backup server to DB host" -f backup_to_db

.ssh/config

Host db_host
Hostname db_host.domain.tld
User _postgresql
Port 22
PreferredAuthentications publickey
IdentityFile /path/to/backup_to_db

DB host

doas mkdir -p /var/postgresql/.ssh
doas touch /var/postgresql/.ssh/authorized_keys
# copy/paste ssh public key of backup_to_db to authorized_keys

Do this for DB host -> Backup server too.

Configure cluster stanza

Configuration reference.

Backup server

[global]
repo1-path=/backups/db
repo1-retention-full-type=count
repo1-retention-full=2
log-level-console=info
log-level-file=warn

[main]
pg1-host=main
pg1-host-user=_postgresql
pg1-path=/var/postgresql/data
pg1-user=postgres

DB host

[global]
repo1-host=pgbackrest-topoli
repo1-host-user=pgbackrest
repo1-path=/backups/db # this exists on the backup server, not locally
log-level-console=info
log-level-file=warn
log-path=/var/log/pgbackrest

[main]
pg1-path=/var/postgresql/data
pg1-user=postgres

PostgreSQL archive mode

archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'

Then restart postgresql.

Run

On DB host

sudo -u _postgresql pgbackrest --stanza=main check

On backup server

chown -R pgbackrest:pgbackrest /backups/db
sudo -u pgbackrest pgbackrest --config /etc/pgbackrest/pgbackrest.conf --stanza=main check
sudo -u pgbackrest pgbackrest --config /etc/pgbackrest/pgbackrest.conf --stanza=main stanza-create
sudo -u pgbackrest pgbackrest --config /etc/pgbackrest/pgbackrest.conf --stanza=main backup
sudo -u pgbackrest pgbackrest --config /etc/pgbackrest/pgbackrest.conf --stanza=main info

Remote PostgreSQL backups with pgbackrest