pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore test procedures (a bit OT)
3+ messages / 2 participants
[nested] [flat]
* pg_restore test procedures (a bit OT)
@ 2008-05-16 16:43 Glyn Astill <glynastill@yahoo.co.uk>
2008-05-19 00:05 ` Re: pg_restore test procedures (a bit OT) Phillip Smith <phillip.smith@weatherbeeta.com.au>
0 siblings, 1 reply; 3+ messages in thread
From: Glyn Astill @ 2008-05-16 16:43 UTC (permalink / raw)
To: pgsql-admin
Hi chaps,
I was just wondering if anyone has any clever way of testing their backups taken with pg_dump on a daily basis?
On a slightly separate note,
I've setup a daily restore onto a staging server that I intend to also use to test the dumps, at the moment I've just set up a cron job. I'm sure capturing the output would be sufficient, however I'm having problems getting it into by logfile.
su - $PGUSER -c "pg_restore -h localhost -U postgres --disable-triggers -c -d $database $backup_dir/$server-$database-data.gz" >> $logfile
or
su - $PGUSER -c "pg_restore -h localhost -U postgres --disable-triggers
-c -d $database $backup_dir/$server-$database-data.gz >> $logfile"
won't let me capture it.
How dumb am I?
__________________________________________________________
Sent from Yahoo! Mail.
A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_restore test procedures (a bit OT)
2008-05-16 16:43 pg_restore test procedures (a bit OT) Glyn Astill <glynastill@yahoo.co.uk>
@ 2008-05-19 00:05 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
0 siblings, 0 replies; 3+ messages in thread
From: Phillip Smith @ 2008-05-19 00:05 UTC (permalink / raw)
To: 'Glyn Astill' <glynastill@yahoo.co.uk>; pgsql-admin
> 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" = "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=true
fi
if [ ${SHOW_ERR} != true ] ; then
### Note: $DATESTRING = yymmdd (eg. 080519)
echo "Creating new database ${DBNAME}${DATESTRING}" >>
$LOG_FILE
/usr/local/bin/psql --command "CREATE DATABASE
${DBNAME}${DATESTRING} ENCODING = '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=true
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
> 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=`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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_restore test procedures (a bit OT)
@ 2008-05-19 11:03 Glyn Astill <glynastill@yahoo.co.uk>
0 siblings, 0 replies; 3+ messages in thread
From: Glyn Astill @ 2008-05-19 11:03 UTC (permalink / raw)
To: Phillip Smith <phillip.smith@weatherbeeta.com.au>; pgsql-admin
Thanks Phillip,
I think the source of my problem was me being dumb and only capturing stdout, rather than stderr and stdout! I needed the "2>&1" on the end.
----- Original Message ----
> From: Phillip Smith <phillip.smith@weatherbeeta.com.au>
> To: Glyn Astill <glynastill@yahoo.co.uk>; pgsql-admin@postgresql.org
> Sent: Monday, 19 May, 2008 1:05:03 AM
> Subject: Re: [ADMIN] pg_restore test procedures (a bit OT)
>
> > 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....
>
>
> if [ "$DAY" = "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=true
> fi
>
> if [ ${SHOW_ERR} != true ] ; then
> ### Note: $DATESTRING = yymmdd (eg. 080519)
> echo "Creating new database ${DBNAME}${DATESTRING}" >>
> $LOG_FILE
> /usr/local/bin/psql --command "CREATE DATABASE
> ${DBNAME}${DATESTRING} ENCODING = '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=true
> fi
> fi
> Fi
>
>
> > su - $PGUSER -c "pg_restore -h localhost -U postgres --disable-triggers -c
> -d $database $backup_dir/$server-$database-data.gz" >> $logfile
> > or
> > 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=`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.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
__________________________________________________________
Sent from Yahoo! Mail.
A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-05-19 11:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-16 16:43 pg_restore test procedures (a bit OT) Glyn Astill <glynastill@yahoo.co.uk>
2008-05-19 00:05 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
2008-05-19 11:03 Re: pg_restore test procedures (a bit OT) Glyn Astill <glynastill@yahoo.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox