v1-0001-vacuumdb-Add-exclude-database-option-to-skip-data.patch

From ea521052103c11283b4bd06e4e7bd25113d3bcc9 Mon Sep 17 00:00:00 2001
From: Mohamed Ali <moali.pg@gmail.com>
Date: Thu, 14 May 2026 13:16:45 -0700
Subject: [PATCH v1] vacuumdb: Add --exclude-database option to skip databases
 with --all

Add a new --exclude-database (-D) option to vacuumdb that allows users
to exclude specific databases when using --all.

Currently "vacuumdb --all" vacuums every connectable database without
exception. This creates operational challenges:

- Excluding test or temporary databases from regular maintenance
- Skipping large inactive databases with historical data

The option can be specified multiple times:

    vacuumdb --all --exclude-database=test_db
    vacuumdb --all -D db1 -D db2 -D db3

The implementation adds an OBJFILTER_DATABASE_EXCLUDE flag and builds
the catalog query dynamically with a "datname NOT IN (...)" clause,
using appendStringLiteralConn() for SQL injection protection.  The
option requires --all, consistent with how --exclude-schema works.
---
 doc/src/sgml/ref/vacuumdb.sgml        | 25 ++++++++++++++++++++
 src/bin/scripts/t/101_vacuumdb_all.pl | 28 +++++++++++++++++++++++
 src/bin/scripts/vacuumdb.c            | 15 ++++++++++--
 src/bin/scripts/vacuuming.c           | 33 ++++++++++++++++++++++++++-
 src/bin/scripts/vacuuming.h           |  2 ++
 5 files changed, 100 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 508c8df..83c51c4 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -162,6 +162,24 @@ PostgreSQL documentation
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><option>-D <replaceable class="parameter">dbname</replaceable></option></term>
+      <term><option>--exclude-database=<replaceable class="parameter">dbname</replaceable></option></term>
+      <listitem>
+       <para>
+        Do not clean or analyze database
+        <replaceable class="parameter">dbname</replaceable>.
+        Multiple databases can be excluded by writing multiple
+        <option>-D</option> switches.
+       </para>
+       <note>
+        <para>
+         This option requires <option>-a</option>/<option>--all</option>.
+        </para>
+       </note>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><option>--disable-page-skipping</option></term>
       <listitem>
@@ -693,6 +711,13 @@ PostgreSQL documentation
 <prompt>$ </prompt><userinput>vacuumdb --schema='foo' --schema='bar' xyzzy</userinput>
 </screen></para>
 
+   <para>
+    To clean all databases except <literal>test_db</literal> and
+    <literal>staging_db</literal>:
+<screen>
+<prompt>$ </prompt><userinput>vacuumdb --all --exclude-database=test_db --exclude-database=staging_db</userinput>
+</screen></para>
+
 
  </refsect1>
 
diff --git a/src/bin/scripts/t/101_vacuumdb_all.pl b/src/bin/scripts/t/101_vacuumdb_all.pl
index c91c332..406615c 100644
--- a/src/bin/scripts/t/101_vacuumdb_all.pl
+++ b/src/bin/scripts/t/101_vacuumdb_all.pl
@@ -31,4 +31,32 @@ $node->command_fails_like(
 	qr/FATAL:  cannot connect to invalid database "regression_invalid"/,
 	'vacuumdb cannot target invalid database');
 
+# --exclude-database tests
+$node->safe_psql('postgres', q(CREATE DATABASE regression_excl_test;));
+$node->safe_psql('postgres', q(CREATE DATABASE regression_excl_test2;));
+
+$node->command_checks_all(
+	[ 'vacuumdb', '--all', '--exclude-database' => 'regression_excl_test' ],
+	0,
+	[qr/^(?!.*vacuuming database "regression_excl_test").*$/s],
+	[qr//],
+	'vacuumdb --all --exclude-database skips specified database');
+
+$node->command_checks_all(
+	[ 'vacuumdb', '--all', '-D' => 'regression_excl_test', '-D' => 'regression_excl_test2' ],
+	0,
+	[qr/^(?!.*vacuuming database "regression_excl_test")(?!.*vacuuming database "regression_excl_test2").*$/s],
+	[qr//],
+	'vacuumdb --all with multiple -D switches');
+
+$node->command_fails_like(
+	[ 'vacuumdb', '--exclude-database' => 'regression_excl_test' ],
+	qr/cannot use --exclude-database without --all option/,
+	'cannot use --exclude-database without --all');
+
+$node->command_fails_like(
+	[ 'vacuumdb', '-d' => 'postgres', '--exclude-database' => 'regression_excl_test' ],
+	qr/cannot use --exclude-database without --all option/,
+	'cannot use --exclude-database with -d');
+
 done_testing();
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index ccc7f88..242b965 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -46,6 +46,7 @@ main(int argc, char *argv[])
 		{"parallel", required_argument, NULL, 'P'},
 		{"schema", required_argument, NULL, 'n'},
 		{"exclude-schema", required_argument, NULL, 'N'},
+		{"exclude-database", required_argument, NULL, 'D'},
 		{"maintenance-db", required_argument, NULL, 2},
 		{"analyze-in-stages", no_argument, NULL, 3},
 		{"disable-page-skipping", no_argument, NULL, 4},
@@ -71,6 +72,7 @@ main(int argc, char *argv[])
 	ConnParams	cparams;
 	vacuumingOptions vacopts;
 	SimpleStringList objects = {NULL, NULL};
+	SimpleStringList excluded_dbs = {NULL, NULL};
 	int			concurrentCons = 1;
 	unsigned int tbl_count = 0;
 	int			ret;
@@ -92,7 +94,7 @@ main(int argc, char *argv[])
 
 	handle_help_version_opts(argc, argv, "vacuumdb", help);
 
-	while ((c = getopt_long(argc, argv, "ad:efFh:j:n:N:p:P:qt:U:vwWzZ",
+	while ((c = getopt_long(argc, argv, "aD:d:efFh:j:n:N:p:P:qt:U:vwWzZ",
 							long_options, &optindex)) != -1)
 	{
 		switch (c)
@@ -129,6 +131,10 @@ main(int argc, char *argv[])
 				vacopts.objfilter |= OBJFILTER_SCHEMA_EXCLUDE;
 				simple_string_list_append(&objects, optarg);
 				break;
+			case 'D':
+				vacopts.objfilter |= OBJFILTER_DATABASE_EXCLUDE;
+				simple_string_list_append(&excluded_dbs, optarg);
+				break;
 			case 'p':
 				cparams.pgport = pg_strdup(optarg);
 				break;
@@ -314,7 +320,7 @@ main(int argc, char *argv[])
 	ret = vacuuming_main(&cparams, dbname, maintenance_db, &vacopts,
 						 &objects, tbl_count,
 						 concurrentCons,
-						 progname);
+						 &excluded_dbs, progname);
 	exit(ret);
 }
 
@@ -339,6 +345,10 @@ check_objfilter(uint32 objfilter)
 	if ((objfilter & OBJFILTER_SCHEMA) &&
 		(objfilter & OBJFILTER_SCHEMA_EXCLUDE))
 		pg_fatal("cannot vacuum all tables in schema(s) and exclude schema(s) at the same time");
+
+	if ((objfilter & OBJFILTER_DATABASE_EXCLUDE) &&
+		!(objfilter & OBJFILTER_ALL_DBS))
+		pg_fatal("cannot use --exclude-database without --all option");
 }
 
 
@@ -352,6 +362,7 @@ help(const char *progname)
 	printf(_("  -a, --all                       vacuum all databases\n"));
 	printf(_("      --buffer-usage-limit=SIZE   size of ring buffer used for vacuum\n"));
 	printf(_("  -d, --dbname=DBNAME             database to vacuum\n"));
+	printf(_("  -D, --exclude-database=DBNAME   exclude database from --all operation\n"));
 	printf(_("      --disable-page-skipping     disable all page-skipping behavior\n"));
 	printf(_("      --dry-run                   show the commands that would be sent to the server\n"));
 	printf(_("  -e, --echo                      show the commands being sent to the server\n"));
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index faac908..1317310 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -35,6 +35,7 @@ static int	vacuum_all_databases(ConnParams *cparams,
 								 vacuumingOptions *vacopts,
 								 SimpleStringList *objects,
 								 int concurrentCons,
+								 SimpleStringList *dbsToExclude,
 								 const char *progname);
 static SimpleStringList *retrieve_objects(PGconn *conn,
 										  vacuumingOptions *vacopts,
@@ -56,6 +57,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
 			   const char *maintenance_db, vacuumingOptions *vacopts,
 			   SimpleStringList *objects,
 			   unsigned int tbl_count, int concurrentCons,
+			   SimpleStringList *dbsToExclude,
 			   const char *progname)
 {
 	setup_cancel_handler(NULL);
@@ -71,6 +73,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
 		return vacuum_all_databases(cparams, vacopts,
 									objects,
 									concurrentCons,
+									dbsToExclude,
 									progname);
 	}
 	else
@@ -440,17 +443,45 @@ vacuum_all_databases(ConnParams *cparams,
 					 vacuumingOptions *vacopts,
 					 SimpleStringList *objects,
 					 int concurrentCons,
+					 SimpleStringList *dbsToExclude,
 					 const char *progname)
 {
 	int			ret = EXIT_SUCCESS;
 	PGconn	   *conn;
 	PGresult   *result;
 	int			numdbs;
+	SimpleStringListCell *cell;
+	PQExpBufferData catalog_query;
+	bool		first = true;
 
 	conn = connectMaintenanceDatabase(cparams, progname, vacopts->echo);
+
+	initPQExpBuffer(&catalog_query);
+	appendPQExpBufferStr(&catalog_query,
+						 "SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2");
+
+	for (cell = dbsToExclude ? dbsToExclude->head : NULL; cell; cell = cell->next)
+	{
+		if (first)
+		{
+			appendPQExpBufferStr(&catalog_query, " AND datname NOT IN (");
+			first = false;
+		}
+		else
+			appendPQExpBufferStr(&catalog_query, ",");
+
+		appendStringLiteralConn(&catalog_query, cell->val, conn);
+	}
+
+	if (!first)
+		appendPQExpBufferChar(&catalog_query, ')');
+
+	appendPQExpBufferStr(&catalog_query, " ORDER BY 1;");
+
 	result = executeQuery(conn,
-						  "SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
+						  catalog_query.data,
 						  vacopts->echo);
+	termPQExpBuffer(&catalog_query);
 	numdbs = PQntuples(result);
 	PQfinish(conn);
 
diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h
index 5a491db..62b38ba 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -62,12 +62,14 @@ typedef struct vacuumingOptions
 #define OBJFILTER_TABLE				0x04	/* --table */
 #define OBJFILTER_SCHEMA			0x08	/* --schema */
 #define OBJFILTER_SCHEMA_EXCLUDE	0x10	/* --exclude-schema */
+#define OBJFILTER_DATABASE_EXCLUDE	0x20	/* --exclude-database */
 
 extern int	vacuuming_main(ConnParams *cparams, const char *dbname,
 						   const char *maintenance_db, vacuumingOptions *vacopts,
 						   SimpleStringList *objects,
 						   unsigned int tbl_count,
 						   int concurrentCons,
+						   SimpleStringList *dbsToExclude,
 						   const char *progname);
 
 extern char *escape_quotes(const char *src);
-- 
2.50.1 (Apple Git-155)