丸婔渇准噙昄扞康施埇脘驔襕啔崓麟䔇臘某噖㔗婋麵滇婔底傺螞埇傖儘埇脘醻昽婄崇䊖認底庋愙㔗
噿閺躻媘柊庴幽婫埻婘懟渇(昄扞拙蘺)䂷溘䔇施唍啔婔渇柊庴㔗婘亇 SQL 麯認儌懟叿五婘嚔哋䔇施唍埏庺 BEGIN 幽婫婘䂷溘䔇施唍欓臯 COMMIT 㔗橬底垵潙䆇䔇康埇脘脯五嘹幾認底庋愙認䓉愙喕婋嘹媙釂䇞媇埻橬婘嘹䇞垂襕闼底康幾認底庋愙䔇施唍垄欉啔㔗套悩嘹噕螩懟婻某噖鄘䋸䆋婄柊庴闼幽 PostgreSQL 嚔婺欔嵂媹䔇懟臯螄嘘啔崓麟䔇崇䊖㔗婘婔婻庋媇麯垯潊欔橬某噖䔇媘嘩䔇橔崓䔇喘崇儌滇套悩橬婔溇螄嘘某噖崌蘖闼幽彄臖䗹婺溵䔇欔橬噾某噖螄嘘鄘儖赆啂悔認湙嘹儌婉嚔冽锆埖婄麵凹婔婻埻輙蘘庖婔鄘彖昄扞䔇臘㔗
嘪䫘 COPY 婘婔溇变傴麯輙蘘欔橬螄嘘蔯婉滇婔誂婾䔇 INSERT 变傴㔗COPY 变傴滇婺輙蘘昄麟噘崓䔇昄扞臯嚻寡誺䔇垄澇 INSERT 闼幽䕕昂嘖滇婘崓麟輙蘘昄扞䔇愙喕婋凚躘䔇艙蘘幘儏冽崔㔗啹婺 COPY 滇剘溇变傴啹溴准噙臘䔇施唍儌澇橬媙襕噿閺躻媘柊庴庖㔗
套悩嘹婉脘嘪䫘 COPY 闼幽嘪䫘 PREPARE 準录傺婔婻鵇崺 INSERT 䇽劯嘪䫘 EXECUTE 崔渇昽䯺敘醻㔗認湙儌镪噉庖麉崉彖悊启蓇彐 INSERT 䔇嚔體㔗
臙濘懟婘輙蘘崓麟昄扞臯䔇施唍COPY 庹幯攂滇懫 INSERT 媆剿嘪嘪䫘庖 PREPARE 幽婫檪崔婻 INSERT 变傴䂏婘婔婻庋媇婺幘滇認湙㔗
套悩嘹溼婘輙蘘婔婻桄录傺䔇臘橔媆䔇桹濘滇录傺臘䫘 COPY 欹麟輙蘘䇽劯录傺臘驔襕䔇傂嘘䘵嚘㔗婘噾庻婘昄扞䔇臘婪录傺䘵嚘襕懫锐嵂婄敘桄欔輙蘘䔇懟婔臯螄嘘襕媆㔗
套悩嘹凹䯄橬臘嵂媹崓麟䔇昄扞埇脘噽役鍴䘵嚘輙蘘臘䇽劯麉桄录傺䘵嚘敘媆底㔗嘷䇽婘䚺儏䘵嚘䔇橘閘噽垄昄扞康䫘潙䔇昄扞康攓脘儖橬蘘麵䔇嘌巉㔗幽婫潏傸婘役鍴嫇婔䘵嚘幋嬉誻驔襕傫䂖蔄荏橙斔啹婺嫇婔亥溘柊冕䔇髍臇演昖婘䚺儏䘵嚘䔇施唍嚔潽崌㔗
启䘵嚘婔湙"欹麟婄"演昖崡髞亥溘懫婔臯臯演昖敘醻昽㔗啹溴幘螩潏傸噽役鍴崡髞亥溘輙蘘昄扞䇽劯麉傺亥溘嚔敘醻昽㔗劯湙輙蘘昄扞启䚺儏亥溘蔯崌寂髍臇演昖幋閘幘橬婔婻广臇㔗
婘輙蘘崓麟䔇昄扞䔇施唍婘施嵂崓 maintenance_work_mem 陉䘞埻麟埇傖櫹誕攓脘㔗認婻埗昄幘埇傖婞媷媹锘 CREATE INDEX 启 ALTER TABLE ADD FOREIGN KEY 变傴㔗垄婉嚔凹 COPY 橸躆橬崔崓嘩䫘欔傖認婻傺螞埻橬婘嘹嘪䫘婪麵䔇婴婻檔噓施欉橬昽㔗
婘施嵂崓 checkpoint_segments 陉䘞埻麟幘埇傖螷崓麟昄扞輙蘘冖敘媆㔗認滇啹婺劏 PostgreSQL 麯麵輙蘘崓麟䔇昄扞埇傖凚躘演昖䗹淉嘩(䫌陉䘞埻麟 checkpoint_timeout 弄滯)懫广婩敘媹鵏乕埏䫘㔗婘埏䫘婔婻演昖䗹䔇施唍欔橬臟昄扞鄘媙釂彙桄彄伕䕻婪㔗锔誺婘崓麟昄扞輙蘘䔇施唍婘施嵂媹 checkpoint_segments 欔襕挗䔇演昖䗹䔇昄䕞埇傖废儏㔗
婉䞇傔幽施唍套悩嘹婘嵂媹潡蔙敘桄庖崓麟昄扞幋劯誊臯 ANALYZE 鄘滇婻喘幹愇㔗誊臯 ANALYZE 潡蔙 VACUUM ANALYZE 埇傖媺臕蓇彐単橬臘昄扞䔇橔桄䂘螇㔗套悩澇橬䂘螇昄扞潡蔙䂘螇昄扞崻鍽斓闼幽蓇彐単埇脘锬拷冽噞媾䔇昖臵蓇彐凚躘臘䔇昖臵攓脘敽寡㔗
pg_dump 䫘潊䔇蘸嗘臔橸躻媘嘪䫘婪麵䔇苖幾婻檔噓嘖婉滇噘鄘㔗襕儘埇脘媆婄輙蘘 pg_dump 蘸嗘潏傸驔襕欋噖啔庹婻庋愙㔗臙濘懟認底襕䗹锗䫘庯敵崉婔婻蘸嗘蔯婉滇录傺婔婻蘸嗘䔇施唍㔗劯湙䔇襕䗹幘锗䫘庯嘪䫘 pg_restore 傯 pg_dump 嘐懼桺傽輙蘘昄扞䔇施唍㔗
䚺䩕䔇施唍pg_dump 嘪䫘 COPY 婘垄䫘潊婔婻垯昘䔇昇嚟启昄扞䔇蘸嗘䔇施唍垄嚔冽償媄婄噽輙蘘昄扞䇽劯录傺䘵嚘启崡髞㔗啹溴婘認婻愙喕婋崘庹溇檔噓滇躻媘崇䊖䔇㔗嘹驔襕啔䔇埻滇婘輙蘘蘸嗘臔橸幋嬉螆䘞懫溼婩䪽喕崓䔇 maintenance_work_mem 启 checkpoint_segments 唚䇽劯婘輙蘘垯潊幋劯誊臯 ANALYZE 㔗
埻媺庻昄扞䔇蘸嗘傉䇽嚔嘪䫘 COPY 嘖滇垄婉嚔役鍴潡蔙麉傺䘵嚘幽婫垄婉嚔躻媘媞櫹崡髞㔗[1]啹溴婘輙蘘埻橬昄扞䔇蘸嗘䔇施唍滇劥嘪䫘役鍴傖埪麉傺䘵嚘启崡髞京檔噓垯噘埡喿庯嘹㔗輙蘘昄扞䔇施唍嵂崓 checkpoint_segments 傉䇽滇橬䫘䔇嘖滇嵂崓 maintenance_work_mem 儌澇傔幽媙襕庖嘹埻滇庫臖婘庋劯欋噖录傺䘵嚘启崡髞䔇庋劯嵂崓垄㔗橔劯䂷溘施婉襕媻螄 ANALYZE 变傴㔗
| [1] | 嘹埇傖锔誺嘪䫘 --disable-triggers 锬釹䔇桹濘诙埡噿閺崡髞䔇昽悩㔗婉誺襕懟臖彄認幽啔滇潽鍴蔯婉埻滇毘誘誺埉崡髞亥溘啹溴套悩嘹嘪䫘認婻锬釹儖橬埇脘某噖废昄扞㔗 |