recovery PITR 即时恢复

最新推荐文章于 2023-07-06 23:18:05 发布

weixin_30781775

最新推荐文章于 2023-07-06 23:18:05 发布

阅读量185

点赞数

文章标签：数据库网络

原文链接：http://www.cnblogs.com/xxvv/p/3766253.html

版权

http://blog.163.com/digoal@126/blog/static/16387704020131410250983/

http://www.cnblogs.com/gaojian/archive/2013/08/07/3243045.html

PITR有三种方式：

1：recovery_target_time

2：recovery_target_xid

3：recovery_target_name

前两种都与recovery_target_inclusive的配置（ recovery.conf ）有关。

pause_at_recovery_target：只对recovery_target_xid和recovery_target_time 有效，对于recovery_target_name 无效。
recovery_target_inclusive：在recovery.conf中recovery_target_inclusive=true 只作用于PITR到

recovery_target_xid (XID)/recovery_target_time (时间) 恢复时，不作用于recovery_target_name 。

下面来分别演示一下这三种方式的PITR：

1：recovery_target_time：

给予时间的恢复需要通过select now();方法来获取恢复的时间点。

[pg93@localhost ms]$ mkdir data
[pg93@localhost ms]$ pwd
/home/pg93/ms
[pg93@localhost ms]$ initdb -D data -E UTF8 --locale=C -U postgres -W  
[pg93@localhost ms]$ cd data
[pg93@localhost data]$ pwd
/home/pg93/ms/data
[pg93@localhost data]$ mkdir arch
[pg93@localhost data]$ vi postgresql.conf 
........
listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
wal_level = archive                     # minimal, archive, or hot_standby
                                        # (change requires restart)
archive_mode = on               # allows archiving to be done
                                # (change requires restart)
archive_command = 'cp %p /home/pg93/ms/data/arch/%f'            # command to use to archive a logfile segment
                                # placeholders: %p = path of file to archive
                                #               %f = file name only
                                # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
........
[pg93@localhost data]$ cd ..
[pg93@localhost ms]$ pwd
/home/pg93/ms
[pg93@localhost ms]$ pg_ctl start -D data

#查看启动是否成功
[pg93@localhost ms]$ netstat -anp|grep post
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      3055/postgres       
tcp        0      0 :::5432                     :::*                        LISTEN      3055/postgres       
udp        0      0 ::1:52886                   ::1:52886                   ESTABLISHED 3055/postgres       
unix  2      [ ACC ]     STREAM     LISTENING     44439  3055/postgres       /tmp/.s.PGSQL.5432

#登陆数据库postgres
[pg93@localhost ms]$ psql -h localhost -p 5432 postgres postgres
psql (9.3.4)
Type "help" for help.

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/2000028
(1 row)

#在此处开始备份（从新开启一个终端）begin
[pg93@localhost ms]$ pwd
/home/pg93/ms
[pg93@localhost ms]$ tar -cvf ./base.tar ./data
#在此处开始备份（从新开启一个终端）end

postgres=# select pg_stop_backup();  
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/20000B8
(1 row)
#插入一条数据200并记录时间
postgres=# insert into test values(200);            
INSERT 0 1
postgres=# select now();                 
              now              
-------------------------------
 2014-05-30 14:52:56.517052+08
#隔一段时间后在插入一条记录并记录时间
postgres=# insert into test values(300); 
INSERT 0 1
postgres=# select now();                 
              now              
-------------------------------
2014-05-30 14:55:07.873633+08
(1 row)
#切换事务日志
postgres=# select pg_switch_xlog();   
 pg_switch_xlog 
----------------
 0/3000298
(1 row)

#kill掉此数据库（在新终端）begin
[pg93@localhost ms]$ netstat -anp|grep post
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      3055/postgres       
tcp        0      0 :::5432                     :::*                        LISTEN      3055/postgres       
tcp        0      0 ::1:5432                    ::1:40192                   ESTABLISHED 3066/postgres: post 
udp        0      0 ::1:52886                   ::1:52886                   ESTABLISHED 3055/postgres       
unix  2      [ ACC ]     STREAM     LISTENING     44439  3055/postgres       /tmp/.s.PGSQL.5432
[pg93@localhost ms]$ kill -s SIGQUIT 3055
#kill掉此数据库（在新终端）end

开始恢复

[pg93@localhost ms]$ pwd
/home/pg93/ms
[pg93@localhost ms]$ mv ./data ./data.bak 
[pg93@localhost ms]$ tar -xvf base.tar ./data 
[pg93@localhost ms]$ rm -rf ./data/pg_xlog 
[pg93@localhost ms]$ cp -r ./data.bak/pg_xlog/ ./data 
[pg93@localhost ms]$ cd ./data/pg_xlog/archive_status/
[pg93@localhost archive_status]$ rm -f * 
#切换到数据库目录
[pg93@localhost data]$ pwd
/home/pg93/ms/data

#拷贝recovery文件
[pg93@localhost data]$ cp /opt/pgsql934/share/recovery.conf.sample recovery.conf
vi recovery.conf
............
recovery_target_time = '2014-05-30 14:53:56.517052+08'  # e.g. '2004-07-14 22:39:00 EST'
restore_command = 'cp /home/pg93/ms/data/arch/%f %p'            # e.g. 'cp /mnt/server/archivedir/%f %p'
............

[pg93@localhost ms]$ pwd
/home/pg93/ms
#启动数据库
[pg93@localhost ms]$ pg_ctl start -D data
pg_ctl: another server might be running; trying to start server anyway
server starting
[pg93@localhost ms]$ LOG:  database system was interrupted; last known up at 2014-05-30 15:30:01 CST
LOG:  starting point-in-time recovery to 2014-05-30 15:31:28.38385+08
cp: cannot stat `/home/pg93/ms/data/arch/000000010000000000000003': No such file or directory
LOG:  redo starts at 0/3000090
LOG:  consistent recovery state reached at 0/30000B8
cp: cannot stat `/home/pg93/ms/data/arch/000000010000000000000004': No such file or directory
LOG:  recovery stopping before commit of transaction 1813, time 2014-05-30 15:32:37.68031+08
LOG:  redo done at 0/4000148
LOG:  last completed transaction was at log time 2014-05-30 15:30:23.75722+08
cp: cannot stat `/home/pg93/ms/data/arch/00000002.history': No such file or directory
LOG:  selected new timeline ID: 2
cp: cannot stat `/home/pg93/ms/data/arch/00000001.history': No such file or directory
LOG:  archive recovery complete
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

[pg93@localhost ms]$
#查看恢复是否成功
[pg93@localhost arch]$ psql -h localhost -p 5432 postgres postgres
psql (9.3.4)
Type "help" for help.

postgres=# \z
                          Access privileges
 Schema | Name | Type  | Access privileges | Column access privileges 
--------+------+-------+-------------------+--------------------------
 public | test | table |                   | 
(1 row)

postgres=# select * from test;
 id  
-----
 100
 200
(2 rows)
#结果没有300这条记录说明恢复成功。

2：recovery_target_xid

恢复需要记录事务的xid，通过select txid_current();获取到

[pg93@localhost ms]$ mkdir data
[pg93@localhost ms]$ pwd
/home/pg93/ms
[pg93@localhost ms]$ initdb -D data -E UTF8 --locale=C -U postgres -W  
[pg93@localhost ms]$ cd data
[pg93@localhost data]$ pwd
/home/pg93/ms/data
[pg93@localhost data]$ mkdir arch
[pg93@localhost data]$ vi postgresql.conf 
........
listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
wal_level = archive                     # minimal, archive, or hot_standby
                                        # (change requires restart)
archive_mode = on               # allows archiving to be done
                                # (change requires restart)
archive_command = 'cp %p /home/pg93/ms/data/arch/%f'            # command to use to archive a logfile segment
                                # placeholders: %p = path of file to archive
                                #               %f = file name only
                                # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
........
[pg93@localhost data]$ cd ..
[pg93@localhost ms]$ pwd
/home/pg93/ms
[pg93@localhost ms]$ pg_ctl start -D data


#登陆数据库postgres
[pg93@localhost ms]$ psql -h localhost -p 5432 postgres postgres
psql (9.3.4)
Type "help" for help.

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/2000028
(1 row)

#在此处开始备份（从新开启一个终端）begin
[pg93@localhost ms]$ pwd
/home/pg93/ms
[pg93@localhost ms]$ tar -cvf ./base.tar ./data
#在此处开始备份（从新开启一个终端）end

postgres=# select pg_stop_backup();  
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/20000B8
(1 row)
#插入一条数据200并记录事务id

postgres=# begin;
BEGIN
postgres=# insert into test values(200); 
INSERT 0 1
postgres=# insert into test values(200); 
INSERT 0 1
postgres=# insert into test values(200); 
INSERT 0 1
postgres=# insert into test values(200); 
INSERT 0 1
postgres=# select txid_current();
 txid_current 
--------------
         1812
(1 row)

postgres=# end;
COMMIT
postgres=# select pg_switch_xlog(); 
 pg_switch_xlog 
----------------
 0/30001E8
(1 row)
#切换事务日志
postgres=# select pg_switch_xlog(); 
 pg_switch_xlog 
----------------
 0/30001E8
(1 row)

#kill掉此数据库（在新终端）begin
通过netstat 查找到数据库的pid为3432然后kill掉.
[pg93@localhost ms]$ netstat -anp|grep post

[pg93@localhost ms]$ kill -s SIGQUIT 3432  
#kill掉此数据库（在新终端）end

开始恢复

[pg93@localhost ms]$ pwd
/home/pg93/ms
[pg93@localhost ms]$ mv ./data ./data.bak 
[pg93@localhost ms]$ tar -xvf base.tar ./data 
[pg93@localhost ms]$ rm -rf ./data/pg_xlog 
[pg93@localhost ms]$ cp -r ./data.bak/pg_xlog/ ./data 
[pg93@localhost ms]$ cd ./data/pg_xlog/archive_status/
[pg93@localhost archive_status]$ rm -f * 
#切换到数据库目录
[pg93@localhost data]$ pwd
/home/pg93/ms/data

#拷贝recovery文件
[pg93@localhost data]$ cp /opt/pgsql934/share/recovery.conf.sample recovery.conf
vi recovery.conf
............
restore_command = 'cp /home/pg93/ms/data/arch/%f %p'            # e.g. 'cp /mnt/server/archivedir/%f %p'
#recovery_target_name = ''      # e.g. 'daily backup 2011-01-26'
#recovery_target_time = ''      # e.g. '2004-07-14 22:39:00 EST'
recovery_target_xid = '1812'
recovery_target_inclusive = false
recovery_target_timeline = 'latest'
pause_at_recovery_target = false
............

[pg93@localhost ms]$ pwd
/home/pg93/ms
#启动数据库
[pg93@localhost ms]$ pg_ctl start -D data
server starting
[pg93@localhost ms]$ LOG:  database system was interrupted; last known up at 2014-05-30 15:57:06 CST
cp: cannot stat `/home/pg93/ms/data/arch/00000002.history': No such file or directory
LOG:  starting point-in-time recovery to XID 1812
cp: cannot stat `/home/pg93/ms/data/arch/000000010000000000000002': No such file or directory
LOG:  redo starts at 0/2000090
LOG:  consistent recovery state reached at 0/20000B8
cp: cannot stat `/home/pg93/ms/data/arch/000000010000000000000003': No such file or directory
LOG:  recovery stopping before commit of transaction 1812, time 2014-05-30 15:57:48.846833+08
LOG:  redo done at 0/3000198
cp: cannot stat `/home/pg93/ms/data/arch/00000002.history': No such file or directory
LOG:  selected new timeline ID: 2
cp: cannot stat `/home/pg93/ms/data/arch/00000001.history': No such file or directory
LOG:  archive recovery complete
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

[pg93@localhost ms]$
#查看恢复是否成功
[pg93@localhost ms]$ psql -h localhost -p 5432 postgres postgres
psql (9.3.4)
Type "help" for help.

postgres=# select * from test;
 id  
-----
 100
(1 row)

此时没有查到插入进去的200数据则恢复成功。

在恢复时如果recovery_target_inclusive为true则在查询的时候会出现200这条数据：

此时没有显示200的原因为：
postgres=# create extension pageinspect ;
CREATE EXTENSION
postgres=#  select * from heap_page_items(get_raw_page('test',0));
 lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid 
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------
  1 |   8160 |        1 |     28 |   1811 |      0 |        0 | (0,1)  |           1 |       2304 |     24 |        |      
  2 |   8128 |        1 |     28 |   1812 |      0 |        0 | (0,2)  |           1 |       2560 |     24 |        |      
  3 |   8096 |        1 |     28 |   1812 |      0 |        0 | (0,3)  |           1 |       2560 |     24 |        |      
  4 |   8064 |        1 |     28 |   1812 |      0 |        0 | (0,4)  |           1 |       2560 |     24 |        |      
  5 |   8032 |        1 |     28 |   1812 |      0 |        0 | (0,5)  |           1 |       2560 |     24 |        |      
(5 rows)

解释几个参数：
t_xmin：为此条记录插入insert时的事务ID。
t_xmax：为此条记录更新update时的事务ID。
t_infomask：为HEAP_XMIN_INVALID ， HEAP_XMAX_INVALID和HEAP_XMIN_COMMITTED ，HEAP_XMAX_COMMITTED 和或关系,如下：
#define HEAP_XMIN_INVALID               0x0200  /* t_xmin invalid/aborted */
#define HEAP_XMAX_INVALID               0x0800  /* t_xmax invalid/aborted */ 
#define HEAP_XMIN_COMMITTED             0x0100  /* t_xmin committed */
#define HEAP_XMAX_COMMITTED             0x0400  /* t_xmax committed */

256=0x0100
2048=0x0800
512=0x0200
1024=0x0400
2304=0x0100|0x0800
768=0x0100|0x0200
1280=0x0100|0x0400
2560=0x0800  |0x0200
3072=0x0800  |0x0400
1536= 0x0200|0x0400

只有当t_infomask的值满足VALID并且COMMITTED时，此条记录才会显示。

对于上面举得XID恢复的例子中recovery_target_inclusive对于xid=1812，如果是true则显示，否则不显示。

3：recovery_target_name

大部分都和上面两种恢复方式相同，区别是

1：不依赖与recovery_target_inclusive和pause_at_recovery_target。

2：需要事先使用pg_create_restore_point 来创建一个还原点，然后在recovery.conf中将其中的recovery_target_name改变为还原点即可。

转载于:https://www.cnblogs.com/xxvv/p/3766253.html

weixin_30781775

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
recovery PITR 即时恢复

http://blog.163.com/digoal@126/blog/static/16387704020131410250983/http://www.cnblogs.com/gaojian/archive/2013/08/07/3243045.htmlPITR有三种方式：1：recovery_target_time2：recovery_target_xid3：recove...
复制链接

扫一扫