vacuumdb: add --dry-run
Started by Corey Huinker2 months ago31 messages
This is a small patch to add a new option to vacuumdb to answer the
question "what commands will actually be run by this combination of
command-line switches against this database?" without actually running the
commands.
Including Nathan because we had previously discussed the utility of just
such a thing.
Attachments:
v1-0001-Add-dry-run-to-vacuumdb.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Add-dry-run-to-vacuumdb.patchDownload
From e64b1795a6a41827b8152e62c05bbbba1dac7dc4 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Mon, 10 Nov 2025 14:33:41 -0500
Subject: [PATCH v1] Add --dry-run to vacuumdb.
This option answers the question "what tables would be affected if I ran
a command using these options" without actually initiating those
actions.
---
src/bin/scripts/vacuumdb.c | 6 ++++++
src/bin/scripts/vacuuming.c | 7 +++++--
src/bin/scripts/vacuuming.h | 1 +
doc/src/sgml/ref/vacuumdb.sgml | 11 +++++++++++
4 files changed, 23 insertions(+), 2 deletions(-)
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index e117dac2242..aa0dc366eb0 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -59,6 +59,7 @@ main(int argc, char *argv[])
{"no-process-main", no_argument, NULL, 12},
{"buffer-usage-limit", required_argument, NULL, 13},
{"missing-stats-only", no_argument, NULL, 14},
+ {"dry-run", no_argument, NULL, 15},
{NULL, 0, NULL, 0}
};
@@ -86,6 +87,7 @@ main(int argc, char *argv[])
vacopts.do_truncate = true;
vacopts.process_main = true;
vacopts.process_toast = true;
+ vacopts.dry_run = false;
/* the same for connection parameters */
memset(&cparams, 0, sizeof(cparams));
@@ -213,6 +215,9 @@ main(int argc, char *argv[])
case 14:
vacopts.missing_stats_only = true;
break;
+ case 15:
+ vacopts.dry_run = true;
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -375,6 +380,7 @@ help(const char *progname)
printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
" stages for faster results; no vacuum\n"));
+ printf(_(" --dry-run do not vacuum/analyze the selected tables, only print\n"));
printf(_(" -?, --help show this help, then exit\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index f836f21fb03..6255d2bac47 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -383,8 +383,11 @@ vacuum_one_database(ConnParams *cparams,
* through ParallelSlotsGetIdle.
*/
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, sql.data,
- echo, tabname);
+ if (vacopts->dry_run)
+ printf("%s (not executed)\n", sql.data);
+ else
+ run_vacuum_command(free_slot->connection, sql.data,
+ echo, tabname);
cell = cell->next;
} while (cell != NULL);
diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h
index 49f968b32e5..50155239e7e 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -51,6 +51,7 @@ typedef struct vacuumingOptions
bool skip_database_stats;
char *buffer_usage_limit;
bool missing_stats_only;
+ bool dry_run;
} vacuumingOptions;
/* Valid values for vacuumingOptions->objfilter */
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 84c76d7350c..100691b579a 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -171,6 +171,17 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--dry-run</option></term>
+ <listitem>
+ <para>
+ Print but do not execute the vacuum or analyze commands generated.
+ This is useful for testing the effects of various command-line options
+ before actually running the commands.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-e</option></term>
<term><option>--echo</option></term>
base-commit: e510378358540703a13b77090a0021853bae0745
--
2.51.1
Re: vacuumdb: add --dry-run
On Mon, Nov 10, 2025 at 02:44:41PM -0500, Corey Huinker wrote:
This is a small patch to add a new option to vacuumdb to answer the
question "what commands will actually be run by this combination of
command-line switches against this database?" without actually running the
commands.
My attempts to test this all got stuck in wait_on_slots(). I haven't
looked too closely, but I suspect the issue is that the socket never
becomes readable because we don't send a query. If I set free_slot->inUse
to false before printing the command, it no longer hangs. We probably want
to create a function in parallel_slot.c to mark slots that we don't intend
to give a query as idle.
--
nathan
Re: vacuumdb: add --dry-run
My attempts to test this all got stuck in wait_on_slots(). I haven't
looked too closely, but I suspect the issue is that the socket never
becomes readable because we don't send a query. If I set free_slot->inUse
to false before printing the command, it no longer hangs. We probably want
to create a function in parallel_slot.c to mark slots that we don't intend
to give a query as idle.
Would that be preferable to skipping the creation of extra connections for
parallel workers? I can see it both ways. On the one hand we want to give
as true a reflection of "what would happen with these options", and on the
other hand one could view the creation of extra workers as "real" vs a dry
run.
Re: vacuumdb: add --dry-run
On Mon, Nov 10, 2025 at 05:33:34PM -0500, Corey Huinker wrote:
My attempts to test this all got stuck in wait_on_slots(). I haven't
looked too closely, but I suspect the issue is that the socket never
becomes readable because we don't send a query. If I set free_slot->inUse
to false before printing the command, it no longer hangs. We probably want
to create a function in parallel_slot.c to mark slots that we don't intend
to give a query as idle.Would that be preferable to skipping the creation of extra connections for
parallel workers? I can see it both ways. On the one hand we want to give
as true a reflection of "what would happen with these options", and on the
other hand one could view the creation of extra workers as "real" vs a dry
run.
I think what I'm proposing actually does skip creating extra connections.
If we're immediately marking the first connection as idle, each loop
iteration should reuse the same connection.
BTW it might be better to modify run_vacuum_command() to skip running the
command in dry-run mode. That would also take care of the
ONLY_DATABASE_STATS stuff. We should probably do something about the
executeCommand() for --analyze-in-stages, too.
--
nathan
Re: vacuumdb: add --dry-run
On Nov 11, 2025, at 06:33, Corey Huinker <corey.huinker@gmail.com> wrote:
My attempts to test this all got stuck in wait_on_slots(). I haven't
looked too closely, but I suspect the issue is that the socket never
becomes readable because we don't send a query. If I set free_slot->inUse
to false before printing the command, it no longer hangs. We probably want
to create a function in parallel_slot.c to mark slots that we don't intend
to give a query as idle.Would that be preferable to skipping the creation of extra connections for parallel workers? I can see it both ways. On the one hand we want to give as true a reflection of "what would happen with these options", and on the other hand one could view the creation of extra workers as "real" vs a dry run.
I test the patch, but my first run was with no luck:
```
% vacuumdb --dry-run --echo -d evantest
SELECT pg_catalog.set_config('search_path', '', false);
vacuumdb: vacuuming database "evantest"
RESET search_path;
SELECT c.relname, ns.nspname FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace ns ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid
CROSS JOIN LATERAL (SELECT c.relkind IN ('p', 'I')) as p (inherited)
LEFT JOIN pg_catalog.pg_class t ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid
WHERE c.relpersistence OPERATOR(pg_catalog.!=) 't'
AND c.relkind OPERATOR(pg_catalog.=) ANY (array['r', 'm'])
ORDER BY c.relpages DESC;
SELECT pg_catalog.set_config('search_path', '', false);
VACUUM (SKIP_DATABASE_STATS) pg_catalog.pg_proc; (not executed)
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
```
After skip the first vacuum command, the process got stuck, and ctrl-c couldn’t break it.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Re: vacuumdb: add --dry-run
My attempts to test this all got stuck in wait_on_slots(). I haven't
looked too closely, but I suspect the issue is that the socket never
becomes readable because we don't send a query. If I setfree_slot->inUse
to false before printing the command, it no longer hangs. We probably
want
to create a function in parallel_slot.c to mark slots that we don't
intend
to give a query as idle.
Would that be preferable to skipping the creation of extra connections
for parallel workers? I can see it both ways. On the one hand we want to
give as true a reflection of "what would happen with these options", and on
the other hand one could view the creation of extra workers as "real" vs a
dry run.
Now with zero hangs and some test cases. I didn't create a function (yet)
as it seemed trivial.
Attachments:
v2-0001-Add-dry-run-to-vacuumdb.patchtext/x-patch; charset=US-ASCII; name=v2-0001-Add-dry-run-to-vacuumdb.patchDownload
From 396f243e729e2aaaf59cd820def4e093e51033bc Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Mon, 10 Nov 2025 14:33:41 -0500
Subject: [PATCH v2] Add --dry-run to vacuumdb.
This option answers the question "what tables would be affected if I ran
a command using these options" without actually initiating those
actions.
---
src/bin/scripts/t/100_vacuumdb.pl | 12 +++++++++
src/bin/scripts/vacuumdb.c | 6 +++++
src/bin/scripts/vacuuming.c | 42 +++++++++++++++++++++++--------
src/bin/scripts/vacuuming.h | 1 +
doc/src/sgml/ref/vacuumdb.sgml | 11 ++++++++
5 files changed, 61 insertions(+), 11 deletions(-)
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index a16fad593f7..9fef3cbb80f 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -169,6 +169,10 @@ $node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', 'postgres' ],
qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
'vacuumdb --schema');
+$node->issues_sql_unlike(
+ [ 'vacuumdb', '--schema' => '"Foo"', 'postgres', '--dry-run' ],
+ qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
+ 'vacuumdb --schema');
$node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', '--schema' => '"Bar"', 'postgres' ],
qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
@@ -241,6 +245,14 @@ $node->safe_psql('postgres', q|
CREATE TABLE regression_vacuumdb_test AS select generate_series(1, 10) a, generate_series(2, 11) b;
ALTER TABLE regression_vacuumdb_test ADD COLUMN c INT GENERATED ALWAYS AS (a + b);
|);
+$node->issues_sql_unlike(
+ [
+ 'vacuumdb', '--analyze-only', '--dry-run',
+ '--missing-stats-only', '-t',
+ 'regression_vacuumdb_test', 'postgres'
+ ],
+ qr/statement:\ ANALYZE/sx,
+ '--missing-stats-only --dry-run with missing stats');
$node->issues_sql_like(
[
'vacuumdb', '--analyze-only',
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index e117dac2242..aa0dc366eb0 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -59,6 +59,7 @@ main(int argc, char *argv[])
{"no-process-main", no_argument, NULL, 12},
{"buffer-usage-limit", required_argument, NULL, 13},
{"missing-stats-only", no_argument, NULL, 14},
+ {"dry-run", no_argument, NULL, 15},
{NULL, 0, NULL, 0}
};
@@ -86,6 +87,7 @@ main(int argc, char *argv[])
vacopts.do_truncate = true;
vacopts.process_main = true;
vacopts.process_toast = true;
+ vacopts.dry_run = false;
/* the same for connection parameters */
memset(&cparams, 0, sizeof(cparams));
@@ -213,6 +215,9 @@ main(int argc, char *argv[])
case 14:
vacopts.missing_stats_only = true;
break;
+ case 15:
+ vacopts.dry_run = true;
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -375,6 +380,7 @@ help(const char *progname)
printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
" stages for faster results; no vacuum\n"));
+ printf(_(" --dry-run do not vacuum/analyze the selected tables, only print\n"));
printf(_(" -?, --help show this help, then exit\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index f836f21fb03..5d9c07f8715 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -378,13 +378,25 @@ vacuum_one_database(ConnParams *cparams,
prepare_vacuum_command(free_slot->connection, &sql,
vacopts, tabname);
- /*
- * Execute the vacuum. All errors are handled in processQueryResult
- * through ParallelSlotsGetIdle.
- */
- ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, sql.data,
- echo, tabname);
+ if (vacopts->dry_run)
+ {
+ /*
+ * Print the command that we would have run in a real run,
+ * the immediately mark the unused slot as free again.
+ */
+ printf("not executed: %s\n", sql.data);
+ free_slot->inUse = false;
+ }
+ else
+ {
+ /*
+ * Execute the vacuum. All errors are handled in processQueryResult
+ * through ParallelSlotsGetIdle.
+ */
+ ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
+ run_vacuum_command(free_slot->connection, sql.data,
+ echo, tabname);
+ }
cell = cell->next;
} while (cell != NULL);
@@ -407,11 +419,19 @@ vacuum_one_database(ConnParams *cparams,
goto finish;
}
- ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, cmd, echo, NULL);
+ if (vacopts->dry_run)
+ {
+ printf("not executed: %s\n", cmd);
+ free_slot->inUse = false;
+ }
+ else
+ {
+ ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
+ run_vacuum_command(free_slot->connection, cmd, echo, NULL);
- if (!ParallelSlotsWaitCompletion(sa))
- ret = EXIT_FAILURE; /* error already reported by handler */
+ if (!ParallelSlotsWaitCompletion(sa))
+ ret = EXIT_FAILURE; /* error already reported by handler */
+ }
}
finish:
diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h
index 49f968b32e5..50155239e7e 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -51,6 +51,7 @@ typedef struct vacuumingOptions
bool skip_database_stats;
char *buffer_usage_limit;
bool missing_stats_only;
+ bool dry_run;
} vacuumingOptions;
/* Valid values for vacuumingOptions->objfilter */
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 84c76d7350c..100691b579a 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -171,6 +171,17 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--dry-run</option></term>
+ <listitem>
+ <para>
+ Print but do not execute the vacuum or analyze commands generated.
+ This is useful for testing the effects of various command-line options
+ before actually running the commands.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-e</option></term>
<term><option>--echo</option></term>
base-commit: 6b46669883fac9521c20fe4e2c55ccfbee778591
--
2.51.1
Re: vacuumdb: add --dry-run
On Wed, Nov 19, 2025 at 05:23:48PM -0500, Corey Huinker wrote:
Now with zero hangs and some test cases. I didn't create a function (yet)
as it seemed trivial.
I still think it could be worth moving the dry-run code into
run_vacuum_command() (which might entail moving the calls to
ParallelSlotSetHandler() there, too). We can probably piggy-back on the
"if (echo)" branch in that function.
Also, we can probably skip the executeCommand() calls for
--analyze-in-stages.
--
nathan
Re: vacuumdb: add --dry-run
On Wed, Nov 19, 2025 at 5:44 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:
On Wed, Nov 19, 2025 at 05:23:48PM -0500, Corey Huinker wrote:
Now with zero hangs and some test cases. I didn't create a function (yet)
as it seemed trivial.I still think it could be worth moving the dry-run code into
run_vacuum_command() (which might entail moving the calls to
ParallelSlotSetHandler() there, too). We can probably piggy-back on the
"if (echo)" branch in that function.
We _could_ get away with moving ParallelSlotGetIdle() in there too. The
only catch would be that we'd have to refactor prepare_vacuum_command() to
take a serverVersionNumber parameter instead of the whole connection.
Thoughts?
Also, we can probably skip the executeCommand() calls for
--analyze-in-stages.
+1
Re: vacuumdb: add --dry-run
On Wed, Nov 19, 2025 at 6:55 PM Corey Huinker <corey.huinker@gmail.com>
wrote:
On Wed, Nov 19, 2025 at 5:44 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:On Wed, Nov 19, 2025 at 05:23:48PM -0500, Corey Huinker wrote:
Now with zero hangs and some test cases. I didn't create a function
(yet)
as it seemed trivial.
I still think it could be worth moving the dry-run code into
run_vacuum_command() (which might entail moving the calls to
ParallelSlotSetHandler() there, too). We can probably piggy-back on the
"if (echo)" branch in that function.We _could_ get away with moving ParallelSlotGetIdle() in there too. The
only catch would be that we'd have to refactor prepare_vacuum_command() to
take a serverVersionNumber parameter instead of the whole connection.
Thoughts?Also, we can probably skip the executeCommand() calls for
--analyze-in-stages.+1
Here's a shopping list of incremental changes. I'm happy with whatever
makes the most sense to you.
Attachments:
v3-0001-Add-dry-run-to-vacuumdb.patchtext/x-patch; charset=US-ASCII; name=v3-0001-Add-dry-run-to-vacuumdb.patchDownload
From 396f243e729e2aaaf59cd820def4e093e51033bc Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Mon, 10 Nov 2025 14:33:41 -0500
Subject: [PATCH v3 1/5] Add --dry-run to vacuumdb.
This option answers the question "what tables would be affected if I ran
a command using these options" without actually initiating those
actions.
---
src/bin/scripts/t/100_vacuumdb.pl | 12 +++++++++
src/bin/scripts/vacuumdb.c | 6 +++++
src/bin/scripts/vacuuming.c | 42 +++++++++++++++++++++++--------
src/bin/scripts/vacuuming.h | 1 +
doc/src/sgml/ref/vacuumdb.sgml | 11 ++++++++
5 files changed, 61 insertions(+), 11 deletions(-)
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index a16fad593f7..9fef3cbb80f 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -169,6 +169,10 @@ $node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', 'postgres' ],
qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
'vacuumdb --schema');
+$node->issues_sql_unlike(
+ [ 'vacuumdb', '--schema' => '"Foo"', 'postgres', '--dry-run' ],
+ qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
+ 'vacuumdb --schema');
$node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', '--schema' => '"Bar"', 'postgres' ],
qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
@@ -241,6 +245,14 @@ $node->safe_psql('postgres', q|
CREATE TABLE regression_vacuumdb_test AS select generate_series(1, 10) a, generate_series(2, 11) b;
ALTER TABLE regression_vacuumdb_test ADD COLUMN c INT GENERATED ALWAYS AS (a + b);
|);
+$node->issues_sql_unlike(
+ [
+ 'vacuumdb', '--analyze-only', '--dry-run',
+ '--missing-stats-only', '-t',
+ 'regression_vacuumdb_test', 'postgres'
+ ],
+ qr/statement:\ ANALYZE/sx,
+ '--missing-stats-only --dry-run with missing stats');
$node->issues_sql_like(
[
'vacuumdb', '--analyze-only',
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index e117dac2242..aa0dc366eb0 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -59,6 +59,7 @@ main(int argc, char *argv[])
{"no-process-main", no_argument, NULL, 12},
{"buffer-usage-limit", required_argument, NULL, 13},
{"missing-stats-only", no_argument, NULL, 14},
+ {"dry-run", no_argument, NULL, 15},
{NULL, 0, NULL, 0}
};
@@ -86,6 +87,7 @@ main(int argc, char *argv[])
vacopts.do_truncate = true;
vacopts.process_main = true;
vacopts.process_toast = true;
+ vacopts.dry_run = false;
/* the same for connection parameters */
memset(&cparams, 0, sizeof(cparams));
@@ -213,6 +215,9 @@ main(int argc, char *argv[])
case 14:
vacopts.missing_stats_only = true;
break;
+ case 15:
+ vacopts.dry_run = true;
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -375,6 +380,7 @@ help(const char *progname)
printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
" stages for faster results; no vacuum\n"));
+ printf(_(" --dry-run do not vacuum/analyze the selected tables, only print\n"));
printf(_(" -?, --help show this help, then exit\n"));
printf(_("\nConnection options:\n"));
printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index f836f21fb03..5d9c07f8715 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -378,13 +378,25 @@ vacuum_one_database(ConnParams *cparams,
prepare_vacuum_command(free_slot->connection, &sql,
vacopts, tabname);
- /*
- * Execute the vacuum. All errors are handled in processQueryResult
- * through ParallelSlotsGetIdle.
- */
- ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, sql.data,
- echo, tabname);
+ if (vacopts->dry_run)
+ {
+ /*
+ * Print the command that we would have run in a real run,
+ * the immediately mark the unused slot as free again.
+ */
+ printf("not executed: %s\n", sql.data);
+ free_slot->inUse = false;
+ }
+ else
+ {
+ /*
+ * Execute the vacuum. All errors are handled in processQueryResult
+ * through ParallelSlotsGetIdle.
+ */
+ ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
+ run_vacuum_command(free_slot->connection, sql.data,
+ echo, tabname);
+ }
cell = cell->next;
} while (cell != NULL);
@@ -407,11 +419,19 @@ vacuum_one_database(ConnParams *cparams,
goto finish;
}
- ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, cmd, echo, NULL);
+ if (vacopts->dry_run)
+ {
+ printf("not executed: %s\n", cmd);
+ free_slot->inUse = false;
+ }
+ else
+ {
+ ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
+ run_vacuum_command(free_slot->connection, cmd, echo, NULL);
- if (!ParallelSlotsWaitCompletion(sa))
- ret = EXIT_FAILURE; /* error already reported by handler */
+ if (!ParallelSlotsWaitCompletion(sa))
+ ret = EXIT_FAILURE; /* error already reported by handler */
+ }
}
finish:
diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h
index 49f968b32e5..50155239e7e 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -51,6 +51,7 @@ typedef struct vacuumingOptions
bool skip_database_stats;
char *buffer_usage_limit;
bool missing_stats_only;
+ bool dry_run;
} vacuumingOptions;
/* Valid values for vacuumingOptions->objfilter */
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 84c76d7350c..100691b579a 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -171,6 +171,17 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--dry-run</option></term>
+ <listitem>
+ <para>
+ Print but do not execute the vacuum or analyze commands generated.
+ This is useful for testing the effects of various command-line options
+ before actually running the commands.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-e</option></term>
<term><option>--echo</option></term>
base-commit: 6b46669883fac9521c20fe4e2c55ccfbee778591
--
2.51.1
v3-0002-switch-from-passing-conn-to-passing-slot.patchtext/x-patch; charset=US-ASCII; name=v3-0002-switch-from-passing-conn-to-passing-slot.patchDownload
From 8ce09dd5c83191827f2faa874edd12610c6852e1 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Wed, 19 Nov 2025 19:13:45 -0500
Subject: [PATCH v3 2/5] switch from passing conn to passing slot
---
src/bin/scripts/vacuuming.c | 11 ++++++-----
1 file changed, 6 insertions(+), 5 deletions(-)
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index 5d9c07f8715..f1fa565679c 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -43,8 +43,8 @@ static SimpleStringList *retrieve_objects(PGconn *conn,
static void free_retrieved_objects(SimpleStringList *list);
static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
vacuumingOptions *vacopts, const char *table);
-static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
- const char *table);
+static void run_vacuum_command(ParallelSlot *free_slot, const char *sql,
+ bool echo, const char *table);
/*
* Executes vacuum/analyze as indicated. Returns 0 if the plan is carried
@@ -394,7 +394,7 @@ vacuum_one_database(ConnParams *cparams,
* through ParallelSlotsGetIdle.
*/
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, sql.data,
+ run_vacuum_command(free_slot, sql.data,
echo, tabname);
}
@@ -427,7 +427,7 @@ vacuum_one_database(ConnParams *cparams,
else
{
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, cmd, echo, NULL);
+ run_vacuum_command(free_slot, cmd, echo, NULL);
if (!ParallelSlotsWaitCompletion(sa))
ret = EXIT_FAILURE; /* error already reported by handler */
@@ -1021,10 +1021,11 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
* Any errors during command execution are reported to stderr.
*/
static void
-run_vacuum_command(PGconn *conn, const char *sql, bool echo,
+run_vacuum_command(ParallelSlot *free_slot, const char *sql, bool echo,
const char *table)
{
bool status;
+ PGconn *conn = free_slot->connection;
if (echo)
printf("%s\n", sql);
--
2.51.1
v3-0003-move-dry_run-test-inside-run_vacuum_command.patchtext/x-patch; charset=US-ASCII; name=v3-0003-move-dry_run-test-inside-run_vacuum_command.patchDownload
From 93130f08a24abef26001199ae17c8812f31df3e5 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Wed, 19 Nov 2025 19:24:26 -0500
Subject: [PATCH v3 3/5] move dry_run test inside run_vacuum_command()
---
src/bin/scripts/vacuuming.c | 57 ++++++++++++++++---------------------
1 file changed, 24 insertions(+), 33 deletions(-)
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index f1fa565679c..f52af273d7e 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -44,7 +44,7 @@ static void free_retrieved_objects(SimpleStringList *list);
static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
vacuumingOptions *vacopts, const char *table);
static void run_vacuum_command(ParallelSlot *free_slot, const char *sql,
- bool echo, const char *table);
+ bool echo, bool dry_run, const char *table);
/*
* Executes vacuum/analyze as indicated. Returns 0 if the plan is carried
@@ -378,25 +378,8 @@ vacuum_one_database(ConnParams *cparams,
prepare_vacuum_command(free_slot->connection, &sql,
vacopts, tabname);
- if (vacopts->dry_run)
- {
- /*
- * Print the command that we would have run in a real run,
- * the immediately mark the unused slot as free again.
- */
- printf("not executed: %s\n", sql.data);
- free_slot->inUse = false;
- }
- else
- {
- /*
- * Execute the vacuum. All errors are handled in processQueryResult
- * through ParallelSlotsGetIdle.
- */
- ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot, sql.data,
- echo, tabname);
- }
+ run_vacuum_command(free_slot, sql.data,
+ echo, vacopts->dry_run, tabname);
cell = cell->next;
} while (cell != NULL);
@@ -419,19 +402,10 @@ vacuum_one_database(ConnParams *cparams,
goto finish;
}
- if (vacopts->dry_run)
- {
- printf("not executed: %s\n", cmd);
- free_slot->inUse = false;
- }
- else
- {
- ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot, cmd, echo, NULL);
+ run_vacuum_command(free_slot, cmd, echo, vacopts->dry_run, NULL);
- if (!ParallelSlotsWaitCompletion(sa))
- ret = EXIT_FAILURE; /* error already reported by handler */
- }
+ if (!ParallelSlotsWaitCompletion(sa))
+ ret = EXIT_FAILURE; /* error already reported by handler */
}
finish:
@@ -1022,11 +996,28 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
*/
static void
run_vacuum_command(ParallelSlot *free_slot, const char *sql, bool echo,
- const char *table)
+ bool dry_run, const char *table)
{
bool status;
PGconn *conn = free_slot->connection;
+ if (dry_run)
+ {
+ /*
+ * Print the command that we would have run in a real run,
+ * the immediately mark the unused slot as free again.
+ */
+ printf("not executed: %s\n", sql);
+ free_slot->inUse = false;
+ return;
+ }
+
+ /*
+ * Execute the vacuum. All errors are handled in processQueryResult
+ * through ParallelSlotsGetIdle.
+ */
+ ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
+
if (echo)
printf("%s\n", sql);
--
2.51.1
v3-0004-switch-prepare-from-conn-to-serverVersion.patchtext/x-patch; charset=US-ASCII; name=v3-0004-switch-prepare-from-conn-to-serverVersion.patchDownload
From 2c0cf278b8e1c239fe90dcf2320f0c8d5c28646d Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Wed, 19 Nov 2025 19:36:59 -0500
Subject: [PATCH v3 4/5] switch prepare from conn to serverVersion
---
src/bin/scripts/vacuuming.c | 9 +++++----
1 file changed, 5 insertions(+), 4 deletions(-)
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index f52af273d7e..14d843bed10 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -41,7 +41,7 @@ static SimpleStringList *retrieve_objects(PGconn *conn,
SimpleStringList *objects,
bool echo);
static void free_retrieved_objects(SimpleStringList *list);
-static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
+static void prepare_vacuum_command(int serverVersion, PQExpBuffer sql,
vacuumingOptions *vacopts, const char *table);
static void run_vacuum_command(ParallelSlot *free_slot, const char *sql,
bool echo, bool dry_run, const char *table);
@@ -178,6 +178,7 @@ vacuum_one_database(ConnParams *cparams,
SimpleStringList *retobjs = NULL;
bool free_retobjs = false;
int ret = EXIT_SUCCESS;
+ int serverVersion;
const char *stage_commands[] = {
"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
"SET default_statistics_target=10; RESET vacuum_cost_delay;",
@@ -351,6 +352,7 @@ vacuum_one_database(ConnParams *cparams,
* for the first slot. If not in parallel mode, the first slot in the
* array contains the connection.
*/
+ serverVersion = PQserverVersion(conn);
sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd);
ParallelSlotsAdoptConn(sa, conn);
@@ -375,7 +377,7 @@ vacuum_one_database(ConnParams *cparams,
goto finish;
}
- prepare_vacuum_command(free_slot->connection, &sql,
+ prepare_vacuum_command(serverVersion, &sql,
vacopts, tabname);
run_vacuum_command(free_slot, sql.data,
@@ -823,10 +825,9 @@ free_retrieved_objects(SimpleStringList *list)
* depends on the server version involved and it is semicolon-terminated.
*/
static void
-prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
+prepare_vacuum_command(int serverVersion, PQExpBuffer sql,
vacuumingOptions *vacopts, const char *table)
{
- int serverVersion = PQserverVersion(conn);
const char *paren = " (";
const char *comma = ", ";
const char *sep = paren;
--
2.51.1
v3-0005-handle-ParallelSlotGetIdle-inside-run_vacuum_comm.patchtext/x-patch; charset=US-ASCII; name=v3-0005-handle-ParallelSlotGetIdle-inside-run_vacuum_comm.patchDownload
From ebd78b3a71c99ae36d934f844c36296407e3e23e Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Wed, 19 Nov 2025 19:50:05 -0500
Subject: [PATCH v3 5/5] handle ParallelSlotGetIdle() inside
run_vacuum_command()
---
src/bin/scripts/vacuuming.c | 41 ++++++++++++++++++-------------------
1 file changed, 20 insertions(+), 21 deletions(-)
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index 14d843bed10..5bd7ade62a1 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -43,7 +43,7 @@ static SimpleStringList *retrieve_objects(PGconn *conn,
static void free_retrieved_objects(SimpleStringList *list);
static void prepare_vacuum_command(int serverVersion, PQExpBuffer sql,
vacuumingOptions *vacopts, const char *table);
-static void run_vacuum_command(ParallelSlot *free_slot, const char *sql,
+static bool run_vacuum_command(ParallelSlotArray *sa, const char *sql,
bool echo, bool dry_run, const char *table);
/*
@@ -362,7 +362,6 @@ vacuum_one_database(ConnParams *cparams,
do
{
const char *tabname = cell->val;
- ParallelSlot *free_slot;
if (CancelRequested)
{
@@ -370,19 +369,14 @@ vacuum_one_database(ConnParams *cparams,
goto finish;
}
- free_slot = ParallelSlotsGetIdle(sa, NULL);
- if (!free_slot)
+ prepare_vacuum_command(serverVersion, &sql, vacopts, tabname);
+
+ if (!run_vacuum_command(sa, sql.data, echo, vacopts->dry_run, tabname))
{
ret = EXIT_FAILURE;
goto finish;
}
- prepare_vacuum_command(serverVersion, &sql,
- vacopts, tabname);
-
- run_vacuum_command(free_slot, sql.data,
- echo, vacopts->dry_run, tabname);
-
cell = cell->next;
} while (cell != NULL);
@@ -396,16 +390,13 @@ vacuum_one_database(ConnParams *cparams,
if (vacopts->mode == MODE_VACUUM && vacopts->skip_database_stats)
{
const char *cmd = "VACUUM (ONLY_DATABASE_STATS);";
- ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL);
- if (!free_slot)
+ if (!run_vacuum_command(sa, cmd, echo, vacopts->dry_run, NULL))
{
ret = EXIT_FAILURE;
goto finish;
}
- run_vacuum_command(free_slot, cmd, echo, vacopts->dry_run, NULL);
-
if (!ParallelSlotsWaitCompletion(sa))
ret = EXIT_FAILURE; /* error already reported by handler */
}
@@ -995,24 +986,29 @@ prepare_vacuum_command(int serverVersion, PQExpBuffer sql,
*
* Any errors during command execution are reported to stderr.
*/
-static void
-run_vacuum_command(ParallelSlot *free_slot, const char *sql, bool echo,
+static bool
+run_vacuum_command(ParallelSlotArray *sa, const char *sql, bool echo,
bool dry_run, const char *table)
+
{
+ ParallelSlot *free_slot;
bool status;
- PGconn *conn = free_slot->connection;
+ PGconn *conn;
if (dry_run)
{
/*
- * Print the command that we would have run in a real run,
- * the immediately mark the unused slot as free again.
+ * Print the command that we would have run in a real run.
*/
printf("not executed: %s\n", sql);
- free_slot->inUse = false;
- return;
+ return true;
}
+ free_slot = ParallelSlotsGetIdle(sa, NULL);
+
+ if (!free_slot)
+ return false;
+
/*
* Execute the vacuum. All errors are handled in processQueryResult
* through ParallelSlotsGetIdle.
@@ -1022,6 +1018,7 @@ run_vacuum_command(ParallelSlot *free_slot, const char *sql, bool echo,
if (echo)
printf("%s\n", sql);
+ conn = free_slot->connection;
status = PQsendQuery(conn, sql) == 1;
if (!status)
@@ -1037,6 +1034,8 @@ run_vacuum_command(ParallelSlot *free_slot, const char *sql, bool echo,
PQdb(conn), PQerrorMessage(conn));
}
}
+
+ return true;
}
/*
--
2.51.1
Re: vacuumdb: add --dry-run
On Wed, Nov 19, 2025 at 07:54:06PM -0500, Corey Huinker wrote:
Here's a shopping list of incremental changes. I'm happy with whatever
makes the most sense to you.
Here is a v4 patch set. I've made a variety of small changes. I think
there's some room to bike-shed on the messages we send to the user to
assure them we're not actually doing anything, but this is roughly what I
imagined.
--
nathan
Attachments:
v4-0001-Add-ParallelSlotSetIdle.patchtext/plain; charset=us-asciiDownload
From 225e0002409fd3026e374290f0b664c63f281dd1 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 20 Nov 2025 14:16:02 -0600
Subject: [PATCH v4 1/2] Add ParallelSlotSetIdle().
---
src/fe_utils/parallel_slot.c | 6 ++----
src/include/fe_utils/parallel_slot.h | 7 +++++++
2 files changed, 9 insertions(+), 4 deletions(-)
diff --git a/src/fe_utils/parallel_slot.c b/src/fe_utils/parallel_slot.c
index 253a840865e..1b9bc8b0da8 100644
--- a/src/fe_utils/parallel_slot.c
+++ b/src/fe_utils/parallel_slot.c
@@ -269,8 +269,7 @@ wait_on_slots(ParallelSlotArray *sa)
else
{
/* This connection has become idle */
- sa->slots[i].inUse = false;
- ParallelSlotClearHandler(&sa->slots[i]);
+ ParallelSlotSetIdle(&sa->slots[i]);
break;
}
}
@@ -509,8 +508,7 @@ ParallelSlotsWaitCompletion(ParallelSlotArray *sa)
if (!consumeQueryResult(&sa->slots[i]))
return false;
/* Mark connection as idle */
- sa->slots[i].inUse = false;
- ParallelSlotClearHandler(&sa->slots[i]);
+ ParallelSlotSetIdle(&sa->slots[i]);
}
return true;
diff --git a/src/include/fe_utils/parallel_slot.h b/src/include/fe_utils/parallel_slot.h
index 7770a20de34..4a5fbfb3d8c 100644
--- a/src/include/fe_utils/parallel_slot.h
+++ b/src/include/fe_utils/parallel_slot.h
@@ -58,6 +58,13 @@ ParallelSlotClearHandler(ParallelSlot *slot)
slot->handler_context = NULL;
}
+static inline void
+ParallelSlotSetIdle(ParallelSlot *slot)
+{
+ slot->inUse = false;
+ ParallelSlotClearHandler(slot);
+}
+
extern ParallelSlot *ParallelSlotsGetIdle(ParallelSlotArray *sa,
const char *dbname);
--
2.39.5 (Apple Git-154)
v4-0002-Add-dry-run-to-vacuumdb.patchtext/plain; charset=us-asciiDownload
From 121cc204f732d5aeffb71cfa513b0ecb4bba1985 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Mon, 10 Nov 2025 14:33:41 -0500
Subject: [PATCH v4 2/2] Add --dry-run to vacuumdb.
This option answers the question "what tables would be affected if I ran
a command using these options" without actually initiating those
actions.
---
doc/src/sgml/ref/vacuumdb.sgml | 10 +++++++
src/bin/scripts/t/100_vacuumdb.pl | 12 ++++++++
src/bin/scripts/vacuumdb.c | 11 ++++++-
src/bin/scripts/vacuuming.c | 50 +++++++++++++++++--------------
src/bin/scripts/vacuuming.h | 3 +-
5 files changed, 62 insertions(+), 24 deletions(-)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 84c76d7350c..89aa7fac4f8 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -171,6 +171,16 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--dry-run</option></term>
+ <listitem>
+ <para>
+ Print, but do not execute, the vacuum and analyze commands that would
+ have been sent to the server (performs a dry run).
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-e</option></term>
<term><option>--echo</option></term>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index a16fad593f7..fb2fecdd3c6 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -169,6 +169,10 @@ $node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', 'postgres' ],
qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
'vacuumdb --schema');
+$node->issues_sql_unlike(
+ [ 'vacuumdb', '--schema' => '"Foo"', 'postgres', '--dry-run' ],
+ qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
+ 'vacuumdb --dry-run');
$node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', '--schema' => '"Bar"', 'postgres' ],
qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
@@ -241,6 +245,14 @@ $node->safe_psql('postgres', q|
CREATE TABLE regression_vacuumdb_test AS select generate_series(1, 10) a, generate_series(2, 11) b;
ALTER TABLE regression_vacuumdb_test ADD COLUMN c INT GENERATED ALWAYS AS (a + b);
|);
+$node->issues_sql_unlike(
+ [
+ 'vacuumdb', '--analyze-only', '--dry-run',
+ '--missing-stats-only', '-t',
+ 'regression_vacuumdb_test', 'postgres'
+ ],
+ qr/statement:\ ANALYZE/sx,
+ '--missing-stats-only --dry-run');
$node->issues_sql_like(
[
'vacuumdb', '--analyze-only',
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index e117dac2242..68323fd2d10 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -59,6 +59,7 @@ main(int argc, char *argv[])
{"no-process-main", no_argument, NULL, 12},
{"buffer-usage-limit", required_argument, NULL, 13},
{"missing-stats-only", no_argument, NULL, 14},
+ {"dry-run", no_argument, NULL, 15},
{NULL, 0, NULL, 0}
};
@@ -70,6 +71,7 @@ main(int argc, char *argv[])
ConnParams cparams;
bool echo = false;
bool quiet = false;
+ bool dry_run = false;
vacuumingOptions vacopts;
SimpleStringList objects = {NULL, NULL};
int concurrentCons = 1;
@@ -213,6 +215,9 @@ main(int argc, char *argv[])
case 14:
vacopts.missing_stats_only = true;
break;
+ case 15:
+ dry_run = true;
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -309,10 +314,13 @@ main(int argc, char *argv[])
pg_fatal("cannot use the \"%s\" option without \"%s\" or \"%s\"",
"missing-stats-only", "analyze-only", "analyze-in-stages");
+ if (dry_run)
+ pg_log_info("Executing in dry-run mode.");
+
ret = vacuuming_main(&cparams, dbname, maintenance_db, &vacopts,
&objects, tbl_count,
concurrentCons,
- progname, echo, quiet);
+ progname, echo, quiet, dry_run);
exit(ret);
}
@@ -351,6 +359,7 @@ help(const char *progname)
printf(_(" --buffer-usage-limit=SIZE size of ring buffer used for vacuum\n"));
printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
+ printf(_(" --dry-run dry run, show commands that would be sent to the server\n"));
printf(_(" -e, --echo show the commands being sent to the server\n"));
printf(_(" -f, --full do full vacuuming\n"));
printf(_(" -F, --freeze freeze row transaction information\n"));
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index f836f21fb03..b7e0ce65c2e 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -30,12 +30,14 @@ static int vacuum_one_database(ConnParams *cparams,
SimpleStringList *objects,
SimpleStringList **found_objs,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname, bool echo, bool quiet,
+ bool dry_run);
static int vacuum_all_databases(ConnParams *cparams,
vacuumingOptions *vacopts,
SimpleStringList *objects,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname, bool echo, bool quiet,
+ bool dry_run);
static SimpleStringList *retrieve_objects(PGconn *conn,
vacuumingOptions *vacopts,
SimpleStringList *objects,
@@ -43,8 +45,8 @@ static SimpleStringList *retrieve_objects(PGconn *conn,
static void free_retrieved_objects(SimpleStringList *list);
static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
vacuumingOptions *vacopts, const char *table);
-static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
- const char *table);
+static void run_vacuum_command(ParallelSlot *free_slot, const char *sql,
+ bool echo, bool dry_run, const char *table);
/*
* Executes vacuum/analyze as indicated. Returns 0 if the plan is carried
@@ -56,7 +58,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
const char *maintenance_db, vacuumingOptions *vacopts,
SimpleStringList *objects,
unsigned int tbl_count, int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname, bool echo, bool quiet, bool dry_run)
{
setup_cancel_handler(NULL);
@@ -71,7 +73,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
return vacuum_all_databases(cparams, vacopts,
objects,
concurrentCons,
- progname, echo, quiet);
+ progname, echo, quiet, dry_run);
}
else
{
@@ -100,7 +102,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
objects,
vacopts->missing_stats_only ? &found_objs : NULL,
concurrentCons,
- progname, echo, quiet);
+ progname, echo, quiet, dry_run);
if (ret != 0)
{
free_retrieved_objects(found_objs);
@@ -116,7 +118,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
ANALYZE_NO_STAGE,
objects, NULL,
concurrentCons,
- progname, echo, quiet);
+ progname, echo, quiet, dry_run);
}
}
@@ -167,7 +169,7 @@ vacuum_one_database(ConnParams *cparams,
SimpleStringList *objects,
SimpleStringList **found_objs,
int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname, bool echo, bool quiet, bool dry_run)
{
PQExpBufferData sql;
PGconn *conn;
@@ -338,7 +340,7 @@ vacuum_one_database(ConnParams *cparams,
* caller requested that mode. We have to prepare the initial connection
* ourselves before setting up the slots.
*/
- if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
+ if (vacopts->mode == MODE_ANALYZE_IN_STAGES && !dry_run)
{
initcmd = stage_commands[stage];
executeCommand(conn, initcmd, echo);
@@ -383,8 +385,8 @@ vacuum_one_database(ConnParams *cparams,
* through ParallelSlotsGetIdle.
*/
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, sql.data,
- echo, tabname);
+ run_vacuum_command(free_slot, sql.data,
+ echo, dry_run, tabname);
cell = cell->next;
} while (cell != NULL);
@@ -408,7 +410,7 @@ vacuum_one_database(ConnParams *cparams,
}
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, cmd, echo, NULL);
+ run_vacuum_command(free_slot, cmd, echo, dry_run, NULL);
if (!ParallelSlotsWaitCompletion(sa))
ret = EXIT_FAILURE; /* error already reported by handler */
@@ -436,7 +438,7 @@ vacuum_all_databases(ConnParams *cparams,
vacuumingOptions *vacopts,
SimpleStringList *objects,
int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname, bool echo, bool quiet, bool dry_run)
{
int ret = EXIT_SUCCESS;
PGconn *conn;
@@ -474,7 +476,7 @@ vacuum_all_databases(ConnParams *cparams,
objects,
vacopts->missing_stats_only ? &found_objs[i] : NULL,
concurrentCons,
- progname, echo, quiet);
+ progname, echo, quiet, dry_run);
if (ret != EXIT_SUCCESS)
break;
}
@@ -499,7 +501,7 @@ vacuum_all_databases(ConnParams *cparams,
objects,
NULL,
concurrentCons,
- progname, echo, quiet);
+ progname, echo, quiet, dry_run);
if (ret != EXIT_SUCCESS)
break;
}
@@ -1001,15 +1003,19 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
* Any errors during command execution are reported to stderr.
*/
static void
-run_vacuum_command(PGconn *conn, const char *sql, bool echo,
- const char *table)
+run_vacuum_command(ParallelSlot *free_slot, const char *sql,
+ bool echo, bool dry_run, const char *table)
{
- bool status;
+ bool status = true;
+ PGconn *conn = free_slot->connection;
- if (echo)
- printf("%s\n", sql);
+ if (echo || dry_run)
+ printf("%s%s\n", sql, dry_run ? " -- not executed" : "");
- status = PQsendQuery(conn, sql) == 1;
+ if (dry_run)
+ ParallelSlotSetIdle(free_slot);
+ else
+ status = PQsendQuery(conn, sql) == 1;
if (!status)
{
diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h
index 49f968b32e5..46e53fc69c3 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -65,7 +65,8 @@ extern int vacuuming_main(ConnParams *cparams, const char *dbname,
SimpleStringList *objects,
unsigned int tbl_count,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname, bool echo, bool quiet,
+ bool dry_run);
extern char *escape_quotes(const char *src);
--
2.39.5 (Apple Git-154)
Re: vacuumdb: add --dry-run
On Thu, Nov 20, 2025 at 4:46 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:
On Wed, Nov 19, 2025 at 07:54:06PM -0500, Corey Huinker wrote:
Here's a shopping list of incremental changes. I'm happy with whatever
makes the most sense to you.Here is a v4 patch set. I've made a variety of small changes. I think
there's some room to bike-shed on the messages we send to the user to
assure them we're not actually doing anything, but this is roughly what I
imagined.
Everything here looks good to me. I have nothing to add.
I have no objections to, but I am curious about the factors that went into
making dry_run an independent boolean rather than part of vacopts.
Re: vacuumdb: add --dry-run
On Thu, Nov 20, 2025 at 05:09:54PM -0500, Corey Huinker wrote:
I have no objections to, but I am curious about the factors that went into
making dry_run an independent boolean rather than part of vacopts.
My thinking was that it's closer to "echo" and "quiet" than anything in
vacuumingOptions. Most of that stuff seems geared towards controlling the
precise behavior of the commands rather than the behavior of the
application. TBH I think it'd be fine either way. We could probably even
move "echo" and "quiet" into vacuumingOptions if we really wanted to.
--
nathan
Re: vacuumdb: add --dry-run
On Thu, Nov 20, 2025 at 04:16:13PM -0600, Nathan Bossart wrote:
On Thu, Nov 20, 2025 at 05:09:54PM -0500, Corey Huinker wrote:
I have no objections to, but I am curious about the factors that went into
making dry_run an independent boolean rather than part of vacopts.My thinking was that it's closer to "echo" and "quiet" than anything in
vacuumingOptions. Most of that stuff seems geared towards controlling the
precise behavior of the commands rather than the behavior of the
application. TBH I think it'd be fine either way. We could probably even
move "echo" and "quiet" into vacuumingOptions if we really wanted to.
Yeah, I'm finding myself liking the idea of moving all of these things into
vacuumingOptions so that we don't have to cart around so many bool
arguments. Here's a new patch set that does it this way.
The remaining question in my mind is where we should let the user know that
we're in dry-run mode. The three options I see are 1) at the beginning of
vacuumdb execution, 2) in the !quiet block for each database, and 3) in
each command (via a comment). In v5, I've added a message to all three,
but I'm eager to hear what folks think.
--
nathan
Attachments:
v5-0001-Move-some-vacuumdb-options-to-vacopts-struct.patchtext/plain; charset=us-asciiDownload
From 69911834a8046c226ce6c54a6afd2b51830084d0 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 3 Dec 2025 16:12:37 -0600
Subject: [PATCH v5 1/3] Move some vacuumdb options to vacopts struct.
---
src/bin/scripts/vacuumdb.c | 8 +++---
src/bin/scripts/vacuuming.c | 50 ++++++++++++++++++-------------------
src/bin/scripts/vacuuming.h | 4 ++-
3 files changed, 31 insertions(+), 31 deletions(-)
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index e117dac2242..f72ad9dd0ba 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -68,8 +68,6 @@ main(int argc, char *argv[])
const char *dbname = NULL;
const char *maintenance_db = NULL;
ConnParams cparams;
- bool echo = false;
- bool quiet = false;
vacuumingOptions vacopts;
SimpleStringList objects = {NULL, NULL};
int concurrentCons = 1;
@@ -110,7 +108,7 @@ main(int argc, char *argv[])
dbname = pg_strdup(optarg);
break;
case 'e':
- echo = true;
+ vacopts.echo = true;
break;
case 'f':
vacopts.full = true;
@@ -143,7 +141,7 @@ main(int argc, char *argv[])
exit(1);
break;
case 'q':
- quiet = true;
+ vacopts.quiet = true;
break;
case 't':
vacopts.objfilter |= OBJFILTER_TABLE;
@@ -312,7 +310,7 @@ main(int argc, char *argv[])
ret = vacuuming_main(&cparams, dbname, maintenance_db, &vacopts,
&objects, tbl_count,
concurrentCons,
- progname, echo, quiet);
+ progname);
exit(ret);
}
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index f836f21fb03..1af242b60d7 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -30,16 +30,15 @@ static int vacuum_one_database(ConnParams *cparams,
SimpleStringList *objects,
SimpleStringList **found_objs,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname);
static int vacuum_all_databases(ConnParams *cparams,
vacuumingOptions *vacopts,
SimpleStringList *objects,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname);
static SimpleStringList *retrieve_objects(PGconn *conn,
vacuumingOptions *vacopts,
- SimpleStringList *objects,
- bool echo);
+ SimpleStringList *objects);
static void free_retrieved_objects(SimpleStringList *list);
static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
vacuumingOptions *vacopts, const char *table);
@@ -56,7 +55,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
const char *maintenance_db, vacuumingOptions *vacopts,
SimpleStringList *objects,
unsigned int tbl_count, int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname)
{
setup_cancel_handler(NULL);
@@ -71,7 +70,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
return vacuum_all_databases(cparams, vacopts,
objects,
concurrentCons,
- progname, echo, quiet);
+ progname);
}
else
{
@@ -100,7 +99,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
objects,
vacopts->missing_stats_only ? &found_objs : NULL,
concurrentCons,
- progname, echo, quiet);
+ progname);
if (ret != 0)
{
free_retrieved_objects(found_objs);
@@ -116,7 +115,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
ANALYZE_NO_STAGE,
objects, NULL,
concurrentCons,
- progname, echo, quiet);
+ progname);
}
}
@@ -167,7 +166,7 @@ vacuum_one_database(ConnParams *cparams,
SimpleStringList *objects,
SimpleStringList **found_objs,
int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname)
{
PQExpBufferData sql;
PGconn *conn;
@@ -192,7 +191,7 @@ vacuum_one_database(ConnParams *cparams,
Assert(stage == ANALYZE_NO_STAGE ||
(stage >= 0 && stage < ANALYZE_NUM_STAGES));
- conn = connectDatabase(cparams, progname, echo, false, true);
+ conn = connectDatabase(cparams, progname, vacopts->echo, false, true);
if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
{
@@ -281,7 +280,7 @@ vacuum_one_database(ConnParams *cparams,
/* skip_database_stats is used automatically if server supports it */
vacopts->skip_database_stats = (PQserverVersion(conn) >= 160000);
- if (!quiet)
+ if (!vacopts->quiet)
{
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
printf(_("%s: processing database \"%s\": %s\n"),
@@ -302,7 +301,7 @@ vacuum_one_database(ConnParams *cparams,
retobjs = *found_objs;
else
{
- retobjs = retrieve_objects(conn, vacopts, objects, echo);
+ retobjs = retrieve_objects(conn, vacopts, objects);
if (found_objs)
*found_objs = retobjs;
else
@@ -341,7 +340,7 @@ vacuum_one_database(ConnParams *cparams,
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
{
initcmd = stage_commands[stage];
- executeCommand(conn, initcmd, echo);
+ executeCommand(conn, initcmd, vacopts->echo);
}
else
initcmd = NULL;
@@ -351,7 +350,8 @@ vacuum_one_database(ConnParams *cparams,
* for the first slot. If not in parallel mode, the first slot in the
* array contains the connection.
*/
- sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd);
+ sa = ParallelSlotsSetup(concurrentCons, cparams, progname,
+ vacopts->echo, initcmd);
ParallelSlotsAdoptConn(sa, conn);
initPQExpBuffer(&sql);
@@ -384,7 +384,7 @@ vacuum_one_database(ConnParams *cparams,
*/
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
run_vacuum_command(free_slot->connection, sql.data,
- echo, tabname);
+ vacopts->echo, tabname);
cell = cell->next;
} while (cell != NULL);
@@ -408,7 +408,7 @@ vacuum_one_database(ConnParams *cparams,
}
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, cmd, echo, NULL);
+ run_vacuum_command(free_slot->connection, cmd, vacopts->echo, NULL);
if (!ParallelSlotsWaitCompletion(sa))
ret = EXIT_FAILURE; /* error already reported by handler */
@@ -436,17 +436,17 @@ vacuum_all_databases(ConnParams *cparams,
vacuumingOptions *vacopts,
SimpleStringList *objects,
int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname)
{
int ret = EXIT_SUCCESS;
PGconn *conn;
PGresult *result;
int numdbs;
- conn = connectMaintenanceDatabase(cparams, progname, echo);
+ conn = connectMaintenanceDatabase(cparams, progname, vacopts->echo);
result = executeQuery(conn,
"SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
- echo);
+ vacopts->echo);
numdbs = PQntuples(result);
PQfinish(conn);
@@ -474,7 +474,7 @@ vacuum_all_databases(ConnParams *cparams,
objects,
vacopts->missing_stats_only ? &found_objs[i] : NULL,
concurrentCons,
- progname, echo, quiet);
+ progname);
if (ret != EXIT_SUCCESS)
break;
}
@@ -499,7 +499,7 @@ vacuum_all_databases(ConnParams *cparams,
objects,
NULL,
concurrentCons,
- progname, echo, quiet);
+ progname);
if (ret != EXIT_SUCCESS)
break;
}
@@ -524,7 +524,7 @@ vacuum_all_databases(ConnParams *cparams,
*/
static SimpleStringList *
retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
- SimpleStringList *objects, bool echo)
+ SimpleStringList *objects)
{
PQExpBufferData buf;
PQExpBufferData catalog_query;
@@ -776,10 +776,10 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
* query for consistency with table lookups done elsewhere by the user.
*/
appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
- executeCommand(conn, "RESET search_path;", echo);
- res = executeQuery(conn, catalog_query.data, echo);
+ executeCommand(conn, "RESET search_path;", vacopts->echo);
+ res = executeQuery(conn, catalog_query.data, vacopts->echo);
termPQExpBuffer(&catalog_query);
- PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, echo));
+ PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, vacopts->echo));
/*
* Build qualified identifiers for each table, including the column list
diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h
index 49f968b32e5..90db4fa1a64 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -51,6 +51,8 @@ typedef struct vacuumingOptions
bool skip_database_stats;
char *buffer_usage_limit;
bool missing_stats_only;
+ bool echo;
+ bool quiet;
} vacuumingOptions;
/* Valid values for vacuumingOptions->objfilter */
@@ -65,7 +67,7 @@ extern int vacuuming_main(ConnParams *cparams, const char *dbname,
SimpleStringList *objects,
unsigned int tbl_count,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname);
extern char *escape_quotes(const char *src);
--
2.39.5 (Apple Git-154)
v5-0002-Add-ParallelSlotSetIdle.patchtext/plain; charset=us-asciiDownload
From 3b35624019529d8aac10dcc81b9e2e0c88cb9748 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 20 Nov 2025 14:16:02 -0600
Subject: [PATCH v5 2/3] Add ParallelSlotSetIdle().
---
src/fe_utils/parallel_slot.c | 6 ++----
src/include/fe_utils/parallel_slot.h | 7 +++++++
2 files changed, 9 insertions(+), 4 deletions(-)
diff --git a/src/fe_utils/parallel_slot.c b/src/fe_utils/parallel_slot.c
index 253a840865e..1b9bc8b0da8 100644
--- a/src/fe_utils/parallel_slot.c
+++ b/src/fe_utils/parallel_slot.c
@@ -269,8 +269,7 @@ wait_on_slots(ParallelSlotArray *sa)
else
{
/* This connection has become idle */
- sa->slots[i].inUse = false;
- ParallelSlotClearHandler(&sa->slots[i]);
+ ParallelSlotSetIdle(&sa->slots[i]);
break;
}
}
@@ -509,8 +508,7 @@ ParallelSlotsWaitCompletion(ParallelSlotArray *sa)
if (!consumeQueryResult(&sa->slots[i]))
return false;
/* Mark connection as idle */
- sa->slots[i].inUse = false;
- ParallelSlotClearHandler(&sa->slots[i]);
+ ParallelSlotSetIdle(&sa->slots[i]);
}
return true;
diff --git a/src/include/fe_utils/parallel_slot.h b/src/include/fe_utils/parallel_slot.h
index 7770a20de34..4a5fbfb3d8c 100644
--- a/src/include/fe_utils/parallel_slot.h
+++ b/src/include/fe_utils/parallel_slot.h
@@ -58,6 +58,13 @@ ParallelSlotClearHandler(ParallelSlot *slot)
slot->handler_context = NULL;
}
+static inline void
+ParallelSlotSetIdle(ParallelSlot *slot)
+{
+ slot->inUse = false;
+ ParallelSlotClearHandler(slot);
+}
+
extern ParallelSlot *ParallelSlotsGetIdle(ParallelSlotArray *sa,
const char *dbname);
--
2.39.5 (Apple Git-154)
v5-0003-Add-dry-run-to-vacuumdb.patchtext/plain; charset=us-asciiDownload
From 1b1860b15f4937f3d7ebbddce14de0bfe302b128 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Mon, 10 Nov 2025 14:33:41 -0500
Subject: [PATCH v5 3/3] Add --dry-run to vacuumdb.
This option answers the question "what tables would be affected if I ran
a command using these options" without actually initiating those
actions.
---
doc/src/sgml/ref/vacuumdb.sgml | 10 +++++++
src/bin/scripts/t/100_vacuumdb.pl | 12 +++++++++
src/bin/scripts/vacuumdb.c | 8 ++++++
src/bin/scripts/vacuuming.c | 44 ++++++++++++++++++++-----------
src/bin/scripts/vacuuming.h | 1 +
5 files changed, 59 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 84c76d7350c..89aa7fac4f8 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -171,6 +171,16 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--dry-run</option></term>
+ <listitem>
+ <para>
+ Print, but do not execute, the vacuum and analyze commands that would
+ have been sent to the server (performs a dry run).
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-e</option></term>
<term><option>--echo</option></term>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index a16fad593f7..fb2fecdd3c6 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -169,6 +169,10 @@ $node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', 'postgres' ],
qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
'vacuumdb --schema');
+$node->issues_sql_unlike(
+ [ 'vacuumdb', '--schema' => '"Foo"', 'postgres', '--dry-run' ],
+ qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
+ 'vacuumdb --dry-run');
$node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', '--schema' => '"Bar"', 'postgres' ],
qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
@@ -241,6 +245,14 @@ $node->safe_psql('postgres', q|
CREATE TABLE regression_vacuumdb_test AS select generate_series(1, 10) a, generate_series(2, 11) b;
ALTER TABLE regression_vacuumdb_test ADD COLUMN c INT GENERATED ALWAYS AS (a + b);
|);
+$node->issues_sql_unlike(
+ [
+ 'vacuumdb', '--analyze-only', '--dry-run',
+ '--missing-stats-only', '-t',
+ 'regression_vacuumdb_test', 'postgres'
+ ],
+ qr/statement:\ ANALYZE/sx,
+ '--missing-stats-only --dry-run');
$node->issues_sql_like(
[
'vacuumdb', '--analyze-only',
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index f72ad9dd0ba..c1d8891736c 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -59,6 +59,7 @@ main(int argc, char *argv[])
{"no-process-main", no_argument, NULL, 12},
{"buffer-usage-limit", required_argument, NULL, 13},
{"missing-stats-only", no_argument, NULL, 14},
+ {"dry-run", no_argument, NULL, 15},
{NULL, 0, NULL, 0}
};
@@ -211,6 +212,9 @@ main(int argc, char *argv[])
case 14:
vacopts.missing_stats_only = true;
break;
+ case 15:
+ vacopts.dry_run = true;
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -307,6 +311,9 @@ main(int argc, char *argv[])
pg_fatal("cannot use the \"%s\" option without \"%s\" or \"%s\"",
"missing-stats-only", "analyze-only", "analyze-in-stages");
+ if (vacopts.dry_run)
+ pg_log_info("Executing in dry-run mode.");
+
ret = vacuuming_main(&cparams, dbname, maintenance_db, &vacopts,
&objects, tbl_count,
concurrentCons,
@@ -349,6 +356,7 @@ help(const char *progname)
printf(_(" --buffer-usage-limit=SIZE size of ring buffer used for vacuum\n"));
printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
+ printf(_(" --dry-run dry run, show commands that would be sent to the server\n"));
printf(_(" -e, --echo show the commands being sent to the server\n"));
printf(_(" -f, --full do full vacuuming\n"));
printf(_(" -F, --freeze freeze row transaction information\n"));
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index 1af242b60d7..32df69bde82 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -42,8 +42,8 @@ static SimpleStringList *retrieve_objects(PGconn *conn,
static void free_retrieved_objects(SimpleStringList *list);
static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
vacuumingOptions *vacopts, const char *table);
-static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
- const char *table);
+static void run_vacuum_command(ParallelSlot *free_slot, const char *sql,
+ bool echo, bool dry_run, const char *table);
/*
* Executes vacuum/analyze as indicated. Returns 0 if the plan is carried
@@ -283,11 +283,14 @@ vacuum_one_database(ConnParams *cparams,
if (!vacopts->quiet)
{
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
- printf(_("%s: processing database \"%s\": %s\n"),
- progname, PQdb(conn), _(stage_messages[stage]));
+ printf(_("%s: processing database \"%s\"%s: %s\n"),
+ progname, PQdb(conn),
+ vacopts->dry_run ? " (dry-run)" : "",
+ _(stage_messages[stage]));
else
- printf(_("%s: vacuuming database \"%s\"\n"),
- progname, PQdb(conn));
+ printf(_("%s: vacuuming database \"%s\"%s\n"),
+ progname, PQdb(conn),
+ vacopts->dry_run ? " (dry-run)" : "");
fflush(stdout);
}
@@ -340,7 +343,11 @@ vacuum_one_database(ConnParams *cparams,
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
{
initcmd = stage_commands[stage];
- executeCommand(conn, initcmd, vacopts->echo);
+
+ if (vacopts->dry_run)
+ printf("%s -- not executed\n", initcmd);
+ else
+ executeCommand(conn, initcmd, vacopts->echo);
}
else
initcmd = NULL;
@@ -383,8 +390,8 @@ vacuum_one_database(ConnParams *cparams,
* through ParallelSlotsGetIdle.
*/
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, sql.data,
- vacopts->echo, tabname);
+ run_vacuum_command(free_slot, sql.data, vacopts->echo,
+ vacopts->dry_run, tabname);
cell = cell->next;
} while (cell != NULL);
@@ -408,7 +415,8 @@ vacuum_one_database(ConnParams *cparams,
}
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, cmd, vacopts->echo, NULL);
+ run_vacuum_command(free_slot, cmd, vacopts->echo,
+ vacopts->dry_run, NULL);
if (!ParallelSlotsWaitCompletion(sa))
ret = EXIT_FAILURE; /* error already reported by handler */
@@ -1001,15 +1009,19 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
* Any errors during command execution are reported to stderr.
*/
static void
-run_vacuum_command(PGconn *conn, const char *sql, bool echo,
- const char *table)
+run_vacuum_command(ParallelSlot *free_slot, const char *sql,
+ bool echo, bool dry_run, const char *table)
{
- bool status;
+ bool status = true;
+ PGconn *conn = free_slot->connection;
- if (echo)
- printf("%s\n", sql);
+ if (echo || dry_run)
+ printf("%s%s\n", sql, dry_run ? " -- not executed" : "");
- status = PQsendQuery(conn, sql) == 1;
+ if (dry_run)
+ ParallelSlotSetIdle(free_slot);
+ else
+ status = PQsendQuery(conn, sql) == 1;
if (!status)
{
diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h
index 90db4fa1a64..586b6caa3d6 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -53,6 +53,7 @@ typedef struct vacuumingOptions
bool missing_stats_only;
bool echo;
bool quiet;
+ bool dry_run;
} vacuumingOptions;
/* Valid values for vacuumingOptions->objfilter */
--
2.39.5 (Apple Git-154)
Re: vacuumdb: add --dry-run
Yeah, I'm finding myself liking the idea of moving all of these things into
vacuumingOptions so that we don't have to cart around so many bool
arguments. Here's a new patch set that does it this way.
+1. Every time I modify a global variable, I hear my high school CS teacher
scolding me.
The remaining question in my mind is where we should let the user know that
we're in dry-run mode. The three options I see are 1) at the beginning of
vacuumdb execution, 2) in the !quiet block for each database, and 3) in
each command (via a comment). In v5, I've added a message to all three,
but I'm eager to hear what folks think.
Looking at them, I think they're all good. I think #3 is a must-have in all
circumstances. I wouldn't be mad if we removed #1 or #2, but I see the
value in each of them.
Re: vacuumdb: add --dry-run
On Thu, Dec 04, 2025 at 06:40:42PM -0500, Corey Huinker wrote:
Looking at them, I think they're all good. I think #3 is a must-have in all
circumstances. I wouldn't be mad if we removed #1 or #2, but I see the
value in each of them.
Alright. I think these are ready to go, but I'll wait for a bit in case
anyone else has feedback.
--
nathan
Re: vacuumdb: add --dry-run
On Fri, 5 Dec 2025 at 02:52, Nathan Bossart <nathandbossart@gmail.com> wrote:
On Thu, Nov 20, 2025 at 04:16:13PM -0600, Nathan Bossart wrote:
On Thu, Nov 20, 2025 at 05:09:54PM -0500, Corey Huinker wrote:
I have no objections to, but I am curious about the factors that went into
making dry_run an independent boolean rather than part of vacopts.My thinking was that it's closer to "echo" and "quiet" than anything in
vacuumingOptions. Most of that stuff seems geared towards controlling the
precise behavior of the commands rather than the behavior of the
application. TBH I think it'd be fine either way. We could probably even
move "echo" and "quiet" into vacuumingOptions if we really wanted to.Yeah, I'm finding myself liking the idea of moving all of these things into
vacuumingOptions so that we don't have to cart around so many bool
arguments. Here's a new patch set that does it this way.The remaining question in my mind is where we should let the user know that
we're in dry-run mode. The three options I see are 1) at the beginning of
vacuumdb execution, 2) in the !quiet block for each database, and 3) in
each command (via a comment). In v5, I've added a message to all three,
but I'm eager to hear what folks think.--
nathan
Hi!
1)
+ <varlistentry> + <term><option>--dry-run</option></term> + <listitem> + <para> + Print, but do not execute, the vacuum and analyze commands that would + have been sent to the server (performs a dry run). + </para> + </listitem> + </varlistentry>
I compared this smgl section to analogous sections of server utilities
(pg_dump, pg_resetwal, pg_rewind), none of them mentions "dry run" in
description of what "--dry run" is. So, I think my feeling is that
this `(performs a dry run).` is unneeded is correct.
2)
- printf(_("%s: vacuuming database \"%s\"\n"), - progname, PQdb(conn)); + printf(_("%s: vacuuming database \"%s\"%s\n"), + progname, PQdb(conn), + vacopts->dry_run ? " (dry-run)" : "");
I am also not sure we need this change. Look:
```
reshke@yezzey-cbdb-bench:~/pg$ ./bin/bin/vacuumdb --dry-run
vacuumdb: Executing in dry-run mode.
vacuumdb: vacuuming database "reshke" (dry-run)
```
We have two lines which say the same. Well, maybe there is value in
this change, if we are vacuuming multiple databases, but given that
--dry-run produces a lot of
`VACUUM ... -- not executed` output, I think It will be obvious that
this vacuumdb run does not modify the system. WDYT?
Overall, 0001 and 0003 are ok, I don't have an opinion on 0002.
--
Best regards,
Kirill Reshke
Re: vacuumdb: add --dry-run
On Sat, Dec 06, 2025 at 12:56:22AM +0500, Kirill Reshke wrote:
Hi!
Thanks for reviewing.
+ <varlistentry> + <term><option>--dry-run</option></term> + <listitem> + <para> + Print, but do not execute, the vacuum and analyze commands that would + have been sent to the server (performs a dry run). + </para> + </listitem> + </varlistentry>I compared this smgl section to analogous sections of server utilities
(pg_dump, pg_resetwal, pg_rewind), none of them mentions "dry run" in
description of what "--dry run" is. So, I think my feeling is that
this `(performs a dry run).` is unneeded is correct.
I borrowed this from pg_archivecleanup's documentation. But to your point,
there doesn't seem to be a tremendous amount of consistency in the dry-run
options for various utilities.
- printf(_("%s: vacuuming database \"%s\"\n"), - progname, PQdb(conn)); + printf(_("%s: vacuuming database \"%s\"%s\n"), + progname, PQdb(conn), + vacopts->dry_run ? " (dry-run)" : "");I am also not sure we need this change. Look:
```
reshke@yezzey-cbdb-bench:~/pg$ ./bin/bin/vacuumdb --dry-run
vacuumdb: Executing in dry-run mode.
vacuumdb: vacuuming database "reshke" (dry-run)
```We have two lines which say the same. Well, maybe there is value in
this change, if we are vacuuming multiple databases, but given that
--dry-run produces a lot of
`VACUUM ... -- not executed` output, I think It will be obvious that
this vacuumdb run does not modify the system. WDYT?
I guess we could probably remove the top-level "Executing in dry-run mode"
message, provided we say the same thing in the per-database message.
However, the latter can be turned off with --quiet. Maybe we should
consider disallowing --quiet and --dry-run.
Overall, I can't claim to have super principled arguments about where I've
added these dry-run messages. I kind-of just sprinkled them around.
--
nathan
Re: vacuumdb: add --dry-run
On Sat, 6 Dec 2025 at 01:08, Nathan Bossart <nathandbossart@gmail.com> wrote:
Overall, I can't claim to have super principled arguments about where I've
added these dry-run messages. I kind-of just sprinkled them around.--
Yep, sure, just polishing a patch. Let's remove the top-level message, indeed.
--
Best regards,
Kirill Reshke
Re: vacuumdb: add --dry-run
I guess we could probably remove the top-level "Executing in dry-run mode"
message, provided we say the same thing in the per-database message.
However, the latter can be turned off with --quiet. Maybe we should
consider disallowing --quiet and --dry-run.
--quiet does appear to be the sworn enemy of "tell me what you would have
done if you were really gonna do it". So yeah, disallowing that combination
would make sense.
Re: vacuumdb: add --dry-run
On Fri, Dec 05, 2025 at 03:34:12PM -0500, Corey Huinker wrote:
I guess we could probably remove the top-level "Executing in dry-run mode"
message, provided we say the same thing in the per-database message.
However, the latter can be turned off with --quiet. Maybe we should
consider disallowing --quiet and --dry-run.--quiet does appear to be the sworn enemy of "tell me what you would have
done if you were really gonna do it". So yeah, disallowing that combination
would make sense.
On the other hand, --quiet is handy if you're trying to save the output of
--dry-run elsewhere to run later. (Of course, if you use --all, that
output won't be tremendously useful.)
--
nathan
Re: vacuumdb: add --dry-run
Hi Nathan,
I just reviewed v5, and overall looks very good patch quality. Just a few nit comments on 0001 and 0003.
On Dec 5, 2025, at 05:52, Nathan Bossart <nathandbossart@gmail.com> wrote:
--
nathan
<v5-0001-Move-some-vacuumdb-options-to-vacopts-struct.patch><v5-0002-Add-ParallelSlotSetIdle.patch><v5-0003-Add-dry-run-to-vacuumdb.patch>
1 - 0001
```
/* initialize options */
memset(&vacopts, 0, sizeof(vacopts));
vacopts.objfilter = 0; /* no filter */
vacopts.parallel_workers = -1;
vacopts.buffer_usage_limit = NULL;
vacopts.no_index_cleanup = false;
vacopts.force_index_cleanup = false;
vacopts.do_truncate = true;
vacopts.process_main = true;
vacopts.process_toast = true;
```
Now echo and print are moved into vacopts and their default values are false. Here, memset() have properly initialized their values. But this piece of code still explicitly set boolean values to vacopts fields. So, to make it consistent, I feel we can also add explicit assignments to echo and print here, or remove those “false” assignments. This is not a correctness issue, just to keep in a consistent style.
2 - 0003
```
+ if (echo || dry_run)
+ printf("%s%s\n", sql, dry_run ? " -- not executed" : "");
```There are two white-spaces before “--“, I think one is enough, In the other place of 0003, you just one white-space before “--“.
3 - 0003
```
@@ -1001,15 +1009,19 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
* Any errors during command execution are reported to stderr.
*/
static void
-run_vacuum_command(PGconn *conn, const char *sql, bool echo,
- const char *table)
+run_vacuum_command(ParallelSlot *free_slot, const char *sql,
+ bool echo, bool dry_run, const char *table)
{
```Here are two comments:
* As run_vacuum_command() takes both echo and dry_run, and both of them are defined in vcaopts, why not change this function to take a const vcaopts * instead of two bools?
* The function comment needs to be updated. Now it won’t always send a command to server, with “dry_run”, it behaves differently.
4 - 0003
```
+ if (vacopts.dry_run)
+ pg_log_info("Executing in dry-run mode.”);
```Feels like “Running” is better than “Executing”.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Re: vacuumdb: add --dry-run
On Sat, Dec 06, 2025 at 07:48:22AM +0800, Chao Li wrote:
I just reviewed v5, and overall looks very good patch quality. Just a few
nit comments on 0001 and 0003.
I've attached an updated patch set. After giving the "dry-run" messages
another look, I think we should just print a note at the very beginning of
vacuumdb execution and leave it at that. The per-database messages weren't
translator friendly and IMHO didn't add much, and the "-- not executed"
comments were noisy and didn't reflect the commands that would've been sent
to the server.
Now echo and print are moved into vacopts and their default values are
false. Here, memset() have properly initialized their values. But this
piece of code still explicitly set boolean values to vacopts fields. So,
to make it consistent, I feel we can also add explicit assignments to
echo and print here, or remove those “false” assignments. This is not a
correctness issue, just to keep in a consistent style.
We are already pretty inconsistent about this. If anything, I think we
should do the opposite, i.e., remove any unnecessary initializations to
0/false/NULL. The memset() makes those redundant and should suffice in
most cases.
* As run_vacuum_command() takes both echo and dry_run, and both of them
are defined in vcaopts, why not change this function to take a const
vcaopts * instead of two bools?* The function comment needs to be updated. Now it won’t always send a
command to server, with “dry_run”, it behaves differently.
Done.
``` + if (vacopts.dry_run) + pg_log_info("Executing in dry-run mode.”); ```Feels like “Running” is better than “Executing”.
Done.
--
nathan
Attachments:
v6-0001-vacuumdb-Move-some-options-to-vacuumingOptions-st.patchtext/plain; charset=us-asciiDownload
From f7629cec0198cdd2f4c00a41f271cf576eb66967 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 3 Dec 2025 16:12:37 -0600
Subject: [PATCH v6 1/3] vacuumdb: Move some options to vacuumingOptions
struct.
TODO
Reviewed-by: Corey Huinker <corey.huinker@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://postgr.es/m/CADkLM%3DckHkX7Of5SrK7g0LokPUwJ%3Dkk8JU1GXGF5pZ1eBVr0%3DQ%40mail.gmail.com
---
src/bin/scripts/vacuumdb.c | 12 ++------
src/bin/scripts/vacuuming.c | 61 ++++++++++++++++++-------------------
src/bin/scripts/vacuuming.h | 4 ++-
3 files changed, 36 insertions(+), 41 deletions(-)
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index e117dac2242..6783c843637 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -68,8 +68,6 @@ main(int argc, char *argv[])
const char *dbname = NULL;
const char *maintenance_db = NULL;
ConnParams cparams;
- bool echo = false;
- bool quiet = false;
vacuumingOptions vacopts;
SimpleStringList objects = {NULL, NULL};
int concurrentCons = 1;
@@ -78,11 +76,7 @@ main(int argc, char *argv[])
/* initialize options */
memset(&vacopts, 0, sizeof(vacopts));
- vacopts.objfilter = 0; /* no filter */
vacopts.parallel_workers = -1;
- vacopts.buffer_usage_limit = NULL;
- vacopts.no_index_cleanup = false;
- vacopts.force_index_cleanup = false;
vacopts.do_truncate = true;
vacopts.process_main = true;
vacopts.process_toast = true;
@@ -110,7 +104,7 @@ main(int argc, char *argv[])
dbname = pg_strdup(optarg);
break;
case 'e':
- echo = true;
+ vacopts.echo = true;
break;
case 'f':
vacopts.full = true;
@@ -143,7 +137,7 @@ main(int argc, char *argv[])
exit(1);
break;
case 'q':
- quiet = true;
+ vacopts.quiet = true;
break;
case 't':
vacopts.objfilter |= OBJFILTER_TABLE;
@@ -312,7 +306,7 @@ main(int argc, char *argv[])
ret = vacuuming_main(&cparams, dbname, maintenance_db, &vacopts,
&objects, tbl_count,
concurrentCons,
- progname, echo, quiet);
+ progname);
exit(ret);
}
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index f836f21fb03..74a1f20e0f3 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -30,21 +30,20 @@ static int vacuum_one_database(ConnParams *cparams,
SimpleStringList *objects,
SimpleStringList **found_objs,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname);
static int vacuum_all_databases(ConnParams *cparams,
vacuumingOptions *vacopts,
SimpleStringList *objects,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname);
static SimpleStringList *retrieve_objects(PGconn *conn,
vacuumingOptions *vacopts,
- SimpleStringList *objects,
- bool echo);
+ SimpleStringList *objects);
static void free_retrieved_objects(SimpleStringList *list);
static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
vacuumingOptions *vacopts, const char *table);
-static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
- const char *table);
+static void run_vacuum_command(PGconn *conn, vacuumingOptions *vacopts,
+ const char *sql, const char *table);
/*
* Executes vacuum/analyze as indicated. Returns 0 if the plan is carried
@@ -56,7 +55,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
const char *maintenance_db, vacuumingOptions *vacopts,
SimpleStringList *objects,
unsigned int tbl_count, int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname)
{
setup_cancel_handler(NULL);
@@ -71,7 +70,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
return vacuum_all_databases(cparams, vacopts,
objects,
concurrentCons,
- progname, echo, quiet);
+ progname);
}
else
{
@@ -100,7 +99,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
objects,
vacopts->missing_stats_only ? &found_objs : NULL,
concurrentCons,
- progname, echo, quiet);
+ progname);
if (ret != 0)
{
free_retrieved_objects(found_objs);
@@ -116,7 +115,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
ANALYZE_NO_STAGE,
objects, NULL,
concurrentCons,
- progname, echo, quiet);
+ progname);
}
}
@@ -167,7 +166,7 @@ vacuum_one_database(ConnParams *cparams,
SimpleStringList *objects,
SimpleStringList **found_objs,
int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname)
{
PQExpBufferData sql;
PGconn *conn;
@@ -192,7 +191,7 @@ vacuum_one_database(ConnParams *cparams,
Assert(stage == ANALYZE_NO_STAGE ||
(stage >= 0 && stage < ANALYZE_NUM_STAGES));
- conn = connectDatabase(cparams, progname, echo, false, true);
+ conn = connectDatabase(cparams, progname, vacopts->echo, false, true);
if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
{
@@ -281,7 +280,7 @@ vacuum_one_database(ConnParams *cparams,
/* skip_database_stats is used automatically if server supports it */
vacopts->skip_database_stats = (PQserverVersion(conn) >= 160000);
- if (!quiet)
+ if (!vacopts->quiet)
{
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
printf(_("%s: processing database \"%s\": %s\n"),
@@ -302,7 +301,7 @@ vacuum_one_database(ConnParams *cparams,
retobjs = *found_objs;
else
{
- retobjs = retrieve_objects(conn, vacopts, objects, echo);
+ retobjs = retrieve_objects(conn, vacopts, objects);
if (found_objs)
*found_objs = retobjs;
else
@@ -341,7 +340,7 @@ vacuum_one_database(ConnParams *cparams,
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
{
initcmd = stage_commands[stage];
- executeCommand(conn, initcmd, echo);
+ executeCommand(conn, initcmd, vacopts->echo);
}
else
initcmd = NULL;
@@ -351,7 +350,8 @@ vacuum_one_database(ConnParams *cparams,
* for the first slot. If not in parallel mode, the first slot in the
* array contains the connection.
*/
- sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd);
+ sa = ParallelSlotsSetup(concurrentCons, cparams, progname,
+ vacopts->echo, initcmd);
ParallelSlotsAdoptConn(sa, conn);
initPQExpBuffer(&sql);
@@ -383,8 +383,7 @@ vacuum_one_database(ConnParams *cparams,
* through ParallelSlotsGetIdle.
*/
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, sql.data,
- echo, tabname);
+ run_vacuum_command(free_slot->connection, vacopts, sql.data, tabname);
cell = cell->next;
} while (cell != NULL);
@@ -408,7 +407,7 @@ vacuum_one_database(ConnParams *cparams,
}
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, cmd, echo, NULL);
+ run_vacuum_command(free_slot->connection, vacopts, cmd, NULL);
if (!ParallelSlotsWaitCompletion(sa))
ret = EXIT_FAILURE; /* error already reported by handler */
@@ -436,17 +435,17 @@ vacuum_all_databases(ConnParams *cparams,
vacuumingOptions *vacopts,
SimpleStringList *objects,
int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname)
{
int ret = EXIT_SUCCESS;
PGconn *conn;
PGresult *result;
int numdbs;
- conn = connectMaintenanceDatabase(cparams, progname, echo);
+ conn = connectMaintenanceDatabase(cparams, progname, vacopts->echo);
result = executeQuery(conn,
"SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
- echo);
+ vacopts->echo);
numdbs = PQntuples(result);
PQfinish(conn);
@@ -474,7 +473,7 @@ vacuum_all_databases(ConnParams *cparams,
objects,
vacopts->missing_stats_only ? &found_objs[i] : NULL,
concurrentCons,
- progname, echo, quiet);
+ progname);
if (ret != EXIT_SUCCESS)
break;
}
@@ -499,7 +498,7 @@ vacuum_all_databases(ConnParams *cparams,
objects,
NULL,
concurrentCons,
- progname, echo, quiet);
+ progname);
if (ret != EXIT_SUCCESS)
break;
}
@@ -524,7 +523,7 @@ vacuum_all_databases(ConnParams *cparams,
*/
static SimpleStringList *
retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
- SimpleStringList *objects, bool echo)
+ SimpleStringList *objects)
{
PQExpBufferData buf;
PQExpBufferData catalog_query;
@@ -776,10 +775,10 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
* query for consistency with table lookups done elsewhere by the user.
*/
appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
- executeCommand(conn, "RESET search_path;", echo);
- res = executeQuery(conn, catalog_query.data, echo);
+ executeCommand(conn, "RESET search_path;", vacopts->echo);
+ res = executeQuery(conn, catalog_query.data, vacopts->echo);
termPQExpBuffer(&catalog_query);
- PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, echo));
+ PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, vacopts->echo));
/*
* Build qualified identifiers for each table, including the column list
@@ -1001,12 +1000,12 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
* Any errors during command execution are reported to stderr.
*/
static void
-run_vacuum_command(PGconn *conn, const char *sql, bool echo,
- const char *table)
+run_vacuum_command(PGconn *conn, vacuumingOptions *vacopts,
+ const char *sql, const char *table)
{
bool status;
- if (echo)
+ if (vacopts->echo)
printf("%s\n", sql);
status = PQsendQuery(conn, sql) == 1;
diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h
index 49f968b32e5..90db4fa1a64 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -51,6 +51,8 @@ typedef struct vacuumingOptions
bool skip_database_stats;
char *buffer_usage_limit;
bool missing_stats_only;
+ bool echo;
+ bool quiet;
} vacuumingOptions;
/* Valid values for vacuumingOptions->objfilter */
@@ -65,7 +67,7 @@ extern int vacuuming_main(ConnParams *cparams, const char *dbname,
SimpleStringList *objects,
unsigned int tbl_count,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname);
extern char *escape_quotes(const char *src);
--
2.39.5 (Apple Git-154)
v6-0002-Add-ParallelSlotSetIdle.patchtext/plain; charset=us-asciiDownload
From 355e93d9a9ff61019520f76edd690fafd0399790 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 20 Nov 2025 14:16:02 -0600
Subject: [PATCH v6 2/3] Add ParallelSlotSetIdle().
TODO
Reviewed-by: Corey Huinker <corey.huinker@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com
Discussion: https://postgr.es/m/CADkLM%3DckHkX7Of5SrK7g0LokPUwJ%3Dkk8JU1GXGF5pZ1eBVr0%3DQ%40mail.gmail.com
---
src/fe_utils/parallel_slot.c | 6 ++----
src/include/fe_utils/parallel_slot.h | 7 +++++++
2 files changed, 9 insertions(+), 4 deletions(-)
diff --git a/src/fe_utils/parallel_slot.c b/src/fe_utils/parallel_slot.c
index 253a840865e..1b9bc8b0da8 100644
--- a/src/fe_utils/parallel_slot.c
+++ b/src/fe_utils/parallel_slot.c
@@ -269,8 +269,7 @@ wait_on_slots(ParallelSlotArray *sa)
else
{
/* This connection has become idle */
- sa->slots[i].inUse = false;
- ParallelSlotClearHandler(&sa->slots[i]);
+ ParallelSlotSetIdle(&sa->slots[i]);
break;
}
}
@@ -509,8 +508,7 @@ ParallelSlotsWaitCompletion(ParallelSlotArray *sa)
if (!consumeQueryResult(&sa->slots[i]))
return false;
/* Mark connection as idle */
- sa->slots[i].inUse = false;
- ParallelSlotClearHandler(&sa->slots[i]);
+ ParallelSlotSetIdle(&sa->slots[i]);
}
return true;
diff --git a/src/include/fe_utils/parallel_slot.h b/src/include/fe_utils/parallel_slot.h
index 7770a20de34..4a5fbfb3d8c 100644
--- a/src/include/fe_utils/parallel_slot.h
+++ b/src/include/fe_utils/parallel_slot.h
@@ -58,6 +58,13 @@ ParallelSlotClearHandler(ParallelSlot *slot)
slot->handler_context = NULL;
}
+static inline void
+ParallelSlotSetIdle(ParallelSlot *slot)
+{
+ slot->inUse = false;
+ ParallelSlotClearHandler(slot);
+}
+
extern ParallelSlot *ParallelSlotsGetIdle(ParallelSlotArray *sa,
const char *dbname);
--
2.39.5 (Apple Git-154)
v6-0003-vacuumdb-Add-dry-run.patchtext/plain; charset=us-asciiDownload
From df0228f6378ffa1861dd5afc2dfd3187d6fa943b Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Mon, 10 Nov 2025 14:33:41 -0500
Subject: [PATCH v6 3/3] vacuumdb: Add --dry-run.
TODO
Author: Corey Huinker <corey.huinker@gmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Discussion: https://postgr.es/m/CADkLM%3DckHkX7Of5SrK7g0LokPUwJ%3Dkk8JU1GXGF5pZ1eBVr0%3DQ%40mail.gmail.com
---
doc/src/sgml/ref/vacuumdb.sgml | 10 ++++++++++
src/bin/scripts/t/100_vacuumdb.pl | 12 ++++++++++++
src/bin/scripts/vacuumdb.c | 8 ++++++++
src/bin/scripts/vacuuming.c | 30 ++++++++++++++++++++----------
src/bin/scripts/vacuuming.h | 1 +
5 files changed, 51 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 84c76d7350c..508c8dfa146 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -171,6 +171,16 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--dry-run</option></term>
+ <listitem>
+ <para>
+ Print, but do not execute, the vacuum and analyze commands that would
+ have been sent to the server.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-e</option></term>
<term><option>--echo</option></term>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index a16fad593f7..fb2fecdd3c6 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -169,6 +169,10 @@ $node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', 'postgres' ],
qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
'vacuumdb --schema');
+$node->issues_sql_unlike(
+ [ 'vacuumdb', '--schema' => '"Foo"', 'postgres', '--dry-run' ],
+ qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
+ 'vacuumdb --dry-run');
$node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', '--schema' => '"Bar"', 'postgres' ],
qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
@@ -241,6 +245,14 @@ $node->safe_psql('postgres', q|
CREATE TABLE regression_vacuumdb_test AS select generate_series(1, 10) a, generate_series(2, 11) b;
ALTER TABLE regression_vacuumdb_test ADD COLUMN c INT GENERATED ALWAYS AS (a + b);
|);
+$node->issues_sql_unlike(
+ [
+ 'vacuumdb', '--analyze-only', '--dry-run',
+ '--missing-stats-only', '-t',
+ 'regression_vacuumdb_test', 'postgres'
+ ],
+ qr/statement:\ ANALYZE/sx,
+ '--missing-stats-only --dry-run');
$node->issues_sql_like(
[
'vacuumdb', '--analyze-only',
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 6783c843637..6e4c809e806 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -59,6 +59,7 @@ main(int argc, char *argv[])
{"no-process-main", no_argument, NULL, 12},
{"buffer-usage-limit", required_argument, NULL, 13},
{"missing-stats-only", no_argument, NULL, 14},
+ {"dry-run", no_argument, NULL, 15},
{NULL, 0, NULL, 0}
};
@@ -207,6 +208,9 @@ main(int argc, char *argv[])
case 14:
vacopts.missing_stats_only = true;
break;
+ case 15:
+ vacopts.dry_run = true;
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -303,6 +307,9 @@ main(int argc, char *argv[])
pg_fatal("cannot use the \"%s\" option without \"%s\" or \"%s\"",
"missing-stats-only", "analyze-only", "analyze-in-stages");
+ if (vacopts.dry_run && !vacopts.quiet)
+ pg_log_info("Running in dry-run mode.");
+
ret = vacuuming_main(&cparams, dbname, maintenance_db, &vacopts,
&objects, tbl_count,
concurrentCons,
@@ -345,6 +352,7 @@ help(const char *progname)
printf(_(" --buffer-usage-limit=SIZE size of ring buffer used for vacuum\n"));
printf(_(" -d, --dbname=DBNAME database to vacuum\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"));
printf(_(" -f, --full do full vacuuming\n"));
printf(_(" -F, --freeze freeze row transaction information\n"));
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index 74a1f20e0f3..73239e86e0a 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -42,8 +42,9 @@ static SimpleStringList *retrieve_objects(PGconn *conn,
static void free_retrieved_objects(SimpleStringList *list);
static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
vacuumingOptions *vacopts, const char *table);
-static void run_vacuum_command(PGconn *conn, vacuumingOptions *vacopts,
- const char *sql, const char *table);
+static void run_vacuum_command(ParallelSlot *free_slot,
+ vacuumingOptions *vacopts, const char *sql,
+ const char *table);
/*
* Executes vacuum/analyze as indicated. Returns 0 if the plan is carried
@@ -340,7 +341,11 @@ vacuum_one_database(ConnParams *cparams,
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
{
initcmd = stage_commands[stage];
- executeCommand(conn, initcmd, vacopts->echo);
+
+ if (vacopts->dry_run)
+ printf("%s\n", initcmd);
+ else
+ executeCommand(conn, initcmd, vacopts->echo);
}
else
initcmd = NULL;
@@ -383,7 +388,7 @@ vacuum_one_database(ConnParams *cparams,
* through ParallelSlotsGetIdle.
*/
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, vacopts, sql.data, tabname);
+ run_vacuum_command(free_slot, vacopts, sql.data, tabname);
cell = cell->next;
} while (cell != NULL);
@@ -407,7 +412,7 @@ vacuum_one_database(ConnParams *cparams,
}
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, vacopts, cmd, NULL);
+ run_vacuum_command(free_slot, vacopts, cmd, NULL);
if (!ParallelSlotsWaitCompletion(sa))
ret = EXIT_FAILURE; /* error already reported by handler */
@@ -995,20 +1000,25 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
/*
* Send a vacuum/analyze command to the server, returning after sending the
- * command.
+ * command. If dry_run is true, the command is printed but not sent to the
+ * server.
*
* Any errors during command execution are reported to stderr.
*/
static void
-run_vacuum_command(PGconn *conn, vacuumingOptions *vacopts,
+run_vacuum_command(ParallelSlot *free_slot, vacuumingOptions *vacopts,
const char *sql, const char *table)
{
- bool status;
+ bool status = true;
+ PGconn *conn = free_slot->connection;
- if (vacopts->echo)
+ if (vacopts->echo || vacopts->dry_run)
printf("%s\n", sql);
- status = PQsendQuery(conn, sql) == 1;
+ if (vacopts->dry_run)
+ ParallelSlotSetIdle(free_slot);
+ else
+ status = PQsendQuery(conn, sql) == 1;
if (!status)
{
diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h
index 90db4fa1a64..586b6caa3d6 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -53,6 +53,7 @@ typedef struct vacuumingOptions
bool missing_stats_only;
bool echo;
bool quiet;
+ bool dry_run;
} vacuumingOptions;
/* Valid values for vacuumingOptions->objfilter */
--
2.39.5 (Apple Git-154)
Re: vacuumdb: add --dry-run
On 2025-Dec-08, Nathan Bossart wrote:
On Sat, Dec 06, 2025 at 07:48:22AM +0800, Chao Li wrote:
``` + if (vacopts.dry_run) + pg_log_info("Executing in dry-run mode.”); ```Feels like “Running” is better than “Executing”.
Done.
I haven't read this thread, but chanced to come across this and wanted
to note recent commit c05dee191125. I'm not opposed to changing what
went in there, but let's make them all the same.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Re: vacuumdb: add --dry-run
On Mon, Dec 08, 2025 at 07:23:16PM +0100, Álvaro Herrera wrote:
I haven't read this thread, but chanced to come across this and wanted
to note recent commit c05dee191125. I'm not opposed to changing what
went in there, but let's make them all the same.
Ah, that must've been where I stole from originally. Will switch it back.
--
nathan
Re: vacuumdb: add --dry-run
On Mon, Dec 08, 2025 at 12:27:26PM -0600, Nathan Bossart wrote:
On Mon, Dec 08, 2025 at 07:23:16PM +0100, Álvaro Herrera wrote:
I haven't read this thread, but chanced to come across this and wanted
to note recent commit c05dee191125. I'm not opposed to changing what
went in there, but let's make them all the same.Ah, that must've been where I stole from originally. Will switch it back.
As promised...
--
nathan
Attachments:
v7-0001-vacuumdb-Move-some-options-to-vacuumingOptions-st.patchtext/plain; charset=us-asciiDownload
From a416a9594fd7433e5abbd4f68de97fed0579ac78 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Wed, 3 Dec 2025 16:12:37 -0600
Subject: [PATCH v7 1/3] vacuumdb: Move some options to vacuumingOptions
struct.
TODO
Reviewed-by: Corey Huinker <corey.huinker@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Discussion: https://postgr.es/m/CADkLM%3DckHkX7Of5SrK7g0LokPUwJ%3Dkk8JU1GXGF5pZ1eBVr0%3DQ%40mail.gmail.com
---
src/bin/scripts/vacuumdb.c | 12 ++------
src/bin/scripts/vacuuming.c | 61 ++++++++++++++++++-------------------
src/bin/scripts/vacuuming.h | 4 ++-
3 files changed, 36 insertions(+), 41 deletions(-)
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index e117dac2242..6783c843637 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -68,8 +68,6 @@ main(int argc, char *argv[])
const char *dbname = NULL;
const char *maintenance_db = NULL;
ConnParams cparams;
- bool echo = false;
- bool quiet = false;
vacuumingOptions vacopts;
SimpleStringList objects = {NULL, NULL};
int concurrentCons = 1;
@@ -78,11 +76,7 @@ main(int argc, char *argv[])
/* initialize options */
memset(&vacopts, 0, sizeof(vacopts));
- vacopts.objfilter = 0; /* no filter */
vacopts.parallel_workers = -1;
- vacopts.buffer_usage_limit = NULL;
- vacopts.no_index_cleanup = false;
- vacopts.force_index_cleanup = false;
vacopts.do_truncate = true;
vacopts.process_main = true;
vacopts.process_toast = true;
@@ -110,7 +104,7 @@ main(int argc, char *argv[])
dbname = pg_strdup(optarg);
break;
case 'e':
- echo = true;
+ vacopts.echo = true;
break;
case 'f':
vacopts.full = true;
@@ -143,7 +137,7 @@ main(int argc, char *argv[])
exit(1);
break;
case 'q':
- quiet = true;
+ vacopts.quiet = true;
break;
case 't':
vacopts.objfilter |= OBJFILTER_TABLE;
@@ -312,7 +306,7 @@ main(int argc, char *argv[])
ret = vacuuming_main(&cparams, dbname, maintenance_db, &vacopts,
&objects, tbl_count,
concurrentCons,
- progname, echo, quiet);
+ progname);
exit(ret);
}
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index f836f21fb03..74a1f20e0f3 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -30,21 +30,20 @@ static int vacuum_one_database(ConnParams *cparams,
SimpleStringList *objects,
SimpleStringList **found_objs,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname);
static int vacuum_all_databases(ConnParams *cparams,
vacuumingOptions *vacopts,
SimpleStringList *objects,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname);
static SimpleStringList *retrieve_objects(PGconn *conn,
vacuumingOptions *vacopts,
- SimpleStringList *objects,
- bool echo);
+ SimpleStringList *objects);
static void free_retrieved_objects(SimpleStringList *list);
static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
vacuumingOptions *vacopts, const char *table);
-static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
- const char *table);
+static void run_vacuum_command(PGconn *conn, vacuumingOptions *vacopts,
+ const char *sql, const char *table);
/*
* Executes vacuum/analyze as indicated. Returns 0 if the plan is carried
@@ -56,7 +55,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
const char *maintenance_db, vacuumingOptions *vacopts,
SimpleStringList *objects,
unsigned int tbl_count, int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname)
{
setup_cancel_handler(NULL);
@@ -71,7 +70,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
return vacuum_all_databases(cparams, vacopts,
objects,
concurrentCons,
- progname, echo, quiet);
+ progname);
}
else
{
@@ -100,7 +99,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
objects,
vacopts->missing_stats_only ? &found_objs : NULL,
concurrentCons,
- progname, echo, quiet);
+ progname);
if (ret != 0)
{
free_retrieved_objects(found_objs);
@@ -116,7 +115,7 @@ vacuuming_main(ConnParams *cparams, const char *dbname,
ANALYZE_NO_STAGE,
objects, NULL,
concurrentCons,
- progname, echo, quiet);
+ progname);
}
}
@@ -167,7 +166,7 @@ vacuum_one_database(ConnParams *cparams,
SimpleStringList *objects,
SimpleStringList **found_objs,
int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname)
{
PQExpBufferData sql;
PGconn *conn;
@@ -192,7 +191,7 @@ vacuum_one_database(ConnParams *cparams,
Assert(stage == ANALYZE_NO_STAGE ||
(stage >= 0 && stage < ANALYZE_NUM_STAGES));
- conn = connectDatabase(cparams, progname, echo, false, true);
+ conn = connectDatabase(cparams, progname, vacopts->echo, false, true);
if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
{
@@ -281,7 +280,7 @@ vacuum_one_database(ConnParams *cparams,
/* skip_database_stats is used automatically if server supports it */
vacopts->skip_database_stats = (PQserverVersion(conn) >= 160000);
- if (!quiet)
+ if (!vacopts->quiet)
{
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
printf(_("%s: processing database \"%s\": %s\n"),
@@ -302,7 +301,7 @@ vacuum_one_database(ConnParams *cparams,
retobjs = *found_objs;
else
{
- retobjs = retrieve_objects(conn, vacopts, objects, echo);
+ retobjs = retrieve_objects(conn, vacopts, objects);
if (found_objs)
*found_objs = retobjs;
else
@@ -341,7 +340,7 @@ vacuum_one_database(ConnParams *cparams,
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
{
initcmd = stage_commands[stage];
- executeCommand(conn, initcmd, echo);
+ executeCommand(conn, initcmd, vacopts->echo);
}
else
initcmd = NULL;
@@ -351,7 +350,8 @@ vacuum_one_database(ConnParams *cparams,
* for the first slot. If not in parallel mode, the first slot in the
* array contains the connection.
*/
- sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd);
+ sa = ParallelSlotsSetup(concurrentCons, cparams, progname,
+ vacopts->echo, initcmd);
ParallelSlotsAdoptConn(sa, conn);
initPQExpBuffer(&sql);
@@ -383,8 +383,7 @@ vacuum_one_database(ConnParams *cparams,
* through ParallelSlotsGetIdle.
*/
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, sql.data,
- echo, tabname);
+ run_vacuum_command(free_slot->connection, vacopts, sql.data, tabname);
cell = cell->next;
} while (cell != NULL);
@@ -408,7 +407,7 @@ vacuum_one_database(ConnParams *cparams,
}
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, cmd, echo, NULL);
+ run_vacuum_command(free_slot->connection, vacopts, cmd, NULL);
if (!ParallelSlotsWaitCompletion(sa))
ret = EXIT_FAILURE; /* error already reported by handler */
@@ -436,17 +435,17 @@ vacuum_all_databases(ConnParams *cparams,
vacuumingOptions *vacopts,
SimpleStringList *objects,
int concurrentCons,
- const char *progname, bool echo, bool quiet)
+ const char *progname)
{
int ret = EXIT_SUCCESS;
PGconn *conn;
PGresult *result;
int numdbs;
- conn = connectMaintenanceDatabase(cparams, progname, echo);
+ conn = connectMaintenanceDatabase(cparams, progname, vacopts->echo);
result = executeQuery(conn,
"SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
- echo);
+ vacopts->echo);
numdbs = PQntuples(result);
PQfinish(conn);
@@ -474,7 +473,7 @@ vacuum_all_databases(ConnParams *cparams,
objects,
vacopts->missing_stats_only ? &found_objs[i] : NULL,
concurrentCons,
- progname, echo, quiet);
+ progname);
if (ret != EXIT_SUCCESS)
break;
}
@@ -499,7 +498,7 @@ vacuum_all_databases(ConnParams *cparams,
objects,
NULL,
concurrentCons,
- progname, echo, quiet);
+ progname);
if (ret != EXIT_SUCCESS)
break;
}
@@ -524,7 +523,7 @@ vacuum_all_databases(ConnParams *cparams,
*/
static SimpleStringList *
retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
- SimpleStringList *objects, bool echo)
+ SimpleStringList *objects)
{
PQExpBufferData buf;
PQExpBufferData catalog_query;
@@ -776,10 +775,10 @@ retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
* query for consistency with table lookups done elsewhere by the user.
*/
appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
- executeCommand(conn, "RESET search_path;", echo);
- res = executeQuery(conn, catalog_query.data, echo);
+ executeCommand(conn, "RESET search_path;", vacopts->echo);
+ res = executeQuery(conn, catalog_query.data, vacopts->echo);
termPQExpBuffer(&catalog_query);
- PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, echo));
+ PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, vacopts->echo));
/*
* Build qualified identifiers for each table, including the column list
@@ -1001,12 +1000,12 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
* Any errors during command execution are reported to stderr.
*/
static void
-run_vacuum_command(PGconn *conn, const char *sql, bool echo,
- const char *table)
+run_vacuum_command(PGconn *conn, vacuumingOptions *vacopts,
+ const char *sql, const char *table)
{
bool status;
- if (echo)
+ if (vacopts->echo)
printf("%s\n", sql);
status = PQsendQuery(conn, sql) == 1;
diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h
index 49f968b32e5..90db4fa1a64 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -51,6 +51,8 @@ typedef struct vacuumingOptions
bool skip_database_stats;
char *buffer_usage_limit;
bool missing_stats_only;
+ bool echo;
+ bool quiet;
} vacuumingOptions;
/* Valid values for vacuumingOptions->objfilter */
@@ -65,7 +67,7 @@ extern int vacuuming_main(ConnParams *cparams, const char *dbname,
SimpleStringList *objects,
unsigned int tbl_count,
int concurrentCons,
- const char *progname, bool echo, bool quiet);
+ const char *progname);
extern char *escape_quotes(const char *src);
--
2.39.5 (Apple Git-154)
v7-0002-Add-ParallelSlotSetIdle.patchtext/plain; charset=us-asciiDownload
From dfaab136d2a7e2e5c93e98ee8f27a7ac0b5084ba Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Thu, 20 Nov 2025 14:16:02 -0600
Subject: [PATCH v7 2/3] Add ParallelSlotSetIdle().
TODO
Reviewed-by: Corey Huinker <corey.huinker@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com
Discussion: https://postgr.es/m/CADkLM%3DckHkX7Of5SrK7g0LokPUwJ%3Dkk8JU1GXGF5pZ1eBVr0%3DQ%40mail.gmail.com
---
src/fe_utils/parallel_slot.c | 6 ++----
src/include/fe_utils/parallel_slot.h | 7 +++++++
2 files changed, 9 insertions(+), 4 deletions(-)
diff --git a/src/fe_utils/parallel_slot.c b/src/fe_utils/parallel_slot.c
index 253a840865e..1b9bc8b0da8 100644
--- a/src/fe_utils/parallel_slot.c
+++ b/src/fe_utils/parallel_slot.c
@@ -269,8 +269,7 @@ wait_on_slots(ParallelSlotArray *sa)
else
{
/* This connection has become idle */
- sa->slots[i].inUse = false;
- ParallelSlotClearHandler(&sa->slots[i]);
+ ParallelSlotSetIdle(&sa->slots[i]);
break;
}
}
@@ -509,8 +508,7 @@ ParallelSlotsWaitCompletion(ParallelSlotArray *sa)
if (!consumeQueryResult(&sa->slots[i]))
return false;
/* Mark connection as idle */
- sa->slots[i].inUse = false;
- ParallelSlotClearHandler(&sa->slots[i]);
+ ParallelSlotSetIdle(&sa->slots[i]);
}
return true;
diff --git a/src/include/fe_utils/parallel_slot.h b/src/include/fe_utils/parallel_slot.h
index 7770a20de34..4a5fbfb3d8c 100644
--- a/src/include/fe_utils/parallel_slot.h
+++ b/src/include/fe_utils/parallel_slot.h
@@ -58,6 +58,13 @@ ParallelSlotClearHandler(ParallelSlot *slot)
slot->handler_context = NULL;
}
+static inline void
+ParallelSlotSetIdle(ParallelSlot *slot)
+{
+ slot->inUse = false;
+ ParallelSlotClearHandler(slot);
+}
+
extern ParallelSlot *ParallelSlotsGetIdle(ParallelSlotArray *sa,
const char *dbname);
--
2.39.5 (Apple Git-154)
v7-0003-vacuumdb-Add-dry-run.patchtext/plain; charset=utf-8Download
From 30c3cd08d298aa75937bed3cf2edb74633dc2c22 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Mon, 8 Dec 2025 13:22:22 -0600
Subject: [PATCH v7 3/3] vacuumdb: Add --dry-run.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
TODO
Author: Corey Huinker <corey.huinker@gmail.com>
Reviewed-by: Chao Li <li.evan.chao@gmail.com>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-by: Álvaro Herrera <alvherre@kurilemu.de>
Discussion: https://postgr.es/m/CADkLM%3DckHkX7Of5SrK7g0LokPUwJ%3Dkk8JU1GXGF5pZ1eBVr0%3DQ%40mail.gmail.com
---
doc/src/sgml/ref/vacuumdb.sgml | 10 ++++++++++
src/bin/scripts/t/100_vacuumdb.pl | 12 ++++++++++++
src/bin/scripts/vacuumdb.c | 9 +++++++++
src/bin/scripts/vacuuming.c | 30 ++++++++++++++++++++----------
src/bin/scripts/vacuuming.h | 1 +
5 files changed, 52 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index 84c76d7350c..508c8dfa146 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -171,6 +171,16 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>--dry-run</option></term>
+ <listitem>
+ <para>
+ Print, but do not execute, the vacuum and analyze commands that would
+ have been sent to the server.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-e</option></term>
<term><option>--echo</option></term>
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index a16fad593f7..fb2fecdd3c6 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -169,6 +169,10 @@ $node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', 'postgres' ],
qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
'vacuumdb --schema');
+$node->issues_sql_unlike(
+ [ 'vacuumdb', '--schema' => '"Foo"', 'postgres', '--dry-run' ],
+ qr/VACUUM \(SKIP_DATABASE_STATS\) "Foo".bar/,
+ 'vacuumdb --dry-run');
$node->issues_sql_like(
[ 'vacuumdb', '--schema' => '"Foo"', '--schema' => '"Bar"', 'postgres' ],
qr/VACUUM\ \(SKIP_DATABASE_STATS\)\ "Foo".bar
@@ -241,6 +245,14 @@ $node->safe_psql('postgres', q|
CREATE TABLE regression_vacuumdb_test AS select generate_series(1, 10) a, generate_series(2, 11) b;
ALTER TABLE regression_vacuumdb_test ADD COLUMN c INT GENERATED ALWAYS AS (a + b);
|);
+$node->issues_sql_unlike(
+ [
+ 'vacuumdb', '--analyze-only', '--dry-run',
+ '--missing-stats-only', '-t',
+ 'regression_vacuumdb_test', 'postgres'
+ ],
+ qr/statement:\ ANALYZE/sx,
+ '--missing-stats-only --dry-run');
$node->issues_sql_like(
[
'vacuumdb', '--analyze-only',
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 6783c843637..0bc443be348 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -59,6 +59,7 @@ main(int argc, char *argv[])
{"no-process-main", no_argument, NULL, 12},
{"buffer-usage-limit", required_argument, NULL, 13},
{"missing-stats-only", no_argument, NULL, 14},
+ {"dry-run", no_argument, NULL, 15},
{NULL, 0, NULL, 0}
};
@@ -207,6 +208,9 @@ main(int argc, char *argv[])
case 14:
vacopts.missing_stats_only = true;
break;
+ case 15:
+ vacopts.dry_run = true;
+ break;
default:
/* getopt_long already emitted a complaint */
pg_log_error_hint("Try \"%s --help\" for more information.", progname);
@@ -303,6 +307,10 @@ main(int argc, char *argv[])
pg_fatal("cannot use the \"%s\" option without \"%s\" or \"%s\"",
"missing-stats-only", "analyze-only", "analyze-in-stages");
+ if (vacopts.dry_run && !vacopts.quiet)
+ pg_log_info("Executing in dry-run mode.\n"
+ "No commands will be sent to the server.");
+
ret = vacuuming_main(&cparams, dbname, maintenance_db, &vacopts,
&objects, tbl_count,
concurrentCons,
@@ -345,6 +353,7 @@ help(const char *progname)
printf(_(" --buffer-usage-limit=SIZE size of ring buffer used for vacuum\n"));
printf(_(" -d, --dbname=DBNAME database to vacuum\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"));
printf(_(" -f, --full do full vacuuming\n"));
printf(_(" -F, --freeze freeze row transaction information\n"));
diff --git a/src/bin/scripts/vacuuming.c b/src/bin/scripts/vacuuming.c
index 74a1f20e0f3..73239e86e0a 100644
--- a/src/bin/scripts/vacuuming.c
+++ b/src/bin/scripts/vacuuming.c
@@ -42,8 +42,9 @@ static SimpleStringList *retrieve_objects(PGconn *conn,
static void free_retrieved_objects(SimpleStringList *list);
static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
vacuumingOptions *vacopts, const char *table);
-static void run_vacuum_command(PGconn *conn, vacuumingOptions *vacopts,
- const char *sql, const char *table);
+static void run_vacuum_command(ParallelSlot *free_slot,
+ vacuumingOptions *vacopts, const char *sql,
+ const char *table);
/*
* Executes vacuum/analyze as indicated. Returns 0 if the plan is carried
@@ -340,7 +341,11 @@ vacuum_one_database(ConnParams *cparams,
if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
{
initcmd = stage_commands[stage];
- executeCommand(conn, initcmd, vacopts->echo);
+
+ if (vacopts->dry_run)
+ printf("%s\n", initcmd);
+ else
+ executeCommand(conn, initcmd, vacopts->echo);
}
else
initcmd = NULL;
@@ -383,7 +388,7 @@ vacuum_one_database(ConnParams *cparams,
* through ParallelSlotsGetIdle.
*/
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, vacopts, sql.data, tabname);
+ run_vacuum_command(free_slot, vacopts, sql.data, tabname);
cell = cell->next;
} while (cell != NULL);
@@ -407,7 +412,7 @@ vacuum_one_database(ConnParams *cparams,
}
ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
- run_vacuum_command(free_slot->connection, vacopts, cmd, NULL);
+ run_vacuum_command(free_slot, vacopts, cmd, NULL);
if (!ParallelSlotsWaitCompletion(sa))
ret = EXIT_FAILURE; /* error already reported by handler */
@@ -995,20 +1000,25 @@ prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
/*
* Send a vacuum/analyze command to the server, returning after sending the
- * command.
+ * command. If dry_run is true, the command is printed but not sent to the
+ * server.
*
* Any errors during command execution are reported to stderr.
*/
static void
-run_vacuum_command(PGconn *conn, vacuumingOptions *vacopts,
+run_vacuum_command(ParallelSlot *free_slot, vacuumingOptions *vacopts,
const char *sql, const char *table)
{
- bool status;
+ bool status = true;
+ PGconn *conn = free_slot->connection;
- if (vacopts->echo)
+ if (vacopts->echo || vacopts->dry_run)
printf("%s\n", sql);
- status = PQsendQuery(conn, sql) == 1;
+ if (vacopts->dry_run)
+ ParallelSlotSetIdle(free_slot);
+ else
+ status = PQsendQuery(conn, sql) == 1;
if (!status)
{
diff --git a/src/bin/scripts/vacuuming.h b/src/bin/scripts/vacuuming.h
index 90db4fa1a64..586b6caa3d6 100644
--- a/src/bin/scripts/vacuuming.h
+++ b/src/bin/scripts/vacuuming.h
@@ -53,6 +53,7 @@ typedef struct vacuumingOptions
bool missing_stats_only;
bool echo;
bool quiet;
+ bool dry_run;
} vacuumingOptions;
/* Valid values for vacuumingOptions->objfilter */
--
2.39.5 (Apple Git-154)
Re: vacuumdb: add --dry-run
On 2025-Dec-08, Nathan Bossart wrote:
On Mon, Dec 08, 2025 at 12:27:26PM -0600, Nathan Bossart wrote:
On Mon, Dec 08, 2025 at 07:23:16PM +0100, Álvaro Herrera wrote:
I haven't read this thread, but chanced to come across this and wanted
to note recent commit c05dee191125. I'm not opposed to changing what
went in there, but let's make them all the same.Ah, that must've been where I stole from originally. Will switch it back.
As promised...
This looks reasonable to me in a quick read.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Re: vacuumdb: add --dry-run
On Mon, Dec 08, 2025 at 08:45:07PM +0100, Álvaro Herrera wrote:
This looks reasonable to me in a quick read.
Thanks. Unless there is more feedback, I plan to commit these tomorrow.
--
nathan
Re: vacuumdb: add --dry-run
On Dec 9, 2025, at 03:24, Nathan Bossart <nathandbossart@gmail.com> wrote:
On Mon, Dec 08, 2025 at 12:27:26PM -0600, Nathan Bossart wrote:
On Mon, Dec 08, 2025 at 07:23:16PM +0100, Álvaro Herrera wrote:
I haven't read this thread, but chanced to come across this and wanted
to note recent commit c05dee191125. I'm not opposed to changing what
went in there, but let's make them all the same.Ah, that must've been where I stole from originally. Will switch it back.
As promised...
--
nathan
<v7-0001-vacuumdb-Move-some-options-to-vacuumingOptions-st.patch><v7-0002-Add-ParallelSlotSetIdle.patch><v7-0003-vacuumdb-Add-dry-run.patch>
I searched over the source tree, and find “Running in xxx mode” in initdb and option.c:
Initdb:
```
printf(_("Running in debug mode.\n"));
printf(_("Running in no-clean mode. Mistakes will not be cleaned up.\n"));```
Options.
```
pg_log(PG_REPORT, "Running in verbose mode”);
```
And “”Executing in dry-run mode” in a few commands: pg_archivecleanup.c, pg_createsubscriber.c, pg_combinebackup.c and pg_rewind.c.
Should we make them all consistent?
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Re: vacuumdb: add --dry-run
On Tue, Dec 09, 2025 at 07:04:24AM +0800, Chao Li wrote:
I searched over the source tree, and find “Running in xxx mode” in initdb
and option.c:[...]
And “”Executing in dry-run mode” in a few commands: pg_archivecleanup.c,
pg_createsubscriber.c, pg_combinebackup.c and pg_rewind.c.Should we make them all consistent?
I think both phrasings clearly convey the information just fine.
--
nathan