From: | Rolando Edwards <redwards(at)logicworks(dot)net> |
---|---|
To: | JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Backing up several PostgreSQL databases |
Date: | 2011-02-05 22:12:45 |
Message-ID: | 0ED65673FA8E634AAB252741A6CD3B5E0E7CFE3AE1@lw-exchange4b.us.logicworks.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Leon Starr | 2011-02-06 06:09:36 | raise statement, how to do multi-line message |
Previous Message | JORGE MALDONADO | 2011-02-05 20:43:31 | Backing up several PostgreSQL databases |