Skip to content

Commit fb2ea12

Browse files
author
Amit Kapila
committed
pg_createsubscriber: Add '--all' option.
The '--all' option indicates that the tool queries the source server (publisher) for all databases and creates subscriptions on the target server (subscriber) for databases with matching names. Without this user needs to explicitly specify all databases by using -d option for each database. This simplifies converting a physical standby to a logical subscriber, particularly during upgrades. The options '--database', '--publication', '--subscription', and '--replication-slot' cannot be used when '--all' is specified. Author: Shubham Khanna <khannashubham1197@gmail.com> Reviewed-by: vignesh C <vignesh21@gmail.com> Reviewed-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> Reviewed-by: Euler Taveira <euler@eulerto.com> Reviewed-by: Hayato Kuroda <kuroda.hayato@fujitsu.com> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Reviewed-by: Peter Smith <smithpb2250@gmail.com> Reviewed-by: Shlok Kyal <shlok.kyal.oss@gmail.com> Discussion: https://postgr.es/m/CAHv8RjKhA=_h5vAbozzJ1Opnv=KXYQHQ-fJyaMfqfRqPpnC2bA@mail.gmail.com
1 parent 890fc82 commit fb2ea12

File tree

3 files changed

+187
-11
lines changed

3 files changed

+187
-11
lines changed

‎doc/src/sgml/ref/pg_createsubscriber.sgml

Lines changed: 30 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -87,17 +87,37 @@ PostgreSQL documentation
8787
command-line arguments:
8888

8989
<variablelist>
90+
<varlistentry>
91+
<term><option>-a</option></term>
92+
<term><option>--all</option></term>
93+
<listitem>
94+
<para>
95+
Create one subscription per database on the target server. Exceptions
96+
are template databases and databases that don't allow connections.
97+
If the database name is not specified in publisher-server, the postgres
98+
database will be used, or if that does not exist, template1 will be used.
99+
Automatically generated names for subscriptions, publications, and
100+
replication slots are used when this option is specified.
101+
This option cannot be used along with <option>--database</option>,
102+
<option>--publication</option>, <option>--replication-slot</option>, or
103+
<option>--subscription</option>.
104+
</para>
105+
</listitem>
106+
</varlistentry>
107+
90108
<varlistentry>
91109
<term><option>-d <replaceable class="parameter">dbname</replaceable></option></term>
92110
<term><option>--database=<replaceable class="parameter">dbname</replaceable></option></term>
93111
<listitem>
94112
<para>
95113
The name of the database in which to create a subscription. Multiple
96114
databases can be selected by writing multiple <option>-d</option>
97-
switches. If <option>-d</option> option is not provided, the database
98-
name will be obtained from <option>-P</option> option. If the database
99-
name is not specified in either the <option>-d</option> option or
100-
<option>-P</option> option, an error will be reported.
115+
switches. This option cannot be used together with <option>-a</option>.
116+
If <option>-d</option> option is not provided, the database name will be
117+
obtained from <option>-P</option> option. If the database name is not
118+
specified in either the <option>-d</option> option, or the
119+
<option>-P</option> option, and <option>-a</option> option is not
120+
specified, an error will be reported.
101121
</para>
102122
</listitem>
103123
</varlistentry>
@@ -253,7 +273,8 @@ PostgreSQL documentation
253273
names must match the number of specified databases, otherwise an error
254274
is reported. The order of the multiple publication name switches must
255275
match the order of database switches. If this option is not specified,
256-
a generated name is assigned to the publication name.
276+
a generated name is assigned to the publication name. This option cannot
277+
be used together with <option>--all</option>.
257278
</para>
258279
</listitem>
259280
</varlistentry>
@@ -269,7 +290,8 @@ PostgreSQL documentation
269290
otherwise an error is reported. The order of the multiple replication
270291
slot name switches must match the order of database switches. If this
271292
option is not specified, the subscription name is assigned to the
272-
replication slot name.
293+
replication slot name. This option cannot be used together with
294+
<option>--all</option>.
273295
</para>
274296
</listitem>
275297
</varlistentry>
@@ -284,7 +306,8 @@ PostgreSQL documentation
284306
names must match the number of specified databases, otherwise an error
285307
is reported. The order of the multiple subscription name switches must
286308
match the order of database switches. If this option is not specified,
287-
a generated name is assigned to the subscription name.
309+
a generated name is assigned to the subscription name. This option cannot
310+
be used together with <option>--all</option>.
288311
</para>
289312
</listitem>
290313
</varlistentry>

‎src/bin/pg_basebackup/pg_createsubscriber.c

Lines changed: 100 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -45,6 +45,7 @@ struct CreateSubscriberOptions
4545
SimpleStringList sub_names; /* list of subscription names */
4646
SimpleStringList replslot_names; /* list of replication slot names */
4747
int recovery_timeout; /* stop recovery after this time */
48+
bool all_dbs; /* all option */
4849
SimpleStringList objecttypes_to_remove; /* list of object types to remove */
4950
};
5051

@@ -124,6 +125,8 @@ static void check_and_drop_existing_subscriptions(PGconn *conn,
124125
const struct LogicalRepInfo *dbinfo);
125126
static void drop_existing_subscriptions(PGconn *conn, const char *subname,
126127
const char *dbname);
128+
static void get_publisher_databases(struct CreateSubscriberOptions *opt,
129+
bool dbnamespecified);
127130

128131
#define USEC_PER_SEC 1000000
129132
#define WAIT_INTERVAL 1 /* 1 second */
@@ -243,6 +246,8 @@ usage(void)
243246
printf(_("Usage:\n"));
244247
printf(_(" %s [OPTION]...\n"), progname);
245248
printf(_("\nOptions:\n"));
249+
printf(_(" -a, --all create subscriptions for all databases except template\n"
250+
" databases or databases that don't allow connections\n"));
246251
printf(_(" -d, --database=DBNAME database in which to create a subscription\n"));
247252
printf(_(" -D, --pgdata=DATADIR location for the subscriber data directory\n"));
248253
printf(_(" -n, --dry-run dry run, just show what would be done\n"));
@@ -1959,11 +1964,65 @@ enable_subscription(PGconn *conn, const struct LogicalRepInfo *dbinfo)
19591964
destroyPQExpBuffer(str);
19601965
}
19611966

1967+
/*
1968+
* Fetch a list of all not-template databases from the source server and form
1969+
* a list such that they appear as if the user has specified multiple
1970+
* --database options, one for each source database.
1971+
*/
1972+
static void
1973+
get_publisher_databases(struct CreateSubscriberOptions *opt,
1974+
bool dbnamespecified)
1975+
{
1976+
PGconn *conn;
1977+
PGresult *res;
1978+
1979+
/* If a database name was specified, just connect to it. */
1980+
if (dbnamespecified)
1981+
conn = connect_database(opt->pub_conninfo_str, true);
1982+
else
1983+
{
1984+
/* Otherwise, try postgres first and then template1. */
1985+
char *conninfo;
1986+
1987+
conninfo = concat_conninfo_dbname(opt->pub_conninfo_str, "postgres");
1988+
conn = connect_database(conninfo, false);
1989+
pg_free(conninfo);
1990+
if (!conn)
1991+
{
1992+
conninfo = concat_conninfo_dbname(opt->pub_conninfo_str, "template1");
1993+
conn = connect_database(conninfo, true);
1994+
pg_free(conninfo);
1995+
}
1996+
}
1997+
1998+
res = PQexec(conn, "SELECT datname FROM pg_database WHERE datistemplate = false AND datallowconn AND datconnlimit <> -2 ORDER BY 1");
1999+
if (PQresultStatus(res) != PGRES_TUPLES_OK)
2000+
{
2001+
pg_log_error("could not obtain a list of databases: %s", PQresultErrorMessage(res));
2002+
PQclear(res);
2003+
disconnect_database(conn, true);
2004+
}
2005+
2006+
for (int i = 0; i < PQntuples(res); i++)
2007+
{
2008+
const char *dbname = PQgetvalue(res, i, 0);
2009+
2010+
simple_string_list_append(&opt->database_names, dbname);
2011+
2012+
/* Increment num_dbs to reflect multiple --database options */
2013+
num_dbs++;
2014+
}
2015+
2016+
PQclear(res);
2017+
disconnect_database(conn, false);
2018+
}
2019+
19622020
int
19632021
main(int argc, char **argv)
19642022
{
19652023
static struct option long_options[] =
19662024
{
2025+
{"all", no_argument, NULL, 'a'},
19672026
{"database", required_argument, NULL, 'd'},
19682027
{"pgdata", required_argument, NULL, 'D'},
19692028
{"dry-run", no_argument, NULL, 'n'},
@@ -2034,6 +2093,7 @@ main(int argc, char **argv)
20342093
0
20352094
};
20362095
opt.recovery_timeout = 0;
2096+
opt.all_dbs = false;
20372097

20382098
/*
20392099
* Don't allow it to be run as root. It uses pg_ctl which does not allow
@@ -2051,11 +2111,14 @@ main(int argc, char **argv)
20512111

20522112
get_restricted_token();
20532113

2054-
while ((c = getopt_long(argc, argv, "d:D:np:P:R:s:t:TU:v",
2114+
while ((c = getopt_long(argc, argv, "ad:D:np:P:R:s:t:TU:v",
20552115
long_options, &option_index)) != -1)
20562116
{
20572117
switch (c)
20582118
{
2119+
case 'a':
2120+
opt.all_dbs = true;
2121+
break;
20592122
case 'd':
20602123
if (!simple_string_list_member(&opt.database_names, optarg))
20612124
{
@@ -2149,6 +2212,28 @@ main(int argc, char **argv)
21492212
}
21502213
}
21512214

2215+
/* Validate that --all is not used with incompatible options */
2216+
if (opt.all_dbs)
2217+
{
2218+
char *bad_switch = NULL;
2219+
2220+
if (num_dbs > 0)
2221+
bad_switch = "--database";
2222+
else if (num_pubs > 0)
2223+
bad_switch = "--publication";
2224+
else if (num_replslots > 0)
2225+
bad_switch = "--replication-slot";
2226+
else if (num_subs > 0)
2227+
bad_switch = "--subscription";
2228+
2229+
if (bad_switch)
2230+
{
2231+
pg_log_error("%s cannot be used with --all", bad_switch);
2232+
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
2233+
exit(1);
2234+
}
2235+
}
2236+
21522237
/* Any non-option arguments? */
21532238
if (optind < argc)
21542239
{
@@ -2202,14 +2287,25 @@ main(int argc, char **argv)
22022287
pg_log_info("validating subscriber connection string");
22032288
sub_base_conninfo = get_sub_conninfo(&opt);
22042289

2290+
/*
2291+
* Fetch all databases from the source (publisher) and treat them as if
2292+
* the user specified has multiple --database options, one for each source
2293+
* database.
2294+
*/
2295+
if (opt.all_dbs)
2296+
{
2297+
bool dbnamespecified = (dbname_conninfo != NULL);
2298+
2299+
get_publisher_databases(&opt, dbnamespecified);
2300+
}
2301+
22052302
if (opt.database_names.head == NULL)
22062303
{
22072304
pg_log_info("no database was specified");
22082305

22092306
/*
2210-
* If --database option is not provided, try to obtain the dbname from
2211-
* the publisher conninfo. If dbname parameter is not available, error
2212-
* out.
2307+
* Try to obtain the dbname from the publisher conninfo. If dbname
2308+
* parameter is not available, error out.
22132309
*/
22142310
if (dbname_conninfo)
22152311
{

‎src/bin/pg_basebackup/t/040_pg_createsubscriber.pl

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -386,6 +386,63 @@ sub generate_db
386386
],
387387
'run pg_createsubscriber without --databases');
388388

389+
# run pg_createsubscriber with '--database' and '--all' without '--dry-run'
390+
# and verify the failure
391+
command_fails_like(
392+
[
393+
'pg_createsubscriber',
394+
'--verbose',
395+
'--pgdata' => $node_s->data_dir,
396+
'--publisher-server' => $node_p->connstr($db1),
397+
'--socketdir' => $node_s->host,
398+
'--subscriber-port' => $node_s->port,
399+
'--database' => $db1,
400+
'--all',
401+
],
402+
qr/--database cannot be used with --all/,
403+
'fail if --database is used with --all');
404+
405+
# run pg_createsubscriber with '--publication' and '--all' and verify
406+
# the failure
407+
command_fails_like(
408+
[
409+
'pg_createsubscriber',
410+
'--verbose',
411+
'--dry-run',
412+
'--pgdata' => $node_s->data_dir,
413+
'--publisher-server' => $node_p->connstr($db1),
414+
'--socketdir' => $node_s->host,
415+
'--subscriber-port' => $node_s->port,
416+
'--all',
417+
'--publication' => 'pub1',
418+
],
419+
qr/--publication cannot be used with --all/,
420+
'fail if --publication is used with --all');
421+
422+
# run pg_createsubscriber with '--all' option
423+
my ($stdout, $stderr) = run_command(
424+
[
425+
'pg_createsubscriber',
426+
'--verbose',
427+
'--dry-run',
428+
'--recovery-timeout' => $PostgreSQL::Test::Utils::timeout_default,
429+
'--pgdata' => $node_s->data_dir,
430+
'--publisher-server' => $node_p->connstr,
431+
'--socketdir' => $node_s->host,
432+
'--subscriber-port' => $node_s->port,
433+
'--all',
434+
],
435+
'run pg_createsubscriber with --all');
436+
437+
# Verify that the required logical replication objects are output.
438+
# The expected count 3 refers to postgres, $db1 and $db2 databases.
439+
is(scalar(() = $stderr =~ /creating publication/g),
440+
3, "verify publications are created for all databases");
441+
is(scalar(() = $stderr =~ /creating the replication slot/g),
442+
3, "verify replication slots are created for all databases");
443+
is(scalar(() = $stderr =~ /creating subscription/g),
444+
3, "verify subscriptions are created for all databases");
445+
389446
# Run pg_createsubscriber on node S. --verbose is used twice
390447
# to show more information.
391448
# In passing, also test the --enable-two-phase option and

0 commit comments

Comments
 (0)