Re: Automated Backup

>
>> 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