autovacuum در PostgreSQL؛ تشخیص عقب‌افتادگی پاک‌سازی و خطر wraparound

SELECT current_user,pg_has_role(current_user,'pg_read_all_stats','USAGE') AS can_read_all_stats;
SELECT c.oid::regclass,c.relowner::regrole,
       has_table_privilege(current_user,c.oid,'MAINTAIN') AS has_maintain
FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname='public' AND c.relname='orders';

SELECT pg_stat_clear_snapshot();
SELECT clock_timestamp(),stats_reset FROM pg_stat_database
WHERE datname=current_database();
SELECT schemaname,relname,n_dead_tup,last_autovacuum,last_vacuum
FROM pg_stat_user_tables
WHERE schemaname='public' AND relname='orders';

SELECT p.pid,p.datname,p.relid,p.phase,
       p.heap_blks_total,p.heap_blks_scanned,p.heap_blks_vacuumed,
       p.index_vacuum_count,a.backend_type,a.wait_event_type,a.wait_event,
       pg_blocking_pids(p.pid) AS blocking_pids
FROM pg_stat_progress_vacuum p
LEFT JOIN pg_stat_activity a USING(pid)
WHERE p.datid=(SELECT oid FROM pg_database WHERE datname=current_database())
ORDER BY p.pid;

\set target_schema 'public'
\set target_table 'orders'
SELECT c.oid,c.relkind,n.nspname,c.relname,c.reloptions,
       age(c.relfrozenxid) AS xid_age,mxid_age(c.relminmxid) AS mxid_age,
       age(t.relfrozenxid) AS toast_xid_age,
       mxid_age(t.relminmxid) AS toast_mxid_age
FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
LEFT JOIN pg_class t ON t.oid=c.reltoastrelid
WHERE n.nspname=:'target_schema' AND c.relname=:'target_table';
SELECT pid,usename,application_name,backend_type,state,
       clock_timestamp()-xact_start AS transaction_duration,
       age(backend_xid) AS xid_age,age(backend_xmin) AS xmin_age,
       wait_event_type,wait_event
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC NULLS LAST LIMIT 30;
SELECT gid,prepared,owner,database,age(transaction) AS xid_age
FROM pg_prepared_xacts ORDER BY age(transaction) DESC;
SELECT slot_name,slot_type,database,active,active_pid,
       age(xmin) AS xmin_age,age(catalog_xmin) AS catalog_xmin_age,
       wal_status
FROM pg_replication_slots ORDER BY age(catalog_xmin) DESC NULLS LAST;