pg_dump備份

pg提供了pg_dump和pg_dumpall命令進行數據庫的備份，pg_dumpall是將整個pg集群轉儲到一個腳本文件中，而pg_dump命令可以選擇一個數據庫或者部分表進行備份。

pg_dump 把一個數據庫轉儲為純文本文件或者是其它格式，使用方法如下：

[postgres@pg01 ~]$ pg_dump --help
?
用法:
  pg_dump [選項]... [數據庫名字]
?
**一般選項**:
  -f, --file=FILENAME          輸出文件或目錄名
  -F, --format=c|d|t|p         輸出文件格式 (c=custom, d=directory, t=tar,p=plain,plain就是sql純文本 (默認值))
  -j, --jobs=NUM               執行多個并行任務進行備份轉儲工作
  -v, --verbose                詳細模式
  -V, --version                輸出版本信息，然后退出
  -Z, --compress=0-9           被壓縮格式的壓縮級別，0表示不壓縮
  --lock-wait-timeout=TIMEOUT  在等待表鎖超時后操作失敗
  --no-sync                    不用等待變化安全寫入磁盤
  -?, --help                   顯示此幫助, 然后退出
?
**控制輸出內容選項(常用)**:
  -a, --data-only              只轉儲數據,不包括模式,只對純文本輸出有意義
  -s, --schema-only            只轉儲模式, 不包括數據
  -c, --clean                  在重新創建之前，先清除（刪除）數據庫對象，如drop table。只對純文本輸出有意義
  -C, --create                 指定輸出文件中是否生成create database語句,只對純文本輸出有意義
  -n, --schema=PATTERN         指定要導出的schema，不指定則導出所有的非系統schema
  -N, --exclude-schema=PATTERN 排除導出哪些schema
  -O, --no-owner               在明文格式中, 忽略恢復對象所屬者
  -t, --table=PATTERN          指定導出的表、視圖、序列，可以使用多個-t匹配多個表，使用-t之后，-n和-N就失效了
  -T, --exclude-table=PATTERN  排除表
  -x, --no-privileges          不要轉儲權限 (grant/revoke)
?
  --disable-triggers           在只恢復數據的過程中禁用觸發器 
  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)
  --if-exists                  當刪除對象時使用IF EXISTS
  --inserts                    以INSERT命令，而不是COPY命令的形式轉儲數據，使用該選項可以把數據加載到非pg數據庫，會使恢復非常慢
                               該選項為每行生成1個單獨的insert命令，?在恢復過程中遇到錯誤，將會丟失1行而不是全部表數據
  --column-inserts             以帶有列名的INSERT命令形式轉儲數據，例如insert into table_name(column,...) values(value1,...)                             
  --load-via-partition-root    通過根表加載分區
  --no-comments                不轉儲注釋
  --no-tablespaces             不轉儲表空間分配信息
  --no-unlogged-table-data     不轉儲沒有日志的表數據
  --on-conflict-do-nothing     將ON CONFLICT DO NOTHING添加到INSERT命令
?
**控制輸出內容選項(不常用)**:
  -S, --superuser=NAME         指定關閉觸發器時需要用到的超級用戶名。 它只有在使用了--disable-triggers時才有影響。一般情況下，最好不要輸入該參數，而是用 超級用戶啟動生成的腳本。
  -b, --blobs                  在轉儲中包括大對象
  -B, --no-blobs               排除轉儲中的大型對象 
  -E, --encoding=ENCODING      轉儲以ENCODING形式編碼的數據
  --binary-upgrade             只能由升級工具使用
  --enable-row-security        啟用行安全性（只轉儲用戶能夠訪問的內容）
  --extra-float-digits=NUM     覆蓋extra_float_digits的默認設置
  --disable-dollar-quoting     取消美元 (符號) 引號, 使用 SQL 標準引號
  --no-publications            不轉儲發布
  --no-security-labels         不轉儲安全標簽的分配
  --no-subscriptions           不轉儲訂閱
  --no-synchronized-snapshots  在并行工作集中不使用同步快照
  --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

pg_restore恢復

對于pg_dump的自定義備份custom和tar類型的備份，需要使用pg_restore進行恢復，pg_restore語法如下：

[postgres@pg01 pg_backup]$ pg_restore --help
pg_restore 從一個歸檔中恢復一個由 pg_dump 創建的 PostgreSQL 數據庫.
?
用法:
  pg_restore [選項]... [文件名]
?
一般選項:
  -d, --dbname=名字        連接數據庫名字
  -f, --file=文件名       輸出文件名(- 對于stdout)
  -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 可以組合使用和指定
多次用于選擇多個對象.
?
如果沒有提供輸入文件名, 則使用標準輸入.

對fxj庫進行備份，保存為sql格式

/data/pg_install/bin/pg_dump --username=fxj --password --host=x.x.x.x --port=8836 --dbname=fxj --format=plain --file=a.sql --verbose

對fxj庫進行備份，保存為sql格式，帶有create database操作

[postgres@pg01 pg_backup]$ pg_dump --username=fxj --password --host=x.x.x.x --port=8836 --dbname=db1 --format=plain --file=/home/postgres/pg_backup/db1_create.sql --create --verbose
# 可以在導出的sql文件中看到創建數據庫的語句 
CREATE DATABASE db1 WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8'; 
ALTER DATABASE db1 OWNER TO postgres;

對fxj庫進行備份，保存為sql格式使用--clean選項創建帶有drop table、drop index的sql文件

[postgres@pg01 pg_backup]$ pg_dump --username=fxj --password --host=x.x.x.x --port=8836 --dbname=db1 --table=schema1.t1 --format=plain --file=/home/postgres/pg_backup/db1_schema1_t1.sql --verbose --clean

對db1數據庫進行備份，保存為轉儲dmp格式

pg_dump --username=fxj --password --host=x.x.x.x --port=8836 --dbname=fxj --format=custom --file=/data/pg_backup/fxj.dump --verbose

備份多個表

[postgres@pg01 pg_backup]$ pg_dump --username=fxj --password --host=x.x.x.x --port=8836 --dbname=db1 --table=schema1.t1  --table=schema1.t2 --format=plain --file=/home/postgres/pg_backup/db1_schema1_t1_t2.sql --verbose

使用pg_restore恢復

跨db恢復

把備份fxj庫的數據恢復到fxj1庫，注意要用dump格式的文件，不能用sql文件，sql文件只能恢復到備份的fxj庫里面，另外也要先建好fxj1庫。

/data/pg_install/bin/psql -d fxj -p 8836 -W -U fxj -h x.x.x.x      
 Password:  
 psql (12.4) 
 Type "help" for help. 
 fxj=# create database fxj1; 
 CREATE DATABASE 
 fxj=# \q 
 [root@VM-8-17-centos pg_backup]# /data/pg_install/bin/pg_restore --username=fxj --password --host=x.x.x.x --port=8836 --dbname=fxj1 --format=custom  /data/pg_backup/fxj.dump  --verbose 
 pg_restore: connecting to database for restore 
 Password:  
 pg_restore: creating TABLE "public.fxj" 
 pg_restore: processing data for table "public.fxj"

恢復sql文件

這里是使用psql工具恢復，不是用pg_restore恢復

刪掉fxj文件，然后恢復

/data/pg_install/bin/psql --username=fxj --password --host=x.x.x.x --port=8836 --dbname=fxj --file=/data/pg_data/a.sql

其他恢復

使用--schema-only只恢復schema

使用--data-only只恢復數據