Enhance pg_createsubscriber to create required standby.

From: Shubham Khanna <khannashubham1197(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Enhance pg_createsubscriber to create required standby.
Date: 2025-06-04 03:56:12
Message-ID: CAHv8RjJcCRLA15UFUm0U7s8qqC-tdjw36zhCR_6ppB7UNcfqDQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 Content-Type Size
v1-0001-pg_basebackup-via-pg_createsubscriber.patch application/octet-stream 11.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Thomas Munro 2025-06-04 04:03:27 Custom Glibc collation version strings under LOCPATH
Previous Message Jeff Davis 2025-06-04 03:22:32 [19] Proposal: function markers to indicate collation/ctype sensitivity