本节简单介绍了几个关于xid freeze的脚本。 **Monitor Database** 数据库监控脚本 ``` [local:/data/run/pg12]:5120 pg12@testdb=# show autovacuum_freeze_max_age; autovacuum_freeze_max_age --------------------------- 200000000 (1 row) [local:/data/run/pg12]:5120 pg12@testdb=# WITH max_age AS ( pg12@testdb(# SELECT 2000000000 as max_old_xid pg12@testdb(# , setting AS autovacuum_freeze_max_age pg12@testdb(# FROM pg_catalog.pg_settings pg12@testdb(# WHERE name = 'autovacuum_freeze_max_age' ) pg12@testdb-# , per_database_stats AS ( pg12@testdb(# SELECT datname pg12@testdb(# , m.max_old_xid::int pg12@testdb(# , m.autovacuum_freeze_max_age::int pg12@testdb(# , age(d.datfrozenxid) AS oldest_current_xid pg12@testdb(# FROM pg_catalog.pg_database d pg12@testdb(# JOIN max_age m ON (true) pg12@testdb(# WHERE d.datallowconn ) pg12@testdb-# SELECT max(oldest_current_xid) AS oldest_current_xid pg12@testdb-# , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound pg12@testdb-# , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac pg12@testdb-# FROM per_database_stats; oldest_current_xid | percent_towards_wraparound | percent_towards_emergency_autovac --------------------+----------------------------+----------------------------------- 3844 | 0 | 0 (1 row) [local:/data/run/pg12]:5120 pg12@testdb=# ``` percent_towards_wraparound=数据库年龄/20亿，如接近100%，那么需要特别注意； percent_towards_emergency_autovac=数据库年龄/autovacuum_freeze_max_age，autovacuum_freeze_max_age参数一般为2亿。 下面的脚本列出了每个数据库的年龄和autovacuum_freeze_max_age参数的设定。 ``` [local:/data/run/pg12]:5120 pg12@testdb=# SELECT datname pg12@testdb-# , age(datfrozenxid) pg12@testdb-# , current_setting('autovacuum_freeze_max_age') pg12@testdb-# FROM pg_database pg12@testdb-# ORDER BY 2 DESC; datname | age | current_setting -----------+------+----------------- postgres | 3844 | 200000000 template1 | 3844 | 200000000 template0 | 3844 | 200000000 db1 | 3844 | 200000000 db2 | 3844 | 200000000 db3 | 3844 | 200000000 testdb | 3844 | 200000000 (7 rows) ``` **Monitor relation** 监控关系（数据表） ``` [local:/data/run/pg12]:5120 pg12@testdb=# SELECT c.oid::regclass pg12@testdb-# , age(c.relfrozenxid) pg12@testdb-# , pg_size_pretty(pg_total_relation_size(c.oid)) pg12@testdb-# FROM pg_class c pg12@testdb-# JOIN pg_namespace n on c.relnamespace = n.oid pg12@testdb-# WHERE relkind IN ('r', 't', 'm') pg12@testdb-# AND n.nspname NOT IN ('pg_toast') pg12@testdb-# ORDER BY 2 DESC LIMIT 100; oid | age | pg_size_pretty --------------------------------------------+------+---------------- pg_policy | 3844 | 24 kB pg_init_privs | 3844 | 72 kB pg_seclabel | 3844 | 16 kB pg_shseclabel | 3844 | 16 kB pg_collation | 3844 | 384 kB pg_partitioned_table | 3844 | 16 kB pg_range | 3844 | 56 kB pg_transform | 3844 | 16 kB pg_sequence | 3844 | 8192 bytes pg_publication | 3844 | 16 kB pg_publication_rel | 3844 | 16 kB pg_subscription_rel | 3844 | 8192 bytes information_schema.sql_packages | 3844 | 48 kB information_schema.sql_features | 3844 | 104 kB information_schema.sql_implementation_info | 3844 | 48 kB information_schema.sql_parts | 3844 | 48 kB information_schema.sql_languages | 3844 | 48 kB information_schema.sql_sizing | 3844 | 48 kB pg_statistic | 3844 | 312 kB pg_type | 3844 | 192 kB pg_foreign_server | 3844 | 24 kB pg_authid | 3844 | 48 kB pg_statistic_ext_data | 3844 | 16 kB --More-- ``` 以上列出了每个relation的age以及relation的大小。 **Auto Generate Script** 该脚本自动创建清理脚本。 ``` [local:/data/run/pg12]:5120 pg12@testdb=# \t Tuples only is on. [local:/data/run/pg12]:5120 pg12@testdb=# \o /tmp/vacuum.sql [local:/data/run/pg12]:5120 pg12@testdb=# select 'vacuum freeze analyze verbose ' || oid::regclass || ';' from pg_class where relkind in ('r', 't', 'm') order by age(relfrozenxid) desc limit 100; [local:/data/run/pg12]:5120 pg12@testdb=# \o [local:/data/run/pg12]:5120 pg12@testdb=# \t Tuples only is off. [local:/data/run/pg12]:5120 pg12@testdb=# \set ECHO all [local:/data/run/pg12]:5120 pg12@testdb=# \! cat /tmp/vacuum.sql vacuum freeze analyze verbose pg_ts_parser; vacuum freeze analyze verbose pg_collation; vacuum freeze analyze verbose pg_partitioned_table; vacuum freeze analyze verbose pg_range; vacuum freeze analyze verbose pg_transform; vacuum freeze analyze verbose pg_sequence; vacuum freeze analyze verbose pg_publication; vacuum freeze analyze verbose pg_publication_rel; vacuum freeze analyze verbose pg_subscription_rel; vacuum freeze analyze verbose information_schema.sql_packages; vacuum freeze analyze verbose pg_toast.pg_toast_13426; vacuum freeze analyze verbose information_schema.sql_features; vacuum freeze analyze verbose pg_toast.pg_toast_13431; vacuum freeze analyze verbose pg_toast.pg_toast_13446; vacuum freeze analyze verbose information_schema.sql_implementation_info; vacuum freeze analyze verbose pg_toast.pg_toast_13436; vacuum freeze analyze verbose information_schema.sql_parts; vacuum freeze analyze verbose information_schema.sql_languages; vacuum freeze analyze verbose pg_toast.pg_toast_13441; vacuum freeze analyze verbose information_schema.sql_sizing; vacuum freeze analyze verbose pg_toast.pg_toast_13451; vacuum freeze analyze verbose pg_statistic; vacuum freeze analyze verbose pg_type; vacuum freeze analyze verbose pg_toast.pg_toast_2600; vacuum freeze analyze verbose pg_toast.pg_toast_2604; vacuum freeze analyze verbose pg_toast.pg_toast_3456; vacuum freeze analyze verbose pg_toast.pg_toast_2606; vacuum freeze analyze verbose pg_toast.pg_toast_826; vacuum freeze analyze verbose pg_toast.pg_toast_2609; vacuum freeze analyze verbose pg_toast.pg_toast_3466; vacuum freeze analyze verbose pg_toast.pg_toast_3079; vacuum freeze analyze verbose pg_toast.pg_toast_2328; vacuum freeze analyze verbose pg_toast.pg_toast_1417; vacuum freeze analyze verbose pg_toast.pg_toast_3118; vacuum freeze analyze verbose pg_toast.pg_toast_3394; vacuum freeze analyze verbose pg_toast.pg_toast_2612; vacuum freeze analyze verbose pg_toast.pg_toast_2615; vacuum freeze analyze verbose pg_foreign_server; vacuum freeze analyze verbose pg_toast.pg_toast_3350; vacuum freeze analyze verbose pg_toast.pg_toast_3256; vacuum freeze analyze verbose pg_toast.pg_toast_1255; vacuum freeze analyze verbose pg_toast.pg_toast_2618; vacuum freeze analyze verbose pg_toast.pg_toast_3596; vacuum freeze analyze verbose pg_toast.pg_toast_2619; vacuum freeze analyze verbose pg_toast.pg_toast_3381; vacuum freeze analyze verbose pg_toast.pg_toast_3429; vacuum freeze analyze verbose pg_toast.pg_toast_2620; vacuum freeze analyze verbose pg_toast.pg_toast_3600; vacuum freeze analyze verbose pg_toast.pg_toast_1247; vacuum freeze analyze verbose pg_toast.pg_toast_1418; vacuum freeze analyze verbose pg_toast.pg_toast_1260; vacuum freeze analyze verbose pg_toast.pg_toast_1262; vacuum freeze analyze verbose pg_toast.pg_toast_2964; vacuum freeze analyze verbose pg_toast.pg_toast_1136; vacuum freeze analyze verbose pg_toast.pg_toast_6000; vacuum freeze analyze verbose pg_toast.pg_toast_2396; vacuum freeze analyze verbose pg_toast.pg_toast_3592; vacuum freeze analyze verbose pg_toast.pg_toast_6100; vacuum freeze analyze verbose pg_toast.pg_toast_1213; vacuum freeze analyze verbose pg_authid; vacuum freeze analyze verbose pg_statistic_ext_data; vacuum freeze analyze verbose pg_user_mapping; vacuum freeze analyze verbose pg_subscription; vacuum freeze analyze verbose pg_attribute; vacuum freeze analyze verbose pg_proc; vacuum freeze analyze verbose pg_class; vacuum freeze analyze verbose pg_attrdef; vacuum freeze analyze verbose pg_constraint; vacuum freeze analyze verbose pg_inherits; vacuum freeze analyze verbose pg_index; vacuum freeze analyze verbose pg_operator; vacuum freeze analyze verbose pg_opfamily; vacuum freeze analyze verbose pg_opclass; vacuum freeze analyze verbose pg_am; vacuum freeze analyze verbose pg_amop; vacuum freeze analyze verbose pg_amproc; vacuum freeze analyze verbose pg_language; vacuum freeze analyze verbose pg_largeobject_metadata; vacuum freeze analyze verbose pg_aggregate; vacuum freeze analyze verbose pg_largeobject; vacuum freeze analyze verbose pg_statistic_ext; vacuum freeze analyze verbose pg_rewrite; vacuum freeze analyze verbose pg_trigger; vacuum freeze analyze verbose pg_event_trigger; vacuum freeze analyze verbose pg_description; vacuum freeze analyze verbose pg_cast; vacuum freeze analyze verbose pg_enum; vacuum freeze analyze verbose pg_namespace; vacuum freeze analyze verbose pg_conversion; vacuum freeze analyze verbose pg_depend; vacuum freeze analyze verbose pg_database; vacuum freeze analyze verbose pg_db_role_setting; vacuum freeze analyze verbose pg_tablespace; vacuum freeze analyze verbose pg_pltemplate; vacuum freeze analyze verbose pg_auth_members; vacuum freeze analyze verbose pg_shdepend; vacuum freeze analyze verbose pg_shdescription; vacuum freeze analyze verbose pg_ts_config; vacuum freeze analyze verbose pg_ts_config_map; vacuum freeze analyze verbose pg_ts_dict; [local:/data/run/pg12]:5120 pg12@testdb=# ``` **参考资料** [Managing Transaction ID Exhaustion (Wraparound) in PostgreSQL](https://info.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql)