RE: speed up a logical replica setup

Dear Vignesh,

Since no one updates, here are new patch set.
Note that comments from Peter E. was not addressed because
Euler seemed to have already fixed.

> 3) Error conditions is verbose mode has invalid error message like
> "out of memory" messages like in below:
> pg_createsubscriber: waiting the postmaster to reach the consistent state
> pg_createsubscriber: postmaster reached the consistent state
> pg_createsubscriber: dropping publication "pg_createsubscriber_5" on
> database "postgres"
> pg_createsubscriber: creating subscription
> "pg_createsubscriber_5_278343" on database "postgres"
> pg_createsubscriber: error: could not create subscription
> "pg_createsubscriber_5_278343" on database "postgres": out of memory

Descriptions were added.

> 4) In error cases we try to drop this publication again resulting in error:
> + /*
> + * Since the publication was created before the
> consistent LSN, it is
> + * available on the subscriber when the physical
> replica is promoted.
> + * Remove publications from the subscriber because it
> has no use.
> + */
> + drop_publication(conn, &dbinfo[i]);
>
> Which throws these errors(because of drop publication multiple times):
> pg_createsubscriber: dropping publication "pg_createsubscriber_5" on
> database "postgres"
> pg_createsubscriber: error: could not drop publication
> "pg_createsubscriber_5" on database "postgres": ERROR: publication
> "pg_createsubscriber_5" does not exist
> pg_createsubscriber: dropping publication "pg_createsubscriber_5" on
> database "postgres"
> pg_createsubscriber: dropping the replication slot
> "pg_createsubscriber_5_278343" on database "postgres"

Changed to ... IF EXISTS. I thought your another proposal looked not good
because if the flag was turned off, the publication on publisher node could
not be dropped.

> 5) In error cases, wait_for_end_recovery waits even though it has
> identified that the replication between primary and standby is
> stopped:
> +/*
> + * Is recovery still in progress?
> + * If the answer is yes, it returns 1, otherwise, returns 0. If an error occurs
> + * while executing the query, it returns -1.
> + */
> +static int
> +server_is_in_recovery(PGconn *conn)
> +{
> + PGresult *res;
> + int ret;
> +
> + res = PQexec(conn, "SELECT pg_catalog.pg_is_in_recovery()");
> +
> + if (PQresultStatus(res) != PGRES_TUPLES_OK)
> + {
> + PQclear(res);
> + pg_log_error("could not obtain recovery progress");
> + return -1;
> + }
> +
>
> You can simulate this by stopping the primary just before
> wait_for_end_recovery and you will see these error messages, but
> pg_createsubscriber will continue to wait:
> pg_createsubscriber: error: could not obtain recovery progress
> pg_createsubscriber: error: could not obtain recovery progress
> pg_createsubscriber: error: could not obtain recovery progress
> pg_createsubscriber: error: could not obtain recovery progress

Based on idea from Euler, I roughly implemented. Thought?

0001-0013 were not changed from the previous version.

V24-0014: addressed your comment in the replied e-mail.
V24-0015: Add disconnect_database() again, per [3]
V24-0016: addressed your comment in [4].
V24-0017: addressed your comment in [5].
V24-0018: addressed your comment in [6].

[1]: https://www.postgresql.org/message-id/3ee79f2c-e8b3-4342-857c-a31b87e1afda%40eisentraut.org
[2]: https://www.postgresql.org/message-id/CALDaNm1ocVQmWhUJqxJDmR8N%3DCTbrH5GCdFU72ywnVRV6dND2A%40mail.gmail.com
[3]: https://www.postgresql.org/message-id/202402201053.6jjvdrm7kahf%40alvherre.pgsql
[4]: https://www.postgresql.org/message-id/CALDaNm2qHuZZvh6ym6OM367RfozU7RaaRDSm%3DF8M3SNrcQG2pQ%40mail.gmail.com
[5]: https://www.postgresql.org/message-id/CALDaNm3Q5W%3DEvphDjHA1n8ii5fv2DvxVShSmQLNFgeiHsOUwPg%40mail.gmail.com
[6]: https://www.postgresql.org/message-id/CALDaNm1M73ds0GBxX-XZX56f1D%2BGPojeCCwo-DLTVnfu8DMAvw%40mail.gmail.com

Best Regards,
Hayato Kuroda
FUJITSU LIMITED
https://www.fujitsu.com/