PostgreSQL Backup Script

This script backups a PostgreSQL cluster. 

It is assumed that this cluster is STIG'd using DISA STIG guidelines.  SSL is turned on and configured correctly.

The following parameters are set in postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'cp %p /data/pgsql/9.6/wals/%f'

For this case:
PGDATA=/data/pgsql/9.6/data
BKUPDIR=/var/lib/pgsql/9.6/backup

For security reason, SSL is turned on.  The following is set in postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

It is assumed that valid certs have been installed for PostgreSQL.

The pg_hba.conf file looks like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostssl         all     all     10.10.10.123/32        cert clientcert=1 map=ssl-test
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv4 network connections
hostnossl    all          all             0.0.0.0/0            reject

The pg_ident.hba file looks like this:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
ssl-test        postgres.dmn.org       postgres

The backup script is shown below:

#!/bin/sh

# pg_basebackup won't work with hostssl and cert clientcert=1
# so, we do the backup using the API
LABEL="Base Backup on $(/bin/hostname) on $(/bin/date +%Y%m%d%S)"
DT=`/bin/date +%Y%m%d%S`
BKUPDIR=/var/lib/pgsql/9.6/backup
HOST=pgs01.dmn.mil
STR="hostssl replication postgres 10.10.10.123/32 trust"

echo $STR
/bin/echo $STR >> /data/pgsql/9.6/data/pg_hba.conf

/usr/pgsql-9.6/bin/pg_ctl reload

/usr/pgsql-9.6/bin/pg_basebackup -D ${BKUPDIR}/${DT} --xlog-method=fetch --format=tar --write-recovery-conf --progress --verbose --username=postgres --no-password --gzip --host=${HOST} --label="${LABEL}"

/bin/sed -i '/hostssl replication postgres 10.10.10.123\/32 trust/d' /data/pgsql/9.6/data/pg_hba.conf
/usr/pgsql-9.6/bin/pg_ctl reload

Before the backup begins, the script adds the hostssl replication line defined by STR above and does a pg_ctl reload so that the changes in pg_hba.conf are picked by the postgres server.  This allows a trusted connection to be made from the local server using SSL (or you could simply change this to 127.0.0.1).

The backup is executed using pg_basebackup.

After the backup is completed, the hostssl replication line is removed and the pg_hba.conf file is reloaded.  This is done for STIG compliance.

The backup is run from root's cron once a week:

# PostgreSQL Weekly Full Backups on Sundays
0 0 * * 0 /bin/su -l postgres -c "/bin/sh /data/pgsql/bkup.sh"