Horton
Menu

Thread

  1. [PATCH] vacuumdb: Add --exclude-database option

    Mohamed ALi <moali.pg@gmail.com> — 2026-05-15T16:47:30Z

    Hi hackers,

Attached is a patch that adds --exclude-database (-D) to vacuumdb,
allowing users to skip specific databases when using --all.

Motivation
----------

"vacuumdb --all" vacuums every connectable database without exception.
In production environments, operators often need to skip certain databases
during maintenance — for example, test databases, large inactive databases
with historical data.
The current workaround is to manually vacuum each database
individually or write wrapper scripts.

Usage
-----

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

The option requires --all

Testing
-------

The patch passes all existing vacuumdb TAP tests (100_vacuumdb,
101_vacuumdb_all, 102_vacuumdb_stages) and includes 4 new TAP tests
in 101_vacuumdb_all.pl covering exclusion, multiple exclusions, and
error cases.

I've also attached a standalone test script (test_exclude_database.sh)
that exercises the feature with 11 test scenarios:

  1. Single database exclusion
  2. Multiple database exclusions (-D db1 -D db2)
  3. Exclude all databases
  4. Exclude non-existent database (silently ignored)
  5. Exclude maintenance database (postgres)
  6. Case sensitivity (exact case excludes, wrong case does not)
  7. --exclude-database without --all (error)
  8. --exclude-database with -d (error)
  9. SQL injection protection
 10. --help output
 11. -d with -D and --all (conflicting options error)

All tests pass. The test script is portable — it uses standard libpq
environment variables and auto-detects binaries. Test output is also
attached (test_exclude_database_results.txt).

-- 
Mohamed Ali
AWS RDS

  2. Re: [PATCH] vacuumdb: Add --exclude-database option

    Quan Zongliang <quanzongliang@yeah.net> — 2026-06-05T09:25:52Z

    
On 5/16/26 12:47 AM, Mohamed ALi wrote:
> Hi hackers,
> 
> Attached is a patch that adds --exclude-database (-D) to vacuumdb,
> allowing users to skip specific databases when using --all.
> 
> Motivation
> ----------
> 
> "vacuumdb --all" vacuums every connectable database without exception.
> In production environments, operators often need to skip certain databases
> during maintenance — for example, test databases, large inactive databases
> with historical data.
> The current workaround is to manually vacuum each database
> individually or write wrapper scripts.
> 
The parameter "D and d(dname)" conforms to the usage of "N and n." 
Adhering to the original definition, this is fine.

In the vacuum_all_databases function. If the condition 'if 
(dbsToExclude)' is used. All 'exclude-database' processing (including 
variable definitions) can be placed in a separate code block. How about 
this? Irrelevant personal preferences.

--
Quan Zongliang

> Usage
> -----
> 
>      vacuumdb --all --exclude-database=test_db
>      vacuumdb --all -D db1 -D db2 -D db3
> 
> The option requires --all
> 
> Testing
> -------
> 
> The patch passes all existing vacuumdb TAP tests (100_vacuumdb,
> 101_vacuumdb_all, 102_vacuumdb_stages) and includes 4 new TAP tests
> in 101_vacuumdb_all.pl covering exclusion, multiple exclusions, and
> error cases.
> 
> I've also attached a standalone test script (test_exclude_database.sh)
> that exercises the feature with 11 test scenarios:
> 
>    1. Single database exclusion
>    2. Multiple database exclusions (-D db1 -D db2)
>    3. Exclude all databases
>    4. Exclude non-existent database (silently ignored)
>    5. Exclude maintenance database (postgres)
>    6. Case sensitivity (exact case excludes, wrong case does not)
>    7. --exclude-database without --all (error)
>    8. --exclude-database with -d (error)
>    9. SQL injection protection
>   10. --help output
>   11. -d with -D and --all (conflicting options error)
> 
> All tests pass. The test script is portable — it uses standard libpq
> environment variables and auto-detects binaries. Test output is also
> attached (test_exclude_database_results.txt).
>

  3. Re: [PATCH] vacuumdb: Add --exclude-database option

    wenhui qiu <qiuwenhuifx@gmail.com> — 2026-06-05T12:51:59Z

    >
> HI
>
> > In the vacuum_all_databases function. If the condition 'if
> > (dbsToExclude)' is used. All 'exclude-database' processing (including
> > variable definitions) can be placed in a separate code block. How about
> > this? Irrelevant personal preferences.
>
   Agree +1




Thanks

  4. Re: [PATCH] vacuumdb: Add --exclude-database option

    Mohamed ALi <moali.pg@gmail.com> — 2026-06-10T06:24:53Z

    Hi Quan, wenhui,

Thanks for the review and feedback.

On Thu, Jun 5, 2026 at 09:25:52 AM, Quan Zongliang wrote:
> In the vacuum_all_databases function. If the condition 'if
> (dbsToExclude)' is used. All 'exclude-database' processing (including
> variable definitions) can be placed in a separate code block.

Good suggestion. Attached is v2 incorporating this change, the
exclude-database logic (including the `cell` and `first` variable
declarations) is now scoped inside an `if (dbsToExclude)` block.
This keeps the variables out of the function's top-level scope when
the feature isn't being used.


The patch is also tracked in CommitFest PG20-1:
https://commitfest.postgresql.org/patch/6833/

-- 
Mohamed Ali
AWS RDS

  5. Re: [PATCH] vacuumdb: Add --exclude-database option

    Japin Li <japinli@hotmail.com> — 2026-06-10T09:39:16Z

    Hi, Mohamed

On Tue, 09 Jun 2026 at 23:24, Mohamed ALi <moali.pg@gmail.com> wrote:
> Hi Quan, wenhui,
>
> Thanks for the review and feedback.
>
> On Thu, Jun 5, 2026 at 09:25:52 AM, Quan Zongliang wrote:
>> In the vacuum_all_databases function. If the condition 'if
>> (dbsToExclude)' is used. All 'exclude-database' processing (including
>> variable definitions) can be placed in a separate code block.
>
> Good suggestion. Attached is v2 incorporating this change, the
> exclude-database logic (including the `cell` and `first` variable
> declarations) is now scoped inside an `if (dbsToExclude)` block.
> This keeps the variables out of the function's top-level scope when
> the feature isn't being used.
>
>
> The patch is also tracked in CommitFest PG20-1:
> https://commitfest.postgresql.org/patch/6833/

Thanks for updating the patch. I have one question.

+$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');

The test is a bit confusing to me.  It does not state that --exclude-database
cannot be used with the -d option.

>
> -- 
> Mohamed Ali
> AWS RDS

-- 
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.

  6. Re: [PATCH] vacuumdb: Add --exclude-database option

    Mohamed ALi <moali.pg@gmail.com> — 2026-06-11T07:35:14Z

     Hi Japin,

Thanks for the review.

On Tue, Jun 10, 2026 at 09:39:16 AM, Japin Li wrote:
> +$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');
>
> The test is a bit confusing to me.  It does not state that
> --exclude-database cannot be used with the -d option.

Good catch.  The issue was that "vacuumdb -d postgres -D test_db1"
produced "cannot use --exclude-database without --all option", which
is misleading — if the user then adds --all, they hit a second error
("cannot vacuum all databases and a specific one at the same time").

Before (v2):

    $ vacuumdb -D test_db1
    vacuumdb: error: cannot use --exclude-database without --all option
    $ vacuumdb -d postgres -D test_db1
    vacuumdb: error: cannot use --exclude-database without --all option
    $ vacuumdb -d postgres -D test_db1 --all
    vacuumdb: error: cannot vacuum all databases and a specific one at
the same time

After (v3):

    $ vacuumdb -D test_db1
    vacuumdb: error: cannot use the "exclude-database" option without
the "all" option
    $ vacuumdb -d postgres -D test_db1
    vacuumdb: error: cannot use the "exclude-database" option with the
"dbname" option
    $ vacuumdb -d postgres -D test_db1 --all
    vacuumdb: error: cannot vacuum all databases and a specific one at
the same time

v3 adds a separate check that fires first when both -d and
--exclude-database are used together, giving a clear error instead
of bouncing the user between two messages.

Changes in v3:
- Added a check for OBJFILTER_DATABASE_EXCLUDE + OBJFILTER_DATABASE
  that fires before the "without all" check, giving a clear error
  when both options are specified together.
- Both error messages now use the pg_fatal("%s") format consistent
  with other option-conflict errors in vacuumdb.c.
- Updated TAP test to match the new error messages.



-- 
Mohamed Ali
AWS RDS

  7. Re: [PATCH] vacuumdb: Add --exclude-database option

    Khoa Nguyen <kdnguyen9.oss@gmail.com> — 2026-06-12T23:47:56Z

    This patch can be very useful for clusters with a large number of databases.

On Thu, Jun 11, 2026 at 12:35 AM Mohamed ALi <moali.pg@gmail.com> wrote:
>
>  Hi Japin,
>
> Thanks for the review.
>
> On Tue, Jun 10, 2026 at 09:39:16 AM, Japin Li wrote:
> > +$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');
> >
> > The test is a bit confusing to me.  It does not state that
> > --exclude-database cannot be used with the -d option.
>
> Good catch.  The issue was that "vacuumdb -d postgres -D test_db1"
> produced "cannot use --exclude-database without --all option", which
> is misleading — if the user then adds --all, they hit a second error
> ("cannot vacuum all databases and a specific one at the same time").
>
> Before (v2):
>
>     $ vacuumdb -D test_db1
>     vacuumdb: error: cannot use --exclude-database without --all option
>     $ vacuumdb -d postgres -D test_db1
>     vacuumdb: error: cannot use --exclude-database without --all option
>     $ vacuumdb -d postgres -D test_db1 --all
>     vacuumdb: error: cannot vacuum all databases and a specific one at
> the same time
>
> After (v3):
>
>     $ vacuumdb -D test_db1
>     vacuumdb: error: cannot use the "exclude-database" option without
> the "all" option
>     $ vacuumdb -d postgres -D test_db1
>     vacuumdb: error: cannot use the "exclude-database" option with the
> "dbname" option
>     $ vacuumdb -d postgres -D test_db1 --all
>     vacuumdb: error: cannot vacuum all databases and a specific one at
> the same time
>
> v3 adds a separate check that fires first when both -d and
> --exclude-database are used together, giving a clear error instead
> of bouncing the user between two messages.
>
> Changes in v3:
> - Added a check for OBJFILTER_DATABASE_EXCLUDE + OBJFILTER_DATABASE
>   that fires before the "without all" check, giving a clear error
>   when both options are specified together.
> - Both error messages now use the pg_fatal("%s") format consistent
>   with other option-conflict errors in vacuumdb.c.
> - Updated TAP test to match the new error messages.

I've installed the v2 and tried out the exclusion option.  Everything
works as expected.

When I reviewed the code, what jumped out and concerned me the most
was the dynamic query built by injecting the excluded db literals into
the NOT IN list of the query.  Looking at this block of code closely,
I saw that the author used appendStringLiteralConn which uses
PQescapeStringConn.  This is the commonly used API for escaping
strings in client tools so we are good.

Having the ability to exclude means that we have to deal with numdbs
being 0.  This is well gated with "for (int i = 0; i < numdbs; i++)".
 When executed with --analyze-in-stages --missing-stats-only,
palloc0(0) -> pg_malloc_internal(0)->malloc(1) and pg_free is also
safe with 1 byte.  I walked through the code path of  "if
(vacopts->mode == MODE_ANALYZE_IN_STAGES)" in gdb and found no issue
for numdbs=0.

Overall I think the patch looks good.  I'll review v3 soon.

  8. Re: [PATCH] vacuumdb: Add --exclude-database option

    Ben Mejia <benjamin.arthur.mejia@gmail.com> — 2026-06-16T21:45:00Z

    I think that the behavior of this patch that silently ignores --exlcude-database "non-existent-db" is broken. What if someone has a large database "my-db-name", and they misspell it:

$ vacuumdb --all --exclude-database "my-dbname"

This will kick off a vacuum including the "my-db-name" they were trying to exclude.

I think it would be better to generate an error and not do anything in this case.

This also tracks with vacuumdb's existing behavior on --table/--schema which will tell you about a typo.

  9. Re: [PATCH] vacuumdb: Add --exclude-database option

    Khoa Nguyen <kdnguyen9.oss@gmail.com> — 2026-06-18T16:36:34Z

    On Wed, Jun 17, 2026 at 7:24 PM Ben Mejia
<benjamin.arthur.mejia@gmail.com> wrote:
>
> I think that the behavior of this patch that silently ignores --exlcude-database "non-existent-db" is broken. What if someone has a large database "my-db-name", and they misspell it:
>
> $ vacuumdb --all --exclude-database "my-dbname"
>
> This will kick off a vacuum including the "my-db-name" they were trying to exclude.
>
> I think it would be better to generate an error and not do anything in this case.
+1

Since exclude-schema validates and stops on error, I think it would
make sense to do the same for exclude-database.  Current users are
used to this behavior so it's best to keep it consistent.

>
> This also tracks with vacuumdb's existing behavior on --table/--schema which will tell you about a typo.