Re: Standby Replication and Replication Delay

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.

>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.

>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.

regards

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