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