SQL 蘸嗘䔇桹濘滇录傺婔婻桺橸桺傽麯麵鄘滇 SQL 变傴嘷檪認婻桺傽啂饽䂍橉媇単施儖麉傺婯蘸嗘施䪽攕婔湙䔇昄扞康㔗PostgreSQL 婺認婻䫘锫柊冕庖 pg_dump 噖噙㔗認溇变傴䔇嘺橸䫘濘滇
pg_dump dbname > outfile
溼套嘹欔蓕pg_dump 檪䂷悩膷庺彄湺庖膷庺㔗潏傸婋麵儌埇傖䩋彄認湙啔橬傔幽喘崇㔗
pg_dump 滇婔婻捞锔䔇 PostgreSQL 垵潙䆇庫䫘(儘䞇滇婻䕩嘷蕻滯䔇婩薪)㔗認儌懟叿五嘹埇傖傯傂嘘埇傖螪閞臖昄扞康䔇誩䆇婂橺婪麵誕臯崺傘噖嘩㔗嘖滇臙螄嘟 pg_dump 婉嚔傖傂嘘䬹枪溄鍊誊臯㔗噙嘷臘準儌滇垄媙釂襕橬嘹愿崺傘䔇臘䔇臂溄鍊啹溴垂鍙婪嘹庹幯攂滇襕潊婺昄扞康轙亓䫘潙㔗
襕弄滯 pg_dump 庫臖傖巻婻䫘潙躆傘誕臯誂毖嘪䫘变傴臯锬釹 -h host 启 -p port 㔗䚺䩕婂橺滇橸婄婂橺潡䯇嵄埻麟 PGHOST 弄滯䔇唚㔗䌂嚚䔇䚺䩕䆇埼滇䯇嵄埻麟 PGPORT 潡(套悩垄婉庻婘䔇臺)䚡臏喘庖䔇䚺䩕唚㔗橉媇単锔婩鄘橬䕩劯䔇䚺䩕欔傖誻䞖桹冪㔗
启傂嘘噽垄 PostgreSQL 垵潙䆇庫䫘婔湙pg_dump 䚺䩕施䫘婯嘷嬉淉嘩係䂘䫘潙劉劯劉䔇昄扞康䫘潙劉誕臯誂毖㔗襕襖䕡認婻劉庖襕幽弄滯 -U 锬釹襕幽螆䘞䯇嵄埻麟 PGUSER 㔗臙濘懟 pg_dump 䔇誂毖幘启捞锔垵潙庫䫘婔湙襕锔誺垵潙螴臕橺彽(婘䆹20麯柟誄)㔗
䫌 pg_dump 录傺䔇崺傘婘喙鄘滇婔躘䔇幘儌滇臘婘 pg_dump 誊臯䔇施唍凹昄扞康䔇敘桄儖婉嚔赆蘸嗘㔗pg_dump 噖嘩䔇施唍幽婉黂凂噽垄䔇凹昄扞康䔇淉嘩(嘖滇嚔黂凂闼底驔襕毐垄髕䔇淉嘩懫套 VACUUM FULL)㔗
㔊麉襕㔏套悩嘹䔇昄扞康䂷悇冺蕡庯 OID(懫套臘䫘啔崡髞)闼幽嘹媙釂只臬 pg_dump 檪 OID 幘凚庺準㔗襕凚庺 OID 埇傖嘪䫘 -o 变傴臯锬釹㔗
pg_dump 䫘潊䔇桺橸桺傽埇傖䫌 psql 䘋废臂埡㔗傯蘸嗘婺敵崉䔇婩䫘变傴滇
psql dbname < infile
認麯䔇 infile 儌滇嘹䂍 pg_dump 变傴䔇 outfile 埗昄㔗認溇变傴婉嚔录傺 dbname 昄扞康嘹媙釂婘欓臯 psql 嬉躻噌傯 template0 录傺(幘儌滇䫘 createdb -T template0 dbname变傴)㔗psql 櫇毕䌂嚚 pg_dump 䔇锬釹䫘傖毓彽昄扞康橉媇単嘉䘞启䫘潙劉㔗埗黙 psql 䔇欋喯诙埡敘崔媇敇㔗
婘嚔哋誊臯敵崉幋嬉䕞湺康启欔橬婘蘸嗘庺準䔇康婺拖橬凹茇䔇䫘潙傖埪敆䂟婘昊底凹茇婪赆蕋庽溄鍊䔇䫘潙鄘媙釂噾䂟庻婘㔗套悩認底婉庻婘闼幽敵崉儖崌蘖啹婺敵崉誺䘋方濘檪認底凹茇敵崉潊寘橬䔇欔橬溄启/潡溄鍊㔗橬施唍嘹婯橕敵崉溄鍊婉誺锔婩嘹婉驔襕認幽啔㔗
䚺䩕施psql 臔橸儖婘镺彄髍臇䔇施唍傉䇽䂓䂺欓臯㔗嘹埇脘婯橕婘臔橸䔇橔嬉麵嘪䫘婋麵䔇变傴準媺臕婘镺蓕髍臇䔇施唍锔庺 psql 幽誫啂䪽攕乕 3 㔗
\set ON_ERROR_STOP
婉䞇婪誄巻䓉桹濘鄘埻脘冖彄鄘彖敵崉庖䔇昄扞康㔗埥崡嘹埇傖儖昘婻敵崉誺䘋嘷潊婔婻剘䋸䔇庋媇認湙儌脘崘媺臕襕幽噘鄘敵崉潊媘襕幽噘鄘啂悔㔗埇傖锔誺劏 psql 嚹锐 -1 潡 --single-transaction 变傴臯埗昄膆彄溴䕞䔇㔗嘪䫘認婻昇嚟䔇施唍剿嘪婔婻冽冞償䔇髍臇幘儖凚躘噾䂟誊臯庖喘庹婻償施䔇敵崉誺䘋啂悔㔗儘䞇套溴認䓉昇嚟幘懫欋媘橙鍴巻底婉垯昘䔇敵崉昄扞嚺㔗
pg_dump 启 psql 埇傖锔誺䞇長臂喍認湙潏傸儌埇脘傯婔埄婂橺婪儖昄扞康䕞嘘蘸嗘彄埥婔埄婂橺婪懫套
pg_dump -h host1 dbname | psql -h host2 dbname
㔊麉襕㔏pg_dump 䫘潊䔇蘸嗘膷庺滇䕩凹庯 template0 䔇㔗認儌懟叿五傂嘘媹噖彄 template1 䔇臺蘔㔕誺䘋京鄘嚔䂟䫌 pg_dump 蘸嗘㔗認湙婘敵崉䔇施唍套悩嘹嘪䫘䔇滇躻垔幬䔇 template1 闼幽嘹媙釂傯 template0 婺录傺䷺䔇昄扞康儌償潏傸婪麵䔇冋床闼湙㔗
婔斥垯潊敵崉婘懟婻昄扞康婪誊臯 ANALYZE 滇滯捺䔇婆媘認湙嚻寡単儌橬埇䫘䔇䂘螇昄扞庖㔗vacuumdb -a -z 京昽庯欋噖誊臯 VACUUM ANALYZE 㔗噿庯套嘘橬昽媹蘘教麟昄扞䔇媇敇埗蔄誗13.4㔗
pg_dump 婪麵䔇桹濘婘崺傘昘婻昄扞康镖䆴䔇施唍懫膄麂䄥蔯婫婉桹冪㔗啹溴潏傸柊冕庖 pg_dumpall 䘋废㔗pg_dumpall 崺傘婔婻䂍庺䔇镖䆴婺䔇懟婻昄扞康劯施誻䇞媺媺䘍償蓐謾启臘䷺閘認湙䔇噘匔昄扞䪽攕㔗認婻变傴䔇嘺橸䫘濘滇
pg_dumpall > outfile
䫘潊䔇蘸嗘埇傖䫘 psql 敵崉
psql -f infile postgres
垂鍙婪嘹埇傖弄滯傂懟䯄橬䔇昄扞康誕臯誂毖嘖滇套悩嘹滇劏婔婻䷺䔇昄扞康镖䆴輙蘘闼幽 postgres 庫臖滇懫膄喘䔇锬拷㔗敵崉 pg_dumpall 䔇蘸嗘䔇施唍锔婩驔襕昄扞康轙亓䫘潙溄鍊啹婺潏傸驔襕垄準敵崉蓐謾启臘䷺閘媇敇㔗套悩嘪䫘庖臘䷺閘驔襕濘懟蘸嗘婺䔇臘䷺閘虇冇媙釂锗劽桄䔇垬輙㔗
啹婺 PostgreSQL 噕螩臘橔崓儺凩崓庯係䂘䔇橔崓桺傽儺凩啹溴䫘潊䔇桺傽儺凩冽埇脘轙誺係䂘鍊彽㔗啹婺 pg_dump 膷庺彄湺庖膷庺嘹埇傖䫘湺庖䔇 Unix 噖噙䂘嚔認婻閞鵻
嘪䫘寋䚷䔇蘸嗘㔗嘪䫘嘹䖘旬䔇寋䚷䘋废(懫套 gzip)
pg_dump dbname | gzip > filename.gz
䫘婋麵变傴敵崉
createdb dbname gunzip -c filename.gz | psql dbname
潡蔙
cat filename.gz | gunzip | psql dbname
嘪䫘 split 噖噙㔗 split 噕螩䫘婋麵䔇桹濘檪膷庺彖蓼潊淉嘩係䂘埇傖毖埖䔇崓償㔗懫套螷懟婻庖崓償婺 1MB
pg_dump dbname | split -b 1m - filename
䫘婋麵变傴敵崉
createdb dbname cat filename* | psql dbname
嘪䫘躻垔幬䔇蘸嗘湚嚟㔗套悩 PostgreSQL 滇婘婔婻垬輙庖 zlib 寋䚷康䔇係䂘婪彽嘩䔇闼幽躻垔幬蘸嗘湚嚟儖婘喍噖膷庺桺傽䔇施唍寋䚷昄扞㔗垄嚔䫘潊启嘪䫘 gzip 䌂嚚崓償䔇蘸嗘桺傽嘖滇誻鍇媹庖婔婻嚻䗹嘹埇傖橬锬拷婄敵崉康婺䔇臘㔗婋麵䔇变傴䫘躻垔幬蘸嗘湚嚟蘸嗘婔婻昄扞康
pg_dump -Fc dbname > filename
躻垔幬湚嚟䔇蘸嗘婉滇臔橸婉脘䫘庯 psql 蔯滇驔襕嘪䫘 pg_restore 蘸嗘㔗臙埗蔄 pg_dump 启 pg_restore 䔇欋喯诙埡䂖誗㔗