Re: recovery_target_time and WAL fetch with streaming replication

Michael,

Am 2018-05-13 um 08:23 schrieb Michael Paquier:
> On Sun, May 13, 2018 at 01:39:48AM +0200, Hannes Erven wrote:
>> what is Postgresql's strategy when to fetch WAL from the master while in
>> streaming replication, and could it be tweaked?
>
> Fetching WAL from a primary (or another standby) cannot be directly per
> se. By definition, any WAL present will e automatically fetched and
> synced on the standby.

I also thought so, but this is not the case (and that's my whole point).

When the standby finds sufficient local WAL in respect to its
recovery_target_time, it seems it does not even attempt to connect to
the primary.

But when new WAL is needed, the standby will fetch /all/ WAL present on
the master.

I'd say, the standby should either:
- always connect to the primary and fetch any WAL present
- stop fetching/streaming WAL when it is not needed for the current
recovery_target

So ideally there would be an option to ask for the desired behaviour?

> For example, what people usually want to be protected from is an
> infortunate DROP TABLE on the primary run by an administrator to be
> immediately replayed on the standby, losing the data. Hence delaying
> when WAL is replayed can offer some protection, and this can be achieved
> by setting recovery_min_apply_delay in recovery.conf. This will cause
> WAL records replaying transactions commits to wait for the amount of
> time specified by this parameter, giving you the time to recover from
> any failures with a standby which has a controlled synced delta.

recovery_min_apply_delay is an int of milliseconds, so the maximum value
is approx. 25 days - which is not enough for my requirements.

Also, transaction/MVCC visibility will not cover all cases; most
important, it cannot protected against TRUNCATE
(https://wiki.postgresql.org/wiki/MVCC_violations).

>> One option of course would be to use some transfer mechanism external to
>> Postgresql... but so far I'm thinking there must be any easier way?
>
> Another option I can think of here is to use a specific restore_command
> instead of streaming replication. Simply archive a WAL segment on the
> primary with some meta-data like the time it was archived, and then
> allow the standby to recover the segment only after a delta has passed.

Yes, but thats far less simple than just setting restore_target_time .

Thanks again and best regards,

-hannes