Message-ID: <a008ba3a3dab42ea0f7f3d47ad3f3304c5f3eeba.camel@cybertec.at>
Subject: Re: pg_rewind problem: cannot find WAL
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Luca Ferrari <fluca1978@gmail.com>, pgsql-general
	 <pgsql-general@lists.postgresql.org>
Date: Wed, 07 May 2025 15:55:08 +0200
In-Reply-To: <CAKoxK+5ypkP1Ts1KNuCgqAxwp=8ejQ-wfiBrpvZ1coZ3dZfOWg@mail.gmail.com>
References: 
	<CAKoxK+5ypkP1Ts1KNuCgqAxwp=8ejQ-wfiBrpvZ1coZ3dZfOWg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/a008ba3a3dab42ea0f7f3d47ad3f3304c5f3eeba.camel%40cybertec.at>
Precedence: bulk

On Wed, 2025-05-07 at 12:51 +0200, Luca Ferrari wrote:
> running 17.4 on ubuntu 24.04 machines. I've three hosts, pg-1
> (primary) and two physical replicas.
> I then promote host pg-3 as a master (pg_promote()) and want to rewind
> the pg-1 to follow the new master, so:
>=20
> ssh pg-3 'sudo -u postgres /usr/lib/postgresql/17/bin/pg_rewind -D
> /var/lib/postgresql/17/main --source-server=3D"user=3Dreplica_fluca
> host=3Dpg-3 dbname=3Dreplica_fluca"'
> pg_rewind: servers diverged at WAL location 0/B8550F8 on timeline 1
> pg_rewind: error: could not open file
> "/var/lib/postgresql/17/main/pg_wal/00000001000000000000000A": No such
> file or directory
> pg_rewind: error: could not find previous WAL record at 0/AFFF4E8
>=20
> But the file 0x010000A is not there:
>=20
>=20
>  % ssh pg-3 'sudo ls /var/lib/postgresql/17/main/pg_wal'
> 00000001000000000000000B.partial
> 00000002.history
> 00000002000000000000000B
> 00000002000000000000000C
> 00000002000000000000000D
> 00000002000000000000000E
> archive_status
> summaries
>=20
> % ssh pg-1 'sudo ls /var/lib/postgresql/17/main/pg_wal'
> 000000010000000000000005.00000028.backup
> 00000001000000000000000B
> 00000001000000000000000C
> 00000001000000000000000D
> 00000001000000000000000E
> archive_status
> summaries
>=20
> Do i have to ensure the old primary pg-1 does a wal switch before
> promoting the other one and try to rewind?

I don't think it is connected to a WAL switch.

I'd say that you should set "wal_keep_size" high enough that all the WAL
needed for pg_rewind is still present.

If you have a WAL archive, you could define a restore_command on the server
you want to rewind.

Yours,
Laurenz Albe