Add test for replica promotion

[MMeent]

This validates that replicas can promote, and start write changes, and that these changes are also persisted. However, this does not check any less-than-happy paths.

Postgres submodules PRs: https://github.com/neondatabase/postgres/pull/648 https://github.com/neondatabase/postgres/pull/649 https://github.com/neondatabase/postgres/pull/650 https://github.com/neondatabase/postgres/pull/651

[github-actions[bot]]

6984 tests run: 6548 passed, 9 failed, 427 skipped (full report)

---

Failures on Postgres 17

• test_replica_promotes: release-x86-64-without-lfc, release-x86-64-with-lfc, release-arm64-without-lfc, debug-x86-64-without-lfc, release-arm64-with-lfc

Failures on Postgres 16

• test_replica_promotes: release-x86-64-with-lfc, release-arm64-with-lfc

Failures on Postgres 15

• test_replica_promotes: release-x86-64-with-lfc, release-arm64-with-lfc

# Run all failed tests locally:
scripts/pytest -vv -n $(nproc) -k "test_replica_promotes[release-pg15] or test_replica_promotes[release-pg15] or test_replica_promotes[release-pg16] or test_replica_promotes[release-pg16] or test_replica_promotes[release-pg17] or test_replica_promotes[release-pg17] or test_replica_promotes[release-pg17] or test_replica_promotes[debug-pg17] or test_replica_promotes[release-pg17]"

Test coverage report is not available

_{The comment gets automatically updated with the latest test results
bd3f4f9638ddb33dcb731f173b4704d4045cd3ab at 2025-05-30T13:02:41.222Z :recycle:}

[knizhnik]

I slightly confused with the test. First of all is fails with error because it attempts to fetchall after insert:

    secondary_cur.execute("INSERT INTO t (payload) SELECT generate_series(101, 200)")
    secondary_cur.fetchall()

If this call is removed then test is failed with assertion

test_runner/regress/test_replica_promote.py:89: AssertionError
assert (100,) == (200,)

[github-actions[bot]]

If this PR added a GUC in the Postgres fork or neon extension, please regenerate the Postgres settings in the cloud repo:

make NEON_WORKDIR=path/to/neon/checkout \
  -C goapp/internal/shareddomain/postgres generate

If you're an external contributor, a Neon employee will assist in making sure this step is done.