PostgreSQL Backups

Basic steps to create bare minimum backups.

Sentinel is using PostgreSQL database for all analytics/reports/history data. PostgreSQL should be done on regular bases to eliminate possible data losses in case of disasters.

Standalone Hot Backup

This is the standalone backups which provide fast and consistent data snapshot. These are backups that cannot be used for point-in-time recovery, yet are typically much faster to backup and restore than pg_dump dumps. This backups can not be used for PostgreSQL version migration and can created and restored only on the same major version.

Create standalone hot backup

Create folder for backups

mkdir /data/pg_backup/
chown postgres:postgres /data/pg_backup/

Run command to create backup:

sudo -u postgres pg_basebackup -P -Ft -z -Z4 -v -Xs -R -D /data/pg_backup/$(date +"+%Y-%m-%d")/

On finish this command will create hot backup of the PostgreSQL cluster. It will create folder with current date which will have two files base.tar.gz and pg_wal.tar.gz

Restore hot backup

Assuming following

• postgres-snt - PostgreSQL service name for Sentinel,

• /opt/pdgata/ - PostgreSQL data directory specified as PGDATA,

• /data/pg_backup/2022-11-07 - Folder with backup which be restored.

Create folder for PostgreSQL and ensure that its empty:

mkdir -p /opt/pgdata/
rm -rf /opt/pgdata/*

Extract data in this folder from preferred backup

tar xvf /data/pg_backup/2022-11-07/base.tar.gz -C /opt/pgdata/
tar xvf /data/pg_backup/2022-11-07/pg_wal.tar.gz -C /opt/pgdata/pg_wal/

Ensure valid permissions is set

chown -R postgres:postgres /opt/pgdata
chmod -R 0700 /opt/pgdata

Restart your PostgreSQL service

systemctl restart postgres-snt

Connect to database and check status:

sudo -u postgres psql sentinel

Run to check status:

SELECT pg_is_in_recovery();
> t
SELECT pg_promote();
> t
SELECT pg_is_in_recovery();
> f

PostgreSQL cluster is ready to be used and Sentinel service can be started.