PostgreSQL備份和恢復(fù)

Postgresql的備份還原主要有:SQL轉(zhuǎn)儲有额、文件系統(tǒng)級備份 和 連續(xù)歸檔和時間點恢復(fù)(PITR)這三種方式馁痴。

主要工具

  1. 備份
    1. 物理備份
      1. pg_basebackup (文件系統(tǒng)級備份)
      2. pg_start_backup() 和 pg_stop_backup() (文件系統(tǒng)級備份)
      3. 開啟日志歸檔 (連續(xù)歸檔)
    2. 邏輯備份 (SQL轉(zhuǎn)儲)
      1. pg_dump
      2. pg_dumpall
      3. copy
  2. 恢復(fù)
    1. 物理恢復(fù)
      1. 文件系統(tǒng)級備份目前只能手工操作
      2. 日志回放(時間點恢復(fù))
    2. 邏輯恢復(fù)
      1. psql
      2. pg_restore

各種工具詳細說明

備份(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)在:

  1. 命令執(zhí)行后在 $PGDATA 目錄創(chuàng)建 standby.signal 標識文件古拴,文件內(nèi)容為空
  2. 命令執(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ù)歸檔備份

備份和恢復(fù)步驟參考

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市速梗,隨后出現(xiàn)的幾起案子肮塞,更是在濱河造成了極大的恐慌,老刑警劉巖姻锁,帶你破解...
    沈念sama閱讀 212,454評論 6 493
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件枕赵,死亡現(xiàn)場離奇詭異,居然都是意外死亡位隶,警方通過查閱死者的電腦和手機拷窜,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,553評論 3 385
  • 文/潘曉璐 我一進店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來涧黄,“玉大人篮昧,你說我怎么就攤上這事∷裢祝” “怎么了懊昨?”我有些...
    開封第一講書人閱讀 157,921評論 0 348
  • 文/不壞的土叔 我叫張陵,是天一觀的道長春宣。 經(jīng)常有香客問我酵颁,道長,這世上最難降的妖魔是什么月帝? 我笑而不...
    開封第一講書人閱讀 56,648評論 1 284
  • 正文 為了忘掉前任躏惋,我火速辦了婚禮,結(jié)果婚禮上嚷辅,老公的妹妹穿的比我還像新娘簿姨。我一直安慰自己,他們只是感情好簸搞,可當(dāng)我...
    茶點故事閱讀 65,770評論 6 386
  • 文/花漫 我一把揭開白布扁位。 她就那樣靜靜地躺著,像睡著了一般趁俊。 火紅的嫁衣襯著肌膚如雪贤牛。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 49,950評論 1 291
  • 那天则酝,我揣著相機與錄音,去河邊找鬼。 笑死沽讹,一個胖子當(dāng)著我的面吹牛般卑,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播爽雄,決...
    沈念sama閱讀 39,090評論 3 410
  • 文/蒼蘭香墨 我猛地睜開眼蝠检,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了挚瘟?” 一聲冷哼從身側(cè)響起叹谁,我...
    開封第一講書人閱讀 37,817評論 0 268
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎乘盖,沒想到半個月后焰檩,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 44,275評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡订框,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,592評論 2 327
  • 正文 我和宋清朗相戀三年析苫,在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片穿扳。...
    茶點故事閱讀 38,724評論 1 341
  • 序言:一個原本活蹦亂跳的男人離奇死亡衩侥,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出矛物,到底是詐尸還是另有隱情茫死,我是刑警寧澤,帶...
    沈念sama閱讀 34,409評論 4 333
  • 正文 年R本政府宣布履羞,位于F島的核電站峦萎,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏吧雹。R本人自食惡果不足惜骨杂,卻給世界環(huán)境...
    茶點故事閱讀 40,052評論 3 316
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望雄卷。 院中可真熱鬧搓蚪,春花似錦、人聲如沸丁鹉。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,815評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽揣钦。三九已至雳灾,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間冯凹,已是汗流浹背谎亩。 一陣腳步聲響...
    開封第一講書人閱讀 32,043評論 1 266
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人匈庭。 一個月前我還...
    沈念sama閱讀 46,503評論 2 361
  • 正文 我出身青樓夫凸,卻偏偏與公主長得像,于是被迫代替她去往敵國和親阱持。 傳聞我的和親對象是個殘疾皇子夭拌,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 43,627評論 2 350

推薦閱讀更多精彩內(nèi)容