概述

PostgreSQL 提供了不同的方法來備份和恢復資料庫，可以是某一時刻資料庫快照的完整備份或增量備份，可以使用 SQL 轉儲或檔案系統級別的備份，在增量備份的基礎上還可以實現基於時間點恢復。 這裡主要介紹增量備份和基於時間點恢復（PITR)

實現增量備份的思路是

1) 記錄資料庫系統的操作記錄（WAL歸檔）

2) 在某一時刻進行一次完整的資料庫備份

3) 需要恢復時,還原上一次完整的資料庫備份,根據操作記錄恢復資料庫至指定的某個時刻(即可以實現PITR 時間點恢復)

一、增量備份

PostgreSQL 在做寫入操作時，對資料檔案做的任何修改資訊，首先會寫入WAL 日 志（預寫日誌），然後才會對資料檔案做物理修改。 當資料庫伺服器掉電或意外宕機， PostgreSQL 在啟動時會首先讀取 WAL 日誌，對資料檔案進行恢復。 因此，從理論上講， 如果我們有一個數據庫的基礎備份（也稱為全備），再配合 WAL 日誌，是可以將資料庫恢復到任意時間點的。

1、修改wal_level引數

wal_level 引數可選的值有 minimal、 replica 和 logical ，從 minimal到 replica 再到 logical 級別， WAL 的級別依次增高，在 WAL 中包含的資訊也越多。由於 minimal 這一級別的 WAL 不包含從基本的備份和 WAL 日誌中重建資料的足夠資訊，在 minimal 模式下無法開啟 archive_mode，所以開啟 WAL 歸檔 wal_level 至少設定為 replica，如下所示 ：

--命令列ALTER SYSTEM SET wal_level = 'replica';--配置檔案postgresql.confwal_level = replica

2、修改archive_mode引數

archive_mode 引數可選的值有 on、off 和 always，預設值為 off，開啟歸檔需要修改為 on，重啟生效。

archive_command 引數的預設值是個空字串，它的值可以是一條 shell 命令或者一個複雜的 shell 指令碼。 在 archive_command 的 shell 命令或指令碼中可以用“%p”表示將要歸檔 的 WAL 檔案的包含完整路徑資訊的檔名，用

“%f”代表不包含路徑資訊的 WAL 檔案的檔名，修改後不需要重啟，reload即可。

--linux環境archive_mode = on             archive_command = 'test ! -f  /data/pgarch/%f &&  cp %p  /data/pgarch/%f'--windows環境archive_mode = on             archive_command = 'copy "%p" "D:\\ProgramFiles\\pgdata\\archive\\%f"'

3、基於pg_basebackup建立基礎備份

--建立REPLICATION角色CREATE ROLE replica login replication encrypted password 'replica@1234';--配置pg_hba.conf，允許遠端流式備份echo "host  replication replica  0.0.0.0/0   md5" >> pg_hba.conf--遠端使用pg_basebackup備份systemctl stop postgresqlrm -rf /data/pgdata/* && rm -rf /data/pgtablespace/*#-Fp表示以plain格式資料，-Xs表示以stream方式包含所需的WAL檔案，-P表示顯示進度，-R表示為replication寫配置資訊。#備份完成，使用-R選項，在data目錄下自動生成standby.signal“訊號”檔案（可手工使用touch命令生成）以及更新了postgresql.auto.conf檔案#postgresql.auto.conf中寫入了主庫的連線資訊（可手工新增primary_conninfo資訊）。pg_basebackup --progress -D /data/pgdata -h xx.142 -p 55432 -U replica --password  -Fp -Xs -P -R

1、環境準備

create table hwb(a int);insert into hwb values (generate_series(1,1000000));select now();-- 2020-12-15 15:16:03.645+08select pg_switch_wal();delete from hwb;select now();-- 2020-12-15 15:17:55.078991+08select pg_switch_wal();--傳輸歸檔日誌到異地伺服器恢復scp -r /data/pgarch/*  [email protected]:/data/pgarch

2、恢復到指定時間點

--配置postgresql.conf(註釋archive相關引數)touch recovery.signalcat >> postgresql.conf <<EOFrestore_command = 'cp /data/pgarch/%f %p'recovery_target_time = '2020-12-15 15:16:03.645+08'EOF--啟動資料庫（這時候就恢復了）systemctl start postgresql--恢復正常使用rm -rf recovery.signal在 postgresql.conf註釋restore相關引數後重啟

3、恢復到最近時間點

cat > recovery.signal <<EOFrestore_command = 'cp /data/pgarch/%f %p'recovery_target_timeline='latest'EOF

配置檔案 recovery.conf 新增上面兩項。12 透過 recovery.signal 檔案觸發

4、恢復到指定還原點

postgres=# select pg_create_restore_point('my-restore-point1');pg_create_restore_point-------------------------0/8000290(1 row)restore_command = 'cp /data/pgarch/%f %p'recovery_target_name='my-restore-point1'

配置檔案 recovery.conf 新增上面兩項。12 透過 recovery.signal 檔案觸發

5、恢復到指定事務

select txid_current(); --查詢當前事務 xidrestore_command = 'cp /data/pgarch/%f %p'recovery_target_xid=723

配置檔案 recovery.conf 新增上面兩項。12 透過 recovery.signal 檔案觸發

6、恢復到指定時間線

restore_command = 'cp /data/pgarch/%f %p'recovery_target_timeline=2recovery_target_time='2020-12-15 15:15:00'

配置檔案 recovery.conf 新增上面兩項。12 透過 recovery.signal 檔案觸發

後面會分享更多devops和DBA方面內容，感興趣的朋友可以關注下！