PostgreSQL基于时间点故障恢复PITR（ point-in-time recovery ）

PostgreSQL在使用过程中经常会发生一些失误的操作，但往往是可以弥补的。但是如果真遇到了无法挽回的误操作，只能寄希望于有备份了。

 

接下来的故障恢复也是基于有备份的情况，没有备份的情况，目前还没有想到怎么做。

 

1.首先在数据库中配置日志归档

1）创建归档目录

mkdir -p /var/lib/pgsql/pg10/archive/

 

2)修改postgresql.conf文件

wal_level=replica
archive_mode = on  
archive_command='test ! -f /var/lib/pgsql/9.10/archive/%f && cp %p /var/lib/pgsql/9.10/archive/%f'

 3）重启数据库

pg_ctl restart

 

2.对数据库进行全量备份，这里只是为了测试，就简单的对目录进行拷贝即可

cp -r $PGDATA ~/pg10/full_back

 

3.对数据库进行操作 并记录对应的日志号

select txid_current();
 txid_current
--------------
          557
(1 row)

              now
-------------------------------
 2018-09-03 18:07:14.288787+08
(1 row)

delete first 100 tuple of run_command
DELETE 99
 count
-------
 99901
(1 row)

select txid_current();
 txid_current
--------------
          559
(1 row)

              now
-------------------------------
 2018-09-03 18:07:14.500745+08
(1 row)

delete last 100 tuple of run_command
DELETE 100
 count
-------
 99801
(1 row)

select txid_current();
 txid_current
--------------
          561
(1 row)

              now
-------------------------------
 2018-09-03 18:07:14.571154+08
(1 row)

checkpoint
CHECKPOINT
pg_switch_wal
 pg_switch_wal
---------------
 0/3005FA0
(1 row)

checkpoint
CHECKPOINT
pg_switch_wal
 pg_switch_wal
---------------
 0/40000E8
(1 row)

 

 

4.设置recovery.conf文件

restore_command = 'cp /var/lib/pgsql/pg10/archive/%f %p' 
recovery_target_xid = '557' 
recovery_target_inclusive = false    
recovery_target_timeline = 'latest'

 

5.以为恢复成功了，结果发现系统只读，不能写，paused! 后续补充····，日志：

-bash-4.1$ cat log/postgresql-2018-09-03_181007.log
2018-09-03 18:10:07.160 CST [850] LOG:  database system was interrupted; last known up at 2018-09-03 18:07:12 CST
2018-09-03 18:10:07.160 CST [850] LOG:  creating missing WAL directory "pg_wal/archive_status"
cp: cannot stat `/var/lib/pgsql/pg10/archive/00000002.history': No such file or directory
2018-09-03 18:10:07.431 CST [850] LOG:  starting point-in-time recovery to 2018-09-03 18:07:14.500745+08
2018-09-03 18:10:07.448 CST [850] LOG:  restored log file "000000010000000000000002" from archive
2018-09-03 18:10:07.596 CST [850] LOG:  redo starts at 0/2000028
2018-09-03 18:10:07.613 CST [850] LOG:  consistent recovery state reached at 0/2003C30
2018-09-03 18:10:07.613 CST [848] LOG:  database system is ready to accept read only connections

看其他人使用过程中遇到文件不存在时，会自动创建一个新的时间线，然后恢复完成，而他们都是用的10以前版本，可能因此造成的吧。 

 

6.经过多次分析，在data目录的pg_wal中也没有发现 “00000002.history”文件，于是尝试重新回放日志，终于成功：

postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume
----------------------

(1 row)

postgres=# select pg_wal_replay_resume();
ERROR:  recovery is not in progress
HINT:  Recovery control functions can only be executed during recovery

postgres=# select count(*) from run_command ;
 count
-------
 99901
(1 row)

postgres=# insert into run_command values (1, 'test new');
INSERT 0 1
postgres=# \q

 

执行pg_wal_replay_resume()的日志：

-bash-4.1$ cat log/postgresql-2018-09-03_181007.log
2018-09-03 18:10:07.160 CST [850] LOG:  database system was interrupted; last known up at 2018-09-03 18:07:12 CST
2018-09-03 18:10:07.160 CST [850] LOG:  creating missing WAL directory "pg_wal/archive_status"
cp: cannot stat `/var/lib/pgsql/pg10/archive/00000002.history': No such file or directory
2018-09-03 18:10:07.431 CST [850] LOG:  starting point-in-time recovery to 2018-09-03 18:07:14.500745+08
2018-09-03 18:10:07.448 CST [850] LOG:  restored log file "000000010000000000000002" from archive
2018-09-03 18:10:07.596 CST [850] LOG:  redo starts at 0/2000028
2018-09-03 18:10:07.613 CST [850] LOG:  consistent recovery state reached at 0/2003C30
2018-09-03 18:10:07.613 CST [848] LOG:  database system is ready to accept read only connections
2018-09-03 18:10:07.646 CST [850] LOG:  restored log file "000000010000000000000003" from archive
2018-09-03 18:10:07.779 CST [866] LOG:  duration: 28.273 ms  statement: select count(*) from run_command
2018-09-03 18:10:07.797 CST [868] ERROR:  cannot execute INSERT in a read-only transaction
2018-09-03 18:10:07.797 CST [868] STATEMENT:  insert into run_command values(1, 'test new')
2018-09-03 18:10:07.804 CST [850] LOG:  recovery stopping before commit of transaction 560, time 2018-09-03 18:07:14.52735+08
2018-09-03 18:10:07.804 CST [850] LOG:  recovery has paused
2018-09-03 18:10:07.804 CST [850] HINT:  Execute pg_wal_replay_resume() to continue.
2018-09-03 18:10:21.263 CST [870] LOG:  duration: 0.697 ms  statement: select pg_wal_replay_resume();
2018-09-03 18:10:21.818 CST [850] LOG:  redo done at 0/3005E90
2018-09-03 18:10:21.818 CST [850] LOG:  last completed transaction was at log time 2018-09-03 18:07:14.496615+08
cp: cannot stat `/var/lib/pgsql/pg10/archive/00000002.history': No such file or directory
2018-09-03 18:10:21.886 CST [850] LOG:  selected new timeline ID: 2
cp: cannot stat `/var/lib/pgsql/pg10/archive/00000001.history': No such file or directory
2018-09-03 18:10:22.145 CST [850] LOG:  archive recovery complete
2018-09-03 18:10:22.476 CST [848] LOG:  database system is ready to accept connections
2018-09-03 18:10:22.775 CST [870] ERROR:  recovery is not in progress
2018-09-03 18:10:22.775 CST [870] HINT:  Recovery control functions can only be executed during recovery.
2018-09-03 18:10:22.775 CST [870] STATEMENT:  select pg_wal_replay_resume();

 

7.然后又尝试了使用时间和恢复点来回放，都没问题。

 

8.附上recovery.conf文件的配置：

在恢复过程中，用户可以通过使用recovery.conf文件来指定恢复的各个参数，如下：

归档恢复设置
restore_command：用于获取一个已归档段的XLOG日志文件的命令
archive_cleanup_command：清除不在需要的XLOG日志文件的命令
recovery_end_command：归档恢复结束后执行的命令

恢复目标设置（默认情况下，数据库将会一直恢复到 WAL 日志的末尾）
recovery_target = ’immediate’：在从一个在线备 份中恢复时，这意味着备份结束的那个点
recovery_target_name (string)：这个参数指定(pg_create_restore_point()所创建)的已命名的恢复点,将恢复到该恢复点
recovery_target_time (timestamp)：这个参数指定恢复到的时间戳
recovery_target_xid (string)：这个参数指定恢复到的事务 ID
recovery_target_inclusive (boolean)：指定是否在指定的恢复目标之后停止(true)，或者在恢复目标之前停止 (false)；适用于recovery_target_time或者recovery_target_xid被指定的情况；这个设置分别控制事务是否有准确的目标提交时间或 ID 是否将被包括在该恢复中；默认值为 true
recovery_target_timeline (string)：指定恢复到一个特定的时间线
recovery_target_action (enum)：指定在达到恢复目标时服务器应该立刻采取的动作，包括pause（暂停）、promote（接受连接）、shutdown（停止服务器），其中pause为默认动作

备库参数设置
standby_mode(boolean)：为on表示作为一个备库，否则不为备库
primary_conninfo (string)：指定备库连接主库的连接字符串
primary_slot_name (string)：通过流复制指定主库的一个复制槽来复制主库数据，如果没有设置primary_conninfo，则此参数无效
trigger_file (string)：指定一个触发器文件，该文件存在可以结束备库的恢复，即升级备库为一个独立的主库
recovery_min_apply_delay (integer)：这个参数允许将恢复延迟一段固定的时间，如果没有指定单位则以毫秒为单位。
如果recovery.conf中同时指定了recoveryTargetXid、recoveryTargetName、recoveryTargetTime时，PostgreSQL会按照RECOVERY_TARGET_XID> RECOVERY_TARGET_NAME > RECOVERY_TARGET_TIME的优先级来获取最终的目标恢复位点。

如果在recovery.conf指定recovery_targetTimeLine为latest，则可以基于当前TimeLineID为起点寻找最新时间线：

寻找当前TimeLineID的时间线历史文件“XXX.history”，如果存在则继续寻找，否则错误退出
TimeLineID是线性增长的，将当前TimeLineID自增1寻找是否存在时间线历史文件，直到不存在对应的时间线历史文件为止，即可找到最新的时间线。

 

 

后续准备找找如何在没有备份的情况下，恢复删除数据。。。。。。

转载于:https://www.cnblogs.com/kuang17/p/9566657.html