Thread: Enhance pg_createsubscriber to create required standby.
Hi All, Currently, pg_createsubscriber is designed to convert an existing physical replica into a logical subscriber. To use it, the user must manually set up a standby node beforehand, ensure that physical replication is active, and only then run pg_createsubscriber to perform the switchover to logical replication. To simplify this workflow, I propose enhancing the pg_createsubscriber utility to handle the creation of the standby node as part of the pg_createsubscriber itself. This idea was also suggested earlier by Andres Freund in [1]. To support this, I have introduced a new option: --create-standby This allows the user to specify the parameters needed for pg_basebackup to automatically create the standby node, establish physical replication, and then proceed with the existing steps to convert it into a logical subscriber. Following is the output that creates the standby node: ./pg_createsubscriber -D standby/ -P "host=localhost port=5432 dbname=postgres" --create-standby --verbose pg_createsubscriber: creating the standby server: "/home/shubham/Project/Git/postgres/inst/bin/pg_basebackup" -d'host=localhost port=5432 dbname=postgres' -D standby -P -X stream -R 22881/22881 kB (100%), 1/1 tablespace pg_createsubscriber: set standby port in standby/postgresql.auto.conf: port = 50432 pg_createsubscriber: starting the standby server: "/home/shubham/Project/Git/postgres/inst/bin/pg_ctl" -D "standby" -l "standby/standby.log" -o "-p 50432" start waiting for server to start.... done server started pg_createsubscriber: configuring the standby server: "/home/shubham/Project/Git/postgres/inst/bin/psql" -d postgres -p 50432 -c "ALTER SYSTEM SET wal_level = 'logical';" ALTER SYSTEM pg_createsubscriber: configuring the standby server: "/home/shubham/Project/Git/postgres/inst/bin/psql" -d postgres -p 50432 -c "ALTER SYSTEM SET listen_addresses = '*';" ALTER SYSTEM pg_createsubscriber: stopping the standby server: "/home/shubham/Project/Git/postgres/inst/bin/pg_ctl" -D "standby" -l "standby/standby.log" -o "-p 50432" stop waiting for server to shut down.... done server stopped pg_createsubscriber: validating publisher connection string Currently, pg_basebackup is executed with the following default parameters: pg_basebackup <primary_node_connection_info> -D <sub_dir> -P -U replication -X stream -R If users wish to customize these parameters, a new option pg_basebackup_parameters can be supported in the future. Similarly, the standby node is created using the default configuration, except for the port and wal_level settings. To customize other aspects of the standby setup, a new option standby_config can be supported in the future if required. [1] - https://www.postgresql.org/message-id/20220221232849.x6s24ete4eyg6jol%40alap3.anarazel.de Thanks and regards, Shubham Khanna.
Attachment
On Wed, Jun 4, 2025 at 2:39 PM Peter Eisentraut <peter@eisentraut.org> wrote: > > On 04.06.25 05:56, Shubham Khanna wrote: > > Currently, pg_createsubscriber is designed to convert an existing > > physical replica into a logical subscriber. To use it, the user must > > manually set up a standby node beforehand, ensure that physical > > replication is active, and only then run pg_createsubscriber to > > perform the switchover to logical replication. > > To simplify this workflow, I propose enhancing the pg_createsubscriber > > utility to handle the creation of the standby node as part of the > > pg_createsubscriber itself. > > Yes, this was contemplated when pg_createsubscriber was first proposed. > We opted against it, mainly for simplicity initially. > > It's not clear to me how this change now would substantially improve the > user experience. The number of characters you type is approximately the > same. You still need to support the old mode because the backup might > not come from pg_basebackup. In the current functionality, the user must first manually create a standby or use an existing standby to make it a subscriber. I thought saving this step for users would be quite helpful. It also helps streamline the process into a single, cohesive workflow. > > And then you'd have the maintenance > overhead that every new feature in pg_basebackup would potentially have > to be passed through or somehow be integrated into pg_createsubscriber. > I am not so sure about this because we use other utilities like pg_ctl in this tool, so same argument could be build for it. -- With Regards, Amit Kapila.
On 04.06.25 11:56, Amit Kapila wrote: >> It's not clear to me how this change now would substantially improve the >> user experience. The number of characters you type is approximately the >> same. You still need to support the old mode because the backup might >> not come from pg_basebackup. > > In the current functionality, the user must first manually create a > standby or use an existing standby to make it a subscriber. I thought > saving this step for users would be quite helpful. It also helps > streamline the process into a single, cohesive workflow. Unless I'm missing something, doesn't this merely replace pg_basebackup && pg_createsubscriber with pg_createsubscriber --create-standby I mean, as I'm typing this out, this is literally the same number of characters. Is the second one easier somehow? It's not clear. >> And then you'd have the maintenance >> overhead that every new feature in pg_basebackup would potentially have >> to be passed through or somehow be integrated into pg_createsubscriber. > > I am not so sure about this because we use other utilities like pg_ctl > in this tool, so same argument could be build for it. Yes, and we tried really hard to avoid the dependency on pg_ctl, but it was too hard. (I would entertain patches to get rid of it.)