Automated PostgresDB recovery script

The script below will recover a compressed sql_dump of a postgres DB (from /data/db-backups) into a new clean DB structure using the path /data/db/db-XXXX where XXXX is the PROD, DEV or STAGE environments. You can edit it to remove the 3 environments and make it one. It also calls the /etc/init.d/pg-XXXX service script to start or stop the environment. Postgres supports multiple DB’s starting and stopping on the same server using sym-links to the /etc/init.d/postgresql start/stop script.

To support multiple environments, you can put the DB parameters into files in /etc/sysconfig/pgsql directory, each must be the same name as the service file. I use pg-prod, pg-dev and pg-stage for my 3 environments. The pg-prod file contains the following:

/etc/sysconfig/pgsql/pg-prod

PGDATA=/data/db/db-prod
PGPORT=15432
PGLOG=/data/db/db-prod/pg-prod-startup.log
echo "Executing PG-PROD environment"

NOTE: Each environment would have a diffeent port and path structure.

#!/bin/bash
#
#
# Create a DB and insert the required environment
#
# use DBENV to specify PROD, DEV or STAGE
#
#
# Common loging code
#
function LogStart
{
echo "====== Log Start =========" >> $LF
echo "Time: `date`" >> $LF
echo " " >> $LF
}


function LogEnd
{
echo " " >> $LF
echo "Time: `date`" >> $LF
echo "====== Log End =========" >> $LF
}

FILE_DATE=`date '+%Y-%m-%d'`
LF_DIR=/logs/db-recover
LF=$LF_DIR/db-recover-$FILE_DATE.log
mkdir -p $LF_DIR
chmod 777 $LF_DIR
touch $LF
chmod 664 $LF


#
# create a new DB shell outside of a mounted filesystem
# then mov it into place
#
function BuildEmptyDB
{
echo "Building empty DB"
echo "Building empty DB" >> $LF
rm -fR /data/database.new > /dev/null 2>&1
su - postgres -c "initdb -D /data/database.new" >> $LF
}




function RecoverLastFile
{
echo "Last DB extract"
echo "==============="
SQLFILE=`ls -1t /data/db-backups/*$DBENV*.sql.gz | head -1`
if [ $? -eq 2 ]
then
echo "ERROR - no SQL file to recover from"
else
echo "File to recover [$SQLFILE ]" >> $LF
echo $SQLFILE
echo "Recovery begun `date`" >> $LF
echo "Recovery begun `date`"
su - postgres -c "gunzip -c $SQLFILE | psql -p $DBPORT tempdatabase" >> $LF 2>&1
echo "Recovery ended `date`" >> $LF
echo "Recovery ended `date`"
fi
}


LogStart
#
# test cmd line need prod,dev or stage
#
if [ $# -ne 1 ]
then
echo "ERROR - no environment PROD,DEV etc defined"
echo "ERROR - no environment PROD,DEV etc defined" >> $LF
else
echo "Environment [$DBENV]" >> $LF
DBENV=`echo $1| tr [A-Z] [a-z]`
case $DBENV in
"prod")
DBPATH=/data/db/db-prod
DBPORT=15432
;;
"dev")
DBPATH=/data/db/db-dev
DBPORT=25432
;;
"stage")
DBPATH=/data/db/db-stage
DBPORT=35432
;;
*)
echo "ERROR - invalid env [$DBENV] specified" >> $LF
echo "ERROR - invalid env [$DBENV] specified"
DBENV=""
;;
esac

if [ $DBENV != "" ]
then
echo "Path[$DBPATH]" >> $LF
echo "Stopping service [pg-$DBENV]"
echo "Stopping service [pg-$DBENV]" >> $LF
service pg-$DBENV stop >> $LF 2>&1
if [ -a $DBPATH ]
then
cd $DBPATH
rm -fR base
rm -fR global
rm -fR pg_*
rm -f p*
rm -f PG_VERSION
else
mkdir -p $DBPATH
chown postgres:postgres $DBPATH
chmod 700 postgres:postgres $DBPATH
fi
BuildEmptyDB
mv /data/database.new/* $DBPATH
echo "Staring empty DB"
echo "Staring empty DB" >> $LF

su - postgres -c "nohup /usr/bin/postmaster -p $DBPORT -D $DBPATH &"
sleep 3
su - postgres -c "dropdb -p $DBPORT tempdatabase"
su - postgres -c "createdb -p $DBPORT -O postgres -e tempdatabase"

RecoverLastFile
else
echo "DB recovery aborted"
echo "DB recovery aborted" >> $LF
fi
fi
LogEnd
Advertisements