A postgres backup script for a snapshot environment


#!/bin/bash

# Backup
# Usage: backup-pg.sh
#

# If you have another method for obtaining your filer's ip
# use that instead to assign this variable
FILERIP=`mount | grep db01 | cut -d':' -f1`

# username associated with the key used
UN="snappy"
# location of the ssh key used for above user
SNAPKEY=/opt/bbcollab/voice/postgres.stuff/snapkey

# If you have another method for obtaining the volume name or archive
# Edit the two lower variables; your ARCHIVE name should have a grepable line
VOLUME=`mount | grep db01 | cut -d':' -f2 | sed 's#/vol/##g' | sed 's#/db01.*##g'`
COMNAME="backup_pgdata"
ARCHIVE="${COMNAME}-${HOST}-${TODAY}"

# location of the .snapshot directory
SSDIR=/var/voice/postgres/.snapshot

# retention in snapshot count
RETEN=4

function get_oldest_snapshot_by_filesystem
{
ss=`ls -lct $SSDIR | grep -v "total" | head -1 | awk '{print $9}'`
echo $ss
}

function get_oldest_snapshot_by_netapp
{
# looking for archives with ${COMNAME}
cur_ss=`ssh -i ${SNAPKEY} -o StrictHostKeyChecking=no ${UN}@${FILERIP} "snap list ${VOLUME}" | grep "${COMNAME}" | awk '{print $10}' | grep -v "^$" | tail -1`
echo $cur_ss
}

function remove_oldest
{
ssh -i ${SNAPKEY} -o StrictHostKeyChecking=no ${UN}@${FILERIP} "snap list ${VOLUME}" | grep "${COMNAME}" > /var/tmp/ss.lst
ss_count=`wc -l /var/tmp/ss.lst | awk '{print $1}'`
if [[ "$ss_count" -gt $RETEN ]]
then
obf=`get_oldest_snapshot_by_filesystem`
obn=`get_oldest_snapshot_by_netapp`
if [[ "$obf" == "$obn" ]]
then
ssh -i ${SNAPKEY} -o StrictHostKeyChecking=no ${UN}@${FILERIP} "snap delete ${VOLUME} $obf"
fi
else
echo "none to remove"
fi
}

function usage
{
echo ""
echo "### $0 Usage ###"
echo ""
echo "Call script like:"
echo ""
echo " $0 ()"
echo " (cron uses -a)"
echo ""
echo "Options:"
echo " -a #### Create a new snapshot and delete the oldest"
echo " -c #### Only create a new snapshot"
echo " -l #### List the current snapshots"
echo " -d Delete snapshot (regardless of age or retention)"
echo " -q #### Delete the oldest snapshot (will not delete if you have fewer snapshots than $RETEN)"
echo ""
}

if [[ $# > 0 ]]
then
case $1 in
-a) psql -U postgres -h localhost -c "SELECT pg_start_backup('${ARCHIVE}');" template1
ssh -i ${SNAPKEY} -o StrictHostKeyChecking=no ${UN}@${FILERIP} "snap create ${VOLUME} ${ARCHIVE}"
psql -U postgres -h localhost -c "SELECT pg_stop_backup();" template1
remove_oldest
exit
;;
-c) psql -U postgres -h localhost -c "SELECT pg_start_backup('${ARCHIVE}');" template1
ssh -i ${SNAPKEY} -o StrictHostKeyChecking=no ${UN}@${FILERIP} "snap create ${VOLUME} ${ARCHIVE}"
psql -U postgres -h localhost -c "SELECT pg_stop_backup();" template1
exit
;;
-l) ssh -i ${SNAPKEY} -o StrictHostKeyChecking=no ${UN}@${FILERIP} "snap list ${VOLUME}"
exit
;;
-d) ssh -i ${SNAPKEY} -o StrictHostKeyChecking=no ${UN}@${FILERIP} "snap delete ${VOLUME} $2"
exit
;;
-q) remove_oldest
exit
;;
*) usage
exit
;;
esac
else
usage
exit
fi

Leave a Reply