Have 3 node setup: node1(50.2), node2(50.3) and node3(50.4). node1(50.2) is primary and node2(50.3) and node3(50.4) are standby. I promoted node3 first and then node2 to make them independent. When i try to make the node3 slave of node2, using pg_rewind, it says below:
pg_rewind: connected to server
pg_rewind: source and target cluster are on the same timeline
pg_rewind: no rewind required
After this, when i start postgresql on the node3 in standby mode, i get below:
Dec 15 07:57:33 fsrstandby.for.com postmaster[15411]: 2022-12-15 07:57:33 UTCLOG: entering standby mode
Dec 15 07:57:33 fsrstandby.for.com postmaster[15411]: 2022-12-15 07:57:33 UTCLOG: consistent recovery state reached at 0/2F164268
Dec 15 07:57:33 fsrstandby.for.com postmaster[15411]: 2022-12-15 07:57:33 UTCLOG: invalid record length at 0/2F164268: wanted 24, got 0
Dec 15 07:57:33 fsrstandby.for.com postmaster[15410]: 2022-12-15 07:57:33 UTCLOG: database system is ready to accept read-only connections
Dec 15 07:57:33 fsrstandby.for.com systemd[1]: Started PostgreSQL 14 database server.
Dec 15 07:57:33 fsrstandby.for.com postmaster[15415]: 2022-12-15 07:57:33 UTCLOG: started streaming WAL from primary at 0/2F000000 on timeline 2
Dec 15 07:57:33 fsrstandby.for.com postmaster[15415]: 2022-12-15 07:57:33 UTCFATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000002000000000000002F has already been removed
Dec 15 07:57:33 fsrstandby.for.com postmaster[15418]: 2022-12-15 07:57:33 UTCLOG: started streaming WAL from primary at 0/2F000000 on timeline 2
Dec 15 07:57:33 fsrstandby.for.com postmaster[15418]: 2022-12-15 07:57:33 UTCFATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000002000000000000002F has already been removed
Dec 15 07:57:38 fsrstandby.for.com postmaster[15465]: 2022-12-15 07:57:38 UTCLOG: started streaming WAL from primary at 0/2F000000 on timeline 2
Dec 15 07:57:38 fsrstandby.for.com postmaster[15465]: 2022-12-15 07:57:38 UTCFATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000002000000000000002F has already been removed
Dec 15 07:57:43 fsrstandby.for.com postmaster[16087]: 2022-12-15 07:57:43 UTCLOG: started streaming WAL from primary at 0/2F000000 on timeline 2
There is no WAL archiving set. This is PostgreSQL 14. Does this means PostgreSQL pg_rewind can't be used when forming cluster between two independent primary having common ancestor?
Note: If i just promote node2 and run pg_rewind on node3 to join with node2, then it works correctly.
pg_rewindcan work in that scenario. You should not have promoted both standby servers.pg_rewinddon't fail in this scenario? We look forpg_rewindreturn code and if it non-zero, we usepg_basebackup. But since,pg_rewindreturned zero return code, it failed for us? What is best way to perform join-cluster between two promoted(leave-cluster) nodes? is it pg_basebackup only?pg_basebackupis the only option. I suggest that you change your procedure to only promote one standby. Otherwise you have a split brain, and data loss can ensue.pg_rewindreturns 0. It should not only check the time line number, but also compare the time the time line branched off. You may want to complain to the pgsql-hackers or pgsql-bugs mailing lists about that. Nonetheless, your procedure is wrong. You should identify which standby has the latest LSN received and promote that. Don't try to re-invent the wheel of a clusterware for PostgreSQL, it is a difficult wheel to invent. Use one of the existing solutions.