Re: streaming AND file-based log-shipping?

I think the functions you are looking for are:

pg_last_xlog_receive_location();
pg_last_xlog_replay_location();

-Dan

On Fri, Apr 15, 2011 at 6:09 AM, Ray Stell <stellr(at)cns(dot)vt(dot)edu> wrote:

> 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(at)cns(dot)vt(dot)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.
>
> --
> 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
>