Re: recovery_target_time and standby_mode

On 11/07/2014 12:02 PM, Robert Haas wrote:
> Several people then suggested that you could accomplish your
> originally stated goal - namely "restore a master *and replica* to a
> point in time before Bad Stuff happened, and then have a working
> master-replica pair" - by just connecting the new standby to the
> master directly, without using recovery_target_time. As long as
> primary_conninfo and restore_command are both set, the standby should
> be able to fetch older segments from the archive and then seamlessly
> switch to fetching new segments from the new master. If you tried
> that and it didn't work, I don't see a description of the outcome
> anywhere on this thread.

Aha!

I went ahead and tested that, which I did not ever expect to work since
our documentation says it won't. The problem is that the replica
continues to catch up from the archive *past* the point where the master
is caught up. It doesn't switch to the streaming replication connection
until it runs out of archives (which is as designed, for a variety of
good reasons).

And since the replica is now beyond the master's timeline, replication
fails with "end of wal reached on timeline 1 320/478ff780; new timeline
2 forked timeline 1 before current recovery point 320/47ffffe0".

In order for this to work, the archive would need to stop before
recovery_target_time.

On 11/07/2014 12:07 PM, Robert Haas wrote:> On Fri, Nov 7, 2014 at 1:40
PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> Is the current interaction of recovery_target_time and standby_mode
>> (that is, that recovery_target_time causes standby_mode to be ignorned)
>> the correct behavior?
>
> I think this summary of the behavior is probably not correct in
> detail. For example, if the recovery target isn't reached by the time
> the standby reaches the end of archived WAL, I think standby_mode will
> affect what happens next.

Oh, yeah, good point. So just a doc patch then.

Presumably when the recovery_target_time is reached the replica promotes
even if it's streaming? Will test.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com