Обсуждение: streaming AND file-based log-shipping?

The cookbook text says, log shipping "is mostly superseded by streaming
replication in 9.0, though is still useful as part of a comprehensive
backup strategy."  The implication is that the WAL archive might be
needed for recovery in some cases:

1. Under what circumstances might the WAL archive come into play in recovery?
2. If the streaming rep fails for some reason will the standby begin
   recovery from the WAL archive, assuming the WAL can still be shipped?
3. What query or command set can be used to tell if file-based recovery is
   configured correctly if streaming replication is active?

I've configured both (I think), but it isn't clear to me whether
the file-based part will function.  I guess I could bust the stream somehow
and see what happens, but I can't find this idea documented.

On Fri, Apr 15, 2011 at 2:25 AM, Ray Stell <stellr@cns.vt.edu> wrote:

> The cookbook text says, log shipping "is mostly superseded by streaming
> replication in 9.0, though is still useful as part of a comprehensive
> backup strategy."  The implication is that the WAL archive might be
> needed for recovery in some cases:

Useful, not needed.

> 1. Under what circumstances might the WAL archive come into play in recovery?

If a WAL file is damaged, or if you wish to keep archived files for
longer than they are strictly required to make replication work.

> 2. If the streaming rep fails for some reason will the standby begin
>   recovery from the WAL archive, assuming the WAL can still be shipped?

Yes

> 3. What query or command set can be used to tell if file-based recovery is
>   configured correctly if streaming replication is active?
>
> I've configured both (I think), but it isn't clear to me whether
> the file-based part will function.  I guess I could bust the stream somehow
> and see what happens, but I can't find this idea documented.

Agreed, its slightly harder to test that its all working. But any
action you take that only works if something broke, can only really be
tested by deliberately breaking something. Break it, but on a test
system.

--
 Simon Riggs                   http://www.2ndQuadrant.com/books/
 PostgreSQL Development, 24x7 Support, Training & Services

On Fri, Apr 15, 2011 at 08:52:24AM +0100, Simon Riggs wrote:
> On Fri, Apr 15, 2011 at 2:25 AM, Ray Stell <stellr@cns.vt.edu> wrote:
>
> > The cookbook text says, log shipping "is mostly superseded by streaming
> > replication in 9.0, though is still useful as part of a comprehensive
> > backup strategy." ?The implication is that the WAL archive might be
> > needed for recovery in some cases:
>
> Useful, not needed.
>
> > 1. Under what circumstances might the WAL archive come into play in recovery?
>
> If a WAL file is damaged, or if you wish to keep archived files for
> longer than they are strictly required to make replication work.
>
> > 2. If the streaming rep fails for some reason will the standby begin
> > ? recovery from the WAL archive, assuming the WAL can still be shipped?
>
> Yes
>
> > 3. What query or command set can be used to tell if file-based recovery is
> > ? configured correctly if streaming replication is active?
> >
> > I've configured both (I think), but it isn't clear to me whether
> > the file-based part will function. ?I guess I could bust the stream somehow
> > and see what happens, but I can't find this idea documented.
>
> Agreed, its slightly harder to test that its all working. But any
> action you take that only works if something broke, can only really be
> tested by deliberately breaking something. Break it, but on a test
> system.


I guess the main reason I can think of for generating the archive is
if you need to do a pitr to a time between the backup and some logical
error.  Then the archives would be "useful."

Think I'll point the recovery.conf to a bogus port, restart, and force
the pg_xlog switch.  The standby should recover from the archive.

Thanks for the cookbook.  Another queston from it if you don't mind.
On pg 314 you show the following query for checking the replication delay,
but I get an error with it.  What were you going for?

template1=# SELECT pg_last_xlog_apply_location();
ERROR:  function pg_last_xlog_apply_location() does not exist
LINE 1: SELECT pg_last_xlog_apply_location();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.