recovery PITR 即时恢复

来源：互联网发布：python async def 编辑：程序博客网时间：2024/05/16 03:03

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 postgrespsql (9.3.4)Type "help" for help.postgres=# create table test(id integer);  CREATE TABLEpostgres=#  insert into test values(100);  INSERT 0 1postgres=# 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#在此处开始备份（从新开启一个终端）endpostgres=# 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 1postgres=# select now();                               now              ------------------------------- 2014-05-30 14:52:56.517052+08#隔一段时间后在插入一条记录并记录时间postgres=# insert into test values(300); INSERT 0 1postgres=# 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.confvi 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 datapg_ctl: another server might be running; trying to start server anywayserver starting[pg93@localhost ms]$ LOG:  database system was interrupted; last known up at 2014-05-30 15:30:01 CSTLOG:  starting point-in-time recovery to 2014-05-30 15:31:28.38385+08cp: cannot stat `/home/pg93/ms/data/arch/000000010000000000000003': No such file or directoryLOG:  redo starts at 0/3000090LOG:  consistent recovery state reached at 0/30000B8cp: cannot stat `/home/pg93/ms/data/arch/000000010000000000000004': No such file or directoryLOG:  recovery stopping before commit of transaction 1813, time 2014-05-30 15:32:37.68031+08LOG:  redo done at 0/4000148LOG:  last completed transaction was at log time 2014-05-30 15:30:23.75722+08cp: cannot stat `/home/pg93/ms/data/arch/00000002.history': No such file or directoryLOG:  selected new timeline ID: 2cp: cannot stat `/home/pg93/ms/data/arch/00000001.history': No such file or directoryLOG:  archive recovery completeLOG:  database system is ready to accept connectionsLOG:  autovacuum launcher started[pg93@localhost ms]$#查看恢复是否成功[pg93@localhost arch]$ psql -h localhost -p 5432 postgres postgrespsql (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这条记录说明恢复成功。

恢复结束后数据库目录下的recover.conf会自动更名为recover.done。

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 postgrespsql (9.3.4)Type "help" for help.postgres=# create table test(id integer);  CREATE TABLEpostgres=#  insert into test values(100);  INSERT 0 1postgres=# 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#在此处开始备份（从新开启一个终端）endpostgres=# select pg_stop_backup();  NOTICE:  pg_stop_backup complete, all required WAL segments have been archived pg_stop_backup ---------------- 0/20000B8(1 row)#插入一条数据200并记录事务idpostgres=# begin;BEGINpostgres=# insert into test values(200); INSERT 0 1postgres=# insert into test values(200); INSERT 0 1postgres=# insert into test values(200); INSERT 0 1postgres=# insert into test values(200); INSERT 0 1postgres=# select txid_current(); txid_current --------------         1812(1 row)postgres=# end;COMMITpostgres=# 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.confvi 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 = falserecovery_target_timeline = 'latest'pause_at_recovery_target = false............[pg93@localhost ms]$ pwd/home/pg93/ms#启动数据库[pg93@localhost ms]$ pg_ctl start -D dataserver starting[pg93@localhost ms]$ LOG:  database system was interrupted; last known up at 2014-05-30 15:57:06 CSTcp: cannot stat `/home/pg93/ms/data/arch/00000002.history': No such file or directoryLOG:  starting point-in-time recovery to XID 1812cp: cannot stat `/home/pg93/ms/data/arch/000000010000000000000002': No such file or directoryLOG:  redo starts at 0/2000090LOG:  consistent recovery state reached at 0/20000B8cp: cannot stat `/home/pg93/ms/data/arch/000000010000000000000003': No such file or directoryLOG:  recovery stopping before commit of transaction 1812, time 2014-05-30 15:57:48.846833+08LOG:  redo done at 0/3000198cp: cannot stat `/home/pg93/ms/data/arch/00000002.history': No such file or directoryLOG:  selected new timeline ID: 2cp: cannot stat `/home/pg93/ms/data/arch/00000001.history': No such file or directoryLOG:  archive recovery completeLOG:  database system is ready to accept connectionsLOG:  autovacuum launcher started[pg93@localhost ms]$#查看恢复是否成功[pg93@localhost ms]$ psql -h localhost -p 5432 postgres postgrespsql (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 EXTENSIONpostgres=#  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=0x01002048=0x0800512=0x02001024=0x04002304=0x0100|0x0800768=0x0100|0x02001280=0x0100|0x04002560=0x0800  |0x02003072=0x0800  |0x04001536= 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改变为还原点即可。

0 0