Skip site navigation (1) Skip section navigation (2)

Re: streaming AND file-based log-shipping?

From: Dan Birken <dan(at)thumbtack(dot)com>
To: Ray Stell <stellr(at)cns(dot)vt(dot)edu>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Re: streaming AND file-based log-shipping?
Date: 2011-04-15 20:45:05
Message-ID: BANLkTinC1MV6QvciQMThDv9bWQ-uaYx6uQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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
>

In response to

pgsql-admin by date

Next:From: rdDate: 2011-04-18 17:16:53
Subject: Streaming replication: rsync to switchover
Previous:From: Sam NelsonDate: 2011-04-15 17:50:49
Subject: Re: Version String

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group