PostgreSQL中關(guān)于xidfreeze的腳本有哪些-創(chuàng)新互聯(lián)

這篇文章主要講解了“PostgreSQL中關(guān)于xid freeze的腳本有哪些”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“PostgreSQL中關(guān)于xid freeze的腳本有哪些”吧！

在沙河等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供成都網(wǎng)站建設(shè)、成都網(wǎng)站制作網(wǎng)站設(shè)計制作按需求定制設(shè)計,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),品牌網(wǎng)站制作,全網(wǎng)營銷推廣,成都外貿(mào)網(wǎng)站建設(shè)公司,沙河網(wǎng)站建設(shè)費用合理。

Monitor Database
數(shù)據(jù)庫監(jiān)控腳本

[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=數(shù)據(jù)庫年齡/20億，如接近100%，那么需要特別注意；
percent_towards_emergency_autovac=數(shù)據(jù)庫年齡/autovacuum_freeze_max_age，autovacuum_freeze_max_age參數(shù)一般為2億。

下面的腳本列出了每個數(shù)據(jù)庫的年齡和autovacuum_freeze_max_age參數(shù)的設(shè)定。

[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
監(jiān)控關(guān)系（數(shù)據(jù)表）

[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
該腳本自動創(chuàng)建清理腳本。

[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=#

感謝各位的閱讀，以上就是“PostgreSQL中關(guān)于xid freeze的腳本有哪些”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對PostgreSQL中關(guān)于xid freeze的腳本有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！

本文標(biāo)題：PostgreSQL中關(guān)于xidfreeze的腳本有哪些-創(chuàng)新互聯(lián)
URL分享：http://www.muchs.cn/article28/dhcscp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供小程序開發(fā)、手機(jī)網(wǎng)站建設(shè)、網(wǎng)站收錄、商城網(wǎng)站、企業(yè)建站、網(wǎng)站內(nèi)鏈

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容