Re: Warm Standby looking for already applied log files

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(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Kevin Grittner
Sent: 24 January 2011 17:03
To: Colin Wilson; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Warm Standby looking for already applied log files

Colin Wilson <cwilson(at)blackducksoftware(dot)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(at)postgresql(dot)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
__________________________________________________