From f092de0503cd77a8463e7089c249d5e0586a120b Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Fri, 1 Mar 2019 10:47:44 -0500 Subject: [PATCH] Add --exclude-database option to pg_dumpall This option functions similarly to pg_dump's --exclude-table option, but for database names. The option can be given once, and the argument can be a pattern including wildcard characters. Author: Andrew Dunstan. Reviewd-by: Fabien Coelho and Michael Paquier Discussion: https://postgr.es/m/43a54a47-4aa7-c70e-9ca6-648f436dd6e6@2ndQuadrant.com --- doc/src/sgml/ref/pg_dumpall.sgml | 21 +++++++++ src/bin/pg_dump/pg_dump.c | 3 +- src/bin/pg_dump/pg_dumpall.c | 78 ++++++++++++++++++++++++++++++++ src/bin/pg_dump/t/001_basic.pl | 13 +++++- src/bin/pg_dump/t/002_pg_dump.pl | 11 +++++ 5 files changed, 124 insertions(+), 2 deletions(-) diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml index b3372a641f..e3676cf811 100644 --- a/doc/src/sgml/ref/pg_dumpall.sgml +++ b/doc/src/sgml/ref/pg_dumpall.sgml @@ -311,6 +311,27 @@ PostgreSQL documentation + + + + + + Do not dump databases whose name matches + pattern. + Multiple patterns can be excluded by writing multiple + switches. The + pattern parameter is + interpreted as a pattern according to the same rules used by + psql's \d + commands (see ), + so multiple databases can also be excluded by writing wildcard + characters in the pattern. When using wildcards, be careful to + quote the pattern if needed to prevent shell wildcard expansion. + + + + diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index a1bcf62b85..5d83038348 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -1301,7 +1301,8 @@ expand_schema_name_patterns(Archive *fout, /* * Find the OIDs of all tables matching the given list of patterns, - * and append them to the given OID list. + * and append them to the given OID list. See also expand_dbname_patterns() + * in pg_dumpall.c */ static void expand_table_name_patterns(Archive *fout, diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index 44c3350887..773bbdd04c 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -52,6 +52,8 @@ static PGconn *connectDatabase(const char *dbname, const char *connstr, const ch static char *constructConnStr(const char **keywords, const char **values); static PGresult *executeQuery(PGconn *conn, const char *query); static void executeCommand(PGconn *conn, const char *query); +static void expand_dbname_patterns(PGconn *conn, SimpleStringList *patterns, + SimpleStringList *names); static char pg_dump_bin[MAXPGPATH]; static const char *progname; @@ -87,6 +89,9 @@ static char role_catalog[10]; static FILE *OPF; static char *filename = NULL; +static SimpleStringList database_exclude_patterns = {NULL, NULL}; +static SimpleStringList database_exclude_names = {NULL, NULL}; + #define exit_nicely(code) exit(code) int @@ -123,6 +128,7 @@ main(int argc, char *argv[]) {"column-inserts", no_argument, &column_inserts, 1}, {"disable-dollar-quoting", no_argument, &disable_dollar_quoting, 1}, {"disable-triggers", no_argument, &disable_triggers, 1}, + {"exclude-database", required_argument, NULL, 6}, {"extra-float-digits", required_argument, NULL, 5}, {"if-exists", no_argument, &if_exists, 1}, {"inserts", no_argument, &inserts, 1}, @@ -324,6 +330,10 @@ main(int argc, char *argv[]) appendShellString(pgdumpopts, optarg); break; + case 6: + simple_string_list_append(&database_exclude_patterns, optarg); + break; + default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit_nicely(1); @@ -340,6 +350,16 @@ main(int argc, char *argv[]) exit_nicely(1); } + if (database_exclude_patterns.head != NULL && + (globals_only || roles_only || tablespaces_only)) + { + fprintf(stderr, _("%s: option --exclude-database cannot be used together with -g/--globals-only, -r/--roles-only or -t/--tablespaces-only\n"), + progname); + fprintf(stderr, _("Try \"%s --help\" for more information.\n"), + progname); + exit_nicely(1); + } + /* Make sure the user hasn't specified a mix of globals-only options */ if (globals_only && roles_only) { @@ -454,6 +474,12 @@ main(int argc, char *argv[]) } } + /* + * Get a list of database names that match the exclude patterns + */ + expand_dbname_patterns(conn, &database_exclude_patterns, + &database_exclude_names); + /* * Open the output file if required, otherwise use stdout */ @@ -620,6 +646,7 @@ help(void) printf(_(" --column-inserts dump data as INSERT commands with column names\n")); printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n")); printf(_(" --disable-triggers disable triggers during data-only restore\n")); + printf(_(" --exclude-database=PATTERN exclude databases whose name matches PATTERN\n")); printf(_(" --extra-float-digits=NUM override default setting for extra_float_digits\n")); printf(_(" --if-exists use IF EXISTS when dropping objects\n")); printf(_(" --inserts dump data as INSERT commands, rather than COPY\n")); @@ -1358,6 +1385,48 @@ dumpUserConfig(PGconn *conn, const char *username) destroyPQExpBuffer(buf); } +/* + * Find a list of database names that match the given patterns. + * See also expand_table_name_patterns() in pg_dump.c + */ +static void +expand_dbname_patterns(PGconn *conn, + SimpleStringList *patterns, + SimpleStringList *names) +{ + PQExpBuffer query; + PGresult *res; + + if (patterns->head == NULL) + return; /* nothing to do */ + + query = createPQExpBuffer(); + + /* + * The loop below runs multiple SELECTs, which might sometimes result in + * duplicate entries in the name list, but we don't care, since all + * we're going to do is test membership of the list. + */ + + for (SimpleStringListCell *cell = patterns->head; cell; cell = cell->next) + { + appendPQExpBuffer(query, + "SELECT datname FROM pg_catalog.pg_database n\n"); + processSQLNamePattern(conn, query, cell->val, false, + false, NULL, "datname", NULL, NULL); + + res = executeQuery(conn, query->data); + for (int i = 0; i < PQntuples(res); i++) + { + simple_string_list_append(names, PQgetvalue(res, i, 0)); + } + + PQclear(res); + resetPQExpBuffer(query); + } + + destroyPQExpBuffer(query); +} /* * Dump contents of databases. @@ -1395,6 +1464,15 @@ dumpDatabases(PGconn *conn) if (strcmp(dbname, "template0") == 0) continue; + /* Skip any explicitly excluded database */ + if (simple_string_list_member(&database_exclude_names, dbname)) + { + if (verbose) + fprintf(stderr, _("%s: excluding database \"%s\"...\n"), + progname, dbname); + continue; + } + if (verbose) fprintf(stderr, _("%s: dumping database \"%s\"...\n"), progname, dbname); diff --git a/src/bin/pg_dump/t/001_basic.pl b/src/bin/pg_dump/t/001_basic.pl index a875d540b8..59b77214c6 100644 --- a/src/bin/pg_dump/t/001_basic.pl +++ b/src/bin/pg_dump/t/001_basic.pl @@ -4,7 +4,7 @@ use warnings; use Config; use PostgresNode; use TestLib; -use Test::More tests => 70; +use Test::More tests => 74; my $tempdir = TestLib::tempdir; my $tempdir_short = TestLib::tempdir_short; @@ -150,3 +150,14 @@ command_fails_like( qr/\Qpg_restore: options -C\/--create and -1\/--single-transaction cannot be used together\E/, 'pg_restore: options -C\/--create and -1\/--single-transaction cannot be used together' ); + +command_fails_like( + [ 'pg_dumpall', '--exclude-database' ], + qr/\Qpg_dumpall: option '--exclude-database' requires an argument\E/, + 'pg_dumpall: option --exclude-database requires an argument'); + +# also fails for -r and -t, but it seems pointless to add more tests for those. +command_fails_like( + [ 'pg_dumpall', '--exclude-database=foo', '--globals-only' ], + qr/\Qpg_dumpall: option --exclude-database cannot be used together with -g\/--globals-only\E/, + 'pg_dumpall: option --exclude-database cannot be used together with -g/--globals-only'); diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 0233fcb47f..8fa7f0f61f 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -224,6 +224,12 @@ my %pgdump_runs = ( "--file=$tempdir/pg_dumpall_dbprivs.sql", ], }, + pg_dumpall_exclude => { + dump_cmd => [ + 'pg_dumpall', '-v', "--file=$tempdir/pg_dumpall_exclude.sql", + '--exclude-database', '*dump*', '--no-sync', + ], + }, no_blobs => { dump_cmd => [ 'pg_dump', '--no-sync', @@ -380,6 +386,7 @@ my %full_runs = ( no_owner => 1, no_privs => 1, pg_dumpall_dbprivs => 1, + pg_dumpall_exclude => 1, schema_only => 1,); # This is where the actual tests are defined. @@ -444,6 +451,7 @@ my %tests = ( pg_dumpall_dbprivs => 1, pg_dumpall_globals => 1, pg_dumpall_globals_clean => 1, + pg_dumpall_exclude => 1, }, }, @@ -1318,6 +1326,7 @@ my %tests = ( regexp => qr/^CREATE ROLE regress_dump_test_role;/m, like => { pg_dumpall_dbprivs => 1, + pg_dumpall_exclude => 1, pg_dumpall_globals => 1, pg_dumpall_globals_clean => 1, }, @@ -2442,6 +2451,7 @@ my %tests = ( no_owner => 1, only_dump_test_schema => 1, pg_dumpall_dbprivs => 1, + pg_dumpall_exclude => 1, schema_only => 1, section_post_data => 1, test_schema_plus_blobs => 1, @@ -2512,6 +2522,7 @@ my %tests = ( no_privs => 1, no_owner => 1, pg_dumpall_dbprivs => 1, + pg_dumpall_exclude => 1, role => 1, schema_only => 1, section_post_data => 1, -- 2.40.0