Thread: Automated Backup

> Is there a way to automate the backup databases using pg_dump (like in
> SQL server)?

You can use cron to automate your backup process with pg_dump.

regards,
bhuvaneswaran

Oops! sgnerd@yahoo.com.sg ("Kumar") was seen spray-painting on a wall:
> Is there a way to automate the backup databases using pg_dump (like in SQL server)?

If you can come up with a way of automating the running of programs,
then I imagine that might be possible.  I have heard that a program
called "cron" might be used for this purpose.
-- 
"aa454","@","freenet.carleton.ca"
http://cbbrowne.com/info/x.html
People can be set wondering by loading obscure personal patchable
systems, and sending bug reports.  Who would not stop and wonder upon
seeing "Experimental TD80-TAPE 1.17, MegaDeath 2.5..."?  The same for
provocatively-named functions and variables in stack traces.
-- from the Symbolics Guidelines for Sending Mail

>
>> Is there a way to automate the backup databases using pg_dump (like
>> in SQL server)?
>

Ha! Why would you want to do ANYTHING "like in SQL server"! ;)

You can do you back-ups very nicely using cron and a bash script:

bash-2.05a$ crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (/tmp/crontab.22116 installed on Fri Jun 13 10:41:06 2003)
# (Cron version -- $Id: crontab.c,v 2.13 1994/01/17 03:20:37 vixie Exp $)
11 02 * * 1-6 /usr/local/bin/dump paid postgres@btober.com


Slightly edited, but illustrates the point:

bash-2.05a$ cat /usr/local/bin/dump
#!/bin/bash
# Script to dump a PostgreSQL database, producing
# compressed tar file containing with pg_dump output.
# Author: Berend M. Tober <btober-at-computer-dot-org>
# Date:   August 25, 2003

if [ "${1}" = "" ]
then   echo "Must specify database name"   exit 0
fi


# setup variables

NAIL=/usr/local/bin/nail
PG_DUMP=/usr/bin/pg_dump
TAR=/bin/tar

DBNAME=${1}
UNAME=postgres
TARGET_EMAIL=${2}

OUTPUT_FILE=${DBNAME}.`date +%Y%m%d`


# create dump file

${PG_DUMP} -Fc -U ${UNAME} ${DBNAME} > ~/${OUTPUT_FILE}.dump


# create compressed archive of dump (and other) files

${TAR} -czf ~/${OUTPUT_FILE}.tar.gz  ${OUTPUT_FILE}.dump

# above line uses tar rather than just gzip
# because in reality other files are included in
# my backup archive but which have been omitted
# in this mailing list post for simplicity.


# optionally mail the back-up archive offsite
if [ "${2}" != "" ]
thenecho|${NAIL} -r ${UNAME} -a ~/${OUTPUT_FILE}.tar.gz -s
${OUTPUT_FILE}.tar.gz ${2}
fi