pg_restore 䫘庯敵崉䫌 pg_dump 蘸嗘䔇傂嘘麂亇桺橸湚嚟婺䔇 PostgreSQL 昄扞康㔗垄儖埏庺媙襕䔇变傴麉傺昄扞康幽檪垄敵崉潊蘸嗘施䔇湙床㔗嘐懼(崺傘)桺傽誻噕螩 pg_restore 橬锬拷婄誕臯敵崉䫔躿婘敵崉嬉麉桄毐彖溇䕞䔇釺废㔗嘐懼䔇桺傽螆螇潊埇傖婘婉劯䔇䇸傽嘷係幋閘䓂洉㔗
pg_restore 埇傖毬䙓婴䓉昇嚟淉嘩㔗套悩弄滯庖昄扞康劉庖闼幽嘐懼滇䕘毖敵崉彄昄扞康麯㔗劥彍噽录傺婔婻寙劆麉傺昄扞康欔媙釂䔇 SQL 变傴䔇臔橸幽婫喍噖彄婔婻桺傽潡蔙湺庖膷庺㔗京昽庯 pg_dump 膷庺亇桺橸湚嚟䔇施唍录傺䔇闼䓉臔橸㔗啹溴婔底毓彽膷庺䔇锬釹儌滇昇拘 pg_dump 䔇锬釹螆䘞䔇㔗
滆䇽pg_restore 方濘敵崉闼底婉庻婘嘐懼桺傽婺䔇媇敇懫套套悩嘐懼滇䫘"檪昄扞蘸嗘婺 INSERT 变傴"锬釹彽嘩䔇闼幽 pg_restore 儖婉脘嘪䫘 COPY 臺埖媹蘘昄扞㔗
pg_restore 毖埖婋彖变傴臯埗昄
襕敵崉䔇崺傘桺傽䔇嘉䘞㔗套悩澇橬弄滯彍嘪䫘湺庖膷噖㔗
埻敵崉昄扞蔯婉敵崉臘昇嚟(昄扞垔幬)
录傺昄扞康凹茇嬉噽橙䊖(役鍴)垄傸
婘敵崉昄扞康幋嬉噽录傺垄㔗套悩庺䯄庖認婻锬釹启 -d 婘婔蕙䔇昄扞康劉埻滇䫘庯埏庺橔彺䔇 CREATE DATABASE 变傴㔗欔橬昄扞鄘敵崉彄劉庖庺䯄婘嘐懼婺䔇昄扞康婺寂㔗
婯昄扞康 dbname 誂毖幽婫䕘毖敵崉彄臖昄扞康婺㔗
套悩婘劏昄扞康埏锕 SQL 变傴䔇施唍䵄彄髍臇彍锔庺㔗䚺䩕滇䂓䂺欓臯幽婫婘敵崉䂷溘施滆䴺婔婻髍臇螇昄㔗
毺垔䫘潊䔇臔橸䔇膷庺桺傽潡蔙庺䯄 -l 锬釹施䫘庯彖臘䔇桺傽䚺䩕滇湺庖膷庺㔗
毺垔崺傘桺傽䔇湚嚟㔗啹婺 pg_restore 嚔躻媘彴桺湚嚟欔傖套悩婔垔襕毺垔䔇臺垄埇傖滇婋麵幋婔
崺傘滇婔婻 tar 嘐懼㔗嘪䫘認婻湚嚟噕螩婘敵崉昄扞康䔇施唍麉桄毐废启/潡檪臘昇嚟噄䘹毐鍴庺寂㔗劯施誻埇脘婘敵崉䔇施唍鍊彽媹蘘䔇昄扞㔗
崺傘䔇湚嚟滇準躻 pg_dump 䔇躻垔幬湚嚟㔗認滇橔䕕昂䔇湚嚟啹婺垄噕螩麉桄凹昄扞毐废幘噕螩麉蘘臘昇嚟噄䘹㔗䚺䩕施認婻湚嚟滇寋䚷䔇㔗
媘䘖昄扞康䬽橸演昖
埻敵崉变劉䔇䘵嚘
彖庺崺傘䔇喙垹㔗認婻淉嘩䔇膷庺埇傖䫘 -L 锬釹鍊彽启麉毐欔敵崉䔇釹䕞㔗
傖垄傸婘桺傽婺庺䯄䔇釺废埻敵崉婘 list-file 麯麵䔇噄䘹㔗嘹埇傖䓂媘劇婻臯幽婫幘埇傖锔誺婘臯嚔崘櫆䘞 ; 䔇桹嚟濘麪㔗冋床蓕婋桺㔗
埻敵崉毺垔劉庖䔇昇嚟麯麵䔇垔幬启/潡昄扞㔗認婻锬釹埇傖启 -t 锬釹婔蕙嘪䫘垂䯄埻蘸嗘婔婻臘䔇昄扞㔗
婉襕膷庺螆䘞婯橔彺昄扞康凹茇溄鍊对陉䔇变傴㔗䚺䩕施pg_restore 埏庺 ALTER OWNER 潡 SET SESSION AUTHORIZATION 臺埖螆䘞录傺庺準䔇昇嚟噄䘹䔇欔橬蔙溄鍊㔗套悩橔彺䔇昄扞康誂毖婉滇䫌轙亓䫘潙(潡蔙滇拖橬欔橬录傺庺準䔇凹茇䔇劯婔婻䫘潙)埏蕙䔇闼幽認底臺埖儖崌蘖㔗套悩嘪䫘 -O 闼幽傂嘘䫘潙鄘埇傖䫘庯彺哋䔇誂毖幽婫認婻䫘潙儖拖橬欔橬录傺庺準䔇凹茇㔗
埻敵崉毺垔䔇变劉庘昄㔗臙濘懟傫䂖拚喍庘昄劉埪噽埗昄庫臖启蘸嗘䔇喙垹彖臘婺䔇垯噘婔湙㔗
認婻锬釹噾䂟庘嚄庖嘖滇婺庖媺毕劏婋噚垹傉䇽毖埖㔗
埻敵崉臘䂷悇(昄扞垔幬)㔗婉敵崉昄扞(昄扞臘喙垹)㔗废彖䔇嘷嬉唚幘婉嚔冖彄敵崉㔗臙婉襕启 --schema 锬釹晙晖闼麯嘪䫘庖"昇嚟"(schema)䔇噽垄劆幬㔗
螆䘞噿閺蓥埏単施弄滯轙亓䫘潙䔇䫘潙劉㔗埻橬婘螆䘞庖 --disable-triggers 䔇施唍欉橬䫘㔗
埻敵崉毺垔䔇臘䔇垔幬启/潡昄扞㔗
埻敵崉毺垔䔇蓥埏単
弄滯喖嘍昇嚟
䥕溵敵崉螪閞溄鍊(grant/revoke 变傴)
認婻锬釹埻橬婘欓臯備敵崉昄扞䔇施唍欉䕩噿㔗垄只臬 pg_restore 婘媹蘘昄扞䔇施唍欓臯婔底变傴婘施噿閺婘䕞湺臘婪䔇蓥埏単㔗套悩嘹婘臘婪橬垯昘攓演昖潡蔙噽垄蓥埏単蔯嘹埽婉婯橕婘媹蘘昄扞䔇施唍檔昂垄傸闼幽埇傖嘪䫘認婻锬釹㔗
䕞嬉婺 --disable-triggers 埏庺䔇变傴媙釂傖轙亓䫘潙埏庺㔗啹溴嘹庫臖幘襕䫘 -S 弄滯婔婻轙亓䫘潙劉潡蔙敘喘滇傖轙亓䫘潙躆傘誊臯 pg_restore
膷庺 SQL 湺庖䔇 SET SESSION AUTHORIZATION 变傴蔯婉滇 ALTER OWNER 变傴㔗認湙傴蘸嗘婯湺庖噚垹䔇敘喘嘖滇湹扞蘸嗘婺凹茇䔇寖埾認婻蘸嗘埇脘婉脘敄嘷婄敵崉㔗
䚺䩕施剿嘪录傺臘䔇变傴啹婺臖臘噾䂟庻婘蔯崌蘖庖臘婺䔇昄扞傉儖赆敵崉㔗嘪䫘認婻锬釹幋劯認底臘䔇昄扞儌儖虿誺敵崉淉嘩㔗嘷䕞湺昄扞康埇脘噾䂟寙劆欔驔敵崉䔇昊底臘䔇喙垹施臖锬釹儌冽橬䫘崇庖㔗懫套䫘庯 PostgreSQL 欷匘䔇膙媷臘(冋套 PostGIS)儌埇脘噾䂟婘䕞湺昄扞康婺敵崉誺庖嘪䫘臖锬釹儌埇傖黾溵崔渇敵崉傖躘麉崉潡蔙襖䕡庖噾䂟敵崉䔇昄扞㔗
臖锬釹備婘䕘毖劏婔婻昄扞康婺敵崉䔇施唍橬昽婘䫘潊 SQL 臔橸膷庺施方昽㔗
pg_restore 誻毖埖婋麵䔇变傴臯埗昄啔婺誂毖埗昄
毺垔誊臯橉媇単䔇婂橺劉㔗套悩昄唚傖桩溹嚔崘彍赆䫘嘩彄 Unix 嘘喖毖庖䔇虇冇㔗䚺䩕傯 PGHOST 䯇嵄埻麟婺诙埡(套悩螆䘞庖䔇臺)劥彍儺臘婔婻 Unix 嘘喖毖庖誂毖㔗
毺垔橉媇単溼婘冥劸䔇 TCP 䆇埼潡橸婄 Unix 嘘喖毖庖桺傽䔇欷匘(柟誄严)㔗䚺䩕嘪䫘 PGPORT 䯇嵄埻麟(套悩螆䘞庖䔇臺)劥彍䚡臏施䔇䚺䩕唚㔗
誂毖䔇䫘潙劉
嚺彽埼傴柊䴺㔗套悩橉媇単驔襕埼傴螴臕闼幽認婻媘嘩庫臖躻媘埏䫘㔗
儖昘婻敵崉誺䘋嘩婺婔婻垯昘䔇庋媇準欓臯幘儌滇儖欔橬敵崉变傴櫆婘 BEGIN/COMMIT 幋閘㔗認儖媺臕敵崉襕幽噘鄘潊媘襕幽澇橬傂嘘嘌巉㔗臖锬釹锊劆 --exit-on-error
套悩嘹䔇垬輙䂍 template1 昄扞康嵂媹庖傂嘘嘹躻噌䔇婩薪闼幽臙濘懟檪 pg_restore 䔇膷庺敵崉彄婔婻䩘溼䷺䔇昄扞康婺劥彍嘹埇脘嚔櫽彄啹婺麉崉垔幬欔誘媹䔇凹茇蔯锹潊䔇髍臇媇敇㔗襕彽嘩婔婻澇橬傂嘘橸婄鍇匂䬷䔇昄扞康埇傖傯 template0 蔯婉滇 template1 拙蘺懫套
CREATE DATABASE foo WITH TEMPLATE template0;
pg_restore 䔇匔鍊套婋
嘷劏婔婻噾䂟庻婘䔇臘敵崉昄扞幽婫誻嘪䫘庖 --disable-triggers 锬釹施pg_restore 婘某噖昄扞嬉櫆庺婔底昖臵噿閺䫘潙臘婪䔇蓥埏単婘昄扞某噖垯潊劯麉桄欷嚔垄傸㔗套悩敵崉䔇婺锫啩溵闼幽係䂘臘埇脘崇庯髍臇䪽攕㔗
pg_restore 婉嚔婺剘婔䔇臘敵崉崓凹茇㔗套悩婔婻嘐懼寙劆崓凹茇闼幽欔橬崓凹茇鄘儖赆敵崉㔗
埗黙 pg_dump 䔇桺毇诙埡橬噿 pg_dump 䔇匔鍊䔇䂖誗㔗
婔斥垯潊敵崉橔喘婘懟婻敵崉䔇凹茇婪誊臯 ANALYZE 傖冪䂍嚻寡単橬䫘䔇䂘螇㔗
啺垔潏傸噾䂟蘸嗘庖 mydb 昄扞康彄婔婻躻垔幬湚嚟䔇桺傽婺
$ pg_dump -Fc mydb > db.dump
役鍴臖昄扞康幽傯蘸嗘婺麉傺
$ dropdb mydb $ pg_restore -C -d postgres db.dump
婘 -d 婺毺垔䔇昄扞康埇傖滇嘷嬉镖䆴婺䔇傂懟昄扞康pg_restore 備䫘臖劉庖準婺 mydb 埏庺 CREATE DATABASE 变傴㔗嘪䫘 -C 埇傖䇞媺昄扞攂滇嚔赆敵崉彄蘸嗘桺傽婺毺垔劉庖䔇昄扞康麯麵㔗
儖蘸嗘庺準䔇昄扞麉桄媹蘘彄婔婻桄傺䔇昄扞康 newdb 婺
$ createdb -T template0 newdb $ pg_restore -d newdb db.dump
濘懟認麯澇橬嘪䫘 -C 锬釹蔯滇䕘毖鷆毖彄儖襕敵崉䔇昄扞康婪㔗誻襕濘懟䔇滇潏傸傯 template0 蔯婉滇 template1 录傺庖桄昄扞康婔䇞媺幾应㔗
襕凹釹䕞麉桄毐废饡噽媙釂蘸嗘嘐懼䔇䕞嘘
$ pg_restore -l db.dump > db.list
認婻桺傽䫌婔臯崘启懟婻溇䕞婔臯䂇潊懫套㔗
; ; Archive created at Fri Jul 28 22:28:36 2000 ; dbname: mydb ; TOC Entries: 74 ; Compression: 0 ; Dump Version: 1.4-0 ; Format: CUSTOM ; ; ; Selected TOC Entries: ; 2; 145344 TABLE species postgres 3; 145344 ACL species 4; 145359 TABLE nt_header postgres 5; 145359 ACL nt_header 6; 145402 TABLE species_records postgres 7; 145402 ACL species_records 8; 145416 TABLE ss_old postgres 9; 145416 ACL ss_old 10; 145433 TABLE map_resolutions postgres 11; 145433 ACL map_resolutions 12; 145443 TABLE hs_old postgres 13; 145443 ACL hs_old
認麯彖埙滇濘麪彖锫严蔯臯嚔崘䔇昄庖傼臘蕋䂍懟婻釹䕞䔇喙鄘嘐懼 ID 㔗
桺傽喙䔇臯埇傖濘麪㔕役鍴启/潡麉桄毐彖㔗懫套
10; 145433 TABLE map_resolutions postgres ;2; 145344 TABLE species postgres ;4; 145359 TABLE nt_header postgres 6; 145402 TABLE species_records postgres ;8; 145416 TABLE ss_old postgres
埇傖䫘啔 pg_restore 䔇膷噖幽婫埻嚔敵崉釹䕞 10 启 6 (傖認婻釺废)
$ pg_restore -L db.list db.dump