RE: speed up a logical replica setup - Mailing list pgsql-hackers

From Hayato Kuroda (Fujitsu)
Subject RE: speed up a logical replica setup
Date
Msg-id TYCPR01MB12077CD333376B53F9CAE7AC0F5562@TYCPR01MB12077.jpnprd01.prod.outlook.com
Whole thread Raw
In response to Re: speed up a logical replica setup  (vignesh C <vignesh21@gmail.com>)
Responses Re: speed up a logical replica setup
List pgsql-hackers
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/ 


Attachment

pgsql-hackers by date:

Previous
From: Matthias van de Meent
Date:
Subject: Re: btree: downlink right separator/HIKEY optimization
Next
From: "Hayato Kuroda (Fujitsu)"
Date:
Subject: RE: speed up a logical replica setup