From f4afe05fc7e73c5c23bcdeba4fc65a538c83b8ba Mon Sep 17 00:00:00 2001 From: Euler Taveira Date: Mon, 29 Jul 2024 19:44:16 -0300 Subject: [PATCH 1/2] pg_createsubscriber: fix slow recovery If the primary server is idle when you are running pg_createsubscriber, it used to take some time during recovery. The reason is that it was using the LSN returned by pg_create_logical_replication_slot as recovery_target_lsn. This LSN points to the next WAL record that might not be available at WAL, hence, the recovery routine waits until some activity writes a WAL record to end the recovery. Inject a new WAL record after the last replication slot to avoid slowness. Discussion: https://www.postgresql.org/message-id/2377319.1719766794%40sss.pgh.pa.us --- src/bin/pg_basebackup/pg_createsubscriber.c | 23 +++++++++++++++++++++ 1 file changed, 23 insertions(+) diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c index b02318782a6..00976c643a1 100644 --- a/src/bin/pg_basebackup/pg_createsubscriber.c +++ b/src/bin/pg_basebackup/pg_createsubscriber.c @@ -778,6 +778,29 @@ setup_publisher(struct LogicalRepInfo *dbinfo) else exit(1); + /* + * An idle server might not write a new WAL record until the recovery + * is about to end. Since pg_createsubscriber is using the LSN + * returned by the last replication slot as recovery_target_lsn, this + * LSN is ahead of the current WAL position and the recovery waits + * until something writes a WAL record to reach the target and ends + * the recovery. To avoid the recovery slowness in this case, injects + * a new WAL record here. + */ + if (i == num_dbs - 1 && !dry_run) + { + PGresult *res; + + res = PQexec(conn, "SELECT pg_log_standby_snapshot()"); + if (PQresultStatus(res) != PGRES_TUPLES_OK) + { + pg_log_error("could not write an additional WAL record: %s", + PQresultErrorMessage(res)); + disconnect_database(conn, true); + } + PQclear(res); + } + disconnect_database(conn, false); } -- 2.30.2