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索引页

磨砺技术珠矶,践行数据之道,追求卓越价值

posted @ 2013-08-07 14:26  健哥的数据花园  阅读(3465)  评论(0编辑  收藏