PFA a patch to provide an --exclude-database option for pg_dumpall. The
causes pg_dumpall to skip any database whose name matches the argument
pattern. The option can be used multiple times.
Among other use cases, this is useful where a database name is visible
but the database is not dumpable by the user. Examples of this occur in
some managed Postgres services.
I will add this to the September CF.
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index 94d76c3..41a7ed7 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -300,6 +300,28 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><option>--exclude-database=<replaceable class="parameter">dbname</replaceable></option></term>
+ <listitem>
+ <para>
+ Do not dump databases whose name matches
+ <replaceable class="parameter">dbname</replaceable>.
+ Multiple databases can be excluded by writing multiple
+ <option>--exclude-database</option> switches. Also, the
+ <replaceable class="parameter">dbname</replaceable> parameter is
+ interpreted as a pattern according to the same rules used by
+ <application>psql</application>'s <literal>\d</literal>
+ commands (see <xref
+ linkend="app-psql-patterns" endterm="app-psql-patterns-title"/>),
+ 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 the shell from expanding
+ the wildcards.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>--if-exists</option></term>
<listitem>
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index eb29d31..43058e9 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -52,6 +52,9 @@ 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 bool database_excluded(char * datname);
+static void expand_dbname_patterns(PGconn *conn, SimpleStringList *patterns,
+ SimpleStringList *names);
static char pg_dump_bin[MAXPGPATH];
static const char *progname;
@@ -87,6 +90,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 +129,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, 5},
{"if-exists", no_argument, &if_exists, 1},
{"inserts", no_argument, &inserts, 1},
{"lock-wait-timeout", required_argument, NULL, 2},
@@ -318,6 +325,10 @@ main(int argc, char *argv[])
appendPQExpBufferStr(pgdumpopts, " --no-sync");
break;
+ case 5:
+ simple_string_list_append(&database_exclude_patterns, optarg);
+ break;
+
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit_nicely(1);
@@ -449,6 +460,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
*/
if (filename)
@@ -614,6 +631,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(_(" --if-exists use IF EXISTS when dropping objects\n"));
printf(_(" --inserts dump data as INSERT commands, rather than COPY\n"));
printf(_(" --load-via-partition-root load partitions via the root table\n"));
@@ -1351,6 +1369,66 @@ dumpUserConfig(PGconn *conn, const char *username)
destroyPQExpBuffer(buf);
}
+/*
+ * Find a list of database names that match the given patterns.
+ * This is similar to code in pg_dump.c for handling matching table names.
+ */
+static void
+expand_dbname_patterns(PGconn *conn,
+ SimpleStringList *patterns,
+ SimpleStringList *names)
+{
+ PQExpBuffer query;
+ PGresult *res;
+ SimpleStringListCell *cell;
+ int i;
+
+ if (patterns->head == NULL)
+ return; /* nothing to do */
+
+ query = createPQExpBuffer();
+
+ /*
+ * The loop below runs multiple SELECTs. It might sometimes result in
+ * duplicate entries in the names list, but we don't care, since a
+ * database is going to be excluded if it matches any entry on the list.
+ */
+
+ for (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 (i = 0; i < PQntuples(res); i++)
+ {
+ simple_string_list_append(names, PQgetvalue(res, i, 0));
+ }
+
+ PQclear(res);
+ resetPQExpBuffer(query);
+ }
+
+ destroyPQExpBuffer(query);
+}
+
+/*
+ * Is the datname on the list of excluded databases?
+ */
+static bool
+database_excluded(char * datname)
+{
+ SimpleStringListCell *cell;
+
+ for (cell = (&database_exclude_names)->head; cell; cell = cell->next)
+ {
+ if (strcmp(datname, cell->val) == 0)
+ return true;
+ }
+ return false;
+}
/*
* Dump contents of databases.
@@ -1388,6 +1466,15 @@ dumpDatabases(PGconn *conn)
if (strcmp(dbname, "template0") == 0)
continue;
+ /* Skip any explicitly excluded database */
+ if (database_excluded(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 17edf44..65c6f9f 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 => 72;
my $tempdir = TestLib::tempdir;
my $tempdir_short = TestLib::tempdir_short;
@@ -150,3 +150,8 @@ command_fails_like(
[ 'pg_dumpall', '--if-exists' ],
qr/\Qpg_dumpall: option --if-exists requires option -c\/--clean\E/,
'pg_dumpall: option --if-exists requires option -c/--clean');
+
+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");
