Thread: 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-UPDATED > > It'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