Thread: Warm Standby looking for already applied log files
Hello all,
We have a two Postgres servers running 8.3 set up for warm standby using xlog shipping. We had some problems with our warm standby locking up and needing to be rebooted and have grub reinstalled for the system to boot. Once the server was back up we noticed the xlog file it was looking for was one that the logs say was already applied. It was 7 xlogs back to be exact. We have our log shipping target replay the xlogs and then delete them when it is done so we don't have these old 7 to move back so Postgres can replay them. Is Postgres looking for an older xlog as part of its start up consistency check? If the xlogs were really applied as we can see in the logs can we skip over the next 7 some how to get the system to start applying the logs we have? Thanks for the help.
--Colin
Colin Wilson
Linux Systems Administrator
Colin Wilson <cwilson@blackducksoftware.com> wrote: > Once the server was back up we noticed the xlog file it was > looking for was one that the logs say was already applied. It > was 7 xlogs back to be exact. It is not unusual for WAL replay to ask for files out of order or multiple times in certain circumstances. A standby could never hope to keep up with a busy master if it waited for a disk flush on every WAL file before requesting the next, so recovery is one of those times where it has to go back and ask for files containing data which wasn't flushed to disk. > We have our log shipping target replay the xlogs and then delete > them when it is done Not a good idea. > how to get the system to start applying the logs we have? Since you don't know how much of what it applied the first time actually made it to disk, I wouldn't trust any recovery attempt. Time to grab a new base backup from the master, I think. And fix that script to not delete. Personally, I like to keep the last two base backups and all the WAL files needed to restore from the earlier of those forward. We have cleanup script that deletes the oldest backup and WAL files only needed for recovery from it when we receive a new one. -Kevin
Agree with Kevin on this one, a new base backup and essentially re-start the log shipping from scratch. I try and keep at least the last 20-30 applied xlogs on the standby server before removing them, so i have never seen this issue personally. On our databases, I try to do a base backup at least once a month, and then apply it to standby to ensure we remain as consistent as possible. If you have the maintenance slot & resources, I would strongly recommend it. Cheers -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Kevin Grittner Sent: 24 January 2011 17:03 To: Colin Wilson; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Warm Standby looking for already applied log files Colin Wilson <cwilson@blackducksoftware.com> wrote: > Once the server was back up we noticed the xlog file it was > looking for was one that the logs say was already applied. It > was 7 xlogs back to be exact. It is not unusual for WAL replay to ask for files out of order or multiple times in certain circumstances. A standby could never hope to keep up with a busy master if it waited for a disk flush on every WAL file before requesting the next, so recovery is one of those times where it has to go back and ask for files containing data which wasn't flushed to disk. > We have our log shipping target replay the xlogs and then delete > them when it is done Not a good idea. > how to get the system to start applying the logs we have? Since you don't know how much of what it applied the first time actually made it to disk, I wouldn't trust any recovery attempt. Time to grab a new base backup from the master, I think. And fix that script to not delete. Personally, I like to keep the last two base backups and all the WAL files needed to restore from the earlier of those forward. We have cleanup script that deletes the oldest backup and WAL files only needed for recovery from it when we receive a new one. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ___________________________________________________ This email is intended for the named recipient. The information contained in it is confidential. You should not copy it for any purposes, nor disclose its contents to any other party. If you received this email in error, please notify the sender immediately via email, and delete it from your computer. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company. PCI Compliancy: Please note, we do not send or wish to receive banking, credit or debit card information by email or any other form of communication. Cromwell Tools Limited, PO Box 14, 65 Chartwell Drive Wigston, Leicester LE18 1AT. Tel 0116 2888000 Registered in England and Wales, Reg No 00986161 VAT GB 115 5713 87 900 __________________________________________________
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > that script to not delete. Personally, I like to keep the last two > base backups and all the WAL files needed to restore from the > earlier of those forward. We have cleanup script that deletes the > oldest backup and WAL files only needed for recovery from it when we > receive a new one. PostgreSQL 9.0 now ships with such a script per default: http://www.postgresql.org/docs/9.0/interactive/pgarchivecleanup.html Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Dimitri Fontaine <dimitri@2ndQuadrant.fr> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> that script to not delete. Personally, I like to keep the last >> two base backups and all the WAL files needed to restore from the >> earlier of those forward. We have cleanup script that deletes >> the oldest backup and WAL files only needed for recovery from it >> when we receive a new one. > > PostgreSQL 9.0 now ships with such a script per default: > > http://www.postgresql.org/docs/9.0/interactive/pgarchivecleanup.html Thanks for pointing that out. Next time we touch the scripts, I'll see about using this instead of the bash script we call to do the equivalent. I don't suppose there's something which looks *into* a backup file and deletes all WAL files not needed to restore the related base backup? That would eliminate an even *uglier* bash script here. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > I don't suppose there's something which looks *into* a backup file > and deletes all WAL files not needed to restore the related base > backup? That would eliminate an even *uglier* bash script here. Well with some luck Magnus will be able to complete pg_basebackup in 9.1 so that it's able to include all necessary WAL files, and just them… I could have missed it but I didn't read about any effort towards shipping a base backup cleaning tool in 9.1, or even after that. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support