1

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.

6
  • I don't think that pg_rewind can work in that scenario. You should not have promoted both standby servers. Dec 15, 2022 at 8:40
  • Thats true, but we have operation called 'leave-cluster' which actually promote the node and make it read write. I did 'leave-cluster' on both node and then tried performing join-cluster between them. Why pg_rewind don't fail in this scenario? We look for pg_rewind return code and if it non-zero, we use pg_basebackup. But since, pg_rewind returned 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? Dec 15, 2022 at 8:43
  • I think that pg_basebackup is 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. Dec 15, 2022 at 8:46
  • Ok but this is by design and we are aware on how to manage that data loss. We make new primary based on which node promoted last. Isn't there a way to detect that pg_rewind would not work and go for pg_basebackup? Here, problem is pg_rewind return success code as 0 and then PostgreSQL die saying WAL segment removed? Dec 15, 2022 at 8:49
  • I would say that it is a bug that pg_rewind returns 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. Dec 15, 2022 at 8:58

1 Answer 1

0

This bug has been fixed with commit 009eeee74682. If you upgrade to PostgreSQL v16 or better, it should work as you want. You could lobby for a backpatch to older versions by replying to this thread.

2
  • Is it possible to backpatch this to Postgres 14? Feb 27, 2023 at 10:30
  • In the thread I linked, Heikki says that "the patch didn't apply cleanly to v13 and before", so I assume that v14 would be fairly painless. As I wrote, you'd have to write to a PostgreSQL mailing list (ideally pgsql-hackers) and express your wish there. Feb 27, 2023 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.