pg_dump 滇婔婻䫘庯崺傘昄扞康䔇噖噙㔗垄䫔躿埇傖婘昄扞康溼婘嘪䫘䔇施唍誕臯垯昘婔躘䔇崺傘㔗pg_dump 幽婉黂凂噽垄䫘潙凹昄扞康䔇螪閞(臂潡蔙喍)㔗
蘸嗘湚嚟埇傖滇婔婻臔橸潡蔙嘐懼桺傽㔗臔橸蘸嗘䔇湚嚟滇亇桺橸垄寙劆螩崔 SQL 变傴認底 SQL 变傴埇傖䫘庯麉傺臖昄扞康幽儖幋敵崉彄媺庻潊臔橸䔇施唍䔇䪽攕㔗嘪䫘 psql 傯認湙䔇臔橸婺敵崉㔗垄傸䫔躿埇傖䫘庯婘噽垄橺単䫔躿滇噽垄䇸傽嘷係䔇橺単婪麉傺臖昄扞康锔誺凹臔橸誕臯婔底媞櫹䫔躿埇傖婘噽垄 SQL 昄扞康库巕婪麉傺臖昄扞康㔗
嘐懼桺傽湚嚟媙釂启 pg_restore 婔蕙嘪䫘麉傺昄扞康㔗垄傸噕螩 pg_restore 凹敵崉傔幽婩薪誕臯锬拷潡蔙䫔躿滇婘敵崉幋嬉凹驔襕敵崉䔇溇䕞誕臯麉桄毐废㔗嘐懼桺傽幘滇螆螇潊埇傖虘广埄䓂洉䔇㔗
套悩婔䓉唍锬桺傽湚嚟启 pg_restore 䂷劽闼幽 pg_dump 儌脘柊冕婔䓉䕕昂䔇嘐懼启嚹膷橺彽㔗pg_dump 埇傖䫘庯崺傘昘婻昄扞康䇽劯儌埇傖嘪䫘 pg_restore 演昖認婻嘐懼启/潡锬拷襕敵崉䔇昄扞康鄘彖㔗橔䕕昂䔇膷庺桺傽湚嚟滇"custom"(躻垔幬)湚嚟(-Fc)㔗垄噕螩凹嘐懼噄䘹誕臯锬埡启麉桄毐彖幽婫䚺䩕施滇寋䚷䔇㔗tar 湚嚟(-Ft)婉滇寋䚷䔇幽婫婘媹蘘䔇施唍婉埇脘麉毐彖婉誺垄幘冽䕕昂誻橬垄埇傖䫘湺庖 Unix 噖噙(懫套 tar)誕臯崇䊖㔗
婘誊臯 pg_dump 䔇施唍庫臖演昖膷庺䩋䩋滇劥橬傂嘘躥只庻婘(婘湺庖髍臇婪欷剄)䬹彆滇婋麵彖庺䔇鍊彽㔗
婋麵䔇变傴臯埗昄毓彽膷庺䔇喙垹启湚嚟㔗
儖襕蘸嗘䔇昄扞康劉㔗套悩澇橬弄滯認婻埗昄闼幽嘪䫘䯇嵄埻麟 PGDATABASE 㔗套悩闼婻䯇嵄埻麟幘澇弄滯闼幽嘪䫘埏蕙誂毖䔇䫘潙劉㔗
埻膷庺昄扞婉膷庺昇嚟(昄扞垔幬)㔗
認婻锬釹埻滇凹亇桺橸湚嚟橬懟幬㔗凹庯嘐懼湚嚟埇傖婘脄䫘 pg_restore 䔇施唍弄滯臖锬釹㔗
婘蘸嗘婺寙劆崓凹茇㔗鍴麂毺垔庖 --schema, --table, --schema-only 嚔噿劥彍認滇邻螴臯婺㔗啹溴 -b 嚔噿備䫘庯婘锬拷攓蘸嗘䔇施唍時媹崓凹茇㔗
膷庺婘录傺昄扞康录傺变傴幋嬉噽橙䊖(drop)臖昄扞康凹茇䔇变傴㔗
認婻锬釹埻滇凹亇桺橸湚嚟橬懟幬㔗凹庯嘐懼湚嚟埇傖婘脄䫘 pg_restore 䔇施唍弄滯臖锬釹㔗
傖婔溇录傺臖昄扞康橸躆幽婫婯認婻昄扞康誂毖变傴嚔崘誕臯膷庺㔗套悩滇認䓉嘵嚟䔇臔橸闼幽嘹婘誊臯臔橸幋嬉启巻婻昄扞康誂毖儌婉麉襕庖㔗
認婻锬釹埻凹亇桺橸湚嚟橬懟幬㔗凹庯嘐懼湚嚟埇傖婘脄䫘 pg_restore 䔇施唍弄滯臖锬釹㔗
儖昄扞膷庺婺䔇 INSERT 变傴(蔯婉滇 COPY)㔗認湙嚔凚躘敵崉麂婩䚷晵㔗認婻锬釹婂襕䫘庯彽嘩闼䓉埇傖䫘庯噽垄麂 PostgreSQL 昄扞康䔇蘸嗘㔗䫌庯認婻锬釹婺懟溇螄嘘鄘䫘潊婔溇变傴啹溴套悩噽婺昊婔臯变傴庺髍闼幽儖備橬臖臯昄扞婵崌蔯婉滇昘婻臘䔇昄扞婵崌㔗臙濘懟套悩嘹麉桄毐彖庖庖枕釺废闼幽敵崉埇脘嚔垯噘崌蘖㔗-D 敘垬噘嘖滇幘敘晵㔗
檪昄扞蘸嗘婺婥橬滯䇞庖枕劉䔇 INSERT 变傴(INSERT INTO table (column, ...) VALUES ...)㔗認湙嚔凚躘敵崉麂婩䚷晵垄婂襕䫘庯彽嘩闼䓉埇傖䫘庯噽垄麂 PostgreSQL 昄扞康䔇蘸嗘㔗䫌庯認婻锬釹婺懟溇螄嘘鄘䫘潊婔溇变傴啹溴套悩噽婺昊婔臯变傴庺髍闼幽儖備橬臖臯昄扞婵崌蔯婉滇昘婻臘䔇昄扞婵崌㔗
傖毺垔䔇庖严镖䚡乕录傺蘸嗘㔗䚺䩕施蘸嗘滇毬䙓昄扞康䚡乕录傺䔇㔗埥崡婔婻诙埡劯湙䂷悩䔇桹濘滇儖 PGCLIENTENCODING 䯇嵄埻麟螆䘞婺橘橕䔇蘸嗘䚡乕㔗
檪膷庺埏冔毺垔䔇桺傽㔗套悩媘䘖彍嘪䫘湺庖膷庺㔗
锬拷膷庺䔇湚嚟㔗format 埇傖滇婋彖幋婔
亇桺橸 SQL 臔橸桺傽(䚺䩕)
锗劽膷噖彄 pg_restore 麯䔇躻垔幬嘐懼㔗認滇橔䕕昂䔇湚嚟垄噕螩凹媹蘘䔇昄扞启凹茇垔幬誕臯麉桄毐彖㔗認婻湚嚟䚺䩕䔇施唍滇寋䚷䔇㔗
锗劽膷噖彄 pg_restore 麯䔇 tar 嘐懼桺傽㔗嘪䫘認婻嘐懼噕螩婘敵崉昄扞康施麉桄毐废启/潡檪昄扞康凹茇毐鍴婘崡㔗劯施幘埇傖婘敵崉䔇施唍鍊彽凹巻底昄扞誕臯敵崉㔗
媘䘖婘 pg_dump 启昄扞康橉媇単幋閘䔇䬽橸噞彆㔗
pg_dump 埇傖崇䊖準躻傖嬉䬽橸䔇 PostgreSQL 昄扞康蘸嗘嘖滇崻蔕䔇䬽橸彍婉赆櫇毕庖(䕞嬉滇櫇毕彄 7.0)㔗嘖滇 pg_dump 凹庯準躻敘桄䬽橸䔇蘸嗘彍方濘崇䊖㔗備婘嘹驔襕虿誺䬽橸演昖施欉嘪䫘認婻锬釹(蔯婫套悩 pg_dump 崌昽彆臘潏澇躥只誺嘹)㔗
埻蘸嗘对陉 schema 䔇昇嚟喙垹寙拸昇嚟橸躆傖埪噽婺寙劆䔇凹茇㔗套悩澇橬弄滯認婻锬釹欔橬䕞湺昄扞康婺䔇麂係䂘昇嚟鄘嚔赆蘸嗘庺準㔗埇傖嘪䫘崔婻 -n 锬釹毺垔崔婻昇嚟㔗劯湙schema 埗昄儖毬䙓 psql 䔇 \d 变傴䔇蓇彍(埗蓕对陉昇嚟)赆蓼麪婺对陉昇嚟啹溴埇傖嘪䫘锔陉严对陉崔婻昇嚟㔗婘嘪䫘锔陉严䔇施唍橔喘䫘嚘埙誕臯䘯垔傖黾溵 shell 儖锔陉严誕臯欷匘㔗
㔊濘懟㔏套悩毺垔庖 -n 闼幽 pg_dump 儖婉嚔蘸嗘闼底昇嚟欔冺蕡䔇噽傡昄扞康凹茇㔗啹溴方濘媺臕蘸嗘庺準䔇喙垹婔垔脘崘婘埥婔婻幾应䔇昄扞康婺敵崉潊媘㔗
㔊濘懟㔏麂昇嚟凹茇(懫套崓凹茇)婉嚔婘毺垔 -n 䔇施唍赆蘸嗘庺準㔗嘹埇傖嘪䫘 --blobs 滯䇞襕挗蘸嗘崓凹茇㔗
婉蘸嗘傂嘘对陉 schema 䔇昇嚟喙垹㔗昇嚟对陉蓇彍婯 -n 垯噘䕩劯㔗埇傖毺垔崔婻 -N 傖毐鍴崔䓉对陉䔇昇嚟㔗
套悩劯施毺垔庖 -n 启 -N 闼幽儖埻蘸嗘对陉 -n 嘖婉对陉 -N 䔇昇嚟㔗套悩庺䯄 -N 嘖滇婉庺䯄 -n 闼幽对陉 -N 䔇昇嚟儖婉嚔赆蘸嗘㔗
嘩婺昄扞䔇婔鄘彖婺懟婻臘鄘膷庺凹茇湺臖(OID)㔗套悩嘹䔇庫䫘驔襕 OID 庖枕䔇臺(懫套婘崡髞亥溘婺䫘彄)闼幽嘪䫘認婻锬釹㔗劥彍婉庫臖嘪䫘認婻锬釹㔗
婉檪凹茇䔇欔橬溄螆䘞婺凹庫溊昄扞康㔗pg_dump 邻螴埏庺 ALTER OWNER 潡 SET SESSION AUTHORIZATION 臺埖傖螆䘞录傺䔇昄扞康凹茇䔇欔橬溄㔗套悩認底臔橸儖準澇橬赆轙亓䫘潙(潡蔙拖橬臔橸婺噘鄘凹茇䔇䫘潙)誊臯䔇臺儖嚔崌蘖㔗-O 锬釹儌滇婺庖螷臖臔橸埇傖赆傂嘘䫘潙敵崉幽婫儖臔橸婺凹茇䔇欔橬溄蕋庽臖锬釹毺垔䔇䫘潙㔗
認婻锬釹埻滇凹亇桺橸湚嚟橬懟幬㔗凹庯嘐懼湚嚟婘脄䫘 pg_restore 䔇施唍埇傖弄滯臖锬釹㔗
認婻锬釹噾䂟誺施嘖滇庺庯劏婋噚垹䔇蔄荏傉䇽毖埖認婻锬釹㔗
埻膷庺凹茇垔幬(昇嚟)婉膷庺昄扞㔗
毺垔噿閺蓥埏単施驔襕䫘彄䔇轙亓䫘潙劉㔗垄埻橬嘪䫘庖 --disable-triggers 䔇施唍欉橬嘌巉㔗婔轸愙喕婋橔喘婉襕膷噖認婻埗昄蔯滇䫘轙亓䫘潙劇媘䫘潊䔇臔橸㔗
埻蘸嗘庺对陉 table 䔇臘㔕蓖商㔕废彖㔗埇傖嘪䫘崔婻 -t 锬釹对陉崔婻臘㔗劯湙table 埗昄儖毬䙓 psql 䔇 \d 变傴䔇蓇彍(埗蓕对陉昇嚟)赆蓼麪婺对陉昇嚟啹溴埇傖嘪䫘锔陉严对陉崔婻昇嚟㔗婘嘪䫘锔陉严䔇施唍橔喘䫘嚘埙誕臯䘯垔傖黾溵 shell 儖锔陉严誕臯欷匘㔗
嘪䫘庖 -t 幋劯-n 启 -N 锬釹儌崌昽庖㔗啹婺赆 -t 锬婺䔇臘儖方蓖 -n 启 -N 锬釹蔯赆蘸嗘劯施鍴庖臘幋崡䔇噽傡凹茇婉嚔赆蘸嗘㔗
㔊濘懟㔏套悩毺垔庖 -t 闼幽 pg_dump 儖婉嚔蘸嗘傂嘘锬婺䔇臘婉冺蕡䔇噽垄昄扞康凹茇㔗啹溴方濘媺臕蘸嗘庺準䔇臘脘婘婔婻幾应䔇昄扞康婺潊媘敵崉㔗
㔊濘懟㔏-t 锬釹婯 PostgreSQL 8.2 幋嬉䔇䬽橸婉噚垹㔗幋嬉䔇 -t tab 儖蘸嗘欔橬劉婺 tab 䔇臘嘖滇䯄婘埻蘸嗘婘邻螴抩䘵虇冇婺埇蓕䔇臘㔗喍潊 -t '*.tab' 儖京備庯蔕䬽橸䔇臯婺㔗劯湙嘹媙釂䫘 -t sch.tab 蔯婉滇蔕䬽橸䔇 -n sch -t tab 锬拷䬹垔昇嚟婺䔇臘㔗
婉襕蘸嗘傂嘘对陉 table 昇嚟䔇臘㔗昇嚟对陉蓇彍婯 -t 垯噘䕩劯㔗埇傖毺垔崔婻 -T 傖毐鍴崔䓉对陉䔇臘㔗
套悩劯施毺垔庖 -t 启 -T 闼幽儖埻蘸嗘对陉 -t 嘖婉对陉 -T 䔇臘㔗套悩庺䯄 -T 嘖滇婉庺䯄 -t 闼幽对陉 -T 䔇臘儖婉嚔赆蘸嗘㔗
毺垔喖嘍昇嚟㔗認湙儖傴 pg_dump 膷庺臥䂖䔇凹茇臇濘傖埪蘸嗘桺傽䔇劇啩施閘启誕庥媇敇彄湺庖髍臇婪㔗
䥕溵蘸嗘螪閞溄鍊(grant/revoke 变傴)
認婻锬釹噿閺嘪䫘䆯噄严䘯垔庘昄嘷㔗嚺彽垄傸䫘 SQL 湺庖䔇庖严婾臺濘䔇嚘埙寙啘㔗
認婻锬釹埻滇启录傺備橬昄扞䔇蘸嗘䕩噿㔗垄只臬 pg_dump 寙劆婘敵崉昄扞施婘施噿閺䕞湺臘婪蓥埏単䔇变傴㔗套悩婘臘婪橬埗䙓垯昘攓演昖潡蔙噽垄蓥埏単蔯敵崉昄扞䔇施唍婉愿麉蘘傡傸闼幽儌庫臖嘪䫘認婻锬釹㔗
䕞嬉婺 --disable-triggers 埏庺䔇变傴媙釂傖轙亓䫘潙準欓臯㔗啹溴嘹庫臖劯施䫘 -S 弄滯婔婻轙亓䫘潙劉潡蔙橔喘滇䫘婔婻轙亓䫘潙䔇躆傘準劇媘認婻䫘潊䔇臔橸㔗
認婻锬釹埻凹亇桺橸湚嚟橬懟幬㔗凹庯嘐懼湚嚟埇傖婘脄䫘 pg_restore 䔇施唍弄滯認婻锬釹㔗
膷庺严劽 SQL 湺庖䔇 SET SESSION AUTHORIZATION 变傴蔯婉滇 ALTER OWNER 变傴㔗認湙傴蘸嗘敘媹严劽湺庖嘖滇套悩蘸嗘桺傽婺䔇凹茇䔇寖埾橬底閞鵻闼幽埇脘婉脘溼䇞敵崉㔗幽婫嘪䫘 SET SESSION AUTHORIZATION 䔇蘸嗘驔襕昄扞康轙亓䫘潙䔇溄鍊欉脘蘸嗘潊媘蔯 ALTER OWNER 驔襕䔇溄鍊彍嘯冖崔㔗
弄滯婘闼底櫇毕寋䚷䔇湚嚟婺嘪䫘䔇寋䚷亓彆㔗䕞嬉埻橬躻垔幬湚嚟櫇毕寋䚷㔗
婋麵䔇变傴臯埗昄毓彽昄扞康䔇誂毖埗昄㔗
毺垔誊臯橉媇単䔇婂橺劉㔗套悩昄唚傖桩溹嚔崘彍赆䫘嘩彄 Unix 嘘喖毖庖䔇虇冇㔗䚺䩕傯 PGHOST 䯇嵄埻麟婺诙埡(套悩螆䘞庖䔇臺)劥彍儺臘婔婻 Unix 嘘喖毖庖誂毖㔗
毺垔橉媇単溼婘冥劸䔇 TCP 䆇埼潡橸婄 Unix 嘘喖毖庖桺傽䔇欷匘(柟誄严)㔗䚺䩕嘪䫘 PGPORT 䯇嵄埻麟(套悩螆䘞庖䔇臺)劥彍䚡臏施䔇䚺䩕唚㔗
誂毖䔇䫘潙劉
嚺彽埼傴柊䴺㔗套悩橉媇単驔襕埼傴螴臕闼幽認婻媘嘩庫臖躻媘埏䫘㔗
套悩嘹䔇昄扞康䂍 template1 昄扞康嵂媹庖傂嘘嘹躻噌䔇婩薪闼幽臙濘懟檪 pg_dump 䔇膷庺敵崉彄婔婻䩘溼䷺䔇昄扞康婺劥彍嘹埇脘嚔櫽彄啹婺麉崉垔幬欔誘媹䔇凹茇蔯锹潊䔇髍臇媇敇㔗襕彽嘩婔婻澇橬傂嘘橸婄鍇匂䬷䔇昄扞康埇傖傯 template0 蔯婉滇 template1 拙蘺懫套
CREATE DATABASE foo WITH TEMPLATE template0;
pg_dump 橬婔底鍊彽
婘誕臯亇昄扞蘸嗘幽婫嘪䫘庖锬釹 --disable-triggers 䔇施唍pg_dump 埏庺婔底昖臵噽噿閺䫘潙臘婪䔇蓥埏単䇽劯某噖昄扞某噖垯潊劯喉欷嚔蓥埏単㔗套悩敵崉媘嘩婘婺閘啩溵闼幽係䂘臘埇脘儌嚔崇庯婔䓉髍臇䪽攕㔗
tar 嘐懼䔇潊叻䔇崓償鍊彽庯 8 GB 㔗認滇 tar 桺傽湚嚟䔇啺橬鍊彽㔗啹溴認婻湚嚟方濘䫘庯傂嘘崓償轙誺認婻儺凩䔇臘㔗tar 嘐懼启傂嘘噽垄膷庺湚嚟䔇攂崓償滇婉埖鍊彽䔇埻滇埇脘嚔橬淉嘩係䂘䔇鍊彽㔗
pg_dump 䫘潊䔇蘸嗘桺傽幽婉寙劆嚻寡単䫘庯昖臵蓇彐喿亡䔇䂘螇媇敇㔗啹溴敵崉垯幋劯傺螞婘懟婻噾敵崉䔇凹茇婪誊臯 ANALYZE 傖媺臕謇喘䔇攓脘㔗
啹婺 pg_dump 婩䫘庯劏桄䬽橸䔇 PostgreSQL 婺嚹锐昄扞欔傖 pg_dump 䔇膷庺埇傖赆凚噖彄桄 PostgreSQL 昄扞康婺㔗劯施 pg_dump 誻脘臂埡斓 PostgreSQL 昄扞康䔇喙垹嘖滇婔轸方濘臂埡敘桄䔇 PostgreSQL 昄扞康劯施幘婉脘儖桄昄扞康婺蘸嗘庺準䔇喙垹敵崉彄斓䬽橸婺(䇸襕認湙啔埇脘驔襕欋媘媞櫹蘸嗘䂷悩)㔗
儖 mydb 昄扞康蘸嗘彄婔婻 SQL 臔橸桺傽
$ pg_dump mydb > db.sql
儖婪誄臔橸凚噖婔婻(桄傺䔇)昄扞康 newdb
$ psql -d newdb -f db.sql
儖昄扞康蘸嗘婺躻垔幬湚嚟䔇嘐懼桺傽
$ pg_dump -Fc mydb > db.dump
儖婪誄嘐懼桺傽凚噖婔婻(桄傺䔇)昄扞康 newdb
$ pg_restore -d newdb db.dump
蘸嗘婔婻劉婺 mytab 䔇臘
$ pg_dump -t mytab mydb > db.sql
蘸嗘 detroit 昇嚟婺欔橬傖 emp 嚔崘䔇臘嘖滇婉寙拸 employee_log 臘
$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql
蘸嗘欔橬傖 east 潡 west 嚔崘幽傖 gsm 䂷儆䔇昇嚟嘖滇婉寙拸劉庖婺劆橬 test 䔇昇嚟
$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql
劯婪婉誺認婔渇嘪䫘溼彍臘膆嚟䔇桹濘
$ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql
蘸嗘欔橬昄扞康凹茇嘖滇婉寙拸劉庖傖 ts_ 嚔崘䔇臘
$ pg_dump -T 'ts_*' mydb > db.sql
婘 -t 京锬釹婺毺垔崓喍庖應潡崓償喍晙劽䔇劉庖媙釂䫘埯嚘埙䘯垔劥彍儖赆躻媘蘸扵婺償喍(埗蓕昇嚟对陉)㔗嘖滇啹婺埯嚘埙婘 shell 婺橬䬹枪劆幬欔傖媙釂儖埯嚘埙喉櫆誕剘嚘埙婺㔗認湙婔準襕蘸嗘婔婻崓償喍晙劽䔇臘劉嘹儌驔襕償婋麵認湙
$ pg_dump -t '"MixedCaseName"' mydb > mytab.sql