Date: Tue, 28 May 2024 21:11:36 +0200
From: Johannes Truschnigg <johannes@truschnigg.info>
To: Mohan NBSPS <mohan.nbs.ont@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Postgresql 9.5: Streaming Replication: Secondaries Fail To Start
 Post WAL Error
Message-ID: <ZlYsaFDpLdR9SgAM@vault.lan>
References: 
 <CAPCvfWcm0JDC+q54MSW7N90PYvh+PefaP6SxfonbkGcUwpS1+g@mail.gmail.com>
 <ZlYmsG4NX7WjzFs-@vault.lan>
 <CAPCvfWdaymCYkLz-AseQLwGj8qh6pT1Kx73LoS-wkNNrukEX_Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="LyhJ0vIsPTr8jlHA"
Content-Disposition: inline
In-Reply-To: 
 <CAPCvfWdaymCYkLz-AseQLwGj8qh6pT1Kx73LoS-wkNNrukEX_Q@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZlYsaFDpLdR9SgAM%40vault.lan>
Precedence: bulk


--LyhJ0vIsPTr8jlHA
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, May 28, 2024 at 03:00:23PM -0400, Mohan NBSPS wrote:
> [...]
> Thank you Johannes for the advice.
>=20
> We are looking at moving to 16.
> We did not implement slots to avoid disk space issues on primary (possible
> network disconnect may fill up primary `pg_xlog`).

Yes, replication slots can interrupt your primary. Relying on
wal_keep_segments alone can kill your replicas. Having a WAL archive can be
the best of both worlds, but also needs careful monitoring and management.


> We have changed the WAL settings to retain more WAL files.
>=20
> Since we have not seen this issue before, (have been running postgresql f=
or
> over 10 years), what kind
> of scenario would trigger this ?

Every time you interrupt the replication stream (such as when a replica
reboots, or its postgres master process is stopped), you enter a race
condition between WAL segments accumulating on the primary, and the
replication stream to pick up again once the replica is up once more. So if,
during your replica restart, enough WAL was produced to exceed
wal_keep_segments, the lineage is broken, and the replica cannot ever catch=
 up
again.

Also, the "invalid resource manager" log line you reported *might* hint at
data corruption in your WAL segments. I think that data checksums and WAL
compression could both make detection of such conditions more reliable.

--=20
with best regards:
- Johannes Truschnigg ( johannes@truschnigg.info )

www:   https://johannes.truschnigg.info/

--LyhJ0vIsPTr8jlHA
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCgAdFiEEGu9IhkI+7/aKLUWF95W3jMsYfLUFAmZWLGgACgkQ95W3jMsY
fLUhrA//fmRHoyleEZZ7uvpu9fU1GMPnbIOr9Wbvb6QIIrOItI5bOL9vG0PTtPbu
XcMXYHL3QSCPzkvU2+0wDlREBjHFllh6U43MyHRFA0D/lnWpNGq274W2WdxTmURa
kJRMWgbG/MVey9OdkUhs18NgLvuw0IzpMm26FSMRA2DFQX3onPyO4SX20HvOEz7q
IwP2LJmHhPYUcf4EBr3TwCchs8RIgR1o2jqWrGBri35qsovKHn0jbavkBmCQoWov
Ftu8WbrhxrzMzy8Ril2gKLyUywFS9mkSfhqgV+SGyoSWy09EF032gMeY0k+9D1/8
Iancy+K5Jf97zpMBATm0u3F/ctBSQD33iu0y+xH1ZRtFtqexm3eeZtWPAowE/Fq+
bwXGjYt1yDrJfiJ9z6IsZRrqZJ8FIXPfU1p11uVuol1edPCoQzqUqVKic/VdT9ws
tcoRjzbKr+3+CG0KVjVsZgLS97XMq2DZ1f/5yr4C+lIu6aapixlg2iUjhblLBgnM
hJ90D/pyM+UdeK1jh1EN9aDZdVVnVug9i2ZDmwevIbj8vZN11qEEOrrCbie8YrXV
pWYJmOhFbUkc/qS/iWQTx+8aujBHzI+4NyQVnwJPA9nTue/lHvi4w+IRpcwIt80n
wlEpCEaJ5HDGJW7Di25kK6n2vs2gSrOsUPOrvKRbKltFblBqXjo=
=Y44W
-----END PGP SIGNATURE-----

--LyhJ0vIsPTr8jlHA--