Script for testing PostgreSQL database backups and WAL files. Script downloads backup file from S3 bucket and creates a new PostgreSQL server from that backup. The script uses restore_command to replay WAL files.

restore_test.sh

#!/bin/bash

# Exit immediately if any step fails 
set -eo pipefail

# Variables

host=<hostname>
datetime=$(date +"%Y-%m-%dT%H%M")
tmp_folder=/tmp/restore_test_${datetime}
bucket=<s3_bucket_for_postgresql_backups>
wal_bucket=<s3_bucket_for_postgresql_wal_files>
postgresql_version=<postgresql_version>
server_name=restore_test
restore_conf_dir=/etc/postgresql/${postgresql_version}/${server_name}
restore_dir=/var/lib/postgresql/${postgresql_version}/${server_name}

# Functions

cleanup() {
  code=$?
  if [ ${code} != 0 ]; then
    echo ""
    echo "########################"
    echo "## test status: ERROR ##"
    echo "########################"
    echo ""
    msg="Backup restore test for ${bucket}/${path} failed on ${datetime}"
    /usr/local/bin/send_mail.py --subject "Backup restore test FAILED for ${host}" --msg "${msg}" --attachment /var/log/postgresql/postgresql-${postgresql_version}-${server_name}.log
    /usr/bin/logger -t db-restore-test "ALERT exited abnormally with [$code]"
  else
    echo ""
    echo "########################"
    echo "## test status: OK    ##"
    echo "########################"
    echo ""
    msg="Backup restore test for ${bucket}/${path} was success on ${datetime}"
    /usr/local/bin/send_mail.py --subject "Backup restore test ok for ${host}" --msg "${msg}" --attachment /var/log/postgresql/postgresql-${postgresql_version}-${server_name}.log
  fi

  echo "## cleaning up ##"
  echo "## shutting down test server ##"
  pg_ctlcluster ${postgresql_version} ${server_name} stop
  pg_dropcluster ${postgresql_version} ${server_name}
  rm -rf ${restore_dir} ${restore_conf_dir} ${tmp_folder}
  echo "## done ##"  
}

trap 'cleanup $?' EXIT

# Main code

echo "## running database server backup restore test ##"

# Get filename of latest modified file in the bucket
latest=$(aws s3api list-objects-v2 \
--bucket ${bucket} \
--prefix main \
--query 'reverse(sort_by(Contents,&LastModified))[0].Key' \
--output text)

# We are only interested in the "main/<date>" part of the filename
IFS='/' read -ra path_arr <<< "${latest}"
path="${path_arr[0]}/${path_arr[1]}"

echo "## downloading latest backup [${path}] ##"

aws s3 cp --recursive s3://${bucket}/${path} ${tmp_folder}/

# Remove old folders just in case
rm -rf ${restore_dir}
rm -rf ${restore_conf_dir}

# Create new server
echo "## create test server ##"
pg_createcluster ${postgresql_version} ${server_name}

# Clear existing files which are created by default
rm -r ${restore_dir}/*

# Unpack the backup archives
echo "## unpacking files ##"
tar -C ${restore_dir}/ -zxf ${tmp_folder}/base.tar.gz
tar -C ${restore_dir}/pg_wal -zxf ${tmp_folder}/pg_wal.tar.gz

# Init test server
touch ${restore_dir}/recovery.signal
echo "local   all             postgres                                peer" > ${restore_conf_dir}/pg_hba.conf
echo "port = 6000" >> ${restore_conf_dir}/conf.d/01-test.conf
echo "unix_socket_directories = '${restore_dir}'" >> ${restore_conf_dir}/conf.d/01-test.conf
echo "hot_standby = off" >> ${restore_conf_dir}/conf.d/01-test.conf
echo "restore_command = 'aws s3 cp s3://${wal_bucket}/%f "%p"'" >> ${restore_conf_dir}/postgresql.conf

# Set owner to postgres
chown -R postgres:postgres ${restore_dir}
chown -R postgres:postgres ${restore_conf_dir}

# Start a new Postgres server for the cluster on port 6000
echo "## starting postgresql server for restore test ##"
sudo -u postgres pg_ctlcluster start ${postgresql_version} ${server_name}

# Perform a simple test
echo "## running test query ##"
sudo -u postgres psql -h ${restore_dir}/ -p 6000 -d restore_test_db -c "select * from main limit 1"

# And now as we exit, cleanup function is called