X-Antivirus-mail.weatherbeeta.com.au-Mail-From: 
 phillip.smith@weatherbeeta.com.au via mail.weatherbeeta.com.au
X-Antivirus-mail.weatherbeeta.com.au: 1.25-st-qms (Clear:RC:1(172.20.0.155):.
	Processed in 6.540318 secs Process 21465)
Received: from it-phil.weatherbeeta.com.au (HELO wbaus090) (172.20.0.155)
	by mail.weatherbeeta.com.au with SMTP; 19 May 2008 10:05:08 +1000
From: "Phillip Smith" <phillip.smith@weatherbeeta.com.au>
To: "'Glyn Astill'" <glynastill@yahoo.co.uk>,
	<pgsql-admin@postgresql.org>
Subject: Re: pg_restore test procedures (a bit OT)
Date: Mon, 19 May 2008 10:05:03 +1000
Organization: Weatherbeeta Pty Ptd
Message-ID: <003401c8b944$03e827c0$9b0014ac@wbaus090>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.6626
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
In-Reply-To: <152155.68667.qm@web25805.mail.ukl.yahoo.com>
X-Scanned-By-wbeetaau1: Virus scan performed by network-box
X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1211155518.464-6697-000
X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in
	0.02/0.04 secs)
X-Scanned-By-wbeetaau1: Spam-Check-Result: No,
	hits=0 required=7 tests= autolearn=no version=2.0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=1.097 tagged_above=0 required=5 tests=AWL=-0.822,
	MIME_QP_LONG_LINE=1.819, RDNS_DYNAMIC=0.1
X-Spam-Level: *
X-Archive-Number: 200805/160
X-Sequence-Number: 29244

> I was just wondering if anyone has any clever way of testing their =
backups
taken with pg_dump on a daily basis?
I have a daily bash script to backup of one of my databases to file, =
then
file to tape, which I test restore once per week. I've changed some of =
my
variables to hard-coded strings to make it shorter and easier to =
understand
at a quick read. Yes I know it's kinda fschked but it's working atm. =
I'll
tidy it up eventually....

<snip>
if [ "$DAY" =3D "Mon" ] ; then
	echo "Test restoring database..." >> $LOG_FILE

	if [ 1 -eq 1 ] ; then
		# Debugging
		$ECHO "Restoring from:           ${BACKUP_PATH}"
		$ECHO "Restoring to Directory:   ${TMPDNAME}"
		$ECHO "Restore Database:         ${DBNAME}${DATESTRING}"
		$ECHO "Restore Source File:      ${TMPFILE}"
	fi

	echo -n "Extracting database dump from tape... "
	tar xf /dev/st0 --directory /tmp
	if [ $? -ne 0 ] ; then
		echo "FAIL"
		echo "   Failed to Restore from /dev/st0 to /tmp/" >>
$LOG_FILE
		SHOW_ERR=3Dtrue
	fi

	if [ ${SHOW_ERR} !=3D true ] ; then
		### Note: $DATESTRING =3D yymmdd (eg. 080519)
		echo "Creating new database ${DBNAME}${DATESTRING}" >>
$LOG_FILE
		/usr/local/bin/psql --command "CREATE DATABASE
${DBNAME}${DATESTRING} ENCODING =3D 'SQL_ASCII';" > /dev/null
		if [ $? -eq 0 ] ; then
			echo "Restoring /tmp/psqldump.sql.502 to
${DBNAME}${DATESTRING}" >> $LOG_FILE
			/usr/local/bin/psql ${DBNAME}${DATESTRING} <
/tmp/psqldump.sql.502
		else
			echo "Failed to Create Database!" >> $LOG_FILE
			SHOW_ERR=3Dtrue
		fi
	fi
Fi
</snip>

> su - $PGUSER -c "pg_restore -h localhost -U postgres =
--disable-triggers -c
-d $database $backup_dir/$server-$database-data.gz" >> $logfile
> or=20
> su - $PGUSER -c "pg_restore -h localhost -U postgres =
--disable-triggers -c
-d $database $backup_dir/$server-$database-data.gz >> $logfile"

Perhaps something like this: (it's Monday morning, so I might be =
suggesting
something stupid)
LOG=3D`su - $PGUSER -c "pg_restore -h localhost -U postgres =
--disable-triggers
-c -d $database $backup_dir/$server-$database-data.gz"`
echo $LOG >> $logfile


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.