23.3. 婘亪崺傘傖埪剿施敵崉(PITR)

23.3.1. 螆䘞 WAL 嘐懼

檘償準䩋婔婻誊臯五䔇 PostgreSQL 係䂘䫘潊婔婻方鍊阪䔇 WAL 斖媖废彖㔗係䂘䬷䊖婪檪認婻废彖彖锫潊 WAL 枕桺傽锔婩懟枕 16M(婘䚡臏 PostgreSQL 䔇施唍埇傖櫹埻噽崓償)㔗認底枕桺傽䔇劉庖滇昄唚变劉䔇認底昄唚埉滹傡傸婘檘埡庺準䔇 WAL 废彖婺䔇嘉䘞㔗婘婉锗䫘 WAL 嘐懼䔇施唍係䂘锔婩埻滇录傺庹婻枕桺傽䇽劯"冻䯇"嘪䫘垄傸桹濘滇檪婉喉嘪䫘䔇枕桺傽䔇劉庖麉变劉婺敘醻䔇枕䚡埙㔗係䂘啺螆闼底喙垹懫嬉婔渇演昖䗹敘蔕䔇枕桺傽噾䂟澇䫘庖䇽劯儌埇傖冻䯇彷䫘㔗

婘嘐懼 WAL 昄扞䔇施唍潏傸婯橕婘懟婻枕桺傽准噙悇幋劯托诙幋幽婫檪認底昄扞婘枕桺傽赆冻䯇彷䫘幋嬉媺庻婘昊崇㔗湹扞庫䫘傖埪埇䫘䔇䇸傽䔇婉劯潏傸埇傖橬螩崔婉劯䔇桹濘"檪昄扞媺庻婘昊崇"潏傸埇傖檪枕桺傽拙蘺彄婔婻 NFS 毗蘘䔇䕞嘘檪垄傸櫆彄埥崡婔埄橺単婪潡蔙檪垄傸喍噖伕婥橺麯(驔襕媺臕嘹橬媂濘檪桺傽敵崉婺寘劉)潡蔙檪垄傸欷潊寙䄓嘘彄 CD 麯潡蔙滇噽垄䔇傔幽桹濘㔗婺庖䂍昄扞康䞇䊖叻柊冕橔崓埇脘攓䔇䕕昂攓PostgreSQL 臘商婉凹套嘘嘐懼啔傂嘘啺螆㔗埡蔯傼幋䔇滇PostgreSQL 螷䞇䊖叻弄滯婔婻 shell 变傴欓臯準拙蘺婔婻垯昘䔇枕桺傽彄垄驔襕寂䔇婄桹㔗臖变傴埇傖䞔剘冖儌滇婔婻 cp 潡蔙垄埇傖脄䫘婔婻崉溗䔇 shell 臔橸認底鄘䫌䞇䊖叻喿垔㔗

欔嘪䫘䔇 shell 变傴䫌陉䘞埗昄 archive_command 弄滯垄垂鍙婪攂滇櫆婘 postgresql.conf 桺傽麯䔇㔗婘認婻庖严婾麯傂嘘 %p 鄘赆襕嘐懼桺傽䔇䂺凹虇冇傼敪蔯傂嘘 %f 埻滇赆桺傽劉傼敪㔗套悩嘹驔襕婘变傴麯啯噖婔婻䩘溼䔇 % 庖严闼幽媙釂埯喍(%%)㔗橔䞔剘䔇橬䫘变傴䌂嚚婋麵認湙

archive_command = 'cp -i %p /mnt/server/archivedir/%f </dev/null'

垄儖檪 WAL 枕拙蘺彄 /mnt/server/archivedir 䕞嘘㔗認婻埻滇婔婻冋床幽麂潏傸傺螞䔇桹濘埇脘婉脘婘欔橬係䂘婪鄘溼䇞誊臯㔗

嘐懼变傴儖婘誊臯 PostgreSQL 橉媇単䔇劯婔婻䫘潙䔇溄鍊婋欓臯㔗啹溴赆嘐懼䔇 WAL 桺傽垂鍙婪寙劆嘹䔇昄扞康麯䔇欔橬婩薪欔傖嘹庫臖䇞媺躻噌䔇嘐懼昄扞婉嚔赆彆庺他毵懫套嘐懼彄婔婻澇橬䂇潡蔙噘匔臂溄鍊䔇䕞嘘麯㔗

橬婔䗹冽麉襕嘷婫備嘷嘐懼变傴潊媘施垄欉誫啂镽㔗婘冖彄婔婻镽唚䂷悩幋劯PostgreSQL 儖啺螆臖 WAL 枕桺傽噾䂟潊媘嘐懼啹溴垄䘉劯儖赆役鍴潡蔙赆桄䔇昄扞襖䕡㔗嘖滇婔婻麂镽唚只臬 PostgreSQL 臖桺傽澇橬赆嘐懼啹溴垄嚔变橘攓䔇麉臘䕘彄潊媘㔗

嘐懼变傴锔婩庫臖螆螇潊拐䂺襖䕡噾䂟庻婘䔇嘐懼桺傽㔗認滇婔婻麂婩麉襕䔇垬噘䬹攓埇傖婘䞇䊖叻淉嘩崌臇(懫套檪婴婻婉劯䔇橉媇単䔇膷庺埏锕彄劯婔婻嘐懼䕞嘘)䔇施唍媺毕嘹䔇嘐懼䔇垯昘攓㔗潏傸傺螞嘹饡噽襕敋臘嘹庖崺嘪䫘彄嘐懼变傴傖媺臕垄垂鍙婪婉嚔襖䕡䯄橬䔇桺傽幽婫婘認䓉愙喕婋垄誫啂麂镽䪽攕㔗潏傸埏䯄婘認桹麵cp -i 婘昊底广埄婪滇溼䇞䔇蔯婘噽垄广埄婪滇婉溼䇞䔇㔗套悩锬垔䔇变傴橸躆幽婉脘溼䇞崇䊖認婻閞鵻嘹庫臖嵂媹婔婻变傴鵇噽毵敋嘐懼桺傽滇劥庻婘㔗懫套䌂嚚婋麵䔇婩薪㔗

archive_command = 'test ! -f .../%f && cp %p .../%f'

婘庹幯欔橬䔇 Unix 埻䓉婪鄘噖嘩溼䇞㔗

婘螆螇嘹䔇嘐懼䯇嵄鄘施唍臙蔄荏婔婋套悩嘐懼变傴婉啩崌蘖嚔埏䫘傔幽愙喕啹婺橬底桹麵襕挗淉嘩蔙䔇幾潬潡蔙滇嘐懼䷺閘婉崘庖㔗懫套套悩嘹冔伕婥橺婪喍嘖滇澇橬躻媘扵婥橺闼幽儌橬埇脘埏䫘認䓉愙喕套悩伕婥悇庖闼儌鍴麂扵伕婥劥彍嘖庋幘啔婉庖㔗嘹庫臖䇞媺庺启髍臇溇傽潡蔙庺启襕挗淉嘩叻幾潬婥髍臇鄘嚔溼䇞檖只認湙欉脘誙锘蓼喿認底閞鵻㔗劥彍 pg_xlog/ 䕞嘘嚔婉啩婄准噙 WAL 枕桺傽䕘彄閞鵻蓼喿㔗

嘐懼变傴䔇锘庥幽婉襕䘓埻襕垄脘虘婪嘹䔇橉媇単䫘潊 WAL 昄扞䔇广庺锘庥剿埇㔗剿嘪嘐懼誕䘋芘婘庖劯麵婔䗹溼婩䔇淉嘩幘嚔䂓䂺誕臯㔗套悩嘐懼誕䘋晵冽崔儌嚔嵂媹䕆锆埏䫘施䔇昄扞婵崌麟㔗劯施幘懟叿五 pg_xlog/ 䕞嘘寙劆崓麟橻嘐懼䔇斖媖枕桺傽幽婫埇脘橔劯轙庺庖伕䕻䷺閘㔗潏傸傺螞嘹䕏毓嘐懼誕䘋䇞媺垄滇毬䙓嘹䔇懟臖誊蘸䔇㔗

婘喍躻噌䔇嘐懼变傴䔇施唍嘹庫臖啺螆赆嘐懼䔇桺傽橔崔 64 婻庖严阪幽婫埇傖寙劆ASCII庖應㔕昄庖㔕䗹䔇傂懟䂇劽㔗潏傸婉媙襕螄嘟寘哋䔇噘虇冇(%p)嘖滇橬媙襕螄嘟桺傽劉(%f)㔗

臙濘懟儘䞇 WAL 嘐懼噕螩嘹敵崉傂嘘凹 PostgreSQL 昄扞康䔇媞櫹婘橔彺䔇嘺䇔崺傘幋劯垄誻滇婉嚔敵崉凹陉䘞桺傽䔇媞櫹(postgresql.conf, pg_hba.conf, pg_ident.conf)啹婺認底桺傽鄘滇欋噖䚡膏䔇蔯婉滇锔誺 SQL 淉嘩準䚡膏䔇㔗欔傖嘹埇脘嚔驔襕檪嘹䔇陉䘞桺傽櫆婘婔婻斖婩桺傽係䂘崺傘誺䘋剿埇崇䊖彄䔇婄桹㔗埗黙誗17.2诙埡套嘘麉垔嘉陉䘞桺傽䔇䘖臖㔗

啹婺嘐懼变傴備婘噾䂟垯潊䔇 WAL 枕婪脄䫘啹溴套悩嘹䔇橉媇単埻库䫘冽償䔇 WAL 敕麟潡枕幋閘䔇閘锫冽阪闼幽婘庋媇垯潊幋劯婯噽赆垬噘嘐懼幋閘儌嚔庻婘冽阪䔇傽施㔗婺庖鍊彽橻嘐懼昄扞䔇橔阪橘鍊埇傖螆䘞 archive_timeout 嚺彽橉媇単婘彺扵 WAL 枕幋閘䔇施閘閘锫㔗驔襕濘懟䔇滇䫌庯嚺彽彺扵蔯柊斷䂷溘䔇噾嘐懼桺傽䔇崓償婯垯昘䔇嘐懼桺傽䕩劯㔗啹溴儖 archive_timeout 螆婺婔婻冽償䔇唚滇婉滯捺䔇垄儖冽媆蔖儘嘐懼䷺閘㔗儖 archive_timeout 螆䘞婺 60 䓐噥埿锔婩滇懫膄劽䊖䔇㔗

劯湙套悩嘹愿䇞媺彔彔垯潊䔇庋媇赆䆋剿嘐懼闼幽幘埇傖锔誺 pg_switch_xlog 欋媘嚺彽彺扵枕桺傽㔗噽垄婯 WAL 䞇䊖䕩噿䔇噖噙庘昄婘臘9-47婺彖庺㔗

23.3.2. 誕臯婔渇嘺䇔崺傘

誕臯嘺䇔崺傘䔇誺䘋䕩嘷䞔剘

1. 䇞媺 WAL 嘐懼欷嚔幽婫埇傖誊蘸㔗

2. 傖昄扞康轙亓䫘潙躆傘誂毖彄昄扞康埏庺变傴

   SELECT pg_start_backup('label');

   認麯䔇 label 滇傂懟嘹愿嘪䫘䔇認渇崺傘淉嘩䔇嫇婔湺臖(婔婻喘幹愇滇嘪䫘崺傘蘸嗘桺傽䔇櫆䘞婄噘虇冇)㔗pg_start_backup 䫘崺傘媇敇婘镖䆴䕞嘘麯录傺婔婻崺傘湺了桺傽 backup_label 㔗

   躿庯嘹誂毖彄镖䆴婺䔇闼婻昄扞康澇傔幽噿係㔗嘹埇傖媘䘖庘昄誫啂䔇䂷悩嘖滇套悩垄檖只髍臇闼幽婘䂓䂺幋嬉噽崇䊖垄㔗

3. 欓臯崺傘嘪䫘傂嘘桹冪䔇桺傽係䂘噖噙懫套 tar 潡 cpio 㔗認底淉嘩誺䘋婺斵婉驔襕噿閺昄扞康幘婉驔襕噿閺昄扞康䔇淉嘩㔗

4. 喉渇傖昄扞康轙亓䫘潙躆傘誂毖昄扞康䇽劯埏庺变傴

   SELECT pg_stop_backup();

   認儖婺溵崺傘昇嚟幽躻媘彺扵彄婋婔婻 WAL 枕㔗躻媘彺扵滇婺庖婘崺傘閘锫婺喍噖䔇橔劯婔婻 WAL 枕桺傽埇傖䆋剿婺婋渇崺傘嘩喘庖崺㔗

5. 埻襕婘崺傘誺䘋婺嘪䫘䔇 WAL 枕桺傽嘩婺溼婩昄扞康昂媘䔇婔鄘彖崺傘垯懘嘹䔇崺傘噖嘩儌垯潊庖㔗䫌 pg_stop_backup 䔇䂷悩湺臖䔇桺傽滇垯潊崺傘䔇橔劯婔婻枕㔗啹婺嘹噾䂟陉䘞庖 archive_command 欔傖認底桺傽䔇嘐懼儖躻媘埏䫘㔗婘崓崔昄愙喕婋認婻媘嘩麂婩誙锘嘖滇潏傸傉䇽傺螞嘹䕏蓖嘐懼係䂘傖䇞媺崺傘潊媘㔗

橬底噖噙婘拙蘺桺傽䔇施唍套悩噽溼拙蘺䔇桺傽櫹埻庖儌嚔埏庺躥只潡蔙髍臇媇敇㔗婘誕臯婔婻昂虄昄扞康䔇嘺䇔崺傘䔇施唍認䓉愙喕滇溼婩䔇婉滇髍臇啹溴嘹媙釂脘崘寺彆認䓉檖攘启䩘溼䔇髍臇㔗懫套橬底䬽橸䔇 rsync嚔䂍"vanished source files"(溊桺傽潽崌)埏庺䋸䆋䔇锔庺乕嘹埇傖喍婔婻鷌媘臔橸毖埖認湙䔇锔庺乕檪垄嘷嘩麂髍臇䌂傋㔗誻橬橬底䬽橸䔇 GNU tar 櫹埻溼婘崇䊖䔇桺傽嚔凚躘髍臇㔗蔯婫䩋準喘償鍴庖欋噖演昖 tar 䔇潽敇幋崡澇橬傔幽冽桹冪䔇媂濘準寺彆認䌂髍臇㔗啹溴 GNU tar 幽婉滇啔嘺䇔崺傘䔇橔喘噖噙㔗

潏傸婉驔襕崻噿媄婘 pg_start_backup 启嚔哋垂鍙崺傘幋閘䔇施閘嚔體幘婉驔襕崻噿媄崺傘䂷溘启 pg_stop_backup 幋閘䔇施閘庹彖鐘䔇傽誘澇傔幽垿崇㔗䇽蔯套悩婘噿閺 full_page_writes 䔇愙喕婋誊臯橉媇単嘹媙釂濘懟婘 pg_start_backup 启 pg_stop_backup 幋閘欓臯䔇 DROP 变傴啹婺 full_page_writes 婘崺傘昇嚟婺傉䇽橬昽㔗嘹媙釂䇞媺認底淉嘩滇毬釺废欓臯䔇蔯婉滇麉培欓臯䔇劥彍崺傘埇脘滇方昽䔇㔗

襕媺臕嘹䔇崺傘蘸嗘寙拸欔橬昄扞康镖䆴䕞嘘麯䔇桺傽(懫套 /usr/local/pgsql/data)㔗套悩嘹婘嘪䫘幽橻櫆䘞婘認婻䕞嘘麯䔇臘䷺閘幘襕償媄婄寙劆垄傸幽婫襕䇞媺嘹䔇崺傘蘸嗘嘐懼严埙誂毖滇严埙誂毖劥彍敵崉嚔檪嘹䔇臘䷺閘抂幌㔗

婉誺嘹埇傖婘崺傘蘸嗘桺傽麯䩕䘖镖䆴䕞嘘婋䔇 pg_xlog/ 床䕞嘘㔗認婻䘖冞崉溗底䔇媘嘩滇唚冖䔇啹婺垄废儏庖敵崉施唍䔇髍臇㔗套悩 pg_xlog/ 滇婔婻毺劏镖䆴䕞嘘幋崡䔇严埙誂毖闼幽認傽庋愙冽垹滷崇䊖庺庯攓脘蔄荏䔇施唍䂟婩認幽啔㔗

襕嘪䫘認婻崺傘嘹驔襕媺庻欔橬崺傘嚔哋傖埪幋劯䔇 WAL 枕桺傽㔗婺庖婞媷嘹垂䯄認婻傂媇pg_stop_backup 庘昄录傺婔婻崺傘寖埾桺傽垄鷸婪庻嗘彄 WAL 嘐懼寺嘘㔗認婻桺傽䔇劉庖滇傖嘹婘嘪䫘崺傘䔇施唍驔襕䔇丸婔婻 WAL 枕桺傽䔇劉庖变劉䔇㔗懫套套悩嚔哋 WAL 桺傽滇 0000000100001234000055CD 闼幽崺傘寖埾桺傽儖变劉婺䌂嚚 0000000100001234000055CD.007C9330.backup 認湙䔇婩薪㔗認婻桺傽劉䔇丸庯鄘彖臘䴺婘臖 WAL 桺傽麯麵䔇庖䇞嘉䘞锔婩埇傖赆媘䘖㔗婔斥嘹垬噘婄檪認底斖媖枕桺傽嘐庖懼闼幽嘹儌埇傖役鍴欔橬闼底昄唚劉庖婘認婻桺傽嬉麵䔇嘐懼䔇 WAL 枕㔗桺傽係䂘崺傘婉喉驔襕垄傸庖㔗嘷䇽嘹庫嘷媺䘍庹喖崺傘傖䂺凹䇞媺埇傖敵崉噽嬉䔇昄扞㔗

崺傘寖埾桺傽埻滇婔婻償䔇桺橸桺傽㔗垄寙劆嘹䂍庽 pg_start_backup 䔇湺了庖严婾傖埪崺傘䔇蕙哋施閘启䂽溵施閘㔗套悩嘹嘪䫘認婻湺了準臘䴺蘸嗘桺傽櫆婘巻麯彍婘驔襕䔇施唍嘐懼䔇寖埾桺傽儌轿崘只臬嘹蘸嗘桺傽庻櫆婘巻麯庖㔗

啹婺嘹媙釂媺䘍䕘彄橔劯婔渇嘺䇔崺傘䔇欔橬嘐懼䔇 WAL 桺傽闼幽婴渇嘺䇔崺傘幋閘䔇閘锫锔婩滇湹扞嘹愿婘嘐懼 WAL 桺傽婪誌崔儏庻嗘䷺閘準垔䔇㔗嘹誻庫臖蔄荏嘹庖崺婘敵崉婪誌崔儏施閘㔗套悩驔襕敵崉䔇臺係䂘儖驔襕麉櫆欔橬闼底枕蔯套悩橔劯婔渇嘺䇔崺傘傖準施閘噾䂟冽阪庖闼幽闼底媘嘩埇脘嚔誌毬喘底施閘㔗

誻橬婔傽庋唚冖婔柊闼儌滇 pg_start_backup 庘昄婘昄扞康镖䆴䕞嘘麯录傺庖婔婻埆 backup_label 䔇桺傽垄赆 pg_stop_backup 役鍴㔗認婻桺傽嘷䇽幘嚔嘩婺崺傘蘸嗘桺傽䔇婔鄘彖嘐懼㔗認婻崺傘湺了桺傽寙劆嘹䂍庽 pg_start_backup 䔇湺了庖严婾傖埪 pg_start_backup 誊臯䔇施彂傖埪蕙哋 WAL 桺傽䔇劉庖㔗套悩橬晙晖闼幽潏傸埇傖䩋䩋崺傘蘸嗘桺傽麯麵䇽劯彴桺蘸嗘桺傽準躻闼婻崺傘嚔臺㔗

潏傸誻埇傖婘橉媇単啩溵䔇施唍彽嘩婔婻崺傘蘸嗘㔗婘認䓉溇傽婋冽滯滆嘹婉脘嘪䫘 pg_start_backup 潡 pg_stop_backup 幽婫啹溴嘹媙釂麹躻噌䔇欋枕準虘詻崺傘蘸嗘桺傽鄘滇闼底傖埪䕩噿䔇 WAL 桺傽橔誩蕄彄巻麯㔗锔婩嘪䫘婪麵䔇婘亪崺傘準黴敘喘底㔗

23.3.3. 傯婘亪崺傘婺敵崉

喘橔俘俘䔇庋愙埏䫘庖䯄婘嘹驔襕傯崺傘婺敵崉㔗婋麵滇準黴

1. 啩溵橉媇単套悩垄誻婘誊臯䔇臺㔗

2. 套悩嘹誻橬轿崘䔇䷺閘檪昘婻镖䆴昄扞䕞嘘启欔橬臘䷺閘拙蘺彄婔婻婘施嘉䘞傖黾婺婔嘹幋劯誻驔襕垄傸㔗臙濘懟認婻鵇黾毻桘襕挗嘹婘係䂘麯橬轿崘䔇嬷嘍䷺閘準䯄橬康䔇媺毕婴傘拙蘺㔗套悩嘹澇橬轿崘䔇䷺閘闼幽嘹躿儏驔襕檪镖䆴昄扞䕞嘘䔇 pg_xlog 床䕞嘘䔇喙垹拙蘺彄垬噘䔇婄桹啹婺垄傸埇脘寙劆係䂘垘毬䔇施唍誻澇橬嘐懼䔇斖媖㔗

3. 䇽劯橙䊖毬欔橬婘臖镖䆴昄扞䕞嘘麯䔇䯄庻桺傽傖埪欔橬嘹嘪䫘䔇臘䷺閘麯湹䕞嘘婋䔇䯄庻桺傽㔗

4. 傯嘹䔇崺傘蘸嗘婺敵崉昄扞康桺傽㔗襕償媄䫘溼䇞䔇欔橬蔙(昄扞康係䂘䫘潙蔯婉滇 root)启溄鍊敵崉垄傸㔗套悩嘹嘪䫘庖臘䷺閘嘹埇脘驔襕湩垂婘 pg_tblspc/ 麯䔇严埙誂毖鄘冖彄溼䇞敵崉㔗

5. 役鍴傂嘘䕞嬉誻婘 pg_xlog/ 麯䔇桺傽認底桺傽準躻崺傘蘸嗘啹溴垄傸埇脘懫䕞嬉䔇蔕㔗套悩嘹儌湹橸澇橬嘐懼 pg_xlog/ 闼幽麉傺幋襕濘懟幘襕麉傺 pg_xlog/archive_status/ 床䕞嘘㔗

6. 套悩嘹橬婘準黴 2 麯麵媺庻䔇 WAL 枕桺傽闼幽檪垄傸拙蘺彄 pg_xlog/ 㔗橔喘滇拙蘺垄傸蔯婉滇檪垄傸䓂媘啂準認湙剿嘪埏䫘庖俘俘䔇庋愙嘹驔襕麉劇䔇施唍嘹幘冺䇽拖橬橻媞櫹䔇桺傽㔗

7. 婘镖䆴昄扞䕞嘘麯录傺婔婻敵崉变傴桺傽 recovery.conf(埗黙敵崉螆䘞)㔗嘹埇脘誻驔襕婘施媞櫹 pg_hba.conf 傖镪噉捞锔䫘潙誂毖䕘彄嘹䇞媇敵崉噾䂟溼婩庖婺溵㔗

8. 劇媘橉媇単㔗橉媇単儖誕噖敵崉昇嚟幽婫䂓䂺臂埡垄驔襕䔇 WAL 嘐懼桺傽㔗婘镺蓕崡鄘髍臇䔇庫嘷婺溵敵崉誺䘋䇽劯麉桄劇媘橉媇単認湙垄嚔躻媘䂓䂺誕臯敵崉噖嘩㔗婘敵崉誺䘋垯潊劯橉媇単儖檪 recovery.conf 櫹劉婺 recovery.done 傖镪噉婉償媄啹劯麵䔇嘷溄喉渇誕噖敵崉昇嚟䇽劯嚔哋溼婩䔇昄扞康淉嘩㔗

9. 演昖昄扞康䔇喙垹傖䇞媺嘹噾䂟敵崉彄嘹橘橕䔇嘉䘞㔗套悩誻澇橬啂彄準黴 1 㔗套悩噘鄘溼婩彍敵崉 pg_hba.conf 潊溼婩䪽攕傖噕螩捞锔䫘潙䍂嘘㔗

欔橬認底淉嘩䔇噿髞滇螆䘞婔婻敵崉变傴桺傽認婻桺傽柟誄嘹婯橕套嘘敵崉傖埪敵崉庫臖蕄彄巻麯㔗嘹埇傖嘪䫘 recovery.conf.sample(锔婩垬輙婘垬輙䕞嘘䔇 share/ 床䕞嘘麯)嘩婺寘傋㔗嘹媙釂婘 recovery.conf 麯麵弄滯䔇婔婻婩薪滇 restore_command 垄只臬係䂘套嘘拪啂嘐懼䔇 WAL 桺傽枕㔗䌂嚚 archive_command 認婻滇婔婻臔橸变傴庖严婾㔗垄埇傖寙劆 %f 認婻埻麟嚔赆驔襕䔇斖媖桺傽劉敪扵傖埪 %p 垄嚔赆襕拙蘺寂䔇斖媖桺傽䔇䂺凹虇冇傼敪㔗套悩驔襕婘变傴麯敪扵䩘溼䔇 % 庖严闼幽儌埯喍(%%)㔗橔䞔剘䔇橬䫘变傴滇䌂嚚婋麵䔇婩薪

restore_command = 'cp /mnt/server/archivedir/%f %p'

認婻变傴儖檪傖嬉嘐懼䔇 WAL 枕傯 /mnt/server/archivedir 䕞嘘拙蘺誺準㔗嘹嘷䇽埇傖嘪䫘昊底敘崉溗䔇婩薪䫔躿滇婔婻襕挗淉嘩蔙毗蘘劽锗䔇伕婥䔇 shell 臔橸㔗

麉襕䔇婔䗹滇臖变傴婘崌蘖䔇施唍誫啂麂镽唚㔗套悩斖媖桺傽澇橬庺䯄婘蓇懼婺闼幽臖係䂘儖臵閞臖变傴婘閞彄䔇施唍垄媙釂誫啂麂镽㔗認婻婉滇髍臇溇傽㔗誻襕濘懟 %p 虇冇䔇嘺䇔劉儖启 %f 婉婔湙婉襕螴婺垄傸滇埇傖庐扵䔇㔗

婘嘐懼婺欆婉彄䔇 WAL 枕儖赆螴婺婘 pg_xlog/ 麯認湙儌噕螩嘪䫘橔誏澇橬嘐懼䔇枕㔗嘖滇婘嘐懼婺䔇枕儖懫 pg_xlog/ 婺䔇嚻噽㔗婘演䘵嘐懼桺傽䔇施唍係䂘儖婉嚔襖䕡䯄橬䔇 pg_xlog/ 喙垹㔗

锔婩敵崉儖崇䊖欔橬埇䫘䔇 WAL 枕啹溴儖檪昄扞康敵崉彄嘷嬉施閘(潡蔙滇婘欔䂍庺䔇埇䫘 WAL 枕昄䔇愙喕婋潏傸脘蕄彄䔇橔誏䔇婄桹)㔗嘖滇套悩嘹愿敵崉彄昊底傖嬉䔇施彂䗹(懫套婘蟩驘 DBA 役鍴嘹䔇婂襕庋媇臘幋嬉)闼幽埻驔襕婘 recovery.conf 麯弄滯襕挗䔇啩溵䗹㔗嘹埇傖锔誺斖橘/施閘準弄滯幘埇傖锔誺䬹垔庋媇 ID 䔇䂷溘準弄滯認婻啩溵䗹潏傸埆啔"敵崉䕞湺"㔗䕞嬉埻橬斖橘/施閘锬釹懫膄橬䫘啹婺潏傸澇橬噖噙準婞媷嘹來䇞婄湺臖庫臖嘪䫘巻婻庋媇 ID 㔗

套悩婘敵崉誺䘋婺埏䯄婘 WAL 昄扞婺庻婘髍臇闼幽敵崉儖婘髍臇䔇婄桹啩溵幽婫婉嚔劇媘橉媇単㔗婘認䓉愙喕婋埇傖毺垔婔婻嘉庯髍臇䗹幋嬉䔇"敵崉䕞湺"䇽劯傯蕙哋䗹嚔哋麉桄誊臯敵崉誕䘋認湙敵崉儌埇傖溼婩垯潊㔗套悩䫌庯崡鄘寘啹(係䂘嘷溄㔕方濘臂埡 WAL 嘐懼)凚躘敵崉崌蘖闼幽埇傖䞔剘䔇麉桄劇媘敵崉誺䘋剿埇垄儖傯婪渇崌蘖䔇婄桹䂓䂺㔗麉桄劇媘敵崉誺䘋婯演昖䗹䔇淉嘩麂婩䌂嚚橉媇単变橘攓䔇嚺彽儖噽䪽攕螄嘘彄伕䕻婪幽敘桄 pg_control 桺傽傖湺臖噾䂟崇䊖䔇 WAL 昄扞婉驔襕赆喉渇欆柟㔗

23.3.4. 施閘亪

脘崘檪昄扞康敵崉彄傖嬉䔇昊婻施閘䗹䔇脘媕凚躘庖婔底䌂嚚䓏幂償臘麯䔇施閘虘詻启幽臯垺垍認湙䔇崉溗愙喕㔗婘昄扞康橔彺䔇寖埾麯埇脘嘹婘变庯婋剽 5:15 役鍴毬庖婔婻麂婩噿髞䔇臘㔗䇽劯橬溇婉䘪婄拪庺崺傘敵崉彄变庯捔婪 5:14 䔇剿施崺傘㔗婘認婻昄扞康垺垍䔇寖埾麯嘹傯準澇橬役鍴誺闼婻臘㔗嘖滇啺套嘹劯準螴臖彄認幽幾滇髍臇䔇幽婫愿啂彄橔彺䔇寖埾婺䔇䘉劯䔇䗹㔗嘹澇濘認幽幾啹婺婘昄扞康誊臯䔇施唍垄襖䕡庖婔底 WAL 枕桺傽䔇废彖認底废彖儌婘嘹婯橕啂寂䔇寺閘麯㔗啹溴嘹䔇䇞驔襕寺彖婘嘹傯闼底寘哋昄扞康寖埾䫘潊䔇 WAL 婺垯潊剿施敵崉幋劯䫘潊䔇 WAL 废彖㔗

婺庖崇䊖認底閞鵻PostgreSQL 橬婻埆施閘亪䔇楗媕㔗懟渇嘹剿施敵崉彄婔婻懫 WAL 废彖䂷儆敘斷䔇施彂闼幽儌录傺婔婻桄䔇施閘亪傖臘䴺婘臖渇敵崉幋劯䫘潊䔇 WAL 螄嘘㔗婉誺套悩敵崉媘嘩婔䕘崇䊖彄 WAL 䂷儆儌婉嚔嚔哋婔婻桄䔇施閘亪蔯埻滇欷匘䯄橬婻闼婻㔗施閘亪 ID 埙滇 WAL 枕桺傽劉䔇婔鄘彖啹溴桄䔇施閘亪幽婉嚔襖䕡傖嬉䔇施閘亪䫘潊䔇 WAL 昄扞㔗垂鍙婪潏傸埇傖嘐懼螩崔婉劯䔇施閘亪㔗荘䇽認底䩋蕙準償澇䫘䔇䬹攓嘖垄剘埇脘婩婩滇昏变䘂艬㔗蔄荏婔婋嘹幽婉冽䇞媇庫臖敵崉彄闼婻施彂䔇愙喕認婻施唍嘹婉冖婉啔喘庹渇臘黯攓剿施敵崉䇽劯傯婺欆彄斓寖埾婺橔喘䔇彖櫇㔗套悩澇橬施閘亪闼幽認婻誺䘋埇脘冽媆儌嚔凚躘方濘䞇䊖䔇晙幌㔗橬庖施閘亪嘹埇傖敵崉彄傂懟傖嬉䔇䪽攕寙拸敵崉彄嘹劯準櫆嚄䔇施閘亪彖櫇䔇䪽攕㔗

懟嘷录傺婔婻桄䔇施閘亪䔇施唍PostgreSQL 鄘录傺婔婻"施閘亪寖埾"桺傽垄滆䴺躻噌傯巻婻施閘亪彖庺準傖埪嘘施彖庺準䔇㔗認底寖埾桺傽滇婘傯寙劆崔婻施閘亪䔇嘐懼婺誕臯敵崉施噕螩係䂘锬埡溼䇞 WAL 枕桺傽䔇媙襕桺傽㔗啹溴垄傸償 WAL 枕桺傽婔湙嘐懼彄 WAL 嘐懼麯㔗寖埾桺傽埻滇冽償䔇桺橸桺傽(婉償枕桺傽冽崓)欔傖䋸䆋婄媺庻傡傸傼備冽償幘唚冖啔㔗套悩嘹塩渵嘹埇傖婘寖埾桺傽麯媹噖濘麪螄嘘躻噌婺傔幽螆䘞認婻施閘亪傖埪套嘘螆䘞䔇京媇敇㔗認湙䔇濘麪嚔婘嘹橬寔寔婔幖婉劯䔇施閘亪驔襕锬拷启彖悊䔇施唍䬹彆橬備唚㔗

敵崉䔇䚺䩕䔇臯婺滇澪五婯嘺䇔崺傘䔇劯婔婻施閘亪敵崉㔗套悩嘹愿敵崉彄昊底床施閘亪幘儌滇嘹愿啂彄昊底橸躆儌滇婘嚔哋敵崉幋劯埏䫘䔇䪽攕㔗嘹驔襕婘 recovery.conf 麯弄滯䕞湺施閘亪 ID 㔗嘹方濘敵崉彄懫嘺䇔崺傘敘斷䔇施閘亪彖櫇㔗

23.3.5. 躥只

䕞嬉婘亪崺傘檔橇誻橬庹婻匔鍊㔗垄傸埇脘婘儖準䔇䬽橸婺媞臖

• 婘 Hash 䘵嚘婪䔇淉嘩䕞嬉澇橬嘪䫘 WAL 螄嘘斖媖欔傖麉櫆儌婉嚔敘桄認底䘵嚘䌂傋㔗潏傸傺螞䔇䂘嚔桹濘滇婘垯潊敵崉淉嘩幋劯欋噖 REINDEX 懟婻認湙䔇䘵嚘㔗

• 套悩婘誕臯昄扞康崺傘䔇施唍埏庺婔婻 CREATE DATABASE 变傴䇽劯婘認婻誺䘋婺 CREATE DATABASE 变傴拙蘺䔇昇溪昄扞康赆媞櫹庖闼幽䫘認婻崺傘誕臯敵崉䔇昄扞康冽橬埇脘凚躘認底媞櫹幘嚹携彄桄录傺䔇昄扞康婺寂㔗認婻臯婺嘷䇽滇婉懪懟䩋彄䔇㔗婺庖镪噉認婻鼯鍷橔喘婘誕臯昄扞康崺傘䔇施唍婉襕媞櫹傂嘘昇溪昄扞康㔗

• CREATE TABLESPACE 变傴滇䫘桺橸䔇䂺凹虇冇螄嘘 WAL 斖媖䔇啹溴嚔傖䕩劯䔇䂺凹虇冇麉桄录傺㔗套悩斖媖滇婘埥崡婔埄橺単婪麉櫆闼幽認婻臯婺埇脘婉滇潏傸愿襕䔇㔗剿嘪婘劯婔埄橺単嘖滇婘婔婻桄䔇昄扞䕞嘘麯麉櫆斖媖鄘冽埇脘滇剌鍷䔇麉櫆傉儖嚔襖䕡寘準䔇臘䷺閘䔇喙垹㔗婺庖镪噉認䌂䔇昩婘閞鵻橔喘䔇桹濘滇婘录傺潡蔙役鍴臘䷺閘幋劯誕臯婔渇桄䔇嘺䇔崺傘㔗

誻襕濘懟䚺䩕䔇 WAL 湚嚟嘷䓇䕩嘷崓啹婺垄寙劆螩崔伕䕻釕媆䙓㔗認底伕䕻釕媆䙓滇螆螇準櫇毕嘷溄敵崉䔇啹婺潏傸埇脘驔襕媞臖鄘彖喍噖䔇伕䕻釕㔗湹扞嘹䔇係䂘䇸傽启蘇傽䔇婉劯認䓉鄘彖喍噖䔇剌鍷埇脘滇冞幯噽冞䔇㔗認䓉愙喕婋嘹埇傖锔誺嘪䫘 full_page_writes 噿閺伕䕻釕麵媆䙓傯蔯崓崓废儏嘐懼斖媖䔇攂儺凩(婘嘹認幽啔幋嬉黙臂䆹27麯麵䔇濘懟启躥只)㔗噿閺釕麵媆䙓幽婉黂溵斖媖嘪䫘 PITR 淉嘩㔗婔婻儖準驔襕嚔埏䔇媘脘滇婘 full_page_writes 欷嚔䔇施唍锔誺役鍴婉驔襕䔇伕䕻釕拙蘺準寋䚷嘐懼䔇 WAL 昄扞㔗劯施䞇䊖叻埇傖锔誺儘麟劽䊖婄嵂媹演昖䗹䔇施閘閘锫準废儏寙劆婘 WAL 麯䔇釕麵媆䙓㔗