diff --git a/src/bin/scripts/t/103_vacuumdb_anti.pl b/src/bin/scripts/t/103_vacuumdb_anti.pl new file mode 100644 index 0000000000..06d885690a --- /dev/null +++ b/src/bin/scripts/t/103_vacuumdb_anti.pl @@ -0,0 +1,14 @@ +use strict; +use warnings; + +use PostgresNode; +use Test::More tests => 2; + +my $node = get_new_node('main'); +$node->init; +$node->start; + +$node->issues_sql_like( + [ 'vacuumdb', '--anti-wraparound' ], + qr/statement: VACUUM.*statement: VACUUM/s, + 'vacuum anti-wraparound'); diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 8c2eade1d5..e9b94c4776 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -32,6 +32,7 @@ typedef struct vacuumingOptions bool full; bool freeze; bool disable_page_skipping; + bool anti_wraparound; bool skip_locked; int min_xid_age; int min_mxid_age; @@ -97,6 +98,7 @@ main(int argc, char *argv[]) {"min-mxid-age", required_argument, NULL, 7}, {"no-index-cleanup", no_argument, NULL, 8}, {"no-truncate", no_argument, NULL, 9}, + {"anti-wraparound", no_argument, NULL, 10}, {NULL, 0, NULL, 0} }; @@ -233,6 +235,9 @@ main(int argc, char *argv[]) case 9: vacopts.do_truncate = false; break; + case 10: + vacopts.anti_wraparound = true; + break; default: fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname); exit(1); @@ -277,6 +282,12 @@ main(int argc, char *argv[]) "disable-page-skipping"); exit(1); } + if (vacopts.anti_wraparound) + { + pg_log_error("cannot use the \"%s\" option when performing only analyze", + "anti-wraparound"); + exit(1); + } if (!vacopts.do_index_cleanup) { pg_log_error("cannot use the \"%s\" option when performing only analyze", @@ -446,6 +457,14 @@ vacuum_one_database(const ConnParams *cparams, exit(1); } + if (vacopts->anti_wraparound && PQserverVersion(conn) < 140000) + { + PQfinish(conn); + pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s", + "anti-wraparound", "14"); + exit(1); + } + if (!vacopts->do_truncate && PQserverVersion(conn) < 120000) { PQfinish(conn); @@ -611,11 +630,19 @@ vacuum_one_database(const ConnParams *cparams, has_where = true; } + /* + * If we use anti_wraparound option then vacuum relations in priority order + * by the age of their relfrozenxid. + */ + if (vacopts->anti_wraparound) + appendPQExpBufferStr(&catalog_query, " ORDER BY age(c.relfrozenxid) DESC;"); + else + appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;"); + /* * Execute the catalog query. We use the default search_path for this * 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); termPQExpBuffer(&catalog_query); @@ -750,9 +777,21 @@ vacuum_all_databases(ConnParams *cparams, int i; conn = connectMaintenanceDatabase(cparams, progname, echo); - result = executeQuery(conn, - "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;", - echo); + + /* + * If we use anti_wraparound option then vacuum databases in priority order + * by the age of their datfrozenxid. + */ + if (vacopts->anti_wraparound) + result = executeQuery(conn, + "SELECT datname FROM pg_database WHERE datallowconn " + "ORDER BY age(datfrozenxid) DESC;", + echo); + else + result = executeQuery(conn, + "SELECT datname FROM pg_database WHERE datallowconn " + "ORDER BY 1;", + echo); PQfinish(conn); if (analyze_in_stages) @@ -855,6 +894,13 @@ prepare_vacuum_command(PQExpBuffer sql, int serverVersion, appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep); sep = comma; } + if (vacopts->anti_wraparound) + { + /* ANTI_WRAPAROUND is supported since v14 */ + Assert(serverVersion >= 140000); + appendPQExpBuffer(sql, "%sANTI_WRAPAROUND", sep); + sep = comma; + } if (!vacopts->do_index_cleanup) { /* INDEX_CLEANUP is supported since v12 */ @@ -979,6 +1025,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(_(" --anti-wraparound scan aggressively to avoid transaction wraparound\n")); printf(_(" -?, --help show this help, then exit\n")); printf(_("\nConnection options:\n")); printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));