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
