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: 'Shlok Kyal' <shlok.kyal.oss@gmail.com>, Euler Taveira <euler@eulerto.com>
Cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>, vignesh C <vignesh21@gmail.com>, 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>, Fabrízio de Royes Mello <fabriziomello@gmail.com>
Date: 2024-02-22T12:43:53Z
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

Dear Shlok,

> Hi,
> 
> I have reviewed the v21 patch. And found an issue.
> 
> Initially I started the standby server with a new postgresql.conf file
> (not the default postgresql.conf that is present in the instance).
> pg_ctl -D ../standby start -o "-c config_file=/new_path/postgresql.conf"
> 
> And I have made 'max_replication_slots = 1' in new postgresql.conf and
> made  'max_replication_slots = 0' in the default postgresql.conf file.
> Now when we run pg_createsubscriber on standby we get error:
> pg_createsubscriber: error: could not set replication progress for the
> subscription "pg_createsubscriber_5_242843": ERROR:  cannot query or
> manipulate replication origin when max_replication_slots = 0
> NOTICE:  dropped replication slot "pg_createsubscriber_5_242843" on publisher
> pg_createsubscriber: error: could not drop publication
> "pg_createsubscriber_5" on database "postgres": ERROR:  publication
> "pg_createsubscriber_5" does not exist
> pg_createsubscriber: error: could not drop replication slot
> "pg_createsubscriber_5_242843" on database "postgres": ERROR:
> replication slot "pg_createsubscriber_5_242843" does not exist
> 
> I observed that when we run the pg_createsubscriber command, it will
> stop the standby instance (the non-default postgres configuration) and
> restart the standby instance which will now be started with default
> postgresql.conf, where the 'max_replication_slot = 0' and
> pg_createsubscriber will now fail with the error given above.
> I have added the script file with which we can reproduce this issue.
> Also similar issues can happen with other configurations such as port, etc.

Possible. So the issue is that GUC settings might be changed after the restart
so that the verification phase may not be enough. There are similar GUCs in [1]
and they may have similar issues. E.g., if "hba_file" is set when the server
started, the file cannot be seen after the restart, so pg_createsubscriber may
not connect to it anymore.

> The possible solution would be
> 1) allow to run pg_createsubscriber if standby is initially stopped .
> I observed that pg_logical_createsubscriber also uses this approach.
> 2) read GUCs via SHOW command and restore them when server restarts
>

I also prefer the first solution. 
Another reason why the standby should be stopped is for backup purpose.
Basically, the standby instance should be saved before running pg_createsubscriber.
An easiest way is hard-copy, and the postmaster should be stopped at that time.
I felt it is better that users can run the command immediately later the copying.
Thought?

[1]: https://www.postgresql.org/docs/current/storage-file-layout.html

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