Base Backups from PITR Standby
After bringing up a PG 8.2.5 database restored from a base backup
taken from a warm standby, the following warnings are logged:
...
[2007-10-26 19:21:22 MDT] LOG: archived transaction log file
"000000010000017C000000E2"
WARNING: relation "category_click_history" page 250226 is
uninitialized --- fixing
WARNING: relation "category_click_history" page 250227 is
uninitialized --- fixing
[2007-10-26 19:22:22 MDT] LOG: archived transaction log file
"000000010000017C000000E3"
...
WARNING: relation "category_product" page 30364 is uninitialized ---
fixing
Most warnings are for tables that are constantly being inserted into.
When I restore the database to the same server using a base backup
from the main server, I don't get this problem. I'm not sure if it's a
bug or a problem with my procedures. If it's a bug fixed in 8.3, I can
wait for that.
The process I use that leads to the warnings is simple:
I use pg_controldata to determine the current checkpoint WAL location
of the standby server. I ensure I have this WAL file and all newer
WALs. I backup all files under the standby's database cluster
directory, including all tablespaces, which are soft linked. I restore
the database cluster directory to its new location and create soft
links in pg_tblspc to point to the proper tablespace directories just
backed up. I create the pg_xlog directory containing an archive_status
directory.
I bring up the database and it goes into archive recovery mode (using
the recovery.conf from the original standby's backup). I let it replay
WALs until it's in sync with the production server. I then bring it
out of archive recovery mode by having my restore_comand script return
non-zero once and the database is up and running. With the database
sitting idle, other than autovac, the warnings noted above are logged.
Other than the warnings, the database seems okay.
I have resumed taking base backups from the main server and it's not
terribly important that this even works. If anyone could shed any
insight though, I would appreciate the feedback.
Brian Wipf
<brian@clickspace.com>
Brian Wipf <brian@clickspace.com> writes:
The process I use that leads to the warnings is simple:
I use pg_controldata to determine the current checkpoint WAL location
of the standby server. I ensure I have this WAL file and all newer
WALs. I backup all files under the standby's database cluster
directory, including all tablespaces, which are soft linked. I restore
the database cluster directory to its new location and create soft
links in pg_tblspc to point to the proper tablespace directories just
backed up. I create the pg_xlog directory containing an archive_status
directory.
This seems not exactly per spec. Why are you not using pg_start_backup
and pg_stop_backup around the copying of the database files?
regards, tom lane
On 29-Oct-07, at 11:06 PM, Tom Lane wrote:
Brian Wipf <brian@clickspace.com> writes:
The process I use that leads to the warnings is simple:
I use pg_controldata to determine the current checkpoint WAL location
of the standby server. I ensure I have this WAL file and all newer
WALs. I backup all files under the standby's database cluster
directory, including all tablespaces, which are soft linked. I
restore
the database cluster directory to its new location and create soft
links in pg_tblspc to point to the proper tablespace directories just
backed up. I create the pg_xlog directory containing an
archive_status
directory.This seems not exactly per spec. Why are you not using
pg_start_backup
and pg_stop_backup around the copying of the database files?
I'm trying to take a base backup from the standby server in archive
recovery mode. I don't believe it's possible to connect to it to issue
pg_start_backup/pg_stop_backup.
http://www.postgresql.org/docs/current/interactive/warm-standby.html#BACKUP-INCREMENTAL-UPDATED
On Tue, 2007-10-30 at 08:25 -0600, Brian Wipf wrote:
I'm trying to take a base backup from the standby server in archive
recovery mode. I don't believe it's possible to connect to it to issue
pg_start_backup/pg_stop_backup.http://www.postgresql.org/docs/current/interactive/warm-standby.html#BACKUP-INCREMENTAL-UPDATED
It's safest to shutdown the standby first, take a backup then crank it
up again.
It's possible to do it online in the way you suggest, but only when
running with either full_page_writes = on or when making the backup with
rsync, or another method that uses atomic 8Kb reads. The backup is only
valid if you rollforward past the next restartpoint after the backup
completes, which is not technically the right place but definitely far
enough. So its not exactly for the feint-hearted, at the moment.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
On 30-Oct-07, at 2:42 PM, Simon Riggs wrote:
It's safest to shutdown the standby first, take a backup then crank it
up again.It's possible to do it online in the way you suggest, but only when
running with either full_page_writes = on or when making the backup
with
rsync, or another method that uses atomic 8Kb reads
I'm using rsync with the -a flag (equivalent to -rlptgoD)
I can give rsync a block size with the -B flag. According to the rsync
man page, "[the blocksize] is normally selected based on the size of
each file being updated". Maybe this is all I need to set. I'll give
that a try.
. The backup is only
valid if you rollforward past the next restartpoint after the backup
completes
I can ensure this is done.
, which is not technically the right place but definitely far
enough. So its not exactly for the feint-hearted, at the moment.
Thanks for the input Simon, that helps a lot. If I can't get the
online backup of the warm standby to work properly, I'll simply keep
two warm standby's replaying WALs on the backup server. I'll have one
installation continually replaying WALs for failover and a second
which can be shut down periodically for taking base backups.
Brian Wipf
<brian@clickspace.com>
On Oct 30, 2007, at 3:42 PM, Simon Riggs wrote:
On Tue, 2007-10-30 at 08:25 -0600, Brian Wipf wrote:
I'm trying to take a base backup from the standby server in archive
recovery mode. I don't believe it's possible to connect to it to
issue
pg_start_backup/pg_stop_backup.http://www.postgresql.org/docs/current/interactive/warm-
standby.html#BACKUP-INCREMENTAL-UPDATEDIt's safest to shutdown the standby first, take a backup then crank it
up again.
Yeah, I was doing this in a completely wrong way at first and have
since found that doing just stopping the standby and making the
backup is easiest.
Erik Jones
Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com