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

開源數(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提供的對(duì)應(yīng)工具為pg_dump和pg_restore。pg_dump是用于備份PostgreSQL數(shù)據(jù)庫的工具。它可以在數(shù)據(jù)庫正在使用的時(shí)候進(jìn)行完整一致的備份，并不阻塞其它用戶對(duì)數(shù)據(jù)庫的訪問。轉(zhuǎn)儲(chǔ)格式可以是一個(gè)腳本或者歸檔文件。轉(zhuǎn)儲(chǔ)腳本的格式是純文本，包含許多SQL命令，這些SQL命令可以用于重建該數(shù)據(jù)庫并將之恢復(fù)到保存腳本時(shí)的狀態(tài)。 可以使用 psql從這樣的腳本中恢復(fù)。它們甚至可以用于在其它機(jī)器甚至是其它硬件體系的機(jī)器上重建數(shù)據(jù)庫，通過對(duì)腳本進(jìn)行一些修改，甚至可以在其它SQL數(shù)據(jù)庫產(chǎn) 品上重建數(shù)據(jù)庫。歸檔文件格式必須和pg_restore一起使用重建數(shù)據(jù)庫。它們?cè)试Spg_restore對(duì)恢復(fù)什么東西進(jìn)行選擇，甚至是在恢復(fù)之前對(duì)需要恢復(fù)的條目進(jìn)行重新排序。歸檔文件也是可以跨平臺(tái)移植的。D:Program FilesPowerCmdpg_dump -helppg_dump 把一個(gè)數(shù)據(jù)庫轉(zhuǎn)儲(chǔ)為純文本文件或者是其它格式.用法: pg_dump 選項(xiàng). 數(shù)據(jù)庫名字一般選項(xiàng): -f, -file=FILENAME output file or directory name -F, -format=c|d|t|p output file format (custom, directory, tar, plain text) -v, -verbose 詳細(xì)模式 -Z, -compress=0-9 被壓縮格式的壓縮級(jí)別-lock-wait-timeout=TIMEOUT 在等待表鎖超時(shí)后操作失敗 -help 顯示此幫助信息, 然后退出 -versoin 輸出版本信息, 然后退出控制輸出內(nèi)容選項(xiàng): -a, -data-only 只轉(zhuǎn)儲(chǔ)數(shù)據(jù),不包括模式 -b, -blobs 在轉(zhuǎn)儲(chǔ)中包括大對(duì)象 -c, -clean 在重新創(chuàng)建之前，先清除（刪除）數(shù)據(jù)庫對(duì)象 -C, -create 在轉(zhuǎn)儲(chǔ)中包括命令,以便創(chuàng)建數(shù)據(jù)庫 -E, -encoding=ENCODING 轉(zhuǎn)儲(chǔ)以ENCODING形式編碼的數(shù)據(jù) -n, -schema=SCHEMA 只轉(zhuǎn)儲(chǔ)指定名稱的模式-N, -exclude-schema=SCHEMA 不轉(zhuǎn)儲(chǔ)已命名的模式 -o, -oids 在轉(zhuǎn)儲(chǔ)中包括 OID -O, -no-owner 在明文格式中, 忽略恢復(fù)對(duì)象所屬者 -s, -schema-only 只轉(zhuǎn)儲(chǔ)模式, 不包括數(shù)據(jù) -S, -superuser=NAME 在轉(zhuǎn)儲(chǔ)中, 指定的超級(jí)用戶名 -t, -table=TABLE 只轉(zhuǎn)儲(chǔ)指定名稱的表 -T, -exclude-table=TABLE 只轉(zhuǎn)儲(chǔ)指定名稱的表 -x, -no-privileges 不要轉(zhuǎn)儲(chǔ)權(quán)限 (grant/revoke) -binary-upgrade 只能由升級(jí)工具使用 -column-inserts 以帶有列名的INSERT命令形式轉(zhuǎn)儲(chǔ)數(shù)據(jù) -disable-dollar-quoting 取消美元 (符號(hào)) 引號(hào), 使用 SQL 標(biāo)準(zhǔn)引號(hào) -disable-triggers 在只恢復(fù)數(shù)據(jù)的過程中禁用觸發(fā)器 -inserts 以INSERT命令，而不是COPY命令的形式轉(zhuǎn)儲(chǔ)數(shù)據(jù) -no-security-labels do not dump security label assignments -no-tablespaces 不轉(zhuǎn)儲(chǔ)表空間分配信息 -no-unlogged-table-data do not dump unlogged table data -quote-all-identifiers quote all identifiers, even if not key words -serializable-deferrable wait until the dump can run without anomalies-use-set-session-authorization 使用 SESSION AUTHORIZATION 命令代替ALTER OWNER 命令來設(shè)置所有權(quán)聯(lián)接選項(xiàng): -h, -host=主機(jī)名 數(shù)據(jù)庫服務(wù)器的主機(jī)名或套接字目錄 -p, -port=端口號(hào) 數(shù)據(jù)庫服務(wù)器的端口號(hào) -U, -username=名字 以指定的數(shù)據(jù)庫用戶聯(lián)接 -w, -no-password 永遠(yuǎn)不提示輸入口令 -W, -password 強(qiáng)制口令提示 (自動(dòng)) -role=ROLENAME do SET ROLE before dump如果沒有提供數(shù)據(jù)庫名字, 那么使用 PGDATABASE 環(huán)境變量的數(shù)值. 二、pg_dump的使用實(shí)例1、創(chuàng)建兩個(gè)數(shù)據(jù)庫CREATE DATABASE TestDb1 WITH OWNER = TestRole1 ENCODING = UTF8 TABLESPACE = TestTbs1;CREATE DATABASE TestDb2 WITH OWNER = TestRole1 ENCODING = UTF8 TABLESPACE = TestTbs1;在TestDb1中創(chuàng)建表csm_bill、cfg_public_int_transport插入幾條記錄，并創(chuàng)建索引，索引使用索引表空間TestTbsIndex。 2、僅遷移數(shù)據(jù)庫結(jié)構(gòu)：E:pg_dump -U TestRole1 -s -f TestDb1.sql TestDb1口令:-U TestRole1和超級(jí)用戶-U postgres結(jié)果完全相同：E:pg_dump -U postgres -s -f TestDb11.sql TestDb1E:psql -U TestRole2 -f TestDb1.sql TestDb2 a.txt 2&1用戶 TestRole2 的口令：導(dǎo)入時(shí)，使用-U TestRole2往往有很多權(quán)限不夠，要想成功導(dǎo)入需要修改相關(guān)數(shù)據(jù)庫對(duì)象的屬主，所以最好使用超級(jí)用戶-U postgres：E:psql -U postgres -f TestDb1.sql TestDb2 a.txt 2&1不轉(zhuǎn)儲(chǔ)權(quán)限選項(xiàng)：-xE:pg_dump -U postgres -x -s -f TestDb12.sql TestDb1TestDb12.sql比TestDb1.sql少了一下幾行： 為了可以多次運(yùn)行TestDb1.sql，可以在文件開始加以下兩行：drop schema public cascade;create schema public;或者使用-c選項(xiàng)：E:pg_dump -U postgres -c -x -s -f TestDb13.sql TestDb1TestDb13.sql比TestDb1.sql多以下幾行： 此時(shí)，可以多次運(yùn)行：E:psql -U postgres -f TestDb13.sql TestDb2 a.txt 2&1但是，如果兩個(gè)庫有不同的表或索引，應(yīng)該使用第一種方法，因?yàn)榈诙N方法在找不到某些數(shù)據(jù)庫對(duì)象時(shí)會(huì)報(bào)錯(cuò)。 3、遷移數(shù)據(jù)庫結(jié)構(gòu)和數(shù)據(jù)（可以實(shí)現(xiàn)數(shù)據(jù)庫的備份與恢復(fù)）數(shù)據(jù)的復(fù)制使用copy命令：E:pg_dump -U postgres TestDb1TestDb14.sql 數(shù)據(jù)的復(fù)制使用insert語句：E:pg_dump -U postgres -column-inserts TestDb1TestDb15.sql 4、把遠(yuǎn)程linux上PostgreSQL上的cpost數(shù)據(jù)庫結(jié)構(gòu)遷移至本地PostgreSQL（1）在本地建一個(gè)完全相同的環(huán)境create user cpost inherit createdb;create tablespace pis_data owner cpost location E:PostgreSQL/data/pis_data;create tablespace pis_index owner cpost location E:PostgreSQL/data/pis_index;遠(yuǎn)程數(shù)據(jù)庫cpost仍使用了默認(rèn)表空間：CREATE DATABASE cpost WITH OWNER = cpost -ENCODING = LATIN9 TABLESPACE = pg_default -LC_COLLATE = C -LC_CTYPE = C CONNECTION LIMIT = -1;使用以上三個(gè)參數(shù)報(bào)錯(cuò)，建成后的數(shù)據(jù)庫如下：CREATE DATABASE cpost WITH OWNER = cpost ENCODING = UTF8 TABLESPACE = pg_default LC_COLLATE = Chinese (Simplified)_Peoples Republic of China.936 LC_CTYPE = Chinese (Simplified)_Peoples Republic of China.936 CONNECTION LIMIT = -1;（2）使用pg_dump遷移表結(jié)構(gòu)使用-h選項(xiàng)，使導(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)入成功，但報(bào)了一個(gè)錯(cuò)誤：psql:cpost.sql:22: ERROR: character 0xe99499 of encoding UTF8 has no equivalent in LATIN9字符集錯(cuò)誤，字符集問題詳見我的另一篇blog：由PostgreSQL的區(qū)域與字符集說起 三、使用pg_dump和pg_restore實(shí)現(xiàn)數(shù)據(jù)庫的備份與恢復(fù)E:pg_restore -helppg_restore 從一個(gè)歸檔中恢復(fù)一個(gè)由 pg_dump 創(chuàng)建的 PostgreSQL 數(shù)據(jù)庫.用法: pg_restore 選項(xiàng). 文件名一般選項(xiàng): -d, -dbname=名字 連接數(shù)據(jù)庫名字 -f, -file=文件名 輸出文件名 -F, -format=c|d|t backup file format (should be automatic) -l, -list 打印歸檔文件的 TOC 概述 -v, -verbose 詳細(xì)模式 -help 顯示此幫助信息, 然后退出 -version 輸出版本信息, 然后退出恢復(fù)控制選項(xiàng): -a, -data-only 只恢復(fù)數(shù)據(jù), 不包括模式 -c, -clean 在重新創(chuàng)建數(shù)據(jù)庫對(duì)象之前需要清除（刪除）數(shù)據(jù)庫對(duì)象 -C, -create 創(chuàng)建目標(biāo)數(shù)據(jù)庫 -e, -exit-on-error 發(fā)生錯(cuò)誤退出, 默認(rèn)為繼續(xù) -I, -index=名稱 恢復(fù)指定名稱的索引 -j, -jobs=NUM 可以執(zhí)行多個(gè)任務(wù)并行進(jìn)行恢復(fù)工作 -L, -use-list=文件名 從這個(gè)文件中使用指定的內(nèi)容表排序輸出 -n, -schema=NAME 在這個(gè)模式中只恢復(fù)對(duì)象 -O, -no-owner 忽略恢復(fù)對(duì)象所屬者 -P, -function=名字(參數(shù)) 恢復(fù)指定名字的函數(shù) -s, -schema-only 只恢復(fù)模式, 不包括數(shù)據(jù) -S, -superuser=NAME 使用指定的超級(jí)用戶來禁用觸發(fā)器 -t, -table=NAME 恢復(fù)指定命字的表 -T, -trigger=NAME 恢復(fù)指定命字的觸發(fā)器 -x, -no-privileges 跳過處理權(quán)限的恢復(fù) (grant/revoke) -1, -single-transaction 作為單個(gè)事務(wù)恢復(fù)-disable-triggers 在只恢復(fù)數(shù)據(jù)的過程中禁用觸發(fā)器 -no-data-for-failed-tables 沒有恢復(fù)無法創(chuàng)建表的數(shù)據(jù) -no-security-labels do not restore security labels-no-tablespaces 不恢復(fù)表空間的分配信息 -use-set-session-authorization 使用 SESSION AUTHORIZATION 命令代替ALTER OWNER命令來設(shè)置對(duì)象所有權(quán)聯(lián)接選項(xiàng): -h, -host=主機(jī)名 數(shù)據(jù)庫服務(wù)器的主機(jī)名或套接字目錄 -p, -port=端口號(hào) 數(shù)據(jù)庫服務(wù)器的端口號(hào) -U, -username=名字 以指定的數(shù)據(jù)庫用戶聯(lián)接 -w, -no-password 永遠(yuǎn)不提示輸入口令 -W, -password 強(qiáng)制口令提示 (自動(dòng)) -role=ROLENAME 在恢復(fù)前執(zhí)行SET ROLE操作如果沒有提供輸入文件名, 則使用標(biāo)準(zhǔn)輸入.1、使用dump格式備份和恢復(fù)：E:pg_dump -U postgres -Fc TestDb1 TestDb1.dumppostgres=# drop database TestDb2;DROP DATABASEpostgres=# create database TestDb2postgres-# with owner=TestRole2postgres-# tablespace=TestTbs2;CREATE DATABASEE:pg_restore -U postgres -d TestDb2