From: "Nicolas Ross" <rossnick-lists@cybercat.net>
To: <pgsql-admin@lists.postgresql.org>
References: <web-254322712@mail.cybercat.ca>
In-Reply-To: <web-254322712@mail.cybercat.ca>
Subject: RE: Slave stuck in recovery mode 
Date: Sat, 9 Oct 2021 15:36:35 -0400
Message-ID: <011501d7bd44$f8b70a10$ea251e30$@cybercat.net>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Language: fr-ca
Thread-Index: AQHqu9+kpKB0pYakQsrKw2z3kSDkC6ulEcew
Archived-At: 
 <https://www.postgresql.org/message-id/011501d7bd44%24f8b70a10%24ea251e30%24%40cybercat.net>
Precedence: bulk

I ended up googling some more and found this :

https://www.enterprisedb.com/blog/be-sure-stop-your-backups

Which is exactly what was happening. Even though I had no backup =
running, I did the stop_backup command etc.

I planned a restart of the master server, and then re-cloned, I was then =
all OK !

Strange.

-----Message d'origine-----
De : Nicolas Ross <rossnick-lists@cybercat.net>=20
Envoy=C3=A9 : 8 octobre 2021 19:16
=C3=80 : pgsql-admin@lists.postgresql.org
Objet : Slave stuck in recovery mode=20

Hi !

We=E2=80=99ve been using postgres since some time now (since the
9.3 days).

I=E2=80=99ve got a pair of 9.6 server with 2 nodes, a primary and a
slave. We use repmgr to manage the cluster. When it was
installed, it was something like repmgr 4.x or even 3.

This week, for some reason, I had to rebuild the slave
instance. So I cloned the slave using a command like :

/usr/pgsql-9.6/bin/repmgr -h pgserver2.qualite -U repmgr -f
/etc/repmgr/9.6/repmgr.conf standby clone

After some time (it=E2=80=99s like 250 gigs, so it=E2=80=99s kinda an =
hour
or 2), the command ends.

If I start the postgres server on the slave with OS
systemcl script, it doesn=E2=80=99t return to the CLI (presumably
it waits for something).

In the log I see :

< 2021-10-08 16:16:47.861 EDT > LOG:  database system was
shut down in recovery at 2021-10-08 16:04:10 EDT
< 2021-10-08 16:16:47.877 EDT > LOG:  entering standby mode
< 2021-10-08 16:16:48.599 EDT > LOG:  redo starts at
13BF/CF000028
< 2021-10-08 16:16:52.899 EDT > LOG:  consistent recovery
state reached at 13BF/D53BA0F0
(Some time passes)
< 2021-10-08 16:46:10.363 EDT > LOG:  started streaming WAL
from primary at 13C9/8C000000 on timeline 1

After that, if I try to connect to the slave, I get :

FATAL:  the database system is starting up

No matter how long I wait (tried more than a day later).

During that time, the master still streams the wal to the
slave.


Notes :

That last log example was taken after trying to clone from
our barman server (tried with and without)

use_replication_slots is set to yes.

hot_standby is on on the primary, hence when cloned it is
also.

Before one of my clone command, I=E2=80=99ve tried cleaning all
residue of repmgr, ie remove the extension, re-register the
master, etc, still the same issue.

If I comment out hot_standby on the slave, it starts
normally, but still doesn=E2=80=99t allow connections.

Recovery.conf is :

standby_mode =3D 'on'
primary_conninfo =3D 'host=3DMASTERIP user=3Drepmgr
application_name=3DSLAVENAME'
recovery_target_timeline =3D 'latest'
primary_slot_name =3D 'repmgr_slot_1'


Any help troubleshooting this would be appreciated !