1 邏輯備份(單庫)

postgres=#pg_dump --help
?
使用方法:
? pg_dump [選項]... [數據庫名字]

一般選項: ? ? ? ? ? ? ? ?
? -f, --file=FILENAME ? ? ? ? ? ?輸出文件或目錄名
? -F, --format=c|d|t|p ? ? ? ? ? 輸出文件格式 (c 自定義壓縮格式輸出, d 目錄, tar,p 備份為文本明文(默認值),大庫不推薦)
? -j, --jobs=NUM ? ? ? ? ? ? ? ? 執行多個并行任務進行備份轉儲工作
? -v, --verbose ? ? ? ? ? ? ? ? ?詳細模式
? -V, --version ? ? ? ? ? ? ? ? ?輸出版本信息, 然后退出
? -Z, --compress=0-9 ? 被壓縮格式的壓縮級別 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? --lock-wait-timeout=TIMEOUT ? ?在等待表鎖超時后操作失敗
? --no-sync ? ? ? ? ? ? ? ? ? ? ?不用等待變化安全寫入磁盤 ? ??
? -?, --help ? ? ? ? ? ? ? ? ? ? 顯示此幫助, 然后退出

-F c 備份為二進制格式, ? ? ? ? ?壓縮存儲. 并且可被 ? ? ? ? ?pg_restore 用于精細還原
-F p 備份為文本, 大庫不推薦

控制輸出內容選項:
? -a, --data-only ? ? ? ? ? ? ? ?只轉儲數據,不包括模式
? -b, --blobs ? ? ? ? ? ? ? ? ? ?在轉儲中包括大對象
? -B, --no-blobs ? ? ? ? ? ? ? ? 排除轉儲中的大型對象
? -c, --clean ? ? ? ? ? ? ? ? ? ?在重新創建之前，先清除（刪除）數據庫對象
? -C, --create ? ? ? ? ? ? ? ? ? 在轉儲中包括命令,以便創建數據庫
? -e, --extension=PATTERN ? ? ? ?僅轉儲指定的擴展名
? -E, --encoding=ENCODING ? ? ? ?轉儲以 ? ?ENCODING 形式編碼的數據
? -n, --schema=PATTERN ? ? ? ? ? 只轉儲指定的模式
? -N, --exclude-schema=PATTERN 不轉儲指定的模式
? -O, --no-owner ? ? ? ? ? ? ? ? 在明文格式中, 忽略恢復對象所屬者
? -s, --schema-only ? ? ? ? ? ? ?只轉儲模式, 不包括數據
? -S, --superuser=NAME ? ? ? ? ? 在明文格式中使用指定的超級用戶名
? -t, --table=PATTERN ? ? ? ? ? ?只轉儲指定的表
? -T, --exclude-table=PATTERN ? ?不轉儲指定的表
? -x, --no-privileges ? ? ? ? ? ?不要轉儲權限(grant/revoke)
? --binary-upgrade ? ? ? ? ? ? ? 只能由升級工具使用
? --column-inserts ? ? ? ? ? ? ? 以帶有列名的 ? ? ? ?INSERT 命令形式轉儲數據
? --disable-dollar-quoting ? ? ? 取消美元 ? ? ?(符號) 引號, 使用SQL ?標準引號
? --disable-triggers ? ? ? ? ? ? 在只恢復數據的過程中禁用觸發器
? --enable-row-security ? ? ? ? ?啟用行安全性（只轉儲用戶能夠訪問的內容）
? --exclude-table-data=PATTERN 不轉儲指定的表中的數據
? --extra-float-digits=NUM ? ? ? 覆蓋 ? extra_float_digits 的默認設置
? --if-exists ? ? ? ? ? ? ? ? ? ?當刪除對象時使用IF EXISTS
? --include-foreign-data=PATTERN，包含外部服務器上與模式匹配的，外部表的數據
? --inserts ? ? ? ? ? ? ? ? ? ? ?以INSERT 命令，而不是COPY 命令的形式轉儲數據
? --load-via-partition-root ? ? ?通過根表加載分區
? --no-comments ? ? ? ? ? ? ? ? ?不轉儲注釋
? --no-publications ? ? ? ? ? ? ?不轉儲發布
? --no-security-labels ? ? ? ? ? 不轉儲安全標簽的分配
? --no-subscriptions ? ? ? ? ? ? 不轉儲訂閱
? --no-table-access-method ? ? ? do not dump table access methods
? --no-tablespaces ? ? ? ? ? ? ? 不轉儲表空間分配信息
? --no-toast-compression ? ? ? ? 不轉儲 ? ?TOAST 壓縮方法
? --no-unlogged-table-data ? ? ? 不轉儲沒有日志的表數據
? --on-conflict-do-nothing ? ? ? 將ON CONFLICT DO NOTHING 添加到INSERT 命令
? --quote-all-identifiers ? ? ? ?所有標識符加引號，即使不是關鍵字
? --rows-per-insert=NROWS ? ? ? ?每個插入的行數；意味著--inserts
? --section=SECTION 備份命名的節 (數據前, 數據, 及 數據后) ? ? ? ? ??
? --serializable-deferrable 等到備份 可以無 異常運行
? --snapshot=SNAPSHOT ? ? ? ? ? ?為轉儲使用給定的快照
? --strict-names ? ? ? ? ? ? ? ? 要求每個表和(或)schema 包括模式以匹配至少一個實體
? --use-set-session-authorization，使用 ? ? ? SESSION AUTHORIZATION 命令代替，ALTER OWNER 命令來設置所有權

連接選項:
? -d, --dbname=DBNAME ? ? ? ?對數據庫 ? ? ?DBNAME 備份
? -h, --host=主機名 ? ? ? ? ? ? 數據庫服務器的主機名或套接字目錄
? -p, --port=端口號 數據庫服務器的端口號 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? -U, --username=名字 以指定的數據庫用戶聯接 ? ? ? ? ? ??
? -w, --no-password ? ? ? ? ?永遠不提示輸入口令
? -W, --password ? ? ? ? ? ? 強制口令提示 ? ? ? ?(自動)
? --role=ROLENAME ? ? ? ? ? ?在轉儲前運行 ? ? ? ?SET ROLE

#案例

--導出指定庫（不含create database語句）
pg_dump -h 連接串 -U 讀寫用戶 ?庫名> dbname.sql
pg_dump -h 192.168.1.51 -U zyb?mydb > mydb-1.sql

--導出指定庫（包含create database語句）
pg_dump -h 連接串 -U 讀寫用戶 -C ?庫名 > dbname.sql
pg_dump -h 192.168.1.51 -U zyb?-C mydb > mydb-2.sql

--僅導出指定庫結構（不含create database語句）
pg_dump -h 連接串 -U 讀寫用戶 -s ?庫名 > dbname.sql
pg_dump -h 192.168.1.51 -U zyb?-s mydb > mydb -3.sql

--僅導出指定庫結構（含create database語句）
pg_dump -h 192.168.1.51 -U postgres -C -s mydb > mydb-33.sql

--導出指定庫，結果以二進制壓縮格式輸出
pg_dump -h 連接串 -U 讀寫用戶 -F c -b -v ?庫名 > dbname.backup
pg_dump -h 192.168.1.51 -U zyb?-F c -b -v mydb > mydb-4.dmp

--導出指定schema
pg_dump -h 連接串 -U 讀寫用戶 -n schemaname 庫名 > dbname.sql
pg_dump -h 192.168.1.51 -U zyb?-n public mydb > mydb-5.sql

--導出指定庫public和public2兩個schema中的所有數據，結果以二進制壓縮格式輸出
pg_dump -h 連接串 -U 讀寫用戶 -F c -b -v -n hr -n public 庫名 > hr_payroll.backup
pg_dump -h 192.168.1.51 -U zyb?-F c -b -v -n public -n public mydb > mydb-6.dmp

--導出指定庫public schema以外的所有數據，結果以二進制壓縮格式輸出
pg_dump -h 連接串 -U 讀寫用戶 -F c -b -v -N public 庫名 > all_sch_except_pub.backup
pg_dump -h 192.168.1.51 -U zyb-F c -b -v -N public mydb > mydb-7.dmp

--導出特定的表
pg_dump -h 連接串 -U 讀寫用戶 -t 表名 庫名 > dbname.sql
pg_dump -h 192.168.1.51 -U zyb -t test mydb > mydb-8.sql

--導出某個db中所有以“itpux"開頭的表
pg_dump -h 連接串 -U 讀寫用戶 -t *.itpux* ?庫名 > dbname.sql
pg_dump -h 192.168.1.51 -U zyb -t *.itpux* mydb > mydb-9.sql

2 多庫備份

#導出整個cluster
pg_dumpall -h 192.168.1.51 -U postgres -f bak_alldb1.sql

#僅備份角色和表空間定義
pg_dumpall -h 192.168.1.51 -U postgres --port=5432 -f bak_roles_tbs.sql --globals-only

#僅備份交色定義 ? ? ? ? ? ? ? ? ? ? ? ?
pg_dumpall -h 192.168.1.51 -U postgres --port=5432 -f bak_roles.sql --roles-only

3 恢復

包含create database語句

psql -h 192.168.1.51 -U postgres? <?mydb-2.sql

不含create database語句

postgres#create database mydb with owner zyb;

psql -h 192.168.1.51 -U postgres mydb <?mydb-2.sql

4 copy 導入導出

#導出 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
COPY { table_name [ ( column_name [, ...] ? ) ] | ( query ) }
? ? ?TO { 'filename' | PROGRAM 'command' | STDOUT }
? ? ?[ [ WITH ] ( option [, ...] ) ]

#導入
COPY table_name [ ( column_name [, ...] ) ]
? ? ?FROM { 'filename' | PROGRAM 'command | STDIN }
? ? ?[ [ WITH ] ( option [, ...] ) ] ? ?

#案例

#把文本數據導入到表中
mydb=# \copy test from /backup/test _copy.txt ;

#表導出csv文件
mydb=#\copy test to /backup/test _copy.txt.csv with csv;

可以導出指定的列
COPY test (id, name) TO ' /backup/output.csv' WITH csv;