Postgresql的備份還原主要有:SQL轉(zhuǎn)儲有额、文件系統(tǒng)級備份 和 連續(xù)歸檔和時間點恢復(fù)(PITR)這三種方式馁痴。
主要工具
- 備份
- 物理備份
- pg_basebackup (文件系統(tǒng)級備份)
- pg_start_backup() 和 pg_stop_backup() (文件系統(tǒng)級備份)
- 開啟日志歸檔 (連續(xù)歸檔)
- 邏輯備份 (SQL轉(zhuǎn)儲)
- pg_dump
- pg_dumpall
- copy
- 物理備份
- 恢復(fù)
- 物理恢復(fù)
- 文件系統(tǒng)級備份目前只能手工操作
- 日志回放(時間點恢復(fù))
- 邏輯恢復(fù)
- psql
- pg_restore
- 物理恢復(fù)
各種工具詳細說明
備份(copy命令)
copy命令主要用于備份部分列或行, 或者任意查詢結(jié)果宪肖。
注意: psql提供有\(zhòng)copy命令, 而copy是一條sql命令, 連著功能雷同仔粥。
# 查看幫助
\h copy
Command: COPY
Description: copy data between a file and a table
Syntax:
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | PROGRAM 'command' | STDIN }
[ [ WITH ] ( option [, ...] ) ]
[ WHERE condition ]
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | PROGRAM 'command' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
where option can be one of:
FORMAT format_name
FREEZE [ boolean ]
DELIMITER 'delimiter_character'
NULL 'null_string'
HEADER [ boolean ]
QUOTE 'quote_character'
ESCAPE 'escape_character'
FORCE_QUOTE { ( column_name [, ...] ) | * }
FORCE_NOT_NULL ( column_name [, ...] )
FORCE_NULL ( column_name [, ...] )
ENCODING 'encoding_name'
URL: https://www.postgresql.org/docs/12/sql-copy.html
備份(pg_dump/pg_dumpall)
pg_dump -h [127.0.0.1] -U [postgres] -d [dbname] -f [dump.sql]
注: -U/--username 以指定的數(shù)據(jù)庫用戶連接
-d/--dbname 對數(shù)據(jù)庫dbname備份
-h/--host 數(shù)據(jù)庫服務(wù)器的主機名或套接字目錄
-p/--port 數(shù)據(jù)庫服務(wù)器的端口號
-w/--no-password 永遠不提示輸入口令
-W/--password 強制口令提示
--role=rolename 在轉(zhuǎn)儲前運行set role
恢復(fù)(psql)
psql -h [127.0.0.1] -U [postgres] -d [dbname] -f [dump.sql/*.dmp]
恢復(fù)(pg_restore)
使用pg_dump的自定義轉(zhuǎn)儲格式呆万。. 如果PostgreSQL所在的系統(tǒng)上安裝了zlib壓縮庫思犁,自定義轉(zhuǎn)儲格式將在寫出數(shù)據(jù)到輸出文件時對其壓縮。
下面的命令使用自定義轉(zhuǎn)儲格式來轉(zhuǎn)儲一個數(shù)據(jù)庫:
pg_dump -Fc dbname > filename
自定義格式的轉(zhuǎn)儲不是psql的腳本歉摧,只能通過pg_restore恢復(fù)艇肴,例如:
pg_restore -d dbname filename
pg_basebackup
pg_basebackup:是pgsql提供的一個執(zhí)行基礎(chǔ)備份的工具。經(jīng)常用來搭建流復(fù)制環(huán)境, 屬于物理備份叁温。
pgsql的邏輯備份是pg_dump工具再悼。
參數(shù)說明(pg_basebackup --help 詳細查看):
-h 指定連接的數(shù)據(jù)庫的主機名或IP地址,這里就是主庫的ip膝但。
-U 指定連接的用戶名冲九,專門負責(zé)流復(fù)制的repl用戶。
-F 指定了輸出的格式锰镀,支持p(原樣輸出)或者t(tar格式輸出)娘侍。
-x 表示備份開始后咖刃,啟動另一個流復(fù)制連接從主庫接收WAL日志。
-P 表示允許在備份過程中實時的打印備份的進度憾筏。
-R 表示會在備份結(jié)束后自動生成recovery.conf文件嚎杨,這樣也就避免了手動創(chuàng)建。(12.0有差異)
-D 指定把備份寫到哪個目錄氧腰,這里尤其要注意一點就是做基礎(chǔ)備份之前從庫的數(shù)據(jù)目錄(比如:/usr/local/postgresql/data)目錄需要手動清空枫浙。
-l 表示指定一個備份的標識
如:pg_basebackup -h 192.168.23.193 -p 3012 -U testacc -F p -P -X s -R -D /data -r 50M -l label_1576225683"
注意:
12 版本pg_basebackup 命令的 -R 參數(shù)的效果和之前不同,主要體現(xiàn)在:
- 命令執(zhí)行后在 $PGDATA 目錄創(chuàng)建 standby.signal 標識文件古拴,文件內(nèi)容為空
- 命令執(zhí)行后在 $PGDATA 目錄的 postgresql.auto.conf 文件中添加 primary_conninfo 參數(shù)信息
pg_start_backup() 和 pg_stop_backup()
pg_start_backup() 和 pg_stop_backup()是postgreSQL提供的一種備份方式箩帚,由于無法并行備份,現(xiàn)在已經(jīng)逐漸被pg_basebackup
https://blog.csdn.net/pg_hgdb/article/details/78505641
pg_start_backup()
1黄痪、強制發(fā)生一次checkpoint點紧帕。 將未寫到磁盤上的臟數(shù)據(jù)全部刷到磁盤上去。這樣從這之后產(chǎn)生的日志就是記錄整個數(shù)據(jù)塊桅打∈鞘龋可以“確保”恢復(fù)的正確性挺尾。
2鹅搪、置寫日志標志
為:XLogCtl->Insert.forcePageWrites = true
,這樣wal日志將會記錄整個數(shù)據(jù)塊遭铺。避免了在進行備份時候(讀操作——舊數(shù)據(jù))持續(xù)向數(shù)據(jù)庫寫入數(shù)據(jù)(寫操作——新數(shù)據(jù))造成的前后數(shù)據(jù)不一致丽柿。
“確保”恢復(fù)的正確性帶上了引號魂挂,這是為什么呢甫题?
想要知道為什么不能真正確保數(shù)據(jù)恢復(fù)正確性,首先應(yīng)該了解涂召,pg_start_backup()開啟期間(不執(zhí)行pg_stop_backup())幔睬,wal日志仍然會進行循環(huán)使用。從我們使用者的角度來看也許數(shù)據(jù)庫應(yīng)該是持續(xù)的將數(shù)據(jù)塊變化記錄到wal中芹扭,備份不停止,wal日志也不應(yīng)該被覆蓋赦抖,但事實上并不是如此舱卡,也許是postgreSQL為了不至于太復(fù)雜和為了避免撐爆xlog日志,pg_start_backup()開啟期間wal仍會進行覆蓋循環(huán)使用队萤。
我們知道pg_start_backup() 和 pg_stop_backup()的使用是不需要開啟歸檔的(強烈建議開啟)轮锥,那么在進行備份的時候,應(yīng)該進行歸檔的一部分日志就會無法保存直接被覆蓋掉要尔,如果再想恢復(fù)到“歸檔日志之后”的時間段數(shù)據(jù)也就會發(fā)生丟失舍杜。
所以說新娜,對于歸檔日志,在存儲允許的情況下既绩,請務(wù)必使用!
pg_stop_backup()
pg_stop_backup()相對而言就簡單多了,它的作用就是結(jié)束此次備份狀態(tài)栅迄,以便進行下次備份(非并發(fā)性備份)号俐,一直不執(zhí)行pg_stop_backup()也并不會撐爆xlog目錄,但是是無法執(zhí)行下次備份的救欧。
日志歸檔
也就是把WAL日志復(fù)制到另一個地方保存起來, 這樣出問題就多了一些手段恢復(fù)數(shù)據(jù)了
修改postgresql.conf
wal_level = replica
# 該參數(shù)的可選的值有minimal衰粹,replica和logical,wal的級別依次增高笆怠,在wal的信息也越多铝耻。
# 由于minimal這一級別的wal不包含從基礎(chǔ)的備份和wal日志重建數(shù)據(jù)的足夠信息,在該模式下蹬刷,無法開啟wal日志歸檔
# 是不是還有 hot_standby, archive 等等的值??
# 正常情況下瓢捉,wal日志段在達到16M后會自動歸檔。
# 觸發(fā)歸檔有三種方式:
# 1. 手動切換wal日志箍铭,select pg_switch_xlog()
# 2. wal日志寫滿后觸發(fā)歸檔泊柬,配置文件默認達到16M后就會觸發(fā)歸檔,wal_keep_segments = 16
# 3. 歸檔超時觸發(fā)歸檔诈火,archive_timeout
archive_mode =on
# %p = path of file to archive
# %f = file name only
archive_command ='cp %p /u01/pgsql/archive/pg_%f'
# 當(dāng)postgresql需要傳輸歸檔日志時兽赁,會調(diào)用archive_command指定的shell命令。
# 歸檔文件傳輸成功時冷守,shell命令要返回0刀崖,
# 此時,postgresql會認為歸檔文件已經(jīng)傳輸成功拍摇,因此可以刪除或者重新循環(huán)利用歸檔文件亮钦。
# 當(dāng)shell命令返回非0值時,postgresql會保留所有未成功傳輸?shù)臍w檔日志充活,并不斷嘗試重新傳輸蜂莉,直到成功。
# 如果歸檔命令一直不成功混卵,pg_xlog目錄會持續(xù)增長映穗,有耗盡服務(wù)器存儲空間的可能,
# 此時postgresql會PANIC關(guān)閉幕随,直到釋放存儲空間蚁滋。
# command 使用邏輯運算符
# 1. 命令1 && 命令2 命令1執(zhí)行成功后才會執(zhí)行命令2
# 2. 命令1 || 命令2 命令1執(zhí)行失敗后才會執(zhí)行命令2
# 每天生成一個歸檔目錄
# archive_command = 'DIR=/u01/pgsql/archive/`date +%F`; sudo test ! -d $DIR && sudo mkdir $DIR; sudo test ! -f $DIR/%f && sudo cp %p $DIR/%f'
# 命令放開來, 方便查看:
# DIR=/u01/pgsql/archive/`date +%F`;
# sudo test ! -d $DIR
# && sudo mkdir $DIR;
# sudo test ! -f $DIR/%f
# && sudo cp %p $DIR/%f
重啟postgresql
pg_ctl stop
pg_ctl start
# 手工觸發(fā)歸檔:
> checkpoint;
> select pg_switch_xlog();
# 然后就可以到歸檔目錄看看有沒有文件生成了
查看歸檔設(shè)置
select name,setting from pg_settings where name like 'archive%' or name = 'wal_level';
連續(xù)歸檔模式
pg_dump/pg_dumpall屬于一致性邏輯備份,可以用其進行跨PostgreSQL版本,跨系統(tǒng)平臺的數(shù)據(jù)遷移辕录。用于常規(guī)備份則其速度和靈活性略顯不足睦霎。
而連續(xù)歸檔模式則類似于oracle的rman備份方式,可用于大型數(shù)據(jù)庫的增量備份和恢復(fù)走诞,以及用于搭建高可用standby鏡像備份副女。
其原理就是先做一次全庫的基礎(chǔ)備份(全量備份/物理備份), 然后把后續(xù)的所有WAL歸檔日志保存起來(增量備份), 恢復(fù)的時候先用基礎(chǔ)備份恢復(fù)數(shù)據(jù)庫, 然后用歸檔日志進行回放恢復(fù)增量數(shù)據(jù), 具體操作過程請看
PostgreSQL連續(xù)歸檔備份