PostgreSQL 線上備份及即時回復設定及操作

PostgreSQL在任何時候都在維護一套預寫日誌(WAL)，這些日誌記錄著每次對資料庫的異動內容，其主要的目的是為了防止資料庫系統崩潰，資料庫可以透過重新放置上次檢查點，藉以日誌記錄來回復資料庫的完整性。日誌的存在還可以利用在另一種備份策略，日誌文件透過檔案系統備份，如需要回復時，以可以依據日誌文件的備份時間，重新放置日誌文件，把備份回復到備份的時間，以有效的管理資料庫的備份及回復作業。

環境說明
首先我們需要二台資料庫伺服器，第一台為Active的Primary server，而第二台為Standby的standby warm server，Standby warm server的目的為同步Primary server的資料，並且等待Primary server系統崩潰時，Standby warm server會轉變為Primary server，繼續提供服務。

環境設定
1. 建立Primary(192.168.0.100)及Standby warm server(192.168.0.101)之間的遠端日誌目錄
設定於Primary及Standby warm server，系統創建日誌目錄/pg_archivelog， Standby warm server透過nfs掛載Primary server的日誌目錄，以同步Primary server的資料。

a.創建日誌目錄
# mkdir /pg_archivelog
# chown postgres:postgres /pg_archivelog

b.設定nfsd於Primary server(需確認已安裝nfsd service)
# echo "/pg_archivelog 192.168.0.101(rw,sync,no_subtree_check)" >>/etc/exportfs
# exportfs -a

c.設定Standby warm server掛載Primary server的日誌目錄
# echo "192.168.0.100:/pg_archivelog /pg_archivelog nfs timeo=14,rsize=8192,wsize=8192,nolock,intr 0 0" >> /etc/fstab
# mount -a

d.設定Archive log，新增下列兩行於postgresql.conf，重新載入資料庫系統。
其中 %p 為資料庫要被歸檔的檔案絕對路徑， %f 為檔名。
archive_command = 'cp "%p" /pg_archivelog/"%f"'
archive_timeout = 120

$ pg_ctl reload

e.啟動Primary server，並且要確認日誌記錄檔是否正常產生。

2. 設定Primary server
a.建立基礎備份，執行 fullBackup.sh 備份，於 Primary server。
#!/bin/bash
BACKUP_FILE=`date +mydb_%Y%m%d.tgz`
PGHOME=/home/postgres

echo "Start dfdb backup ..."
echo "SELECT pg_start_backup('mydb');" | psql -U postgres

cd $PGHOME
echo "Backup $BACKUP_FILE ..."
tar cfz $BACKUP_FILE data pg_archivelog

echo "End dfdb backup ..."
echo "SELECT pg_stop_backup();" | psql -U postgres

3. 設定Standby warm server
a.停止資料庫系統
pg_ctl stop

b.複製基礎備份檔至Standby warm server
scp postgres@192.168.0.100:/home/postgres/mydb_20091007.tgz .

c.解開基礎備份檔，並且覆蓋資料庫系統
tar xfz mydb_20091007.tgz

d.刪除備份標記檔
rm /home/postgres/data/backup_label

e.設定回復檔 /home/postgres/data/recovery.conf
echo "restore_command='pg_standby -l -d -s 5 -k 50 -t /tmp/pgsql.trigger.5442 /pg_archivelog %f %p %r 2>>standby.log'" >/home/postgres/data/recovery.conf

f.啟動Standby warm server
pg_ctl start

※Standby warm server在啟動後會每5秒檢查一次是否有更新WAL日誌檔，並且更新至Standby warm server， 所以系統並沒有完全開啟，而是一直在回復資料(即是同步Primary server的資料)，因此系統啟動後並不能使用Standby warm server。

4. 模擬Primary server崩潰，Standby warm server接手工作
重頭戲來啦，要如何模擬Primary server崩潰呢？其實並不好模擬出來，所以我們要做SOP來標準化作業模式，當Primary server發生異常時，若決議要Standby warm server接續作業後，無論Primary server上頭是否還有正常的功能能運作，都需要把Primary server停止下來，接著產生Standby warm server的停止作業觸發檔(/tmp/pgsql.trigger.5442)，回復作業即會停止，Standby warm server即會正常啟動，變成Primary server提供服務給應用系統。
a.停止Primary server
pg_ctl stop

b.產生停止作業觸發檔
touch /tmp/pgsql.trigger.5442

c.更新應用系統的資料庫連線參數

5. Primary server回復作業
這個議題是在備援系統中往往會遺忘的部份，Primary server在修復後，總是要把工作交回給Primary server，因為Primary server往往設備會比Standby warm server較好一些，所以回復Primary server作業就會被需要。首先要做一份Primary server(原Standby warm server)基礎備份，在利用備份檔回復Primary server，即可開啟系統，Standby warm server再執行步驟 3 的作業。
a.做一份Primary server(原Standby warm server)基礎備份
請參照步驟 2

b.停止Primary server(原Standby warm server)
pg_ctl stop

c.複製基礎備份檔至Primary server
scp postgres@192.168.0.101:/home/postgres/mydb_20091007.tgz .

d.解開基礎備份檔，並且覆蓋資料庫系統
tar xfz mydb_20091007.tgz

e.刪除備份標記檔
rm /home/postgres/data/backup_label

f.設定回復檔 /home/postgres/data/recovery.conf
echo "cp /pg-archivelog/%f %p" >/home/postgres/data/recovery.conf

g.啟動Primary server
pg_ctl start

h.更新應用系統的資料庫連線參數

i.設定Standby warm server
請參照步驟 3

終於完工啦~~