Re: "Stand-in" server recovery techniques

On 8/22/07, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:

> THE QUESTION
>
> How do we create the PostgreSQL instance on the stand-in box?
>
> I see four possibilities:
>
> (1) Restore the latest base backup and apply all WAL files available.
> This is likely to be the slowest option.

I'm leaning towards option 1, because you mentioned that you will be
doing this ONLY in the event the primary on site server can't come
back up. So, I'm going to assume that you're going to spend a few
minutes (30 or so) trying to resurrect the primary server.

WHILE doing that, I would have the backup server running the WAL files
to get ready to go.

The nice thing about this setup is that it's the simplest to
implement, hence the least error prone, and you can therefore hand it
off to a worker bee while you work on getting the main server up and
running.

Then, when it's ready, you have your decision time, do you keep trying
to get the real server back up, or apply your remaining transactions
to the new server and go from there.

> (2) Kick the warm standby on "the farm" into production mode, shut down
> the instance, and then copy the instance directory. This should be
> relatively quick and safe, but has the down side of needing to restart
> the warm standby from the latest base backup afterwards, if that is even
> possible. It seems like we might need to make a fresh base backup from the
> (stopped) instance on the warm standby farm.

I'm not sure exactly what you're saying here. If you're saying what I
think you're saying, then you're already constantly playing the WAL
files as they arrive from off site. If that's the case then this
option seems quite attractive in terms of getting you back up and
running fast.

Since the standby would now become production, making a snapshot of
the standby before making it production would mean that you can then
use the snapshot elsewhere for the standby.

If I understand what you're saying correctly.