pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedstreaming AND file-based log-shipping?
4+ messages / 3 participants
[nested] [flat]
* streaming AND file-based log-shipping?
@ 2011-04-15 01:25 Ray Stell <stellr@cns.vt.edu>
2011-04-15 07:52 ` Re: streaming AND file-based log-shipping? Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ray Stell @ 2011-04-15 01:25 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: streaming AND file-based log-shipping?
2011-04-15 01:25 streaming AND file-based log-shipping? Ray Stell <stellr@cns.vt.edu>
@ 2011-04-15 07:52 ` Simon Riggs <simon@2ndQuadrant.com>
2011-04-15 13:09 ` Re: streaming AND file-based log-shipping? Ray Stell <stellr@cns.vt.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Simon Riggs @ 2011-04-15 07:52 UTC (permalink / raw)
To: Ray Stell <stellr@cns.vt.edu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: streaming AND file-based log-shipping?
2011-04-15 01:25 streaming AND file-based log-shipping? Ray Stell <stellr@cns.vt.edu>
2011-04-15 07:52 ` Re: streaming AND file-based log-shipping? Simon Riggs <simon@2ndQuadrant.com>
@ 2011-04-15 13:09 ` Ray Stell <stellr@cns.vt.edu>
2011-04-15 20:45 ` Re: streaming AND file-based log-shipping? Dan Birken <dan@thumbtack.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ray Stell @ 2011-04-15 13:09 UTC (permalink / raw)
To: Simon Riggs <simon@2ndQuadrant.com>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: streaming AND file-based log-shipping?
2011-04-15 01:25 streaming AND file-based log-shipping? Ray Stell <stellr@cns.vt.edu>
2011-04-15 07:52 ` Re: streaming AND file-based log-shipping? Simon Riggs <simon@2ndQuadrant.com>
2011-04-15 13:09 ` Re: streaming AND file-based log-shipping? Ray Stell <stellr@cns.vt.edu>
@ 2011-04-15 20:45 ` Dan Birken <dan@thumbtack.com>
0 siblings, 0 replies; 4+ messages in thread
From: Dan Birken @ 2011-04-15 20:45 UTC (permalink / raw)
To: Ray Stell <stellr@cns.vt.edu>; +Cc: Simon Riggs <simon@2ndquadrant.com>; 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@cns.vt.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@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.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-04-15 20:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-15 01:25 streaming AND file-based log-shipping? Ray Stell <stellr@cns.vt.edu>
2011-04-15 07:52 ` Simon Riggs <simon@2ndQuadrant.com>
2011-04-15 13:09 ` Ray Stell <stellr@cns.vt.edu>
2011-04-15 20:45 ` Dan Birken <dan@thumbtack.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox