Date: Tue, 28 May 2024 20:47:12 +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: <ZlYmsG4NX7WjzFs-@vault.lan>
References: 
 <CAPCvfWcm0JDC+q54MSW7N90PYvh+PefaP6SxfonbkGcUwpS1+g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="f41ptKoBkbyxmUm+"
Content-Disposition: inline
In-Reply-To: 
 <CAPCvfWcm0JDC+q54MSW7N90PYvh+PefaP6SxfonbkGcUwpS1+g@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ZlYmsG4NX7WjzFs-%40vault.lan>
Precedence: bulk


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

Hi Mohan,

On Tue, May 28, 2024 at 02:26:41PM -0400, Mohan NBSPS wrote:
> Dear Community,
> [...]
> ```
> FATAL:  could not receive data from WAL stream: ERROR:  requested WAL
> segment 000000010000004100000049 has already been removed
>  FATAL:  the database system is starting up
> ```
>=20
> from my understanding, the WAL file is streamed over the network (seconda=
ry
> pulls from primary) and creates a WAL file in the secondary.
> then it replays the copied WAL file using a different process.
>=20
> in order for the local WAL file to go out of sync,
>=20
> 1. the primary removed the WAL file, the secondary was streaming
> 2. the WAL file on the secondary got corrupted
> 3 ....
>=20
> Questions
>=20
> - what do those error messages mean ?
> - how can I prevent this from happening ?

It means that, unless you have archived the required WAL segments somewhere
and can recover them from there, your replica is now broken, and you will h=
ave
to re-create it anew.

You can prevent this by correctly configuring streaming replication either =
by
using replication slots (not sure if that's already implemented in 9.5,
actually - you should prioritize upgrading to a supported release while you
are working this problem!), or by introducing a WAL archive[0] for replicas=
 to
retrieve WAL from that the primary has already evicted from its kept segmen=
ts.

Hth!

[0]:
https://www.postgresql.org/docs/9.5/runtime-config-wal.html#RUNTIME-CONFIG-=
WAL-ARCHIVING

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

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

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

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

iQIzBAEBCgAdFiEEGu9IhkI+7/aKLUWF95W3jMsYfLUFAmZWJrAACgkQ95W3jMsY
fLUKwQ/9FqbxIVLJrA+DryHUV9TDXjG42c4tFpYLMB+UQ0oAzGC0R0NpXGNeWOqs
vXpUniWVQ0+/+sciSQew1P3A125YM+Cn0aAxp1gT2rTFqzGLil2Te/SdqIVyF+kQ
0fjLmisVxIe1Mhnul3R793nFv1HYsIwqASL/HkSkUgTS1L8NezQeUBhEprOvJgcB
oRuk/OMVCu9hTLaPQ3qWu2bCWPR/SgI5jOjn8SI6GVQ95MNIFA3MQJjxPWccnTSN
nBvWrTOXG3LkLhc6MkJzaiBDwdI8spCEyIQDQlBRk/Jk1rSAdTR9W33rJPcNB/Vu
To+2WB/DWYJQEqmVywQXGVjIkrRg2NEz3fyrx27HPIp18vcJugeDmNYGE+Qq/W4O
snRGIoQ6KvphegqehYFE06Kkfr8AgS17vQwS5TNpiaUvUCkt6eUW8DKRMR99Vp7f
dA5Lkab4IHM4y6uEl5fzLuZ+TnzcfQTDV1fYKJpHTfAlthTHcioZxBbDMm9IQLkZ
EtzyCptijH6vft9RufEuBmlGJETB/Z5EtJMXhyfXMLyyKIZEQb+phqomUE1qLoDR
8qZoyTZkipb921cUGHOLZmADJfEKtyBckeC655FzWJExRukDf76KV7Dyvqt9Aq3x
nyuWc6mWxvOYCFXoLIYGXyaYRrdX6AoxptXlsqnO9kJNDneReJ0=
=adWL
-----END PGP SIGNATURE-----

--f41ptKoBkbyxmUm+--