Re: streamlined standby procedure

On 2/7/06 1:19 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andrew Rawnsley <ronz(at)investoranalytics(dot)com> writes:
>> IMHO the #1 priority in the current PITR/WAL shipping system is to make the
>> standby able to tolerate being shut down and restarted, i.e. actually having
>> a true standby mode and not the current method of doing it only on startup.
>
> How is shutting down the standby a good idea? Seems like that will
> block the master too --- or at least result in WAL log files piling up
> rapidly. If the standby goes off-line, abandoning it and starting from
> a fresh base backup when you are ready to restart it seems like the most
> likely recovery path. For sure I don't see this as the "#1 priority".
>
> regards, tom lane

I wasn't suggesting this in the context of Csaba's auto-ship plan (and, to
be clear, not #1 in the context of the entire database development. Just
PITR).

For one, sometimes you have no choice about the standby being shut down, but
most of the time you can plan for that. As for Csaba's question of why I
would want to create a copy of a standby, its the easiest way to create
development and testing snapshots at standby locations, and for making
paranoid operations people confident that your standby procedures are
working. I do it with my Oracle (pardon the 'O' word) installations all the
time, and I despise being able to do something with Oracle that I can't with
PG.

I ship WAL logs around in batches independent of the archive command to
several locations. Either I :

A) let the logs 'pile up' on the standby (crap has to pile up somewhere),
and apply them should the standby be needed (could take some time should the
'pile' be large). The only way here to keep the recover time short is to
re-image the database frequently and ship it around. Not nice with big
databases.

B) Do the blocking recover command to continually apply the logs as they get
moved around. While this can generate good clever points, its a rig.
Fragile.

To me the question isn't 'How is shutting down the standby a good idea?',
its 'How is shutting down the standby not a bad idea?'. Different points of
view, I suppose - In my situation the standby going offline is not a
catastrophic event like the primary would be; its even a useful thing. If
there was some rman-style thing like people have suggested to auto-ship logs
around, then yeah, dealing with an offline standby could be a tricky thing
(but would need some solution anyway). But hell, Slony and Mammoth can
tolerate it, I just would like log shipping to handle it also.

Maybe it isn't #1 priority, but its something I view as a limitation, and
not just lacking a feature. Its something I can't control. As I originally
mentioned, the customizable archive/restore feature is great, superior to
dealing with it in Oracle. But the standby mode makes the Oracle setup more
bulletproof.

--

Andrew Rawnsley
Chief Technology Officer
Investor Analytics, LLC
(740) 587-0114
http://www.investoranalytics.com