Re: Standby Replication and Replication Delay

Hi Tomas,

I'm using Postgresql 10.10 on the standbys and 10.5 on the primary.

On 14 Sep 2019, at 21:16, Tomas Vondra wrote:

> On Sat, Sep 14, 2019 at 06:03:34PM +0200, Thomas Rosenstein wrote:
>> Hi,
>>
>> so I got two questions:
>>
>> 1) I have multiple Postgresql Standby servers replicating over WAN, 
>> and I would like to reduce that to a single connection.
>
> Presumably the standbys are all located on the same LAN / in the same
> DC? Why don't you use cascading replication, then? I.e. one standby
> connecting to the primary, the rest connecting to the first standby.
>
> You can also archive the WAL on the first standby (since 9.5) and the
> other standby nodes can get the WAL from the local WAL.

Yes they are on the same LAN, but if a long running query is executed on 
one of them, then the replication lag increases and all of the standbys 
also increase their replication delay.
I don't have the free resources to just run a standby with a full 
dataset.

The wal is archived from the primary anyways, but I would like to have 
to streaming replication as a backup to the wal archival. (and the 
standbys can restore from that archive)

>
>> Is there a utility that can be put in between and store the wal files 
>> from the primary and provide it to the standby server, even if they 
>> are delayed by > 1 day or more (provided there is storage?)
>>
>
> Not sure what utility you have in mind. The first standby can act as a
> local primary, creating a local WAL archive etc.

See above, Wal archives are anyways available, the idea is as a 
secondary backup, in case the wal archival lags behind  (i.e. issue with 
storage or the server where the wal archival happens)

>
>> 2) These standby servers sometimes run very long queries (2 - 3 
>> hours) and at some point the replication stops, because I guess some 
>> row version which are used are removed on the master.
>> I do have hot_standby_feedback "on", why does this still happen, 
>> shouldn't this prevent the removal on the primary and allow 
>> replication to continue even if queries are active?
>>
>
> Well, you haven't really told us what "replication stops" does means.
> hot_standby_feedback does prevent aborts of of queries on the standby,
> it should not stop replication AFAIK.
>
> Maybe show us the error messages, tell us which PostgreSQL version are
> you actually using, etc.

Replication stops means that the standby servers do not replay the WAL 
archive and the replication lag increases.
There is no error message.

I have also set:

max_standby_archive_delay = -1
max_standby_streaming_delay = -1


>
>
> regards
>
> -- 
> Tomas Vondra                  http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services