PostgreSQL autovacuum: diagnose cleanup lag and wraparound risk

Use an approved Linux administration host with the major-18 client already installed. On Ubuntu/Debian, the versioned postgresql-client-18 package supplies the explicit launcher below; the generic Debian wrapper can select a different installed client. On the RHEL family this path requires the PGDG postgresql18 client, not the distribution postgresql package. Arch and Manjaro use postgresql-libs and the unversioned launcher; check that its major is 18.

If the selected package or launcher is absent, stop and provision the approved client through the linked vendor repository instructions in a maintenance change. Do not initialize a cluster, add a repository or upgrade a server during an incident. Manjaro keeps its own branches; do not install an Arch package or switch branches for this guide.

cat /etc/os-release
id
pacman -Q postgresql-libs
pacman -Qo /usr/bin/psql
test -x /usr/bin/psql
/usr/bin/psql --version

On the administration host, open the observation profile. Keep this terminal for all diagnostic SQL. The connection must lead directly to a PostgreSQL backend with a stable session, rather than a transaction-pooling endpoint.

/usr/bin/psql -X -v ON_ERROR_STOP=1 'service=academy_vacuum_observe'

Read the connection identity before every investigation. The check accepts only server major 18 and the approved statistics role, then puts subsequent transactions into read-only mode. A replica can help with some observation, but its recovery flag means it is not a target for manual vacuum.

Keep autocommit on and do not wrap these panels in a long BEGIN block: a diagnostic session must not become another old transaction. The freeze and failsafe settings describe different stages; reaching the autovacuum trigger is not the same as reaching the wraparound shutdown boundary.

\conninfo
SELECT current_database(), current_user, version(), pg_is_in_recovery();
SELECT current_setting('server_version_num')::integer BETWEEN 180000 AND 189999
       AND pg_has_role(current_user,'pg_read_all_stats','USAGE') AS ready \gset
\if :ready
\else
\echo 'STOP: require PostgreSQL 18 and approved pg_read_all_stats access.'
\quit
\endif
SET statement_timeout='15s';
SET lock_timeout='2s';
SET default_transaction_read_only=on;
SHOW autovacuum;
SHOW track_counts;
SHOW autovacuum_freeze_max_age;
SHOW autovacuum_multixact_freeze_max_age;
SHOW vacuum_failsafe_age;
SHOW vacuum_multixact_failsafe_age;

Read the cluster-wide database catalog. XID age counts consumed transaction IDs, not seconds or rows; a read-only transaction does not necessarily allocate a new XID. Multixact age tracks a different ID space used by shared row locks. A write-heavy cluster can consume headroom quickly even when the oldest row is not old in wall-clock time.

Compare both age series with their own configured freeze thresholds and trend them at a reasonable interval. This ordering is by XID age; explicitly inspect the multixact column too. A small value in one column does not clear the other. A database that disallows connections needs the DBA’s database-specific procedure, not a blind connection-property change.

SELECT clock_timestamp() AS captured_at, datname, datallowconn,
       age(datfrozenxid) AS xid_age, mxid_age(datminmxid) AS mxid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;