開源數(shù)據(jù)庫postgresql 數(shù)據(jù)庫備份恢復(fù)的方法介紹

1、開源數(shù)據(jù)庫postgresql數(shù)據(jù)庫備份恢復(fù)的方法介紹 pg_dump的用法：數(shù)據(jù)庫的導(dǎo)入導(dǎo)出是最常用的功能之一，每種數(shù)據(jù)庫都提供有這方面的工具，例 如 Oracle 的 exp/imp， Informix 的 dbexp/dbimp， MySQL 的 mysqldump，而 PostgreSQL 提供的對應(yīng)工具為 pg_dump 和 pg_restore。pg_dump是用于備份PostgreSQL數(shù)據(jù)庫的工具。它可以在數(shù)據(jù)庫正在使用的時 候進行完整一致的備份，并不阻塞其它用戶對數(shù)據(jù)庫的訪問。轉(zhuǎn)儲格式可以是一個腳本或者歸檔文件。轉(zhuǎn)儲腳本的格式是純文本，包含許多 SQL命令，這些SQL命令可以

2、用于重建該數(shù)據(jù)庫并將之恢復(fù)到保存腳本時的狀態(tài)。 可以使用psql從這樣的腳本中恢復(fù)。它們甚至可以用于在其它機器甚至是其它 硬件體系的機器上重建數(shù)據(jù)庫，通過對腳本進行一些修改，甚至可以在其它SQL 數(shù)據(jù)庫產(chǎn)品上重建數(shù)據(jù)庫。歸檔文件格式必須和pg_restore 一起使用重建數(shù)據(jù)庫。它們允許pg_restore 對恢復(fù)什么東西進行選擇，甚至是在恢復(fù)之前對需要恢復(fù)的條目進行重新排序。 歸檔文件也是可以跨平臺移植的。D:Program FilesPowerCmdpg_dump -helppg_dump把一個數(shù)據(jù)庫轉(zhuǎn)儲為純文本文件或者是其它格式.數(shù)據(jù)庫名字用法：pg_dump 選項.一般選項：outpu

3、t file or directory nameoutput file format (custom, directory, tar,-f, -file=FILENAME-F, -format=c|d|t|pplain text）詳細模式被壓縮格式的壓縮級別-v, -verbose-Z, -compress=0-9 -lock-wait-timeout=TIMEOUT在等待表鎖超時后操作失敗help-versoin控制輸出內(nèi)容選項：-data-only-blobs-clean-create-encoding二ENCODING-schema=SCHEMA顯示此幫助信息，然后退出 輸出版本信息，然

4、后退出只轉(zhuǎn)儲數(shù)據(jù)，不包括模式在轉(zhuǎn)儲中包括大對象在重新創(chuàng)建之前，先清除（刪除）數(shù)據(jù)庫對象在轉(zhuǎn)儲中包括命令，以便創(chuàng)建數(shù)據(jù)庫轉(zhuǎn)儲以ENCODING形式編碼的數(shù)據(jù)只轉(zhuǎn)儲指定名稱的模式不轉(zhuǎn)儲已命名的模式在轉(zhuǎn)儲中包括 OID在明文格式中，忽略恢復(fù)對象所屬者只轉(zhuǎn)儲模式，不包括數(shù)據(jù)在轉(zhuǎn)儲中，指定的超級用戶名只轉(zhuǎn)儲指定名稱的表只轉(zhuǎn)儲指定名稱的表-a,-b,-c,-C,-E,-N, -exclude-schema=SCHEMA-o,-O,-s,-S,-t,-T,-x,-n,-oids-no-owner-schema-only-superuser=NAME-table=TABLE-exclude-table=TAB

5、LE-binary-upgrade-no-privileges不要轉(zhuǎn)儲權(quán)限 (grant/revoke)只能由升級工具使用-column-inserts-disable-dollar-quoting號-disable-triggers-inserts據(jù)-no-security-labels-no-tablespaces-no-unlogged-table-data-quote-all-identifierswords-serializable-deferrableanomalies-use-set-session-authorization使用SESSION AUTHORIZATION命令代替

6、ALTER OWNER命令來設(shè)置所有權(quán) 聯(lián)接選項：-h,-p,-U,-w,-W,-host=主機名-port 二端口號-username二名字-no-password-password-role=ROLENAME如果沒有提供數(shù)據(jù)庫名字，二、pg_dump的使用實例 1、創(chuàng)建兩個數(shù)據(jù)庫 CREATEWITH以帶有列名的INSERT命令形式轉(zhuǎn)儲數(shù)據(jù)取消美元（符號）引號，使用SQL標準引在只恢復(fù)數(shù)據(jù)的過程中禁用觸發(fā)器以INSERT命令，而不是COPY命令的形式轉(zhuǎn)儲數(shù)do not dump security label assignments不轉(zhuǎn)儲表空間分配信息do not dump unlogged

7、 table dataquote all identifiers, even if not keywait until the dump can run without數(shù)據(jù)庫服務(wù)器的主機名或套接字目錄 數(shù)據(jù)庫服務(wù)器的端口號以指定的數(shù)據(jù)庫用戶聯(lián)接永遠不提示輸入口令強制口令提示（自動）do SET ROLE before dump那么使用PGDATABASE環(huán)境變量的數(shù)值.DATABASE TestDb1OWNER = TestRole1ENCODING = UTF8TABLESPACE = TestTbs1；DATABASE TestDb2OWNER = TestRole1ENCODING =

8、UTF8TABLESPACE = TestTbs1；在 TestDb1 中創(chuàng)建表 csm_bill、cfg_public_int_transport 插入幾條記錄，并 創(chuàng)建索引，索引使用索引表空間TestTbsIndex。CREATEWITH2、僅遷移數(shù)據(jù)庫結(jié)構(gòu)：E:pg_dump -U TestRole1 -s -f TestDb1.sql TestDb1 口令：-U TestRole1和超級用戶-U postgres結(jié)果完全相同： E:pg_dump -U postgres -s -f TestDb11.sql TestDb1 E:psql -U TestRole2 -f TestDb1.

9、sql TestDb2 a.txt 2&1 用戶TestRole2的口令：導(dǎo)入時，使用-u TestRole2往往有很多權(quán)限不夠，要想成功導(dǎo)入需要修改相關(guān) 數(shù)據(jù)庫對象的屬主，所以最好使用超級用戶-U postgres：E:psql -U postgres -f TestDbl.sql TestDb2 a.txt 2&1不轉(zhuǎn)儲權(quán)限選項：-xE:pg_dump -U postgres -x -s -f TestDb12.sql TestDb1TestDb12.sql 比 TestDb1.sql 少了一下幾行: Name: public; Type: ACL;Schema:Owner: TestRo

10、lelPUBLIC；FROM nTestRoleln;ALL 01 SCHEMA publicRETWE ALL Cl- SCHEMA publicGRANT ALL CN SCHEMA public TO nTestRoleln； GRAWT ALL ON SCHEMA public TO PUBLIC；為了可以多次運行TestDb1.sql，可以在文件開始加以下兩行：drop schema public cascade;create schema public;或者使用-c選項：E:pg_dump -U postgres -c -x -s -f TestDb13.sql TestDb1Te

11、stDb13.sql 比 TestDb1.sql 多以下幾行：&二 searcli_patli = public, pg_catalog;E；?OPpublic . idx_int_transport;二二 : public . idx_csmbill;.LTEP. rJ;-.BLE ONLY public. csm_bill l? ：? CCN8T?_：:：T csmJ3ill_pkey; L . ONLY public . cf g_public_int_trarL3port ：.：* CC：；.： . c f g_public_int_transpor t_pk1 .0? TAEL： p

12、ublic . csm二bill;public. cf g_pLLblic_int_transport;EXTENSION plpgsqf；n? SCHEMA public；一一 Name: public; Type: SCHEMA; Schema:Owner: TestRolelSCHEMA public；： SCHEMA public OWER : TestRolelT,;此時，可以多次運行：E:psql -U postgres -f TestDb13.sql TestDb2 a.txt 2&1但是，如果兩個庫有不同的表或索引，應(yīng)該使用第一種方法，因為第二種方法在 找不到某些數(shù)據(jù)庫對象時會

13、報錯。3、遷移數(shù)據(jù)庫結(jié)構(gòu)和數(shù)據(jù)(可以實現(xiàn)數(shù)據(jù)庫的備份與恢復(fù))數(shù)據(jù)的復(fù)制使用copy命令：E:pg_dump -U postgres TestDb1TestDb14.sql一一 Data for Name: cfg_public_int_transport; Type: TABLE DATA; Schema: public; Owner: TestRoCOPY cfg_priblic_int_transport (data_sernQ, transporcode/ transport_namef transport_mode,3plane3 4carrier route33 123 456 3I

14、44plane4 4carrier route33 456 123 3I一一 Data for Name: csm bill; Type: TABLE DATA; Schema: public; Owner: TestRolelCOPY csm_biil (bill_sern.Q, bilorg, bill_dstf mailategory, bill_n.Qf dom_intf do_office,zhj fortestbilll1236543C1001D0220013offloadepdirtruck201zhj fortestbi2222226543C10 01D0220013offlo

15、adepdirtruck2 01zhj fortestbi3333336543C1001D0220013offloadepdirtruck201數(shù)據(jù)的復(fù)制使用insert語句：E:pg_dump -U postgres -column-inserts TestDb1TestDb15.sqlTABLE DAA; Schema: public; Owner: TestRoJtransport_CQdejr transportamejr transport transport code, transport name, transport- Data for Name: cfg public int

16、 transport; Type:INSERTcfg_public_int_transport (aata_serrLOjrINSERTcfg_public_int_transpQrt (dataernoj一一 Data for Name:Type: TABLE DATA; Schema: public; Owner: TestRolelINSERT csm_billINSERT 7csm_billINSERT 於二 csmjziill(bill_seriiof_serrLOf(bill_sernofbill_orgf bill_dstfbill_Qrgf bill_dstfbill_org

17、抵11_日5頓mailategoryy billOjmail_categoryf billOjmailategoryy bill_nOjrdom_inty do_of dom_in tf do_of dom int, do of4、把遠程linux上PostgreSQL上的cpost數(shù)據(jù)庫結(jié)構(gòu)遷移至本地PostgreSQL(1)在本地建一個完全相同的環(huán)境create user cpost inherit createdb;create tablespace pis_data owner cpost locationE:PostgreSQL/data/pis_data;create tables

18、pace pis_index owner cpost locationE:PostgreSQL/data/pis_index;遠程數(shù)據(jù)庫cpost仍使用了默認表空間：CREATE DATABASE cpostWITH OWNER = cpost-ENCODING = LATIN9TABLESPACE = pg_default-LC_COLLATE = C-LC_CTYPE = CCONNECTION LIMIT = -1;使用以上三個參數(shù)報錯，建成后的數(shù)據(jù)庫如下：CREATE DATABASE cpostWITH OWNER = cpostENCODING = UTF8TABLESPACE =

19、 pg_defaultLC_COLLATE = Chinese (Simplified)_Peoples Republic ofChina.936LC_CTYPE = Chinese (Simplified)_Peoples Republic of China.936CONNECTION LIMIT = -1;(2)使用pg_dump遷移表結(jié)構(gòu)使用-h選項，使導(dǎo)出的sql文件直接存放在本地：E:pg_dump -h 132.10.10.11 -p 1234 -U cpost -x -s -f cpost.sql cpostE:psql -U postgres -f cpost.sql 導(dǎo)入成功

20、，但報了一個錯誤： psql:cpost.sql:22: ERROR: character 0 xe99499 of encoding UTF8 has no equivalent in LATIN9字符集錯誤，字符集問題詳見我的另一篇blog:由PostgreSQL的區(qū)域與字符集 說起 三、使用pg_dump和pg_restore實現(xiàn)數(shù)據(jù)庫的備份與恢復(fù)E:pg_restore -helppg_restore從一個歸檔中恢復(fù)一個由pg_dump創(chuàng)建的PostgreSQL數(shù)據(jù)庫.用法：文件名pg_restore 選項.一般選項：-dbname二名字-file=文件名-format二c|d|t-l

21、ist-verbose連接數(shù)據(jù)庫名字輸出文件名backup file format (should be automatic)打印歸檔文件的TOC概述詳細模式顯示此幫助信息，然后退出輸出版本信息，然后退出恢復(fù)控制選項：只恢復(fù)數(shù)據(jù)，不包括模式在重新創(chuàng)建數(shù)據(jù)庫對象之前需要清除(刪除)數(shù)-d,-f,-F,-l,-v,-help-version-a, -data-only-c, -clean據(jù)庫對象創(chuàng)建目標數(shù)據(jù)庫發(fā)生錯誤退出，默認為繼續(xù)恢復(fù)指定名稱的索引可以執(zhí)行多個任務(wù)并行進行恢復(fù)工作從這個文件中使用指定的內(nèi)容表排序輸出-C, -create-e, -exit-on-error-I, -index二名

22、稱-j, -jobs=NUM-L, -use-list=文件名-n, -o, -P, -s, -S, -t, -T, -x, -1,-schema=NAME在這個模式中只恢復(fù)對象-no-owner忽略恢復(fù)對象所屬者-function二名字（參數(shù)）恢復(fù)指定名字的函數(shù)只恢復(fù)模式，不包括數(shù)據(jù)使用指定的超級用戶來禁用觸發(fā)器恢復(fù)指定命字的表恢復(fù)指定命字的觸發(fā)器跳過處理權(quán)限的恢復(fù)（grant/revoke）作為單個事務(wù)恢復(fù)在只恢復(fù)數(shù)據(jù)的過程中禁用觸發(fā)器-schema-only-superuser=NAME-table二NAME-trigger=NAME-no-privileges-single-transaction -disable-triggers-no-data-for-failed-tables沒有恢復(fù)無法創(chuàng)建表的數(shù)據(jù)-no-security-labelsdo not restore security labels-no-tablespaces不恢復(fù)表空間的分配信息-use-set-session-authorization 使用 SESSION AUTHORIZATION 命令代替 ALTER OWNER命令來設(shè)