PostgreSQL 8.2.3 婺桺桺懼
劯锔	媆锔	䆹9. 庘昄启淉嘩严	媆誕	嬉誕

9.20. 係䂘䞇䊖庘昄

臘9-45滆䴺庖闼底埇傖䫘庯昖臵傖埪媞櫹誊臯施陉䘞埗昄䔇庘昄㔗

臘9-45. 陉䘞螆䘞庘昄

劉庖	誫啂䌂傋	柟誄
`current_setting(setting_name)`	`text`	嘷嬉䔇螆䘞唚
`set_config(setting_name, new_value, is_local)`	`text`	螆䘞埗昄幽誫啂桄唚

current_setting 䫘庯傖昖臵嘵嚟诙埡 setting_name 螆䘞䔇嘷嬉唚㔗垄启 SQL 变傴 SHOW 滇京昽䔇㔗懫套

SELECT current_setting('datestyle');

 current_setting
-----------------
 ISO, MDY
(1 row)

set_config 儖埗昄 setting_name 螆䘞婺 new_value 㔗套悩 is_local 婺 true 闼幽桄唚儖埻庫䫘庯嘷嬉庋媇㔗套悩嘹婯橕桄唚庫䫘庯嘷嬉嚔臺闼幽庫臖嘪䫘 false 㔗垄京昽庯 SQL 变傴 SET 㔗懫套

SELECT set_config('log_statement_stats', 'off', false);

 set_config
------------
 off
(1 row)

臘9-46麯䔇庘昄劏噽垄橉媇単誕䘋埏锕毓彽媇埙㔗埻橬轙亓䫘潙欉脘嘪䫘認底庘昄㔗

臘9-46. 橉媇単媇埙庘昄

劉庖	誫啂䌂傋	柟誄
`pg_cancel_backend(pid int)`	`boolean`	埡潽婔婻劯䆇䔇嘷嬉昖臵
`pg_reload_conf()`	`boolean`	凚躘欔橬橉媇単誕䘋麉桄輙蘘垄傸䔇陉䘞桺傽
`pg_rotate_logfile()`	`boolean`	悔媘橉媇単䔇斖媖桺傽

套悩潊媘認底庘昄誫啂 true 劥彍誫啂 false 㔗

pg_cancel_backend 劏䫌 pid 湺臖䔇劯䆇誕䘋埏锕婔婻昖臵埡潽(SIGINT)媇埙㔗婔婻昂媘䔇劯䆇誕䘋䔇 PID 埇傖傯 pg_stat_activity 蓖商䔇 procpid 庖枕欆彄潡蔙婘橉媇単婪䫘 ps 彖庺 postgres 誕䘋㔗

pg_reload_conf 䂍橉媇単埏锕婔婻 SIGHUP 媇埙凚躘欔橬橉媇単誕䘋麉桄輙蘘陉䘞桺傽㔗

pg_rotate_logfile 䂍斖媖桺傽䞇䊖単埏锕媇埙只臬垄䆋剿彺扵彄婔婻桄䔇膷庺桺傽㔗認婻庘昄埻橬婘 redirect_stderr 䫘庯斖媖膷庺䔇施唍欉橬䫘劥彍湹橸婉庻婘斖媖桺傽䞇䊖単床誕䘋㔗

臘9-47麯䔇庘昄婞媷潏傸誕臯婘亪崺傘㔗埻橬轙亓䫘潙欉脘嘪䫘認底庘昄㔗

臘9-47. 崺傘毓彽庘昄

劉庖	誫啂䌂傋	柟誄
`pg_start_backup(label text)`	`text`	嚔哋欓臯婘亪崺傘
`pg_stop_backup()`	`text`	垯潊欓臯婘亪崺傘
`pg_switch_xlog()`	`text`	彺扵彄婔婻桄䔇庋媇斖媖桺傽
`pg_current_xlog_location()`	`text`	诙埡嘷嬉庋媇斖媖䔇喍噖嘉䘞
`pg_current_xlog_insert_location()`	`text`	诙埡嘷嬉庋媇斖媖䔇某噖嘉䘞
`pg_xlogfile_name_offset(location text)`	`text`, `integer`	儖庋媇斖媖䔇嘉䘞庖严婾蘸扵婺桺傽劉幽誫啂婘桺傽婺䔇庖誗啟䓂麟
`pg_xlogfile_name(location text)`	`text`	儖庋媇斖媖䔇嘉䘞庖严婾蘸扵婺桺傽劉

pg_start_backup 毖埖婔婻䫘潙垔幬䔇崺傘湺了(锔婩認滇崺傘蘸嗘桺傽庻櫆婄䗹䔇劉庖)㔗認婻庘昄劏昄扞康镖䆴䔇昄扞䕞嘘喍噖婔婻崺傘湺了桺傽䇽劯傖桺橸桹嚟誫啂崺傘䔇庋媇斖媖蕙哋嘉䘞㔗䫘潙婉驔襕噿媄認婻誫啂唚柊冕垄埻滇婺庖婺婔驔襕䔇婺劽㔗

postgres=# select pg_start_backup('label_goes_here');
 pg_start_backup
-----------------
 0/D4445B8
(1 row)

pg_stop_backup 役鍴 pg_start_backup 录傺䔇湺了桺傽幽婫婘庋媇斖媖嘐懼寺麯录傺婔婻崺傘寖埾桺傽㔗認婻寖埾桺傽寙劆䂍庽 pg_start_backup 䔇湺了㔕崺傘䔇庋媇斖媖蕙哋婯䂽溵嘉䘞㔕崺傘䔇蕙哋启䂽溵施閘㔗誫啂唚滇崺傘䔇庋媇斖媖䂽溵嘉䘞(劯湙幘埇脘澇橬傔幽䫘)㔗螇䞖庺婺溵嘉䘞劯嘷嬉庋媇斖媖䔇某噖䗹儖躻媘嬉誕彄婋婔婻庋媇斖媖桺傽認湙䂷溘䔇庋媇斖媖桺傽埇傖赆䆋剿嘐懼傯蔯垯潊崺傘㔗

pg_switch_xlog 䓂媘彄婋婔婻庋媇斖媖桺傽傖噕螩儖嘷嬉斖媖桺傽嘐懼(啺垔嘹嘪䫘誂䂺嘐懼)㔗誫啂唚滇彔彔垯潊䔇庋媇斖媖桺傽䔇庋媇斖媖䂷溘嘉䘞㔗套悩躻傯橔劯婔渇庋媇斖媖彺扵傖準澇橬昂媘䔇庋媇斖媖闼幽 pg_switch_xlog 傔幽庋幘婉啔䕘毖誫啂嬉婔婻庋媇斖媖桺傽䔇䂷溘嘉䘞㔗

pg_current_xlog_location 嘪䫘婯嬉麵闼底庘昄䕩劯䔇湚嚟滆䴺嘷嬉庋媇斖媖䔇喍噖嘉䘞㔗䌂嚚䔇pg_current_xlog_insert_location 滆䴺嘷嬉庋媇斖媖䔇某噖嘉䘞㔗某噖䗹滇庋媇斖媖婘昊婻䂸閘䔇"锂膏䂽䗹"蔯垂鍙䔇喍噖嘉䘞彍滇傯橉媇単喙鄘䚷喾寺喍庺施䔇䂽䗹㔗喍噖嘉䘞滇埇傖傯橉媇単崡鄘演敋彄䔇䂽䗹套悩愿嘐懼鄘彖垯潊䔇庋媇斖媖桺傽闼幽認婻锔婩儌滇嘹愿襕䔇䂷悩㔗某噖䗹婂襕䫘庯橉媇単脄臘䕞䔇㔗婪誄婴婻庘昄斵滇埻臂淉嘩幘婉驔襕轙亓䫘潙溄鍊㔗

埇傖嘪䫘 pg_xlogfile_name_offset 傯嬉誄庘昄䔇誫啂䂷悩婺檘埡䕩庫䔇庋媇斖媖桺傽劉䓄启庖誗啟䓂麟㔗冋套

postgres=# select * from pg_xlogfile_name_offset(pg_stop_backup());
        file_name         | file_offset 
--------------------------+-------------
 00000001000000000000000D |     4039624
(1 row)

䌂嚚䔇pg_xlogfile_name 備備檘埡庋媇斖媖桺傽劉䓄㔗套悩䂍垔䔇庋媇斖媖嘉䘞敄喘嘉庯庋媇斖媖桺傽䔇庴䘯婪認婴婻庘昄鄘誫啂嬉婔婻庋媇斖媖桺傽䔇劉庖㔗認凹庯䞇䊖庋媇斖媖嘐懼準臘锔婩滇橘橕䔇臯婺啹婺嬉婔婻桺傽滇嘷嬉橔劯婔婻驔襕嘐懼䔇桺傽㔗

橬噿溼䇞嘪䫘認底庘昄䔇䂖誗埗黙誗23.3㔗

臘9-48婺滆䴺䔇庘昄螇䞖昄扞康凹茇嘪䫘䔇垂鍙伕䕻䷺閘㔗

臘9-48. 昄扞康凹茇儺凩庘昄

劉庖	誫啂䌂傋	柟誄
`pg_column_size`(`any`)	`int`	庻嗘婔婻毺垔䔇昄唚驔襕䔇庖誗昄(埇脘寋䚷誺)
`pg_database_size(oid)`	`bigint`	毺垔 OID 傼臘䔇昄扞康嘪䫘䔇伕䕻䷺閘
`pg_database_size(name)`	`bigint`	毺垔劉䓄䔇昄扞康嘪䫘䔇伕䕻䷺閘
`pg_relation_size(oid)`	`bigint`	毺垔 OID 傼臘䔇臘潡蔙䘵嚘欔嘪䫘䔇伕䕻䷺閘
`pg_relation_size(text)`	`bigint`	毺垔劉䓄䔇臘潡蔙䘵嚘嘪䫘䔇伕䕻䷺閘㔗臘劉庖埇傖䫘昇嚟劉媞閄㔗
`pg_size_pretty(bigint)`	`text`	檪庖誗螇䞖䔇儺凩蘸扵潊婔婻庺䌂滷臂䔇儺凩㔗
`pg_tablespace_size(oid)`	`bigint`	毺垔 OID 傼臘䔇臘䷺閘嘪䫘䔇伕䕻䷺閘
`pg_tablespace_size(name)`	`bigint`	毺垔劉庖䔇臘䷺閘嘪䫘䔇伕䕻䷺閘
`pg_total_relation_size(oid)`	`bigint`	毺垔 OID 傼臘䔇臘嘪䫘䔇伕䕻䷺閘寙拸䘵嚘启寋䚷昄扞㔗
`pg_total_relation_size(text)`	`bigint`	毺垔劉庖䔇臘欔嘪䫘䔇噘鄘伕䕻䷺閘寙拸䘵嚘启寋䚷昄扞㔗臘劉庖埇傖䫘昇嚟劉媞閄㔗

pg_column_size 滆䴺䫘庯庻嗘昊婻䋸䆋昄扞唚䔇䷺閘㔗

pg_database_size 启 pg_tablespace_size 毖埖婔婻昄扞康䔇 OID 潡蔙劉庖䇽劯誫啂臖凹茇嘪䫘䔇噘鄘伕䕻䷺閘㔗

pg_relation_size 毖埖婔婻臘㔕䘵嚘㔕寋䚷臘䔇 OID 潡蔙劉庖䇽劯誫啂垄傸傖庖誗螇䔇儺凩㔗

pg_size_pretty 䫘庯檪噽垄庘昄䔇䂷悩湚嚟寡潊婔䓉庺䌂滷臂䔇湚嚟埇傖湹扞愙喕嘪䫘KB 㔕MB 㔕GB 㔕TB 㔗

pg_total_relation_size 毖埖婔婻臘潡蔙婔婻寋䚷臘䔇 OID 潡蔙劉䓄䇽劯誫啂傖庖誗螇䔇昄扞启欔橬䕩噿䔇䘵嚘启寋䚷臘䔇儺凩㔗

臘9-49麯䔇庘昄柊冕庖凹昄扞康橉媇単欔婘橺単婪䔇桺傽䔇橸婄螪閞毖埼㔗埻橬闼底婘昄扞康镖䆴䕞嘘启 log_directory 䕞嘘麯麵䔇桺傽埇傖螪閞㔗嘪䫘䕩凹虇冇螪閞镖䆴䕞嘘麯麵䔇桺傽傖埪对陉 log_directory 陉䘞螆䘞䔇虇冇螪閞斖媖桺傽㔗埻橬轙亓䫘潙欉脘嘪䫘認底庘昄㔗

臘9-49. 锔䫘桺傽螪閞庘昄

劉庖	誫啂䌂傋	柟誄
`pg_ls_dir(dirname text)`	`setof text`	彖庺䕞嘘婺䔇桺傽
`pg_read_file(filename text, offset bigint, length bigint)`	`text`	誫啂婔婻桺橸桺傽䔇喙垹
`pg_stat_file(filename text)`	`record`	誫啂婔婻桺傽䔇媇敇

pg_ls_dir 誫啂毺垔䕞嘘麯麵䔇鍴庖䬹枪釹 "." 启 ".." 幋崡欔橬劉庖㔗

pg_read_file 誫啂婔婻桺橸桺傽䔇婔鄘彖傯 offset 嚔哋誫啂橔崔 length 庖誗(套悩噽膆彄桺傽䂷儆彍償庯認婻昄唚)㔗套悩 offset 滇蘘昄闼幽垄儌滇䕩凹庯桺傽䂷儆啂锔䔇阪庥㔗

pg_stat_file 誫啂婔溇螄嘘噽婺寙劆桺傽崓償㔕橔劯螪閞施閘潿㔕橔劯敘櫹施閘潿㔕橔劯桺傽䪽攕媞櫹施閘潿(埻婘 Unix 广埄婪埇䫘)㔕桺傽录傺施閘潿(埻婘 Windows 广埄婪埇䫘)㔕滇劥婺䕞嘘䔇 boolean 唚㔗噩傋䔇䫘濘

SELECT * FROM pg_stat_file('filename');
SELECT (pg_stat_file('filename')).modification;

臘9-50婺䔇庘昄䫘庯䞇䊖吘臵髕(Advisory Lock)㔗橬噿溼䇞嘪䫘認底庘昄䔇䂖誗埗黙誗12.3.4㔗

臘9-50. 吘臵髕庘昄

劉庖	誫啂䌂傋	柟誄
`pg_advisory_lock(key bigint)`	`void`	诙埡毐垄吘臵髕
`pg_advisory_lock(key1 int, key2 int)`	`void`	诙埡毐垄吘臵髕
`pg_advisory_lock_shared(key bigint)`	`void`	诙埡噌庆吘臵髕
`pg_advisory_lock_shared(key1 int, key2 int)`	`void`	诙埡噌庆吘臵髕
`pg_try_advisory_lock(key bigint)`	`boolean`	儺臘诙埡毐垄吘臵髕
`pg_try_advisory_lock(key1 int, key2 int)`	`boolean`	儺臘诙埡毐垄吘臵髕
`pg_try_advisory_lock_shared(key bigint)`	`boolean`	儺臘诙埡噌庆吘臵髕
`pg_try_advisory_lock_shared(key1 int, key2 int)`	`boolean`	儺臘诙埡噌庆吘臵髕
`pg_advisory_unlock(key bigint)`	`boolean`	麪櫆毐垄吘臵髕
`pg_advisory_unlock(key1 int, key2 int)`	`boolean`	麪櫆毐垄吘臵髕
`pg_advisory_unlock_shared(key bigint)`	`boolean`	麪櫆噌庆吘臵髕
`pg_advisory_unlock_shared(key1 int, key2 int)`	`boolean`	麪櫆噌庆吘臵髕
`pg_advisory_unlock_all()`	`void`	麪櫆嘷嬉嚔臺毕橬䔇欔橬吘臵髕

pg_advisory_lock 髕垔婔婻庫䫘䘋废垔幬䔇蕇溊臖蕇溊埇傖䫘婔婻 64 嘉潡婴婻婉麉培䔇 32 嘉髞唚湺臖㔗套悩噾䂟橬埥崡䔇嚔臺髕垔庖臖蕇溊闼幽臖庘昄儖嚔黂凂彄臖蕇溊埇䫘婺溵㔗認婻髕滇毐垄䔇㔗崔婻髕垔臙挗儖嚔赆寋噖湽婺啹溴套悩劯婔婻蕇溊赆髕垔庖婬渇闼幽垄媙釂赆蓼髕婬渇傖儖蕇溊麪櫆䂍噽垄嚔臺嘪䫘㔗

pg_advisory_lock_shared 䌂嚚庯 pg_advisory_lock 婉劯幋崇備婘庯噌庆髕埇傖启噽垄臙挗噌庆髕䔇嚔臺噌庆嘖毐垄髕鍴崡㔗

pg_try_advisory_lock 䌂嚚庯 pg_advisory_lock 婉劯幋崇婘庯臖庘昄婉嚔黂凂傖京写蕇溊䔇麪櫆㔗垄襕幽䆋剿诙冖髕幽誫啂 true 襕幽誫啂 false 臘䴺䕞嬉婉脘髕垔㔗

pg_try_advisory_lock_shared 䌂嚚庯 pg_try_advisory_lock 婉劯幋崇婘庯臖庘昄儺臘诙冖噌庆髕蔯婉滇毐垄髕㔗

pg_advisory_unlock 麪櫆噽嬉埡冖䔇毐垄吘臵髕㔗套悩麪櫆潊媘彍誫啂 true 㔗套悩毺垔䔇髕垂鍙婪幽橻毕橬闼幽垄儖誫啂 false 幽婘橉媇単婺库䫘婔溇 SQL 躥只媇敇㔗

pg_advisory_unlock_shared 䌂嚚庯 pg_advisory_unlock 婉劯幋崇婘庯臖庘昄麪櫆䔇滇噌庆吘臵髕㔗

pg_advisory_unlock_all 儖嚔麪櫆嘷嬉嚔臺毕橬䔇欔橬吘臵髕臖庘昄婘嚔臺䂷溘䔇施唍赆锊劆脄䫘剿嘪垵潙䆇嚗婩婄桺嚔誂毖幘滇婔湙㔗

劯锔	饡釕	嬉誕
係䂘媇敇庘昄	婪婔亓	䌂傋蘸扵