數據庫的PITR是一般數據庫都必須滿足的技術。其原理是依據之前的物理備份文件加上wal的預寫日志模式備份做的恢復。該技術支持8.*及以上版本。下面主要概述PITR的準備和恢復過程。 測試環境 ~~~ OS 環境：CentOS 6.2 數據庫 ：PostgreSQL 9.1.3 pg_home=/home/postgres/ pg_data=/database/pgdata/ ~~~ 一、前期工作既要恢復，肯定是需要一個備份基礎的，否則再怎么的巧婦也難為無米之炊。? 1.修改數據庫參數，修改postgresql.conf： ~~~ archive_mode = on archive_timeout = 300 --單位是秒，此處以5分鐘為限強制歸檔，僅作測試 archive_command = 'cp -i %p /home/postgres/archive/%f' wal_level = archive ~~~ 修改完重啟下reload,DB? 2.基于文件級別的持續備份，? a.基礎備份? postgres=# select pg_start_backup('backup_2012_05_20_14:22:10');? b.打包備份pg_data? # cd /database? # tar -cvzf pgdata.tar ./pgdata? c.結束基礎備份并切換歸檔 ~~~ 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 ~~~ 此時在pg_data路徑下會產生一個label,可以查看內容有checkpoint時間,基礎備份的開始和結束時間，以及標簽名稱等。因為之前已經設置了archive的三個參數，可以在archive的備份路徑pg_home/archive下看到歸檔的文件會定時傳過來。? 二、恢復過程 停數據庫? # pg_stop? 假定數據庫的崩潰場景，將pgdata數據刪除? # rm -rf /database/pgdata? 恢復之前備份的tar文件? # tar xvf pgdata.tar? 刪除pg_xlog文件夾并重建? # rm -rf pg_xlog # mkdir -p pg_xlog/archive_status? 拷貝recovery.conf文件并修改? # cp $PG_HOME/recovery.conf.sample /database/pgdata/? # vi /database/pgdata/recovery.conf? --新增內容，指定恢復文件和路徑，%f,%p見上面說明? restore_command = 'cp /home/postgres/archive/%f "%p"'? 啟動數據庫 ~~~ # 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) ~~~ --恢復成功，會恢復到之前接收到的最后一個歸檔文件。另外recovery.conf會改名變成recovery.done? 日志內容: ~~~ 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:若要恢復到指定時間，還需要再recovery.conf中設置recovrey_target_time,recovery_target_timeline等參數 總結：pitr技術對于7*24小時支撐是至關重要的，但是如果數據庫非常小，增大pg_dump備份的頻率可能更方便，但對于大數據庫就需要了。