Horton

RE: speed up a logical replica setup

Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>

View thread
From: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
To: 'vignesh C' <vignesh21@gmail.com>
Cc: Euler Taveira <euler@eulerto.com>, "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>, Michael Paquier <michael@paquier.xyz>, Peter Eisentraut <peter@eisentraut.org>, Andres Freund <andres@anarazel.de>, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>, Shlok Kyal <shlok.kyal.oss@gmail.com>, Fabrízio de Royes Mello <fabriziomello@gmail.com>
Date: 2024-02-22T15:45:02Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. pg_createsubscriber: Remove obsolete comment

  2. pg_createsubscriber: Fix an unpredictable recovery wait time.

  3. Fix unstable test in 040_pg_createsubscriber.

  4. Fix the testcase introduced in commit 81d20fbf7a.

  5. Further weaken new pg_createsubscriber test on Windows.

  6. Temporarily(?) weaken new pg_createsubscriber test on Windows.

  7. Make pg_createsubscriber warn if publisher has two-phase commit enabled.

  8. Make pg_createsubscriber more wary about quoting connection parameters.

  9. pg_createsubscriber: Remove failover replication slots on subscriber

  10. pg_createsubscriber: Remove replication slot check on primary

  11. pg_createsubscriber: Only --recovery-timeout controls the end of recovery process

  12. pg_createsubscriber: creates a new logical replica from a standby server

  13. Add some const decorations

  14. Add option force_initdb to PostgreSQL::Test::Cluster:init()

  15. Remove MSVC scripts

Attachments

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/