在postgreSQL中運行sql腳本和pg_restore命令方式

Posted on by

今天踩坑瞭，把powerdesign生成的sql腳本文件，用pg_restore命令一直運行。。。

過程慘不忍睹，一直以為是編碼問題，修改瞭serve和client的encoding，結果。。。

記錄一下這個錯誤：

postgreSQL運行sql腳本文件：

psql -d dbname -U username -f (腳本所在位置).sql

postgerSQL的pg_restore命令

用法:

pg_restore [選項]… [文件名]

一般選項:

-d, --dbname=名字 連接數據庫名字
-f, --file=文件名 輸出文件名
-F, --format=c|d|t 備份文件格式(應該自動進行)
-l, --list 打印歸檔文件的 TOC 概述
-v, --verbose 詳細模式
-V, --version 輸出版本信息, 然後退出
-?, --help 顯示此幫助, 然後退出

恢復控制選項:

-a, --data-only 隻恢復數據, 不包括模式
-c, --clean 在重新創建之前，先清除（刪除）數據庫對象
-C, --create 創建目標數據庫
-e, --exit-on-error 發生錯誤退出, 默認為繼續
-I, --index=NAME 恢復指定名稱的索引
-j, --jobs=NUM 執行多個並行任務進行恢復工作
-L, --use-list=FILENAME 從這個文件中使用指定的內容表排序

輸出

-n, --schema=NAME 在這個模式中隻恢復對象
-N, --exclude-schema=NAME 不恢復此模式中的對象
-O, --no-owner 不恢復對象所屬者
-P, --function=NAME(args) 恢復指定名字的函數
-s, --schema-only 隻恢復模式, 不包括數據
-S, --superuser=NAME 使用指定的超級用戶來禁用觸發器
-t, --table=NAME 恢復命名關系（表、視圖等）
-T, --trigger=NAME 恢復指定名字的觸發器
-x, --no-privileges 跳過處理權限的恢復 (grant/revoke)
-1, --single-transaction 作為單個事務恢復
–disable-triggers 在隻恢復數據的過程中禁用觸發器
–enable-row-security 啟用行安全性
–if-exists 當刪除對象時使用IF EXISTS
–no-comments 不恢復註釋
–no-data-for-failed-tables 對那些無法創建的表不進行

數據恢復

–no-publications 不恢復發行
–no-security-labels 不恢復安全標簽信息
–no-subscriptions 不恢復訂閱
–no-tablespaces 不恢復表空間的分配信息
–section=SECTION 恢復命名節 (數據前、數據及數據後)
–strict-names 要求每個表和(或)schema包括模式以匹配至少一個實體
–use-set-session-authorization
使用 SESSION AUTHORIZATION 命令代替
ALTER OWNER 命令來設置所有權

聯接選項:

-h, --host=主機名 數據庫服務器的主機名或套接字目錄
-p, --port=端口號 數據庫服務器的端口號
-U, --username=名字 以指定的數據庫用戶聯接
-w, --no-password 永遠不提示輸入口令
-W, --password 強制口令提示 (自動)
–role=ROLENAME 在恢復前執行SET ROLE操作

選項 -I, -n, -N, -P, -t, -T, 以及 –section 可以組合使用和指定

多次用於選擇多個對象.

希望不要和我一樣犯這個低級錯誤

順便記錄一下查看server和client的encoding以及修改

找到postgresql.conf文件，修改參數lc_messages的值為UTF8

重啟PostgreSQL的服務。

—-查看服務端字符集：

test=> show server_encoding;

—查看客戶端字符集：

test=> show client_encoding;

補充：pg_restore及psql恢復數據的用法

一、使用psql恢復SQL文本格式的數據備份（即一個包含SQL腳本的文本文件）

恢復一個SQL備份文件並忽略過程中可能發生的所有錯誤：

psql -U postgres -f myglobals.sql

恢復一個SQL備份文件，如遇任何錯誤則立即停止恢復：

psql -U postgres --set ON_ERROR_STOP=ON -f myglobals.sql

將SQL文本中的數據恢復到某個指定的database：

psql -U postgres -d mydb -f select_objects.sql

二、使用pg_restore進行恢復

功能：

（1）支持並行恢復，使用-j選項可以控制並行恢復的線程數。多個恢復線程可以並行處理，每個線程處理一張表。該模式可以顯著提高恢復速度。

（2）可以使用pg_restore掃描備份文件來生成一張備份內容列表，通過該列表可以確認備份紅包含瞭哪些內容。還可以通過編輯該內容列表來控制恢復哪些內容。

（3）pg_dump支持選擇性地僅備份部分對象以節省備份時間，類似的，pg_restore也支持選擇性的僅恢復部分對象，不管備份文件本身是全庫備份還是部分對象的備份都沒有問題。

（4）pg_restore的大部分功能是向後兼容的，即支持將老版本PostgreSQL生產的備份數據恢復到新版本的PostgreSQL中。

在使用pg_restore執行恢復動作之前，請先創建目標數據庫：

create database mydb;

然後執行恢復：

pg_restore --dbname=mydb --jobs=4 --verbose mydb.backup

如果備份和恢復時使用的database同名，則可以通過加–create選項省去單獨建庫的過程，如下：

pg_restore --dbname=postgres --create --jobs=4 --verbose mydb.backup

註：如果指定瞭–create選項，那麼恢復出來的數據庫名就會默認采用備份時的數據庫名，不允許改名。如果還同時指定瞭–dbname選項，那麼此時連接的數據庫名一定不能是待恢復的數據庫名，因為要恢復數據庫之前必然要建數據庫，而

要建數據庫之前必然要先連到某個已存在的數據庫，–dbname選項指定的就是建立被恢復的數據庫之前先連到哪個數據庫，所以必然不能與待恢復的數據庫同名，我們一般指定先連到postgres數據庫。

9.2版或更新版本的pg_restore支持–section選項，加上該選項後可以實現僅恢復表結構而不恢復表數據。具體做法是先創建目標恢復數據庫：

create database mydb2;

然後使用pg_restore：

pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。如有錯誤或未考慮完全的地方，望不吝賜教。

推薦閱讀：