Message-ID: <2095e0615451e1a5a017a14d829ddda2d59a04b5.camel@cybertec.at>
Subject: Re: Cascading Replication - Standby Recovering Faster from Archive
 rather than Upstream node
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tim <timfosho@gmail.com>, posgres support <pgsql-admin@postgresql.org>
Date: Thu, 09 Feb 2023 21:10:27 +0100
In-Reply-To: 
 <CAKhLO5ju66vc=rWCJ7uwqmfiGP4YZa_0Sk5H8ZGLWmxC_QiELQ@mail.gmail.com>
References: 
	<CAKhLO5ju66vc=rWCJ7uwqmfiGP4YZa_0Sk5H8ZGLWmxC_QiELQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.3 (3.46.3-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/2095e0615451e1a5a017a14d829ddda2d59a04b5.camel%40cybertec.at>
Precedence: bulk

On Thu, 2023-02-09 at 13:41 -0500, Tim wrote:
> We have a cascading replication setup with multiple nodes 2 of which are =
in a different
> cloud region for DR purposes.=C2=A0
>=20
> DRnode01 > DRnode02=C2=A0
>=20
> They are both standby nodes, DRnode01 only recovers from the archive and =
does not connect
> to any upstream node for streaming. DRnode02 has the same restore_command=
 which points to
> that WAL archive, but it is also configured with primary_conninfo to stre=
am from DRnode01.
> I'd like it to just WAL stream instead of using the archive, but it just =
ends up recovering
> slightly faster from the archive than DRnode01 can send WALs so the logs =
end up being
> spammed with=C2=A0
>=20
> > 2023-02-09 13:32:00 EST [1277714]: [1-1] user=3D,db=3D,app=3D,client=3D=
 LOG:=C2=A0 started streaming WAL from primary at 74CC/B5000000 on timeline=
 33
> > 2023-02-09 13:32:00 EST [1277714]: [2-1] user=3D,db=3D,app=3D,client=3D=
 FATAL:=C2=A0 could not receive data from WAL stream: ERROR:=C2=A0 requeste=
d starting point 74CC/B5000000 is ahead of the WAL flush position of
> > this server 74CC/B4FFFA08.=C2=A0
>=20
> pg_wal_replay_pause() does not work, it ends up in the same situation aft=
er resuming.
> Changing restore_command requires a restart and turning it off altogether=
 is not good
> for DR.
>=20
> I cannot get it out of this loop and this has been a recurring issue for =
a while.=C2=A0
>=20
> Is there anything I can do to force to WAL stream instead of recovering f=
rom the archive
> without removing the restore_command setting?

My idea:

- On DRnode01, set "archive_mode =3D always" and configure an "archive_comm=
and" that copies
  WAL segments to a directory shared with DRnode02 (e.g. via NFS)
- DRnode02 has "primary_conninfo" that connects to DRnode01 and a "restore_=
command" that
  copies WAL segments from the shared directory.
- DRnode02 has an "archive_cleanup_command" that removes WAL segments that =
it no longer
  needs from the shared directory.

Yours,
Laurenz Albe