pgBackRest monitoring setup for dbWatch

Purpose

The pgBackRest monitoring jobs in dbWatch depend on backup metadata being available in a PostgreSQL table, normally:

dbwatch_cc.pgbackrest_backup_log

The table name is configurable through the job parameter pgbackrest output table.

Additional resources

Example scripts for configuring pgBackRest backups and collecting backup metadata for dbWatch monitoring are available from GitHub:
Github link

Files to include

createpgbackrest.sql
Creates schema dbwatch_cc and table pgbackrest_backup_log.

get_pgbackrest.sh
Runs `pgbackrest info —stanza=main`, parses FULL, DIFF and INCR backup metadata, and inserts new backup records into the monitoring table.

run_backup_full.sh
bc. sudo -u postgres pgbackrest —type=full —stanza=main backup

run_backup_diff.sh
bc. sudo -u postgres pgbackrest —type=diff —stanza=main backup

run_backup_incr.sh
bc. sudo -u postgres pgbackrest —type=incr —stanza=main backup

Suggested directory

/opt/dbwatch/pgbackrest/

Setup steps

1. Create the monitoring table in the PostgreSQL database monitored by dbWatch:

psql -h <postgres_host_or_vip> -U postgres -d postgres -f /opt/dbwatch/pgbackrest/createpgbackrest.sql

2. Edit `get_pgbackrest.sh` and verify these values:

PG_USER="postgres"
PG_DATABASE="postgres"
PG_SCHEMA="dbwatch_cc"
PG_TABLE="pgbackrest_backup_log"
STANZA="main"
VIP_HOST="<postgres_vip_or_haproxy_ip>"

3. Make the scripts executable:

chmod 700 /opt/dbwatch/pgbackrest/*.sh

4. Add cron scheduling, for example:

# Full backup every Sunday at 02:00
0 2 * * 0 /usr/bin/flock -n /tmp/pgbackrest.lock /opt/dbwatch/pgbackrest/run_backup_full.sh >> /var/log/pgbackrest_backup_full.log 2>&1

# Incremental backup every 2 hours

0 */2 * * * /usr/bin/flock -n /tmp/pgbackrest.lock /opt/dbwatch/pgbackrest/run_backup_incr.sh >> /var/log/pgbackrest_backup_incr.log 2>&1

# Differential backup Monday-Saturday at 02:00

0 2 * * 1-6 /usr/bin/flock -n /tmp/pgbackrest.lock /opt/dbwatch/pgbackrest/run_backup_diff.sh >> /var/log/pgbackrest_backup_diff.log 2>&1

# Collect pgBackRest metadata every 30 minutes

*/30 * * * * /opt/dbwatch/pgbackrest/get_pgbackrest.sh >> /var/log/get_pgbackrest.log 2>&1

dbWatch monitoring jobs

The following dbWatch jobs use the data from the pgBackRest output table:

pgBackRest FULL backup status
pgBackRest DIFF backup status
pgBackRest INCR backup status

All three jobs have the parameter:

pgbackrest output table = dbwatch_cc.pgbackrest_backup_log

The table does not have to be in the dbWatch schema, but it must be accessible from the monitored PostgreSQL database.

Notes

The `flock` lock prevents overlapping pgBackRest backup jobs.
`get_pgbackrest.sh` should be scheduled independently from the backup jobs so dbWatch has updated metadata.
The `VIP_HOST` value should normally point to the PostgreSQL VIP, Patroni VIP, HAProxy address, or another stable connection endpoint.
The stanza name must match the pgBackRest configuration.