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寻找是否存在时间线历史文件,直到不存在对应的时间线历史文件为止,即可找到最新的时间线。
后续准备找找如何在没有备份的情况下,恢复删除数据。。。。。。
严以律己、宽以待人