Following topic will discuss how to Backup and Restore PostgreSQL Databases directly in S3. This topic will help you to leverage AWS S3 -IA storage to use as Backup Storage for any number of PostgreSQL databases running under an instance.

This topic will cover PostgreSQL Instance running on EC2 or on-premises servers.

Step 1:- Create a new mount point or directory in the database server to use as staging location for placing the backup files before moving into S3.

Example:

 

mkdir /postgresql_bkp

chown postgres:postgres /postgresql_bkp

chmod -R 700 /postgresql_bkp

Step 2:- Now Create a bucket in S3 and inside the bucket create a directory.

Example: s3://phi-postgresql-backup/postgresql01-pgbackup

Step 3:- Now install AWS CLI in the DB server under postgres user following AWS recommended guide:-

http://docs.aws.amazon.com/cli/latest/userguide/awscli-install-linux.html

Configure AWS CLI

http://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-started.html

Step 4:- Now configure password of postgres user so during pg_dump or psql using script, the password of the user can be taken from the environment variable.

In postgres user home, create a file .pgpass

vi $HOME/.pgpass

localhost:5432:postgres:postgres:somepassword

In .bash_profile make following entry:–

vi $HOME/.bash_profile

[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/9.6/data
export PGDATA
PATH=$PATH:/usr/pgsql-9.6/bin
export PATH
PGPASSFILE=/var/lib/pgsql/.pgpass
export PGPASSFILE
PGPASSWORD=somepassword
export PGPASSWORD
SCRIPTS=/var/lib/pgsql/9.6/data/scripts
export SCRIPTS
# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile
export PATH=~/.local/bin:$PATH

Step 5:- Now we will create the scripts to backup the postgresql databases.

In Step 4, we configure $SCRIPT to point a directory /var/lib/pgsql/9.6/data/scripts where we will place our scripts.

The backup script will do the following:-

=> Backup all the databases running in the postgresql instance.

=> Move the backup files to S3 bucket in S3 – IA storage class one after another and remove the backup file from the local storage.

=> Delete backup files older than 7 days from the S3 bucket.

cd $SCRIPTS

vi pg_dump_s3.sh

#!/bin/bash

set -e

# Database credentials
PG_HOST=”localhost”
PG_USER=”postgres”

# S3
S3_PATH=”phi-postgresql-backup/postgresql01-pgbackup”

# get databases list
dbs=`psql -l -t | cut -d’|’ -f1 | sed -e ‘s/ //g’ -e ‘/^$/d’`

# Vars
NOW=$(date +”%m-%d-%Y-at-%H-%M-%S”)
#DIR=”$( cd “$( dirname “${BASH_SOURCE[0]}” )” && pwd )”
DIR=”/postgresql_bkp/pgsql_backup_logical”
SCRIPT=”/var/lib/pgsql/9.6/data/scripts”
echo “PostgreSQL Database Logical Backup is started and stored in s3://phi-postgresql-backup/postgresql01-pgbackup”
for db in $dbs; do
if [ “$db” != “template0” ] && [ “$db” != “template1″ ]; then
# Dump database
pg_dump -Fc -h $PG_HOST -U $PG_USER $db > $DIR/”$NOW”_”$db”.dump
# Copy to S3
aws s3 cp $DIR/”$NOW”_”$db”.dump s3://$S3_PATH/”$NOW”_”$db”.dump –storage-class STANDARD_IA
echo “* Database:” $db “is archived at timestamp:” $NOW
# Delete local file
rm $DIR/”$NOW”_”$db”.dump
fi
# Log
#echo “* Database: ” $db “is archived at timestamp: ” $NOW
done

# Delere old files
echo “Following backups older than 7 days are deleted from s3://phi-postgresql-backup/postgresql01-pgbackup”;
$SCRIPT/s3_autodelete.sh $S3_PATH “7 days”

vi s3_autodelete.sh

#!/bin/bash

# Usage:
# ./s3_autodelete.sh bucket/path “7 days”

set -e

# Maximum date (will delete all files older than this date)
maxDate=`date +%s –date=”-$2″`

# Loop thru files
aws s3 ls s3://$1/ | while read -r line; do
# Get file creation date
createDate=`echo $line|awk {‘print $1″ “$2’}`
createDate=`date -d”$createDate” +%s`

if [[ $createDate -lt $maxDate ]]
then
# Get file name
fileName=`echo $line|awk {‘print $4’}`
if [[ $fileName != “” ]]
then
echo “* Delete $fileName”;
aws s3 rm s3://$1/$fileName
fi
fi
done;

Step 6:- Now schedule the script in cron pg_dump_s3.sh to run every day to take the latest backup to S3 and delete the old backup from S3.
sudo crontab -u postgres -e

0 23 * * * /var/lib/pgsql/9.6/data/scripts/pg_dump_s3.sh > /var/lib/pgsql/9.6/data/log/pg_dump_s3-`date +\%Y\%m\%d\%H\%M\%S` 2>&1

Step 7:- This step will explain the restoration part from S3. Only consideration made in this step is the DB name is a single word name which the script will fetch from the backup file name itself. If the DB name contain any special character then the italic line in the restoration script need to change accordingly to fetch the DB name.

The script will do the following:-

=> From the list of available backups in the S3 bucket, the script will identify the database name and the backup file.

=> Fetch the backup from S3 to local storage.

=> Drop old database and create new database with the same owner, tablespace and privileges.

=> Restore the dump to database.

cd $SCRIPTS

vi pg_restore_s3.sh
#!/bin/bash
set -e

# Usage:
# ./pg_restore_s3.sh backup_file_name

AWS_BUCKET=”phi-postgresql-backup/postgresql01-pgbackup”
DUMP_OBJECT=$1
DIR=”/postgresql_bkp/pgsql_backup_logical”

echo “Postgres restore from s3 – looking for dump in s3 at s3://phi-postgresql-backup/postgresql01-pgbackup”
if [ -n “${DUMP_OBJECT}” ]; then
objectSet=$(aws s3 ls s3://${AWS_BUCKET}/${DUMP_OBJECT}| awk ‘{print $4 }’)
if [ -z “$objectSet” ]; then
echo “Backup file not found in s3 bucket”
else
echo “Downloading dump from s3 – $object”
aws s3 cp s3://phi-postgresql-backup/postgresql01-pgbackup/${DUMP_OBJECT} ${DIR}/${DUMP_OBJECT}
dbName=$(echo $DUMP_OBJECT | awk -F _ ‘{print $2 }’| awk -F . ‘{print $1 }’)
echo “dropping old database $dbName”
dbPgOwner=$(echo “SELECT r.rolname FROM pg_catalog.pg_database d JOIN pg_catalog.pg_roles r ON d.datdba = r.oid JOIN pg_catalog.pg_tablespace t on d.dattablespace
= t.oid WHERE d.datname= ‘$dbName’;”| psql 2>&1)
dbOwner=$(echo $dbPgOwner | awk ‘{print $3 }’)
dbPgTblspc=$(echo “SELECT t.spcname FROM pg_catalog.pg_database d JOIN pg_catalog.pg_roles r ON d.datdba = r.oid JOIN pg_catalog.pg_tablespace t on d.dattablespace
= t.oid WHERE d.datname= ‘$dbName’;”| psql 2>&1)
dbTblspc=$(echo $dbPgTblspc | awk ‘{print $3 }’)
PRE_RESTORE_PSQL=”GRANT ALL PRIVILEGES ON DATABASE $dbName to $dbOwner; REVOKE connect ON DATABASE $dbName FROM PUBLIC;”
dropResult=$(echo “SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = ‘$dbName’; \
DROP DATABASE $dbName;” | psql 2>&1)
if echo $dropResult | grep “other session using the database” -> /dev/null; then
echo “RESTORE FAILED – another database session is preventing drop of database $dbName”
exit 1
fi
createResult=$(echo “CREATE DATABASE $dbName OWNER $dbOwner tablespace = $dbTblspc;” | psql 2>&1)
echo “postgres restore from s3 – filling target database with dump”
if [ -n “$PRE_RESTORE_PSQL” ]; then
echo “postgres restore from s3 – executing pre-restore psql”
printf %s “$PRE_RESTORE_PSQL” | psql
fi
pg_restore -d $dbName $DIR/$DUMP_OBJECT
fi
echo “Postgres restore from s3 – complete – $DUMP_OBJECT $dbName”
else
echo “Please pass the backup dump file name as script argument”
fi

Using the scripts above, you are storing the backup of multiple postgresql databases directly in S3 and also restoring particular dump file to postgresql without any manual effort.

One Reply to “PostgreSQL Database Backup to S3 and Restore from S3 using simple shell script”

  1. Hi SAMINDRADEY,
    Awesome scripts for backup and restore on postgresql Database. Additionally i would like to ask some details about the database migration in different platforms..can you please share some materials and ideas regarding database migration from oracle to postgreSQL. How we can migrate all oracle objects (tables/indexes/procedures/packages/views/mviews/sequences/dblinks/synonym/partition tables etc) without any issues. main Objective is same functional tasks has to be performed in postgreSQL as well. What are the restrictions and limitations for the migrations.

    Thanks,
    Poshan

Leave a Reply

Your email address will not be published. Required fields are marked *