開源數據庫postgresql 數據庫備份恢復的方法介紹

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

2、用于重建該數據庫并將之恢復到保存腳本時的狀態(tài)。 可以使用psql從這樣的腳本中恢復。它們甚至可以用于在其它機器甚至是其它 硬件體系的機器上重建數據庫，通過對腳本進行一些修改，甚至可以在其它SQL 數據庫產品上重建數據庫。歸檔文件格式必須和pg_restore 一起使用重建數據庫。它們允許pg_restore 對恢復什么東西進行選擇，甚至是在恢復之前對需要恢復的條目進行重新排序。 歸檔文件也是可以跨平臺移植的。D:Program FilesPowerCmdpg_dump -helppg_dump把一個數據庫轉儲為純文本文件或者是其它格式.數據庫名字用法：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控制輸出內容選項：-data-only-blobs-clean-create-encoding二ENCODING-schema=SCHEMA顯示此幫助信息，然后退出 輸出版本信息，然

4、后退出只轉儲數據，不包括模式在轉儲中包括大對象在重新創(chuàng)建之前，先清除（刪除）數據庫對象在轉儲中包括命令，以便創(chuàng)建數據庫轉儲以ENCODING形式編碼的數據只轉儲指定名稱的模式不轉儲已命名的模式在轉儲中包括 OID在明文格式中，忽略恢復對象所屬者只轉儲模式，不包括數據在轉儲中，指定的超級用戶名只轉儲指定名稱的表只轉儲指定名稱的表-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不要轉儲權限 (grant/revoke)只能由升級工具使用-column-inserts-disable-dollar-quoting號-disable-triggers-inserts據-no-security-labels-no-tablespaces-no-unlogged-table-data-quote-all-identifierswords-serializable-deferrableanomalies-use-set-session-authorization使用SESSION AUTHORIZATION命令代替

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

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

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

9、sql TestDb2 a.txt 2&1 用戶TestRole2的口令：導入時，使用-u TestRole2往往有很多權限不夠，要想成功導入需要修改相關 數據庫對象的屬主，所以最好使用超級用戶-U postgres：E:psql -U postgres -f TestDbl.sql TestDb2 a.txt 2&1不轉儲權限選項：-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但是，如果兩個庫有不同的表或索引，應該使用第一種方法，因為第二種方法在 找不到某些數據庫對象時會

13、報錯。3、遷移數據庫結構和數據(可以實現(xiàn)數據庫的備份與恢復)數據的復制使用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數據的復制使用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數據庫結構遷移至本地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;遠程數據庫cpost仍使用了默認表空間：CREATE DATABASE cpostWITH OWNER = cpost-ENCODING = LATIN9TABLESPACE = pg_default-LC_COLLATE = C-LC_CTYPE = CCONNECTION LIMIT = -1;使用以上三個參數報錯，建成后的數據庫如下：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遷移表結構使用-h選項，使導出的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 導入成功

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)數據庫的備份與恢復E:pg_restore -helppg_restore從一個歸檔中恢復一個由pg_dump創(chuàng)建的PostgreSQL數據庫.用法：文件名pg_restore 選項.一般選項：-dbname二名字-file=文件名-format二c|d|t-l

21、ist-verbose連接數據庫名字輸出文件名backup file format (should be automatic)打印歸檔文件的TOC概述詳細模式顯示此幫助信息，然后退出輸出版本信息，然后退出恢復控制選項：只恢復數據，不包括模式在重新創(chuàng)建數據庫對象之前需要清除(刪除)數-d,-f,-F,-l,-v,-help-version-a, -data-only-c, -clean據庫對象創(chuàng)建目標數據庫發(fā)生錯誤退出，默認為繼續(xù)恢復指定名稱的索引可以執(zhí)行多個任務并行進行恢復工作從這個文件中使用指定的內容表排序輸出-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在這個模式中只恢復對象-no-owner忽略恢復對象所屬者-function二名字（參數）恢復指定名字的函數只恢復模式，不包括數據使用指定的超級用戶來禁用觸發(fā)器恢復指定命字的表恢復指定命字的觸發(fā)器跳過處理權限的恢復（grant/revoke）作為單個事務恢復在只恢復數據的過程中禁用觸發(fā)器-schema-only-superuser=NAME-table二NAME-trigger=NAME-no-privileges-single-transaction -disable-triggers-no-data-for-failed-tables沒有恢復無法創(chuàng)建表的數據-no-security-labelsdo not restore security labels-no-tablespaces不恢復表空間的分配信息-use-set-session-authorization 使用 SESSION AUTHORIZATION 命令代替 ALTER OWNER命令來設