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)
[cid:image001(dot)jpg(at)01CBC557(dot)E63AC930]
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwards(at)logicworks(dot)net<mailto:redwards(at)logicworks(dot)net>
http://www.linkedin.com/in/rolandoedwards

From: pgsql-novice-owner(at)postgresql(dot)org [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of JORGE MALDONADO
Sent: Saturday, February 05, 2011 3:44 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] Backing up several PostgreSQL databases

What would be an efficient way to backup several PostgreSQL databases? The number of DB's is not a constant because it depends on the number of clients our company has. Because our company is new, we actually have 1 customer, but in the future we plan to grow so we probably have to manage many DB's. The application we offer is a web app and we also provide hosting so one server can have more than one database.

Respectfully,
Jorge Maldonado