Re: recovery_target_time and WAL fetch with streaming replication

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?
>
> I'm using a physical streaming replication slave to have a database lagging
> behind about one month behind the primary, by setting "recovery_target_time"
> to the desired point in time.
> This setting is periodically advanced by a cronjob to allow the replica to
> roll forward. It's a 10.3-1 install on Debian.

I think that you are coplicating your life here.

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. Now, it is not usually the moment WAL is fetched
and synced on a standby that matters, it is the moment it is replayed.
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.

> 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.
The can allow a more evenly distribution of segments.
--
Michael