17.5. 鵇喍嚟斖媖

套悩欷嚔認婻锬釹闼幽 PostgreSQL 橉媇単儖婘喘庹婻婄桹嘪䫘 fsync() 係䂘脄䫘(潡京備脄䫘埗蓕 wal_sync_method)準䇞媺敘桄噾䂟䬷䊖婪喍彄伕䕻婺㔗認湙儌媺臕庖昄扞康镖䆴儖婘淉嘩係䂘潡蔙䇸傽嘷溄䔇愙喕婋敵崉彄婔婻婔躘䔇䪽攕㔗

婉誺嘪䫘 fsync 嚔凹攓脘橬嘌巉婘庋媇柊庴䔇施唍PostgreSQL 媙釂京写淉嘩係䂘檪鵇喍斖媖彙桄彄伕䕻婪㔗婘噿閺 fsync 䔇施唍淉嘩係䂘埇傖儘埇脘嚻寡䚷喾㔕毐废启毘誘喍媘嘩㔗認湙埇傖滆菖柊醻攓脘㔗婉誺套悩係䂘嘷溄橔劯柊庴䔇庹婻庋媇䔇䂷悩埇脘鄘彖潡蔙噘鄘婵崌㔗橔俘俘䔇愙喕滇埇脘庺䯄婉埇敵崉䔇嘷溄㔗昄扞康橉媇単橸躆嘷溄幽婉滇認麯䔇鼯鍷啹䘹㔗埻橬淉嘩係䂘亓彆䔇嘷溄欉嚔凚躘懕废昄扞䔇鼯鍷㔗

啹婺潬埪䔇鼯鍷崻醻fsync 䔇螆䘞澇橬捞锗䔇寘彍㔗橬底䞇䊖叻攂滇噿閺 fsync 蔯噽垄婔底埻滇婘欹麟輙蘘䔇施唍噿閺垄啹婺認婻施唍剿嘪庺䯄庖髍臇幘橬滯䇞麉桄嚔哋䔇䗹蔯埥崡婔底䞇䊖叻攂滇欷嚔 fsync 㔗䚺䩕滇欷嚔 fsync 䕞䔇滇橔崓鍊庥䔇埇麹攓㔗套悩嘹媇傂淉嘩係䂘㔕䇸傽㔕噖噙㔕崺傘䫕挹嘹埇傖蔄荏噿閺 fsync 㔗

認婻锬釹埻脘婘橉媇単劇媘䔇施唍潡蔙婘 postgresql.conf 桺傽麯螆䘞㔗套悩認婻锬釹赆噿閺闼幽臙蔄荏檪 full_page_writes 幘噿閺庖㔗

䫘準劏伕䕻嚺彽敘桄 WAL 昄扞䔇桹濘㔗套悩 fsync 滇噿閺䔇闼幽認婻螆䘞儌澇橬懟幬啹婺欔橬敘桄鄘婉嚔嚺彽膷庺㔗埇脘䔇唚滇

• open_datasync(䫘婥 O_DSYNC 锬釹䔇 open() 欷嚔 WAL 桺傽)

• fdatasync(懟渇柊庴䔇施唍鄘脄䫘 fdatasync())

• fsync_writethrough(懟渇柊庴䔇施唍脄䫘 fsync() 嚺彽喍庺傂嘘伕䕻喍䚷喾寺)

• fsync(懟渇柊庴䔇施唍脄䫘 fsync())

• open_sync(䫘婥 O_SYNC 锬釹䔇 open() 喍 WAL 桺傽)

婉滇婘欔橬係䂘婪鄘脘嘪䫘婪麵啕䓉锬釹㔗䚺䩕滇赆櫇毕䔇橔嬉麵婔婻锬釹㔗認婻锬釹埻脘婘橉媇単劇媘䔇施唍潡蔙婘 postgresql.conf 桺傽麯螆䘞㔗

欷嚔認婻锬釹䔇施唍PostgreSQL 橉媇単婘演昖䗹幋劯凹釕麵䔇丸婔渇喍噖施儖昘婻釕麵喍彄 WAL 麯麵㔗認幽啔滇啹婺婘淉嘩係䂘嘷溄誺䘋婺埇脘埻橬鄘彖釕麵喍噖伕䕻傯蔯凚躘婘劯婔婻釕麵婺寙劆桄斓昄扞䔇晙劽㔗婘嘷溄劯䔇敵崉橘閘䫌庯婘 WAL 麯麵庻嗘䔇臯埻寡媇敇婉崘垯昘啹溴方濘垯噘敵崉臖釕㔗檪垯昘䔇釕麵嘌償媺庻婋準儌埇傖媺臕溼䇞庻嗘釕麵傼備滇嵂媹庖喍噖 WAL 䔇昄扞麟㔗啹婺 WAL 麉櫆攂滇傯婔婻演昖䗹嚔哋䔇欔傖婘演昖䗹劯懟婻釕麵丸婔渇櫹埻䔇施唍啔 WAL 崺傘儌轿崘庖㔗啹溴婔婻废償噘釕麵喍嚔體䔇桹濘滇嵂媹演昖䗹䔇閘锫埗昄唚㔗

檪認婻锬釹噿閺嚔媹媆溼婩淉嘩䔇锘庥嘖滇埇脘凚躘係䂘嘷溄潡蔙毬䫕幋劯䔇昄扞康托废垄䔇剌垿䌂嚚 fsync 埻滇懫膄償蔯噾㔗套悩嘹橬废償鄘彖釕麵喍噖鼯鍷䔇䇸傽櫇毕(懫套䫕挹冕䫕䔇伕䕻毓彽単)潡蔙桺傽係䂘櫇毕(懫套 ReiserFS 4)幽婫傡傸埇傖檪鼯鍷鍉嘯彄婔婻埇傖毖埖䔇嘯评䘘闼幽嘹埇傖噿閺認婻锬釹㔗

噿閺認婻锬釹幽婉嘌巉剿施敵崉(PITR)䔇 WAL 嘪䫘(埗黙誗23.3)㔗

認婻锬釹埻脘婘橉媇単劇媘䔇施唍潡蔙婘 postgresql.conf 桺傽麯螆䘞㔗䚺䩕滇 on 㔗

櫆婘噌庆喙庻麯䫘庯 WAL 昄扞䔇伕䕻釕麵䚷喾寺䔇昄䕞㔗䚺䩕唚婺 8 㔗認婻螆䘞埻驔襕崓彄脘媺庻婋婔渇庋媇䫘潊䔇 WAL 昄扞剿埇啹婺認底昄扞婘懟渇庋媇柊庴施鄘嚔喍噖伕䕻㔗認婻唚埻脘婘橉媇単劇媘䔇施唍螆䘞㔗

嵂崓認婻埗昄埇脘凚躘 PostgreSQL 襕挗敘崔䔇 System V 噌庆喙庻埇脘轙誺淉嘩係䂘䔇䚺䩕陉䘞㔗媙襕施埗黙誗16.4.1诙埡套嘘脄誗認底埗昄䔇媇敇㔗

劏 WAL 䚷喾寺喍噖螄嘘启儖䚷喾寺彙桄彄伕䕻婪幋閘䔇施閘傽誘傖冞䓐螇㔗麂镽䔇傽誘噕螩崔婻庋媇噌䫘婔婻 fsync() 係䂘脄䫘柊庴套悩係䂘蘘蘘轿崘醻闼幽婘䂍庺䔇閘锫麯噽垄䔇庋媇埇脘噾䂟庖崺喘柊庴庖㔗嘖滇套悩澇橬噽垄庋媇庖崺柊庴闼幽認婻閘锫儌滇婘敻蘹施閘㔗啹溴認婻傽誘埻滇婘婔婻橉媇単誕䘋喍噽柊庴斖媖施躿儏 commit_siblings 婻噽垄庋媇婘昂虄䔇愙喕婋欓臯㔗䚺䩕滇镽(方傽誘)㔗

婘欓臯 commit_delay 傽誘䔇施唍襕挗橔儏劯施欷嚔䔇幽埏庋媇昄䕞㔗崓婔底䔇昄唚嚔凚躘婘傽誘橘閘埥崡婔婻庋媇庖崺喘柊庴䔇埇脘攓嵂崓㔗䚺䩕滇 5 㔗

婘躻媘䔇 WAL 演昖䗹幋閘䔇橔崓虺䥂傖斖媖桺傽枕螇(锔婩懟婻枕 16MB)㔗䚺䩕滇 3 㔗認婻锬釹埻脘婘橉媇単劇媘䔇施唍潡蔙婘 postgresql.conf 桺傽麯螆䘞㔗

婘躻媘 WAL 演昖䗹幋閘䔇橔阪施閘傖䓐螇㔗䚺䩕滇 300 䓐㔗認婻锬釹埻脘婘橉媇単劇媘䔇施唍潡蔙婘 postgresql.conf 桺傽麯螆䘞㔗

套悩䫌庯准噙演昖䗹枕桺傽凚躘䔇演昖䗹埏䫘施閘閘锫毖誏認婻埗昄臘䴺䔇䓐昄闼幽儌劏橉媇単斖媖埏锕婔婻傺螞嵂媹 checkpoint_segments 唚䔇潽敇㔗䚺䩕滇 30 䓐㔗镽彍噿閺躥只㔗認婻锬釹埻脘婘橉媇単劇媘䔇施唍潡蔙婘 postgresql.conf 桺傽麯螆䘞㔗

儖婔婻垯昘䔇 WAL 桺傽废彖嘐懼䔇 shell 变傴㔗套悩認滇婔婻䷺庖严婾(䚺䩕)闼幽儌噿閺 WAL 嘐懼㔗庖严婾婺傂嘘 %p 鄘赆襕嘐懼䔇桺傽䔇䂺凹虇冇傼敪蔯傂嘘 %f 鄘埻赆臖桺傽劉傼敪(麂䂺凹虇冇鄘䕩凹庯镖䆴䔇昄扞䕞嘘)㔗套悩嘹驔襕婘变傴麯啯噖 % 庖严儌媙釂埯喍(%%)㔗橬噿敘崔䔇媇敇埗黙誗23.3.1㔗認婻锬釹埻脘婘橉媇単劇媘䔇施唍潡蔙婘 postgresql.conf 桺傽麯螆䘞㔗

橬婔䗹冽麉襕認婻变傴媙釂滇嘷婫備嘷潊媘䔇施唍欉誫啂镽㔗懫套

archive_command = 'cp "%p" /mnt/server/archivedir/"%f"'
archive_command = 'copy "%p" /mnt/server/archivedir/"%f"'  # Windows

archive_command 備婘噾垯潊䔇 WAL 枕婪脄䫘㔗啹溴套悩橉媇単埻库䫘冽儏䔇 WAL 敕麟(潡库䫘敕麟䔇变橘冽阪)闼幽婘垯潊庋媇傖埪垬噘嘐懼庻嗘幋閘儖橬婔婻冽阪䔇傽施㔗婺庖鍊彽橻嘐懼昄扞䔇锖䘍施閘嘹埇傖嚺彽橉媇単傖 archive_timeout 毺垔䔇䓐昄婺变橘彺扵彄桄䔇 WAL 枕桺傽㔗臖埗昄京庯镽臘䴺噿閺溴䬹攓㔗濘懟䫌庯嚺彽彺扵蔯柊斷噿閺䔇嘐懼桺傽傉䇽婯垯昘䔇嘐懼桺傽阪庥䕩劯㔗啹溴儖 archive_timeout 螆婺冽償䔇唚滇婉滯捺䔇垄儖凚躘剹䫘噘崓䔇嘐懼庻嗘䷺閘㔗儖 archive_timeout 螆䘞婺 60 䓐噥埿滇懫膄劽䊖䔇㔗認婻锬釹埻脘婘橉媇単劇媘䔇施唍潡蔙婘 postgresql.conf 桺傽麯螆䘞㔗