X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 61E86D1B502 for ; Fri, 19 Sep 2003 08:31:42 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 81700-10 for ; Fri, 19 Sep 2003 05:31:13 -0300 (ADT) Received: from mail.seaworthysystems.com (216-238-112-88.dsl.ct.thebiz.net [216.238.112.88]) by svr1.postgresql.org (Postfix) with ESMTP id 52C4DD1B4E9 for ; Fri, 19 Sep 2003 05:31:11 -0300 (ADT) Received: from seaworthysystems.com (localhost.localdomain [127.0.0.1]) by mail.seaworthysystems.com (Postfix) with SMTP id F3ACA1B693 for ; Fri, 19 Sep 2003 04:37:29 -0400 (EDT) Received: from 66.212.203.144 (SquirrelMail authenticated user btober) by $HOSTNAME with HTTP; Fri, 19 Sep 2003 04:37:30 -0400 (EDT) Message-ID: <64812.66.212.203.144.1063960650.squirrel@$HOSTNAME> Date: Fri, 19 Sep 2003 04:37:30 -0400 (EDT) Subject: Re: Automated Backup From: To: In-Reply-To: References: <024b01c37e6f$a93fb4a0$7502a8c0@hdsc.com> X-Priority: 3 Importance: Normal X-Mailer: SquirrelMail (version 1.2.9) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/228 X-Sequence-Number: 15206 > >> 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 # 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}" != "" ] then echo|${NAIL} -r ${UNAME} -a ~/${OUTPUT_FILE}.tar.gz -s ${OUTPUT_FILE}.tar.gz ${2} fi