PostgreSQL的 PITR实战---运用 recovery_target_time
磨砺技术珠矶，践行数据之道，追求卓越价值
回到上一级页面： PostgreSQL基础知识与基本操作索引页 回到顶级页面：PostgreSQL索引页
看了很多的例子，没有发现具体讲 recovery_target_time的例子，于是自己作一个吧
在开始之前，先把postgresql.conf的配置设置好：
wal_level = archive # minimal, archive, or hot_standby
# (change requires restart)
# - Archiving -
archive_mode = on # allows archiving to be done
# (change requires restart)
archive_command = 'cp %p /usr/local/pgsql/arch/%f' # command to use to archive a logfile segment
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
然后启动PostgreSQL：
[postgres@pg201 pgsql]$ ./bin/pg_ctl -D ./data start
在开始备份前，先作一条数据：此时，时间大约是13：40之前：
postgres=# create table test(id integer);
CREATE TABLE
postgres=# insert into test values(100);
INSERT 0 1
然后开始进行基础备份：
postgres=# select pg_start_backup('gao');
pg_start_backup
-----------------
0/2000020
(1 row)
tar命令打包：
tar -cvf ./base.tar ./data
结束基础备份：
postgres=# select pg_stop_backup();
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
----------------
0/20000A0
(1 row)
此时大约是13：40左右，再插入一条数据：
postgres=# insert into test values(200);
INSERT 0 1
等待，到13：50左右，再插入一条数据：
postgres=# insert into test values(300);
INSERT 0 1
为了方便操作，进行一次强制的日志切换：
postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/30002A0
(1 row)
postgres=#
现在，模拟崩溃，强制杀掉进程：
[root@pg201 ~]# kill -s SIGQUIT Postmaster进程号
然后，原有data目录改名保存，开始恢复过程：
[postgres@pg201 pgsql]$ mv ./data ./data.bak
[postgres@pg201 pgsql]$ tar -xvf base.tar ./data
把崩溃时后的online wal log 也准备好：
[postgres@pg201 pgsql]$ rm -rf ./data/pg_xlog
[postgres@pg201 pgsql]$ cp -r ./data.bak/pg_xlog/ ./data
[postgres@pg201 pgsql]$ cd ./data/pg_xlog
[postgres@pg201 pg_xlog]$
[postgres@pg201 archive_status]$ pwd
/usr/local/pgsql/data/pg_xlog/archive_status
[postgres@pg201 archive_status]$ rm -f *
[postgres@pg201 archive_status]$
作一个 recovery.conf文件，指明要恢复到13：45，也就是200数据已经出现，300数据尚未插入的时候
[postgres@pg201 data]$ pwd
/usr/local/pgsql/data
[postgres@pg201 data]$ cat recovery.conf
restore_command = 'cp /usr/local/pgsql/arch/%f %p'
recovery_target_time = '2013-08-07 13:45:00+08'
[postgres@pg201 data]$
再次启动PostgreSQL，发生了恢复：
[postgres@pg201 pgsql]$ ./bin/pg_ctl -D ./data start
pg_ctl: another server might be running; trying to start server anyway
server starting
[postgres@pg201 pgsql]$ LOG: database system was interrupted; last known up at 2013-08-07 13:39:51 CST
LOG: starting point-in-time recovery to 2013-08-07 13:45:00+08
LOG: restored log file "000000010000000000000002" from archive
LOG: redo starts at 0/2000078
LOG: consistent recovery state reached at 0/3000000
LOG: restored log file "000000010000000000000003" from archive
LOG: recovery stopping before commit of transaction 1685, time 2013-08-07 13:51:05.407695+08
LOG: redo done at 0/3000240
LOG: last completed transaction was at log time 2013-08-07 13:40:44.338862+08
cp: cannot stat `/usr/local/pgsql/arch/00000002.history': No such file or directory
LOG: selected new timeline ID: 2
cp: cannot stat `/usr/local/pgsql/arch/00000001.history': No such file or directory
LOG: archive recovery complete
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
验证：
[postgres@pg201 ~]$ cd /usr/local/pgsql
[postgres@pg201 pgsql]$ ./bin/psql
psql (9.1.2)
Type "help" for help.
postgres=# select * from test;
id
-----
100
200
(2 rows)
postgres=# \q
回到上一级页面： PostgreSQL基础知识与基本操作索引页 回到顶级页面：PostgreSQL索引页
磨砺技术珠矶，践行数据之道，追求卓越价值
