Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the scripts about xid freeze in PostgreSQL". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the scripts about xid freeze in PostgreSQL"?

Monitor Database

Database monitoring script

[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_xidpg12@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 datnamepg12@testdb (#, m.max_old_xid::intpg12@testdb (#, m.autovacuum_freeze_max_age::intpg12@testdb) Age (d.datfrozenxid) AS oldest_current_xidpg12@testdb (# FROM pg_catalog.pg_database dpg12@testdb (# JOIN max_age m ON (true) pg12@testdb (# WHERE d.datallowconn) pg12@testdb-# SELECT max (oldest_current_xid) AS oldest_current_xidpg12@testdb-#, max (ROUND (100* (oldest_current_xid/max_old_xid::float) AS percent_towards_wraparoundpg12@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= database age / 2 billion, if close to 100%, then need to pay special attention

The age / autovacuum_freeze_max_age,autovacuum_freeze_max_age parameter of percent_towards_emergency_autovac= database is generally 200 million.

The following script lists the age and autovacuum_freeze_max_age parameter settings for each database.

[local:/data/run/pg12]: 5120 pg12@testdb=# SELECT datnamepg12@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 | 200,000,000 db1 | 3,844 | 200,000,000 db2 | 3,844 | 200,000,000 db3 | 3844 | 200,000,000 testdb | 3844 | 200,000,000 (7 rows)

Monitor relation

Monitoring relationship (data table)

[local:/data/run/pg12]: 5120 pg12@testdb=# SELECT c.oid::regclasspg12@testdb-#, age (c.relfrozenxid) pg12@testdb-#, pg_size_pretty (pg_total_relation_size (c.oid)) pg12@testdb-# FROM pg_class cpg12@testdb-# JOIN pg_namespace n on c.relnamespace = n.oidpg12@testdb-# WHERE relkind IN ('ritual,' 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 | 3844 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-- |

The size of the age and relation for each relation is listed above.

Auto Generate Script

This script automatically creates a cleanup script.

[local:/data/run/pg12]: 5120 pg12@testdb=#\ tTuples 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 (' ringing, 'tasking,' m') order by age (relfrozenxid) desc limit [local:/data/run/pg12]: 5120 pg12@testdb=#\ o [local:/data/run/pg12]: 5120 pg12@testdb=#\ tTuples 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=# Thank you for your reading. The above is the content of "what are the scripts on xid freeze in PostgreSQL". After the study of this article, I believe you have a deeper understanding of what the scripts on xid freeze in PostgreSQL have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.