On Sat, Sep 14, 2019 at 09:26:26PM +0200, Thomas Rosenstein wrote:
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.
But each existing standby already is a full dataset, the idea was to
reuse one of those.
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)
TBH it's not quite clear to me what problem you're trying to solve. If
you want to reduce the number of WAN connections to the primary, you can
have a single primary standby connected to it. And then you can either
connect the remaining standbys to the first one using streaming, or use
recovery from the archive. Also, WAL archive is usually backup for
streaming, not the other way around.
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)
Well, as I said, it's usually the other way around - WAL archival is
considered backup for the streaming, in case the standby falls behind
for some reason.
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
Not sure.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
