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

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