Обсуждение: Online backups are failing
Hi, We are running into a problem where our online backups are failing randomly. We are using postgres 8.2.7 and we are seeing this problem in both Solaris and Linux platforms. Our online backup script copies the PGDATA dir with the command "cp -r -P". First we saw this problem on our postgres 8.2.7 database where we had the "stats_block_level" parameter turned on in postgresql.conf file. After I turned off stats_block_level parameter, our backups did not fail. Now we are seeing the problem of our online backups failing on our other databases which are on postgres 8.2.7 and 8.1.13 and on these DB's the stats_block_level parameter is turned off. Has anyone else run into this problem? Any suggestions on how to resolve this problem will be highly appreciated. Thanks Paramjeet Bhella
On Tue, 2009-01-20 at 14:21 -0500, Bhella Paramjeet-PFCW67 wrote: > Hi, > > We are running into a problem where our online backups are failing > randomly. We are using postgres 8.2.7 and we are seeing this problem in > both Solaris and Linux platforms. Our online backup script copies the > PGDATA dir with the command "cp -r -P". > > First we saw this problem on our postgres 8.2.7 database where we had > the "stats_block_level" parameter turned on in postgresql.conf file. > After I turned off stats_block_level parameter, our backups did not > fail. > > Now we are seeing the problem of our online backups failing on our other > databases which are on postgres 8.2.7 and 8.1.13 and on these DB's the > stats_block_level parameter is turned off. > > Has anyone else run into this problem? Any suggestions on how to resolve > this problem will be highly appreciated. We need more information on what your script is actually doing. Are you calling pg_start_backup before you do the cp? (also you really should use rsync). Joshua D. Drake > > > Thanks > Paramjeet Bhella > > -- PostgreSQL - XMPP: jdrake@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
Forgot to mention one thing. Even though in the online backup script $?
did not return zero and I got the error "data dir copy failed" but the
PGDATA directory did get copied fine. The size of the PGDATA directory
and the copied PGDATA dir were same.
Here is the snippet of our online backup script
## put the database in backup mode
$PGSQL_HOME/bin/psql -d template1 -U postgres -e -f
$BACKUP_SQL/begin_backup.sql
>> $BACKUP_LOGS_DIR/$backup_log_filename
echo "Copying data dirs now..." >> $BACKUP_LOGS_DIR/$backup_log_filename
echo " " >> $BACKUP_LOGS_DIR/$backup_log_filename
## copy data directory into backup location including postgres.conf and
any soft
links used for tablespaces.
cp -r -P $PGDATA $BACKUP_HOME/a01_data.${TIMESTAMP}
if [ $? -eq 0 ]
then
echo "data dir copy of a01 is successful." >>
$BACKUP_LOGS_DIR/$backup_log_fi
lename
echo " " >> $BACKUP_LOGS_DIR/$backup_log_filename
else
echo "data dir copy of a01 failed." >>
$BACKUP_LOGS_DIR/$backup_log_filename
echo " " >> $BACKUP_LOGS_DIR/$backup_log_filename
fi
## get the database out of backup mode
$PGSQL_HOME/bin/psql -d template1 -U postgres -e -f
$BACKUP_SQL/end_backup.sql >
> $BACKUP_LOGS_DIR/$backup_log_filename
Thanks
Paramjeet kaur
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Bhella
Paramjeet-PFCW67
Sent: Tuesday, January 20, 2009 11:22 AM
To: pgsql-admin@postgresql.org
Cc: Subbiah Stalin-XCGF84
Subject: [ADMIN] Online backups are failing
Hi,
We are running into a problem where our online backups are failing
randomly. We are using postgres 8.2.7 and we are seeing this problem in
both Solaris and Linux platforms. Our online backup script copies the
PGDATA dir with the command "cp -r -P".
First we saw this problem on our postgres 8.2.7 database where we had
the "stats_block_level" parameter turned on in postgresql.conf file.
After I turned off stats_block_level parameter, our backups did not
fail.
Now we are seeing the problem of our online backups failing on our other
databases which are on postgres 8.2.7 and 8.1.13 and on these DB's the
stats_block_level parameter is turned off.
Has anyone else run into this problem? Any suggestions on how to resolve
this problem will be highly appreciated.
Thanks
Paramjeet Bhella
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
On Tue, 2009-01-20 at 14:21 -0500, Bhella Paramjeet-PFCW67 wrote: > First we saw this problem on our postgres 8.2.7 database where we had > the "stats_block_level" parameter turned on in postgresql.conf file. > After I turned off stats_block_level parameter, our backups did not > fail. Seems like the stats files are changing while being copied. I'd suggest excluding global/pgstat* from the backup. Stats are reset at recovery anyway, so you've lost nothing. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support