这篇文章主要讲解了“PostgreSQL中关于xid freeze的脚本有哪些”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“PostgreSQL中关于xid freeze的脚本有哪些”吧！

Monitor Database
数据库监控脚本

[local:/data/run/pg12]:5120pg12@testdb=#showautovacuum_freeze_max_age;autovacuum_freeze_max_age---------------------------200000000(1row)[local:/data/run/pg12]:5120pg12@testdb=#WITHmax_ageAS(pg12@testdb(#SELECT2000000000asmax_old_xidpg12@testdb(#,settingASautovacuum_freeze_max_agepg12@testdb(#FROMpg_catalog.pg_settingspg12@testdb(#WHEREname='autovacuum_freeze_max_age')pg12@testdb-#,per_database_statsAS(pg12@testdb(#SELECTdatnamepg12@testdb(#,m.max_old_xid::intpg12@testdb(#,m.autovacuum_freeze_max_age::intpg12@testdb(#,age(d.datfrozenxid)ASoldest_current_xidpg12@testdb(#FROMpg_catalog.pg_databasedpg12@testdb(#JOINmax_agemON(true)pg12@testdb(#WHEREd.datallowconn)pg12@testdb-#SELECTmax(oldest_current_xid)ASoldest_current_xidpg12@testdb-#,max(ROUND(100*(oldest_current_xid/max_old_xid::float)))ASpercent_towards_wraparoundpg12@testdb-#,max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float)))ASpercent_towards_emergency_autovacpg12@testdb-#FROMper_database_stats;oldest_current_xid|percent_towards_wraparound|percent_towards_emergency_autovac--------------------+----------------------------+-----------------------------------3844|0|0(1row)[local:/data/run/pg12]:5120pg12@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]:5120pg12@testdb=#SELECTdatnamepg12@testdb-#,age(datfrozenxid)pg12@testdb-#,current_setting('autovacuum_freeze_max_age')pg12@testdb-#FROMpg_databasepg12@testdb-#ORDERBY2DESC;datname|age|current_setting-----------+------+-----------------postgres|3844|200000000template1|3844|200000000template0|3844|200000000db1|3844|200000000db2|3844|200000000db3|3844|200000000testdb|3844|200000000(7rows)

Monitor relation
监控关系（数据表）

[local:/data/run/pg12]:5120pg12@testdb=#SELECTc.oid::regclasspg12@testdb-#,age(c.relfrozenxid)pg12@testdb-#,pg_size_pretty(pg_total_relation_size(c.oid))pg12@testdb-#FROMpg_classcpg12@testdb-#JOINpg_namespacenonc.relnamespace=n.oidpg12@testdb-#WHERErelkindIN('r','t','m')pg12@testdb-#ANDn.nspnameNOTIN('pg_toast')pg12@testdb-#ORDERBY2DESCLIMIT100;oid|age|pg_size_pretty--------------------------------------------+------+----------------pg_policy|3844|24kBpg_init_privs|3844|72kBpg_seclabel|3844|16kBpg_shseclabel|3844|16kBpg_collation|3844|384kBpg_partitioned_table|3844|16kBpg_range|3844|56kBpg_transform|3844|16kBpg_sequence|3844|8192bytespg_publication|3844|16kBpg_publication_rel|3844|16kBpg_subscription_rel|3844|8192bytesinformation_schema.sql_packages|3844|48kBinformation_schema.sql_features|3844|104kBinformation_schema.sql_implementation_info|3844|48kBinformation_schema.sql_parts|3844|48kBinformation_schema.sql_languages|3844|48kBinformation_schema.sql_sizing|3844|48kBpg_statistic|3844|312kBpg_type|3844|192kBpg_foreign_server|3844|24kBpg_authid|3844|48kBpg_statistic_ext_data|3844|16kB--More--

以上列出了每个relation的age以及relation的大小。

Auto Generate Script
该脚本自动创建清理脚本。

[local:/data/run/pg12]:5120pg12@testdb=#\tTuplesonlyison.[local:/data/run/pg12]:5120pg12@testdb=#\o/tmp/vacuum.sql[local:/data/run/pg12]:5120pg12@testdb=#select'vacuumfreezeanalyzeverbose'||oid::regclass||';'frompg_classwhererelkindin('r','t','m')orderbyage(relfrozenxid)desclimit100;[local:/data/run/pg12]:5120pg12@testdb=#\o[local:/data/run/pg12]:5120pg12@testdb=#\tTuplesonlyisoff.[local:/data/run/pg12]:5120pg12@testdb=#\setECHOall[local:/data/run/pg12]:5120pg12@testdb=#\!cat/tmp/vacuum.sqlvacuumfreezeanalyzeverbosepg_ts_parser;vacuumfreezeanalyzeverbosepg_collation;vacuumfreezeanalyzeverbosepg_partitioned_table;vacuumfreezeanalyzeverbosepg_range;vacuumfreezeanalyzeverbosepg_transform;vacuumfreezeanalyzeverbosepg_sequence;vacuumfreezeanalyzeverbosepg_publication;vacuumfreezeanalyzeverbosepg_publication_rel;vacuumfreezeanalyzeverbosepg_subscription_rel;vacuumfreezeanalyzeverboseinformation_schema.sql_packages;vacuumfreezeanalyzeverbosepg_toast.pg_toast_13426;vacuumfreezeanalyzeverboseinformation_schema.sql_features;vacuumfreezeanalyzeverbosepg_toast.pg_toast_13431;vacuumfreezeanalyzeverbosepg_toast.pg_toast_13446;vacuumfreezeanalyzeverboseinformation_schema.sql_implementation_info;vacuumfreezeanalyzeverbosepg_toast.pg_toast_13436;vacuumfreezeanalyzeverboseinformation_schema.sql_parts;vacuumfreezeanalyzeverboseinformation_schema.sql_languages;vacuumfreezeanalyzeverbosepg_toast.pg_toast_13441;vacuumfreezeanalyzeverboseinformation_schema.sql_sizing;vacuumfreezeanalyzeverbosepg_toast.pg_toast_13451;vacuumfreezeanalyzeverbosepg_statistic;vacuumfreezeanalyzeverbosepg_type;vacuumfreezeanalyzeverbosepg_toast.pg_toast_2600;vacuumfreezeanalyzeverbosepg_toast.pg_toast_2604;vacuumfreezeanalyzeverbosepg_toast.pg_toast_3456;vacuumfreezeanalyzeverbosepg_toast.pg_toast_2606;vacuumfreezeanalyzeverbosepg_toast.pg_toast_826;vacuumfreezeanalyzeverbosepg_toast.pg_toast_2609;vacuumfreezeanalyzeverbosepg_toast.pg_toast_3466;vacuumfreezeanalyzeverbosepg_toast.pg_toast_3079;vacuumfreezeanalyzeverbosepg_toast.pg_toast_2328;vacuumfreezeanalyzeverbosepg_toast.pg_toast_1417;vacuumfreezeanalyzeverbosepg_toast.pg_toast_3118;vacuumfreezeanalyzeverbosepg_toast.pg_toast_3394;vacuumfreezeanalyzeverbosepg_toast.pg_toast_2612;vacuumfreezeanalyzeverbosepg_toast.pg_toast_2615;vacuumfreezeanalyzeverbosepg_foreign_server;vacuumfreezeanalyzeverbosepg_toast.pg_toast_3350;vacuumfreezeanalyzeverbosepg_toast.pg_toast_3256;vacuumfreezeanalyzeverbosepg_toast.pg_toast_1255;vacuumfreezeanalyzeverbosepg_toast.pg_toast_2618;vacuumfreezeanalyzeverbosepg_toast.pg_toast_3596;vacuumfreezeanalyzeverbosepg_toast.pg_toast_2619;vacuumfreezeanalyzeverbosepg_toast.pg_toast_3381;vacuumfreezeanalyzeverbosepg_toast.pg_toast_3429;vacuumfreezeanalyzeverbosepg_toast.pg_toast_2620;vacuumfreezeanalyzeverbosepg_toast.pg_toast_3600;vacuumfreezeanalyzeverbosepg_toast.pg_toast_1247;vacuumfreezeanalyzeverbosepg_toast.pg_toast_1418;vacuumfreezeanalyzeverbosepg_toast.pg_toast_1260;vacuumfreezeanalyzeverbosepg_toast.pg_toast_1262;vacuumfreezeanalyzeverbosepg_toast.pg_toast_2964;vacuumfreezeanalyzeverbosepg_toast.pg_toast_1136;vacuumfreezeanalyzeverbosepg_toast.pg_toast_6000;vacuumfreezeanalyzeverbosepg_toast.pg_toast_2396;vacuumfreezeanalyzeverbosepg_toast.pg_toast_3592;vacuumfreezeanalyzeverbosepg_toast.pg_toast_6100;vacuumfreezeanalyzeverbosepg_toast.pg_toast_1213;vacuumfreezeanalyzeverbosepg_authid;vacuumfreezeanalyzeverbosepg_statistic_ext_data;vacuumfreezeanalyzeverbosepg_user_mapping;vacuumfreezeanalyzeverbosepg_subscription;vacuumfreezeanalyzeverbosepg_attribute;vacuumfreezeanalyzeverbosepg_proc;vacuumfreezeanalyzeverbosepg_class;vacuumfreezeanalyzeverbosepg_attrdef;vacuumfreezeanalyzeverbosepg_constraint;vacuumfreezeanalyzeverbosepg_inherits;vacuumfreezeanalyzeverbosepg_index;vacuumfreezeanalyzeverbosepg_operator;vacuumfreezeanalyzeverbosepg_opfamily;vacuumfreezeanalyzeverbosepg_opclass;vacuumfreezeanalyzeverbosepg_am;vacuumfreezeanalyzeverbosepg_amop;vacuumfreezeanalyzeverbosepg_amproc;vacuumfreezeanalyzeverbosepg_language;vacuumfreezeanalyzeverbosepg_largeobject_metadata;vacuumfreezeanalyzeverbosepg_aggregate;vacuumfreezeanalyzeverbosepg_largeobject;vacuumfreezeanalyzeverbosepg_statistic_ext;vacuumfreezeanalyzeverbosepg_rewrite;vacuumfreezeanalyzeverbosepg_trigger;vacuumfreezeanalyzeverbosepg_event_trigger;vacuumfreezeanalyzeverbosepg_description;vacuumfreezeanalyzeverbosepg_cast;vacuumfreezeanalyzeverbosepg_enum;vacuumfreezeanalyzeverbosepg_namespace;vacuumfreezeanalyzeverbosepg_conversion;vacuumfreezeanalyzeverbosepg_depend;vacuumfreezeanalyzeverbosepg_database;vacuumfreezeanalyzeverbosepg_db_role_setting;vacuumfreezeanalyzeverbosepg_tablespace;vacuumfreezeanalyzeverbosepg_pltemplate;vacuumfreezeanalyzeverbosepg_auth_members;vacuumfreezeanalyzeverbosepg_shdepend;vacuumfreezeanalyzeverbosepg_shdescription;vacuumfreezeanalyzeverbosepg_ts_config;vacuumfreezeanalyzeverbosepg_ts_config_map;vacuumfreezeanalyzeverbosepg_ts_dict;[local:/data/run/pg12]:5120pg12@testdb=#

感谢各位的阅读，以上就是“PostgreSQL中关于xid freeze的脚本有哪些”的内容了，经过本文的学习后，相信大家对PostgreSQL中关于xid freeze的脚本有哪些这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！