[PostgreSQL/PPAS] How to do point in time recovery with PostgreSQL 9.3 - PITR

PITR一般在PostgreSQL中的應用有兩種:

• 用在Hot Streaming中shipping log用
• 用在Incremental backup做為Archive log用(continuous archiving)

實作PITR，從設定、備份到還原主要有幾個步驟，以下將實作每個步驟並針對各步驟說明。

0. 準備工作
    a. 建立Archive要存放的資料夾，並設定權限 (mkdir -p /pg_backup/arlog  && chown postgres.postgres /pg_backup/arlog)
    b. 建立Full Backup檔要存放的資料夾，並設定權限   (mkdir -p /pg_backup/fullbackup && chown postgres.postgres /pg_backup/fullbackup)
    c. 建立Restore要存放的資料夾，並設定權限 (mkdir -p /pg_backup/restore  && chown postgres.postgres /pg_backup/restore)

1.設定WAL(Write Ahead Log) archiving
於postgresql.conf內定義三個主要參數
wal_level = archive # or hot_standby for hot streaming...
archive_mode = on
archive_command = 'cp %p /pg_backup/arlog/%f'
設定完成後重啟Service當超過conf檔定義的wal log大小跟segement後就會開始copy到archive folder.



psql# -- 長出arlog...
psql# create table testPITR1 as select * from pg_class, pg_description;
psql# select current_timestamp;
psql# comment on table testpitr1 IS 'created at YYYY-MM-DD HH:MI:SS';

2.執行Base Backup (Full Backup)
--於psql下進行
   a. psql# select pg_switch_xlog();
   b. psql# select pg_start_backup('Backup-'||current_timestamp);

--於shell下進行 (tar檔名的命名規則一般以施作日期+pg_start_backup的回傳值，這樣方便管理)

   c. bash# tar -zcvf /pg_backup/fullbackups/bk20140315_12000028.tar data
--於psql下進行
   d. psql# select pg_stop_backup();

可於Backup後看到pg_xlog裡會多出.backup的檔。cat它能看到備份的資訊。

3. Recovering
   a. 先將Full Backup 的Tar檔解到Data目錄
        bash# tar -zxvf /pg_backup/fullbackups/bk20140315_12000028.tar
   b. 清空pg_xlog下所有檔案
        bash# rm -rf /opt/PostgreSQL/9.3/data/pg_xlog/*
   c. mv ARLOG/* RESTORELOG/
        bash# mv /pg_backup/arlog/* /pg_backup/restore/
   d. 編輯recovery.conf
        restore_command = 'cp /pg_backup/restore/%f %p'
        #recovery_target_time = '2014-03-15 15:20:00'    
    e. 重啟PostgreSQL
        bash# bin/pg_ctl -D /opt/PostgreSQL/9.3/data start

• 注意事項:
• 如果是recovery到Server crash前一秒，要記得把原有data folder下的pg_xlog下的所有檔案copy到新的data/pg_xlog下。
• 如果是recovery到某個time line，記得一定要清空tar檔解開的data folder下的pg_xlog內的所有檔案。

由下圖可看出recovery的情形，可注意到

1. recovery是從0...013開始一直到0...023並不是所有檔案。

2. 0...012之前的archive log在此Full backup 的tar下是沒有用的，可以直接purge掉。