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]/messages/by-id/20220221232849.x6s24ete4eyg6jol@alap3.anarazel.de.
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]: /messages/by-id/20220221232849.x6s24ete4eyg6jol@alap3.anarazel.de
Thanks and regards,
Shubham Khanna.
Attachments:
v1-0001-pg_basebackup-via-pg_createsubscriber.patchapplication/octet-stream; name=v1-0001-pg_basebackup-via-pg_createsubscriber.patchDownload
From 16b0864f6daba9e23a8ed53d001099d0e51c0194 Mon Sep 17 00:00:00 2001
From: Khanna <Shubham.Khanna@fujitsu.com>
Date: Tue, 22 Apr 2025 09:36:29 +0530
Subject: [PATCH v1] pg_basebackup via pg_createsubscriber
This patch adds support for initializing a standby node using 'pg_basebackup'
through the 'pg_createsubscriber' tool. A new '--create-standby' option is
introduced, which automates the process of performing a base backup from the
publisher, and configuring the standby node for streaming replication.
To support this functionality, the patch introduces helper functions for
invoking 'pg_basebackup', and configuring the standby server.
It also adds support for managing external binary paths for 'pg_basebackup' and
'psql', making the tool more flexible in varied environments.
By integrating 'pg_basebackup', this patch enhances the usability of
'pg_createsubscriber' by enabling a streamlined, end-to-end setup of
subscriber nodes.
---
doc/src/sgml/ref/pg_createsubscriber.sgml | 13 ++
src/bin/pg_basebackup/pg_createsubscriber.c | 126 +++++++++++++++++-
.../t/040_pg_createsubscriber.pl | 54 ++++++++
3 files changed, 188 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml
index 4b1d08d5f16..44142f23674 100644
--- a/doc/src/sgml/ref/pg_createsubscriber.sgml
+++ b/doc/src/sgml/ref/pg_createsubscriber.sgml
@@ -108,6 +108,19 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-c</option></term>
+ <term><option>--create-standby</option></term>
+ <listitem>
+ <para>
+ Initializes the subscriber node as a physical standby using
+ <xref linkend="app-pgbasebackup"/>. This automates the process of taking
+ a base backup from the publisher server, configuring the standby server,
+ and preparing it for logical replication.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-d <replaceable class="parameter">dbname</replaceable></option></term>
<term><option>--database=<replaceable class="parameter">dbname</replaceable></option></term>
diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c
index f65acc7cb11..0c0a5f1df9a 100644
--- a/src/bin/pg_basebackup/pg_createsubscriber.c
+++ b/src/bin/pg_basebackup/pg_createsubscriber.c
@@ -27,6 +27,9 @@
#include "fe_utils/simple_list.h"
#include "fe_utils/string_utils.h"
#include "getopt_long.h"
+#include <dirent.h>
+#include <unistd.h>
+#include "port.h"
#define DEFAULT_SUB_PORT "50432"
#define OBJECTTYPE_PUBLICATIONS 0x0001
@@ -47,6 +50,7 @@ struct CreateSubscriberOptions
int recovery_timeout; /* stop recovery after this time */
bool all_dbs; /* all option */
SimpleStringList objecttypes_to_remove; /* list of object types to remove */
+ bool create_standby; /* enable standby setup via pg_basebackup */
};
/* per-database publication/subscription info */
@@ -127,6 +131,8 @@ static void drop_existing_subscriptions(PGconn *conn, const char *subname,
const char *dbname);
static void get_publisher_databases(struct CreateSubscriberOptions *opt,
bool dbnamespecified);
+static void create_standby(const struct CreateSubscriberOptions *opt, const char *standby_dir);
+static void configure_standby(const struct CreateSubscriberOptions *opt, const char *standby_dir);
#define USEC_PER_SEC 1000000
#define WAIT_INTERVAL 1 /* 1 second */
@@ -148,6 +154,8 @@ static pg_prng_state prng_state;
static char *pg_ctl_path = NULL;
static char *pg_resetwal_path = NULL;
+static char *pg_basebackup_path = NULL;
+static char *psql_path = NULL;
/* standby / subscriber data directory */
static char *subscriber_dir = NULL;
@@ -248,6 +256,7 @@ usage(void)
printf(_("\nOptions:\n"));
printf(_(" -a, --all create subscriptions for all databases except template\n"
" databases or databases that don't allow connections\n"));
+ printf(_(" -c --create-standby prepare the standby using pg_basebackup\n"));
printf(_(" -d, --database=DBNAME database in which to create a subscription\n"));
printf(_(" -D, --pgdata=DATADIR location for the subscriber data directory\n"));
printf(_(" -n, --dry-run dry run, just show what would be done\n"));
@@ -1405,6 +1414,96 @@ create_logical_replication_slot(PGconn *conn, struct LogicalRepInfo *dbinfo)
return lsn;
}
+/*
+ * Function to create a standby server using pg_basebackup.
+ */
+static void
+create_standby(const struct CreateSubscriberOptions *opt,
+ const char *standby_dir)
+{
+ PQExpBuffer pg_basebackup_cmd = createPQExpBuffer();
+ char auto_conf_path[MAXPGPATH];
+ char port_setting[64];
+
+ appendPQExpBuffer(pg_basebackup_cmd, "\"%s\" -d", pg_basebackup_path);
+ appendShellString(pg_basebackup_cmd, opt->pub_conninfo_str);
+ appendPQExpBufferStr(pg_basebackup_cmd, " -D ");
+ appendShellString(pg_basebackup_cmd, standby_dir);
+ appendPQExpBufferStr(pg_basebackup_cmd, " -P -X stream -R");
+
+ pg_log_info("creating the standby server: %s", pg_basebackup_cmd->data);
+
+ if (!dry_run && system(pg_basebackup_cmd->data) != 0)
+ pg_fatal("pg_basebackup command failed");
+
+ snprintf(auto_conf_path, sizeof(auto_conf_path), "%s/postgresql.auto.conf", standby_dir);
+ snprintf(port_setting, sizeof(port_setting), "port = %s\n", opt->sub_port);
+
+ if (!dry_run)
+ {
+ FILE *f = fopen(auto_conf_path, "a");
+
+ if (!f)
+ pg_fatal("could not open %s for appending: %m", auto_conf_path);
+ if (fprintf(f, "%s", port_setting) < 0)
+ {
+ fclose(f);
+ pg_fatal("could not write port setting to %s: %m", auto_conf_path);
+ }
+ fclose(f);
+ pg_log_info("set standby port in %s: %s", auto_conf_path, port_setting);
+ }
+ else
+ {
+ pg_log_info("dry run: would append to %s: %s", auto_conf_path, port_setting);
+ }
+
+ destroyPQExpBuffer(pg_basebackup_cmd);
+}
+
+/*
+ * Function to configure the standby server for logical replication.
+ */
+static void
+configure_standby(const struct CreateSubscriberOptions *opt,
+ const char *standby_dir)
+{
+ static const char *alter_cmds[] =
+ {
+ "ALTER SYSTEM SET wal_level = 'logical';",
+ "ALTER SYSTEM SET listen_addresses = '*';",
+ };
+
+ char command[MAXPGPATH * 2];
+
+ /* Start the standby server */
+ snprintf(command, sizeof(command), "\"%s\" -D \"%s\" -l \"%s/standby.log\" -o \"-p %s\" start",
+ pg_ctl_path, standby_dir, standby_dir, opt->sub_port);
+ pg_log_info("starting the standby server: %s", command);
+
+ if (!dry_run && system(command) != 0)
+ pg_fatal("failed to start standby server");
+
+ for (int i = 0; i < lengthof(alter_cmds); i++)
+ {
+ snprintf(command, sizeof(command),
+ "\"%s\" -d postgres -p %s -c \"%s\"", psql_path,
+ opt->sub_port, alter_cmds[i]);
+ pg_log_info("configuring the standby server: %s", command);
+
+ if (!dry_run && system(command) != 0)
+ pg_fatal("failed to run: %s", alter_cmds[i]);
+ }
+
+ /* Stop the standby server */
+ snprintf(command, sizeof(command), "\"%s\" -D \"%s\" -l \"%s/standby.log\" -o \"-p %s\" stop",
+ pg_ctl_path, standby_dir, standby_dir, opt->sub_port);
+ pg_log_info("stopping the standby server: %s", command);
+
+ if (!dry_run && system(command) != 0)
+ pg_fatal("failed to stop standby server after ALTER SYSTEM");
+}
+
static void
drop_replication_slot(PGconn *conn, struct LogicalRepInfo *dbinfo,
const char *slot_name)
@@ -2021,6 +2120,7 @@ main(int argc, char **argv)
static struct option long_options[] =
{
{"all", no_argument, NULL, 'a'},
+ {"create-standby", no_argument, NULL, 'c'},
{"database", required_argument, NULL, 'd'},
{"pgdata", required_argument, NULL, 'D'},
{"dry-run", no_argument, NULL, 'n'},
@@ -2092,6 +2192,7 @@ main(int argc, char **argv)
};
opt.recovery_timeout = 0;
opt.all_dbs = false;
+ opt.create_standby = false;
/*
* Don't allow it to be run as root. It uses pg_ctl which does not allow
@@ -2109,7 +2210,7 @@ main(int argc, char **argv)
get_restricted_token();
- while ((c = getopt_long(argc, argv, "ad:D:np:P:R:s:t:TU:v",
+ while ((c = getopt_long(argc, argv, "acd:D:np:P:R:s:t:TU:v",
long_options, &option_index)) != -1)
{
switch (c)
@@ -2117,6 +2218,9 @@ main(int argc, char **argv)
case 'a':
opt.all_dbs = true;
break;
+ case 'c':
+ opt.create_standby = true;
+ break;
case 'd':
if (!simple_string_list_member(&opt.database_names, optarg))
{
@@ -2264,6 +2368,22 @@ main(int argc, char **argv)
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
exit(1);
}
+
+ /*
+ * Get the absolute path of pg_ctl, pg_resetwal, pg_basebackup and psql on
+ * the subscriber
+ */
+ pg_ctl_path = get_exec_path(argv[0], "pg_ctl");
+ pg_resetwal_path = get_exec_path(argv[0], "pg_resetwal");
+ pg_basebackup_path = get_exec_path(argv[0], "pg_basebackup");
+ psql_path = get_exec_path(argv[0], "psql");
+
+ if (opt.create_standby)
+ {
+ create_standby(&opt, subscriber_dir);
+ configure_standby(&opt, subscriber_dir);
+ }
+
pg_log_info("validating publisher connection string");
pub_base_conninfo = get_base_conninfo(opt.pub_conninfo_str,
&dbname_conninfo);
@@ -2346,10 +2466,6 @@ main(int argc, char **argv)
}
}
- /* Get the absolute path of pg_ctl and pg_resetwal on the subscriber */
- pg_ctl_path = get_exec_path(argv[0], "pg_ctl");
- pg_resetwal_path = get_exec_path(argv[0], "pg_resetwal");
-
/* Rudimentary check for a data directory */
check_data_directory(subscriber_dir);
diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
index 2d532fee567..eb924d4d705 100644
--- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
+++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl
@@ -8,6 +8,8 @@ use warnings FATAL => 'all';
use PostgreSQL::Test::Cluster;
use PostgreSQL::Test::Utils;
use Test::More;
+use File::Path qw(rmtree);
+use File::Copy;
program_help_ok('pg_createsubscriber');
program_version_ok('pg_createsubscriber');
@@ -537,6 +539,58 @@ my $sysid_s = $node_s->safe_psql('postgres',
'SELECT system_identifier FROM pg_control_system()');
ok($sysid_p != $sysid_s, 'system identifier was changed');
+$node_s->stop;
+
+my $tempdir = PostgreSQL::Test::Utils::tempdir;
+my $conf_file = $node_s->data_dir . '/' . 'postgresql.conf';
+
+copy("$conf_file", "$tempdir/postgresql.conf") or die "Copy failed: $!";
+
+# Remove an old directory.
+rmtree($node_s->data_dir);
+
+# Run pg_createsubscriber on node S with '--create-standby' option
+command_ok(
+ [
+ 'pg_createsubscriber',
+ '--verbose', '--verbose',
+ '--recovery-timeout' => $PostgreSQL::Test::Utils::timeout_default,
+ '--pgdata' => $node_s->data_dir,
+ '--publisher-server' => $node_p->connstr($db1),
+ '--publication' => 'pub3',
+ '--publication' => 'pub4',
+ '--database' => $db1,
+ '--database' => $db2,
+ '--create-standby',
+ ],
+ 'run pg_createsubscriber on node S with --create-standby');
+
+# Insert a row on P
+$node_p->safe_psql($db1, "INSERT INTO tbl1 VALUES('fourth row')");
+
+my $port = $node_s->port;
+
+$node_s->append_conf(
+ 'postgresql.conf', qq[
+hot_standby_feedback = on
+]);
+$node_s->append_conf(
+ 'postgresql.auto.conf', qq[
+port = $port
+]);
+
+copy("$tempdir/postgresql.conf", "$conf_file") or die "Copy failed: $!";
+
+$node_s->start;
+
+# Check result in database $db1
+$result = $node_s->safe_psql($db1, 'SELECT * FROM tbl1');
+is( $result, qq(first row
+second row
+third row
+fourth row),
+ "logical replication works in database $db1");
+
# clean up
$node_p->teardown_node;
$node_s->teardown_node;
--
2.41.0.windows.3
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. 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 think I prefer the "one tool for one job" approach for this. But
maybe there is some advantage here that I'm not seeing.
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.)
On Thu, 5 Jun 2025 at 01:50, Peter Eisentraut <peter@eisentraut.org> wrote:
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.
I believe adding support for standby creation directly within
pg_createsubscriber offers several key advantages over the manual
two-step approach of running pg_basebackup followed by
pg_createsubscriber. The main benefits include:
1) Improved Progress Reporting: pg_createsubscriber can be enhanced to
provide a clear, step-by-step progress indicator that shows both the
total number of steps and the current step being executed. For
example:
[Step 1/5] Executing pg_basebackup...
[Step 2/5] Validating prerequisites...
[Step 3/5] Creating publication and replication slot...
[Step 4/5] Waiting for standby to reach consistent state...
[Step 5/5] Creating subscription...
2) Graceful Handling of Interruptions: pg_createsubscriber can be
enhanced to handle user interruptions (e.g., Ctrl+C) more gracefully
by cleaning up any partially created directories, such as the base
backup target directory. This can be achieved by registering an atexit
handler for safe cleanup during early termination.
3) Cleanup on Partial Failure: If pg_basebackup completes but a
subsequent step (e.g., subscription or replication slot creation)
fails, pg_createsubscriber could offer the option to automatically
clean up the standby contents. In contrast, with the manual
pg_basebackup && pg_createsubscriber approach, failures after the
backup step leave residual data on disk, as there’s no built-in
cleanup mechanism.
4) Simplified Option Handling: The --create-standby option streamlines
the workflow by removing the need to manually pass options like -D and
-R. With this option, the user can simply run:
pg_createsubscriber -D <target_dir> -P <primary_connstr> -d
<target_database> --create-standby
In the combined commands approach:
#primary is on the different server
pg_basebackup -D <target_dir> -d <primary_connstr> -R &&
pg_createsubscriber -d <target_database> -D <target_dir> -P
<primary_connstr>
#primary is on the same server
pg_basebackup -D <target_dir> -R && pg_createsubscriber -d
<target_database> -D <target_dir> -P <primary_connstr>
The integrated --create-standby option reduces the risk of user error,
misconfiguration, and is shorter to write for users.
Based on the above points I felt the create-standby option through
pg_createsubscriber will be helpful to users. Your thoughts?
Regards,
Vignesh
On Thu, Jun 5, 2025 at 1:50 AM Peter Eisentraut <peter@eisentraut.org> wrote:
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.
As shown in Vignesh's email [1]/messages/by-id/CALDaNm1biZBMOzFMfHYzqrAeosJSD5YRG=82-pp6+JhALsfe6w@mail.gmail.com (point 4), there could be multiple
additional parameters required for the first option suggested by you,
which will make it longer. Additionally, there are some other benefits
of having the second option (pg_createsubscriber --create-standby),
like better cleanup of contents during failures and better progress
reporting. Are you still against adding such an option?
[1]: /messages/by-id/CALDaNm1biZBMOzFMfHYzqrAeosJSD5YRG=82-pp6+JhALsfe6w@mail.gmail.com
--
With Regards,
Amit Kapila.
On Tue, Jun 17, 2025 at 9:22 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
As shown in Vignesh's email [1] (point 4), there could be multiple
additional parameters required for the first option suggested by you,
which will make it longer. Additionally, there are some other benefits
of having the second option (pg_createsubscriber --create-standby),
like better cleanup of contents during failures and better progress
reporting. Are you still against adding such an option?[1]:
/messages/by-id/CALDaNm1biZBMOzFMfHYzqrAeosJSD5YRG=82-pp6+JhALsfe6w@mail.gmail.com
None of those benefits convince me that "let's write a shell script in C
and put it under an annual feature release policy" is the way to go here.
Let's make something like this available to the community, say on the Wiki,
and make it work in PostgreSQL 18 so they can benefit from it today, and
readily use it as a basis to tweak things for their own unique
circumstances.
David J.
On Wed, Jun 18, 2025 at 10:43 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
On Tue, Jun 17, 2025 at 9:22 PM Amit Kapila <amit.kapila16@gmail.com> wrote:
As shown in Vignesh's email [1] (point 4), there could be multiple
additional parameters required for the first option suggested by you,
which will make it longer. Additionally, there are some other benefits
of having the second option (pg_createsubscriber --create-standby),
like better cleanup of contents during failures and better progress
reporting. Are you still against adding such an option?[1]: /messages/by-id/CALDaNm1biZBMOzFMfHYzqrAeosJSD5YRG=82-pp6+JhALsfe6w@mail.gmail.com
None of those benefits convince me that "let's write a shell script in C and put it under an annual feature release policy" is the way to go here.
I don't think it is equivalent to writing a simple script as you are
imagining, for example the future enhancements in this tool could make
such scripts require constant updates. Also, not sure providing Ctrl+C
or progress reporting would be any easier with scripting.
I feel this is a basic requirement to create a subscriber-node from
scratch and for that if we refer users for external scripts, it would
be inconvenient for users.
--
With Regards,
Amit Kapila.
On Wed, Jun 04, 2025 at 03:26:24PM +0530, Amit Kapila wrote:
On Wed, Jun 4, 2025 at 2:39 PM Peter Eisentraut <peter@eisentraut.org> 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.
+ appendPQExpBuffer(pg_basebackup_cmd, "\"%s\" -d", pg_basebackup_path);
+ appendShellString(pg_basebackup_cmd, opt->pub_conninfo_str);
+ appendPQExpBufferStr(pg_basebackup_cmd, " -D ");
+ appendShellString(pg_basebackup_cmd, standby_dir);
+ appendPQExpBufferStr(pg_basebackup_cmd, " -P -X stream -R");The proposed patch generates internally one single pg_basebackup
command that it thinks would be good enough for normal cases, but
pg_basebackup is able to do so much more lately in terms of formats,
client-side compression and server-side compression that I fear we are
going to need to provide an extra option for pg_createsubscriber to be
able to pass a list of options to it, or provide equivalents of what
pg_basebackup is doing. If we do that, then we'll need to worry about
providing correct quoting for the options given to the command line of
pg_createsubscriber, which can be easily itchy especially on WIN32
when passing commands to a command prompt.
I'd rather not go this way, keeping one tool for each job. The
arguments about the possibility to do an automated cleanup of a data
folder when creating a subscriber in one step's failure or the
possibility to embed a pg_resetwal command that reinitializes a system
ID are IMO too thin to justify the potential maintenance cost that we
would have to bear should pg_basebackup be made more complex in the
future. pg_basebackup becoming more complex means that
pg_createsubscriber would need to cope with the facilities added to
the former.
In short, I don't think that this patch is a good idea.
--
Michael
On 2025-07-30 We 8:38 PM, Michael Paquier wrote:
On Wed, Jun 04, 2025 at 03:26:24PM +0530, Amit Kapila wrote:
On Wed, Jun 4, 2025 at 2:39 PM Peter Eisentraut <peter@eisentraut.org> 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.+ appendPQExpBuffer(pg_basebackup_cmd, "\"%s\" -d", pg_basebackup_path); + appendShellString(pg_basebackup_cmd, opt->pub_conninfo_str); + appendPQExpBufferStr(pg_basebackup_cmd, " -D "); + appendShellString(pg_basebackup_cmd, standby_dir); + appendPQExpBufferStr(pg_basebackup_cmd, " -P -X stream -R");The proposed patch generates internally one single pg_basebackup
command that it thinks would be good enough for normal cases, but
pg_basebackup is able to do so much more lately in terms of formats,
client-side compression and server-side compression that I fear we are
going to need to provide an extra option for pg_createsubscriber to be
able to pass a list of options to it, or provide equivalents of what
pg_basebackup is doing. If we do that, then we'll need to worry about
providing correct quoting for the options given to the command line of
pg_createsubscriber, which can be easily itchy especially on WIN32
when passing commands to a command prompt.I'd rather not go this way, keeping one tool for each job. The
arguments about the possibility to do an automated cleanup of a data
folder when creating a subscriber in one step's failure or the
possibility to embed a pg_resetwal command that reinitializes a system
ID are IMO too thin to justify the potential maintenance cost that we
would have to bear should pg_basebackup be made more complex in the
future. pg_basebackup becoming more complex means that
pg_createsubscriber would need to cope with the facilities added to
the former.In short, I don't think that this patch is a good idea.
+1
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com