diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml
index f304627802..41c7f3df79 100644
--- a/doc/src/sgml/ref/vacuumdb.sgml
+++ b/doc/src/sgml/ref/vacuumdb.sgml
@@ -172,6 +172,60 @@ PostgreSQL documentation
+
+
+
+
+ Only execute the vacuum or analyze commands on tables with a multixact
+ ID age of at least mxid_age.
+ This setting is useful for prioritizing tables to process to prevent
+ multixact ID wraparound (see
+ ).
+
+
+ For the purposes of this option, the multixact ID age of a relation is
+ the greatest of the ages of the main relation and its associated
+ TOAST table, if one exists. Since the commands
+ issued by vacuumdb will also process the
+ TOAST table for the relation if necessary, it does
+ not need to be considered separately.
+
+
+
+ This option is only available for servers running
+ PostgreSQL 9.6 and later.
+
+
+
+
+
+
+
+
+
+ Only execute the vacuum or analyze commands on tables with a
+ transaction ID age of at least
+ xid_age. This setting
+ is useful for prioritizing tables to process to prevent transaction
+ ID wraparound (see ).
+
+
+ For the purposes of this option, the transaction ID age of a relation
+ is the greatest of the ages of the main relation and its associated
+ TOAST table, if one exists. Since the commands
+ issued by vacuumdb will also process the
+ TOAST table for the relation if necessary, it does
+ not need to be considered separately.
+
+
+
+ This option is only available for servers running
+ PostgreSQL 9.6 and later.
+
+
+
+
+
diff --git a/src/bin/scripts/t/100_vacuumdb.pl b/src/bin/scripts/t/100_vacuumdb.pl
index 5e87af2d51..7f3a9b14a9 100644
--- a/src/bin/scripts/t/100_vacuumdb.pl
+++ b/src/bin/scripts/t/100_vacuumdb.pl
@@ -3,7 +3,7 @@ use warnings;
use PostgresNode;
use TestLib;
-use Test::More tests => 38;
+use Test::More tests => 44;
program_help_ok('vacuumdb');
program_version_ok('vacuumdb');
@@ -95,3 +95,20 @@ $node->command_checks_all(
[qr/^.*vacuuming database "postgres"/],
[qr/^WARNING.*cannot vacuum non-tables or special system tables/s],
'vacuumdb with view');
+$node->command_fails(
+ [ 'vacuumdb', '--table', 'vactable', '--min-mxid-age', '0',
+ 'postgres'],
+ 'vacuumdb --min-mxid-age with incorrect value');
+$node->command_fails(
+ [ 'vacuumdb', '--table', 'vactable', '--min-xid-age', '0',
+ 'postgres'],
+ 'vacuumdb --min-xid-age with incorrect value');
+$node->issues_sql_like(
+ [ 'vacuumdb', '--table', 'vactable', '--min-mxid-age', '2147483000',
+ 'postgres'],
+ qr/GREATEST.*relminmxid.*2147483000/,
+ 'vacuumdb --table --min-mxid-age');
+$node->issues_sql_like(
+ [ 'vacuumdb', '--min-xid-age', '2147483001', 'postgres' ],
+ qr/GREATEST.*relfrozenxid.*2147483001/,
+ 'vacuumdb --table --min-xid-age');
diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c
index 40ba8283a2..cb503569bb 100644
--- a/src/bin/scripts/vacuumdb.c
+++ b/src/bin/scripts/vacuumdb.c
@@ -43,6 +43,8 @@ typedef struct vacuumingOptions
bool freeze;
bool disable_page_skipping;
bool skip_locked;
+ int min_xid_age;
+ int min_mxid_age;
} vacuumingOptions;
@@ -113,6 +115,8 @@ main(int argc, char *argv[])
{"analyze-in-stages", no_argument, NULL, 3},
{"disable-page-skipping", no_argument, NULL, 4},
{"skip-locked", no_argument, NULL, 5},
+ {"min-xid-age", required_argument, NULL, 6},
+ {"min-mxid-age", required_argument, NULL, 7},
{NULL, 0, NULL, 0}
};
@@ -222,6 +226,24 @@ main(int argc, char *argv[])
case 5:
vacopts.skip_locked = true;
break;
+ case 6:
+ vacopts.min_xid_age = atoi(optarg);
+ if (vacopts.min_xid_age <= 0)
+ {
+ fprintf(stderr, _("%s: minimum transaction ID age must be at least 1\n"),
+ progname);
+ exit(1);
+ }
+ break;
+ case 7:
+ vacopts.min_mxid_age = atoi(optarg);
+ if (vacopts.min_mxid_age <= 0)
+ {
+ fprintf(stderr, _("%s: minimum multixact ID age must be at least 1\n"),
+ progname);
+ exit(1);
+ }
+ break;
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
@@ -370,6 +392,7 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
bool failed = false;
bool parallel = concurrentCons > 1;
bool tables_listed = false;
+ bool has_where = false;
const char *stage_commands[] = {
"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
"SET default_statistics_target=10; RESET vacuum_cost_delay;",
@@ -403,6 +426,20 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
exit(1);
}
+ if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 90600)
+ {
+ fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 9.6\n"),
+ progname, "--min-xid-age");
+ exit(1);
+ }
+
+ if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90600)
+ {
+ fprintf(stderr, _("%s: cannot use the \"%s\" option on server versions older than PostgreSQL 9.6\n"),
+ progname, "--min-mxid-age");
+ exit(1);
+ }
+
if (!quiet)
{
if (stage != ANALYZE_NO_STAGE)
@@ -477,7 +514,9 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
appendPQExpBuffer(&catalog_query,
" FROM pg_catalog.pg_class c\n"
" JOIN pg_catalog.pg_namespace ns"
- " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n");
+ " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
+ " LEFT JOIN pg_catalog.pg_class t"
+ " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
/* Used to match the tables listed by the user */
if (tables_listed)
@@ -491,9 +530,43 @@ vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
* processed in which case the user will know about it.
*/
if (!tables_listed)
+ {
appendPQExpBuffer(&catalog_query, " WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array["
CppAsString2(RELKIND_RELATION) ", "
CppAsString2(RELKIND_MATVIEW) "])\n");
+ has_where = true;
+ }
+
+ /*
+ * For --min-xid-age and --min-mxid-age, the age of the relation is the
+ * greatest of the ages of the main relation and its associated TOAST
+ * table. The commands generated by vacuumdb will also process the TOAST
+ * table for the relation if necessary, so it does not need to be
+ * considered separately.
+ */
+ if (vacopts->min_xid_age != 0)
+ {
+ appendPQExpBuffer(&catalog_query,
+ " %s GREATEST(pg_catalog.age(c.relfrozenxid),"
+ " pg_catalog.age(t.relfrozenxid)) "
+ " OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n"
+ " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
+ " '0'::pg_catalog.xid\n",
+ has_where ? "AND" : "WHERE", vacopts->min_xid_age);
+ has_where = true;
+ }
+
+ if (vacopts->min_mxid_age != 0)
+ {
+ appendPQExpBuffer(&catalog_query,
+ " %s GREATEST(pg_catalog.mxid_age(c.relminmxid),"
+ " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)"
+ " '%d'::pg_catalog.int4\n"
+ " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
+ " '0'::pg_catalog.xid\n",
+ has_where ? "AND" : "WHERE", vacopts->min_mxid_age);
+ has_where = true;
+ }
/*
* Execute the catalog query. We use the default search_path for this
@@ -1152,6 +1225,8 @@ help(const char *progname)
printf(_(" -f, --full do full vacuuming\n"));
printf(_(" -F, --freeze freeze row transaction information\n"));
printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
+ printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
+ printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
printf(_(" -q, --quiet don't write any messages\n"));
printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));