Postgres Pro
Downloads
Home   >   mailing lists

Thread: Online backups are failing

Online backups are failing

From
"Bhella Paramjeet-PFCW67"
Date:
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

Re: Online backups are failing

From
"Joshua D. Drake"
Date:
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

Re: Online backups are failing

From
"Bhella Paramjeet-PFCW67"
Date:
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

Re: Online backups are failing

From
Simon Riggs
Date:
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