MIME-Version: 1.0
From: azeem subhani <azeemsd07@gmail.com>
Date: Sat, 13 Jul 2024 16:33:11 +0500
Message-ID: <CALp3DH1fLZmPvkOteAbUo4TOLZP-LstKOs6Gcw3Bm7acmJqk=w@mail.gmail.com>
Subject: pg_rewind Issue: Trying to read Incorrect WAL file for checkpoint record
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000165be5061d1f5de7"
Archived-At: <https://www.postgresql.org/message-id/CALp3DH1fLZmPvkOteAbUo4TOLZP-LstKOs6Gcw3Bm7acmJqk%3Dw%40mail.gmail.com>
Precedence: bulk

--000000000000165be5061d1f5de7
Content-Type: text/plain; charset="UTF-8"

Hi,

I have a 1 primary, 2 standby streaming replication cluster. The primary
node (`pgsql/16-b/data`) shut down to trigger a failover. One standby
(`psql/16-a/data`) was promoted to primary, and write operations were
performed on the new primary and replicated to the other standby.
I attempted to use pg_rewind to sync the failed primary with the current
primary, but it failed. The error shows pg_rewind is trying to read from an
incorrect WAL file `000000010000000000000015` instead of
`000000040000000000000015` as per the control data:

pg_rewind: servers diverged at WAL location 150000A0/0 on timeline 4
pg_rewind: error: could not open file
"/var/lib/pgsql/16-b/data/pg_wal/000000010000000000000015": No such file or
directory
pg_rewind: error: could not read WAL record at 0/15000028


*Failed Primary Control Data:*pg_control version number: 1300
Catalog version number: 202307071
Database system identifier: 7389684959150618064
Database cluster state: shut down
pg_control last modified: Sat 13 Jul 2024 08:50:32 AM +04
Latest checkpoint location: 0/15000028
Latest checkpoint's REDO location: 0/15000028
Latest checkpoint's REDO WAL file: 000000040000000000000015
Latest checkpoint's TimeLineID: 4
Latest checkpoint's PrevTimeLineID: 4

*Current Primary Control Data:*
pg_control version number: 1300
Catalog version number: 202307071
Database system identifier: 7389684959150618064
Database cluster state: in production
pg_control last modified: Sat 13 Jul 2024 12:09:43 PM +04
Latest checkpoint location: 0/15009590
Latest checkpoint's REDO location: 0/15009558
Latest checkpoint's REDO WAL file: 000000050000000000000015
Latest checkpoint's TimeLineID: 5
Latest checkpoint's PrevTimeLineID: 5

Could anyone provide guidance on resolving this issue? I cannot find the
WAL file that pg_rewind is trying to open and read in the data/pg_wal
directory or my WAL archive directory.
-- 
Thanks
Azeem Subhani

--000000000000165be5061d1f5de7
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi,</div><div><br>I have a 1 primary, 2 standby strea=
ming replication cluster. The primary node (`pgsql/16-b/data`) shut down to=
 trigger a failover. One standby (`psql/16-a/data`) was promoted to primary=
, and write operations were performed on the new primary and replicated to =
the other standby.<br>I attempted to use pg_rewind to sync the failed prima=
ry with the current primary, but it failed. The error shows pg_rewind is tr=
ying to read from an incorrect WAL file `000000010000000000000015` instead =
of `000000040000000000000015` as per the control data:<br><br>pg_rewind: se=
rvers diverged at WAL location 150000A0/0 on timeline 4<br>pg_rewind: error=
: could not open file &quot;/var/lib/pgsql/16-b/data/pg_wal/000000010000000=
000000015&quot;: No such file or directory<br>pg_rewind: error: could not r=
ead WAL record at 0/15000028<br><br><b>Failed Primary Control Data:<br></b>=
pg_control version number: 1300<br>Catalog version number: 202307071<br>Dat=
abase system identifier: 7389684959150618064<br>Database cluster state: shu=
t down<br>pg_control last modified: Sat 13 Jul 2024 08:50:32 AM +04<br>Late=
st checkpoint location: 0/15000028<br>Latest checkpoint&#39;s REDO location=
: 0/15000028<br>Latest checkpoint&#39;s REDO WAL file: 00000004000000000000=
0015<br>Latest checkpoint&#39;s TimeLineID: 4<br>Latest checkpoint&#39;s Pr=
evTimeLineID: 4<br><br><b>Current Primary Control Data:</b><br>pg_control v=
ersion number: 1300<br>Catalog version number: 202307071<br>Database system=
 identifier: 7389684959150618064<br>Database cluster state: in production<b=
r>pg_control last modified: Sat 13 Jul 2024 12:09:43 PM +04<br>Latest check=
point location: 0/15009590<br>Latest checkpoint&#39;s REDO location: 0/1500=
9558<br>Latest checkpoint&#39;s REDO WAL file: 000000050000000000000015<br>=
Latest checkpoint&#39;s TimeLineID: 5<br>Latest checkpoint&#39;s PrevTimeLi=
neID: 5<br><br>Could anyone provide guidance on resolving this issue? I can=
not find the WAL file that pg_rewind is trying to open and read in the data=
/pg_wal directory or my WAL archive directory.</div><span class=3D"gmail_si=
gnature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature" da=
ta-smartmail=3D"gmail_signature"><div dir=3D"ltr">Thanks<div>Azeem Subhani<=
/div></div></div></div>

--000000000000165be5061d1f5de7--