數(shù)據(jù)庫的PITR是一般數(shù)據(jù)庫都必須滿足的技術(shù)。其原理是依據(jù)之前的物理備份文件加上wal的預(yù)寫日志模式備份做的恢復(fù)。該技術(shù)支持8.*及以上版本。下面主要概述PITR的準(zhǔn)備和恢復(fù)過程。 測試環(huán)境

OS 環(huán)境：CentOS 6.2
數(shù)據(jù)庫 ：PostgreSQL 9.1.9


一、前期工作既要恢復(fù)，肯定是需要一個(gè)備份基礎(chǔ)的，否則再怎么的巧婦也難為無米之炊。
1.修改數(shù)據(jù)庫參數(shù)，修改postgresql.conf：

archive_mode = on
archive_timeout = 300   --單位是秒，此處以5分鐘為限強(qiáng)制歸檔，僅作測試
archive_command = 'cp %p /data/pgbackup/archive/%f'  -- 注意/data/pgbackup/archive/目錄權(quán)限， chmod -R 777 /data/pgbackup/archive/
wal_level = archive

修改完重啟下reload,DB

2.基于文件級別的持續(xù)備份，
a.基礎(chǔ)備份
postgres=# select pg_start_backup('backup_2012_05_20_14:22:10');

b.打包備份pg_data
# cd /data
# tar -cvzf pgdata.tar ./postgres
mv pgdata.tar /data/pgbackup/base/

c.結(jié)束基礎(chǔ)備份并切換歸檔

postgres=# select pg_stop_backup();

postgres=# select pg_switch_xlog();
 pg_switch_xlog
----------------
 0/C000020
(1 row)

postgres=# select pg_current_xlog_location();
 pg_current_xlog_location
--------------------------
 0/C000020
(1 row)

postgres=# create table test_1(id int,name varchar(50));
postgres=# insert into test_1 values (1,'kenyon');
INSERT 0 1

此時(shí)在pg_data路徑下會產(chǎn)生一個(gè)label,可以查看內(nèi)容有checkpoint時(shí)間,基礎(chǔ)備份的開始和結(jié)束時(shí)間，以及標(biāo)簽名稱等。因?yàn)橹耙呀?jīng)設(shè)置了archive的三個(gè)參數(shù)，可以在archive的備份路徑pg_home/archive下看到歸檔的文件會定時(shí)傳過來。

二、恢復(fù)過程
停數(shù)據(jù)庫
# pg_stop

假定數(shù)據(jù)庫的崩潰場景，將pgdata數(shù)據(jù)刪除
# rm -rf /database/pgdata

恢復(fù)之前備份的tar文件
# tar xvf pgdata.tar

刪除pg_xlog文件夾并重建
# rm -rf pg_xlog
# mkdir -p pg_xlog/archive_status

新建recovery.conf文件并修改
# vi /data/postgres/recovery.conf
--新增內(nèi)容，指定恢復(fù)文件和路徑，%f,%p見上面說明
restore_command = 'cp /data/pgbackup/archive/%f "%p"'

啟動數(shù)據(jù)庫

# pg_start
[postgres@localhost archive]$ psql
spsql (9.1.3)
Type "help" for help.

postgres=# select * from test_1;
 id |  name  
----+--------
  1 | kenyon
(1 rows)

--恢復(fù)成功，會恢復(fù)到之前接收到的最后一個(gè)歸檔文件。另外recovery.conf會改名變成recovery.done

日志內(nèi)容:

LOG:  shutting down
LOG:  database system is shut down
LOG:  database system was interrupted; last known up at 2012-05-20 22:23:15 CST
LOG:  starting archive recovery
LOG:  restored log file "000000010000000000000002" from archive
LOG:  redo starts at 0/8000078
LOG:  consistent recovery state reached at 0/C000000
LOG:  restored log file "000000010000000000000003" from archive
LOG:  restored log file "000000010000000000000004" from archive
LOG:  restored log file "000000010000000000000005" from archive
LOG:  restored log file "000000010000000000000006" from archive
LOG:  restored log file "000000010000000000000007" from archive
cp: cannot stat `/home/postgres/archive/000000010000000000000008': No such file or directory
LOG:  could not open file "pg_xlog/000000010000000000000008" (log file 0, segment 8): No such file or directory
LOG:  redo done at 0/1C000078
LOG:  last completed transaction was at log time 2012-05-20 23:01:22.960591+08
LOG:  restored log file "000000010000000000000007" from archive
cp: cannot stat `/home/postgres/archive/00000002.history': No such file or directory
LOG:  selected new timeline ID: 2
cp: cannot stat `/home/postgres/archive/00000001.history': No such file or directory
LOG:  archive recovery complete
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

PS:若要恢復(fù)到指定時(shí)間，還需要再recovery.conf中設(shè)置recovrey_target_time,recovery_target_timeline等參數(shù)

總結(jié)：pitr技術(shù)對于7*24小時(shí)支撐是至關(guān)重要的，但是如果數(shù)據(jù)庫非常小，增大pg_dump備份的頻率可能更方便，但對于大數(shù)據(jù)庫就需要了。