Re: Backing up several PostgreSQL databases

I wrote a script a few months ago to pg_dump separate databases and archive them by date-named folder, except the template databases

Enjoy !!!

#!/bin/sh

if [ "${1}" == "" ] ; then exit ; fi

if [ "${2}" == "" ] ; then exit ; fi

BACKUP_FOLDER=${1}

FOLDERS_TO_KEEP=${2}

WHICH=/usr/bin/which

MKDIR=`${WHICH} mkdir`

GREP=`${WHICH} grep`

GZIP=`${WHICH} gzip`

DATE=`${WHICH} date`

ECHO=`${WHICH} echo`

HEAD=`${WHICH} head`

TAIL=`${WHICH} tail`

SORT=`${WHICH} sort`

AWK=`${WHICH} awk`

CAT=`${WHICH} cat`

WC=`${WHICH} wc`

PS=`${WHICH} ps`

RM=`${WHICH} rm`

LS=`${WHICH} ls | ${TAIL} -1 | ${AWK} '{print $1}'`

POSTGRES_RUNNING=0

PSQL=`${WHICH} psql`

PG_CTL=`${WHICH} pg_ctl`

PG_DUMP=`${WHICH} pg_dump`

FOUND_POSTMASTER=`${PS} -ef | ${GREP} "postmaster -D" | ${GREP} -v grep | ${WC} -l`

if [ ${FOUND_POSTMASTER} -gt 0 ]

then

        DATADIR=`${PS} -ef | ${GREP} "postmaster -D" | ${GREP} -v grep | ${SORT} -u | ${AWK} '{print $10}'`

else

        DATADIR=`${PS} -ef | ${GREP}   "postgres -D" | ${GREP} -v grep | ${SORT} -u | ${AWK} '{print $10}'`

fi

POSTGRES_RUNNING=`${PG_CTL} status -D ${DATADIR} | ${GREP} -c "server is running"`

if [ ${POSTGRES_RUNNING} -eq 0 ] ; then exit ; fi

#

#       Launch the Backup

#

cd ${BACKUP_FOLDER}

DBLISTFILE=/tmp/PG_DBList.txt

${PSQL} -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0','template1')" > ${DBLISTFILE}

DBLIST=""

SPC=""

for DB in `${CAT} ${DBLISTFILE}`

do

        DBLIST="${DBLIST}${SPC}${DB}"

        SPC=" "

done

BACKUP_DATE=`${DATE} +"%Y%m%d_%H%M%S"`

${MKDIR} ${BACKUP_DATE}

for DB in `${ECHO} ${DBLIST}`

do

        PGDUMP_FILE=${BACKUP_DATE}/pgData_${DB}.sql.gz

        ${PG_DUMP} ${DB} | ${GZIP} > ${PGDUMP_FILE} &

done

wait

#

#       Delete Old Folders

#

FOLDER_LIST=/tmp/dbbackup_folder_list.txt

FOLDERS_TO_ZAP=/tmp/dbbackup_folder_zaplist.txt

${LS} -l | ${GREP} "^drwxr" > ${FOLDERS_TO_ZAP}

FOLDER_COUNT=`${WC} -l < ${FOLDERS_TO_ZAP}`

if [ ${FOLDER_COUNT} -gt ${FOLDERS_TO_KEEP} ]

then

        DIFF=`${ECHO} ${FOLDER_COUNT}-${FOLDERS_TO_KEEP}|bc`

        ${LS} -l | ${GREP} "^drwxr" | ${AWK} '{print $9}' | ${HEAD} -${DIFF} > ${FOLDERS_TO_ZAP}

        for FOLDER_TO_ZAP in `${CAT} ${FOLDERS_TO_ZAP}`

        do

                ${RM} -rf ${FOLDER_TO_ZAP}

        done

fi

Rolando A. Edwards

MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor

New York, NY 10013

212-625-5307 (Work)

201-660-3221 (Cell)

AIM & Skype : RolandoLogicWorx

redwards@logicworks.net

http://www.linkedin.com/in/rolandoedwards