暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / Postgresql基于时间点恢复

Postgresql基于时间点恢复

漠南的工作笔记 2016-12-09
1151

PostgreSQL的WAL日志文件用于记录着数据库数据文件的每次改变。这个文件存在的意义就在于，当数据库异常崩溃后，能够根据WAL日志记录内容重放最后一次checkpoint之后的操作，保证数据保持一致。这个日志文件的存在，为我们提供了一种数据库热备的方式，也就是我们常说的PITR(Point-in-Time Recovery)。

他的基本实现逻辑就是，先在线备份出一个数据库的全量数据，之后根据WAL日志记录的内容回放操作，达到数据库状态一致（当然，如果你愿意，也可以恢复到某一固定的时间点）。以下是PITR搭建配置的基本流程。

一、  安装环境

OS

CentOS release 6.4  (Final)

PGDATA

/opt/pgsql9.5.1/pgdata

DB Version

Postgresql 9.5.1

二、搭建流程

1.依据安装手册搭建postgresql数据库。

2.修改配置文件postgresql.conf

wal_level =  hot_standby

 archive_mode = on

archive_command =  'test ! -f /home/postgres/arclog/%f &&  cp %p /home/postgres/arclog/%f'

3.全量备份当前数据库

 a. 启动数据库服务

        pg_ctl  start

 b. 模拟数据

     create    table stu(id int,info text);

     insert into    stu values(1,'wererwerwerw');

 c. 进入在线备份模式

        # select pg_start_backup('2016-08-16 12:57:00');

        pg_start_backup

        -----------------

        0/F000060

         (1 row)

  d. 新开一个session，对数据库的数据文件进行打包备份。

      $ tar    -cvzf data.tar pgdata

  e. 结束在线备份模式

         select pg_stop_backup();

         NOTICE:  pg_stop_backup    complete, all required WAL segments have been archived

         pg_stop_backup

         ----------------

         0/F000168

         (1 row)

    f. 切换归档

       postgres=# select pg_switch_xlog();

       pg_switch_xlog

       ----------------

       0/C000158

       (1 row)

 注：调用函数pg_start_backup会生成 backup_label文件。调用函数pg_stop_backup会删除backup_label文件。pg_start_backup会运行StartupXLOG函数，也就是说，backup_label文件，那么意味着它处正在从online backup中恢复的过程中。

        backup_label文件内容：

      [postgres@localhost    pgdata]$ more backup_label

       START WAL LOCATION: 0/F000060 (file    00000001000000000000000F)

       CHECKPOINT LOCATION: 0/F000098

       BACKUP METHOD: pg_start_backup

       BACKUP FROM: master

       START TIME: 2016-08-16 12:57:33 CST

       LABEL: 2016-08-16 12:57:00

备注：全库备份也可以使用其他的方式，例如 cp、pg_basebackup等。

4.关闭数据库服务

$ pg_ctl stop

5. 移除数据库数据文件,模拟数据损坏。

$ mv  /opt/pgsql9.5.1/pgdata /opt/pgsql9.5.1/pgdata_bad

6.恢复备份文件data.tar

$ tar -xvf data.tar

7.删除pg_xlog文件夹并重建

$ rm -rf pg_xlog

$ mkdir -p  pg_xlog/archive_status

8.拷贝配置recovery.conf文件

$ cp  $PGHOME/share/recovery.conf.sample $PGDATA/recovery.conf

$ grep ^[a-Z]  recovery.conf

   restore_command = 'cp  /home/postgres/arclog/%f %p'

   archive_cleanup_command='pg_archivecleanup  /home/postgres/arclog %r'

   recovery_target_time='2016-08-16 11:15:16'

注：恢复目标也可以指定为以下参数：

    recovery_target_name（string）     --恢复到已命名的恢复点

    recovery_target_xid (string)      --恢复到指定事务ID，精准的恢复位置受recovery_target_inclusive参数影响。

    recovery_target_inclusive   --确定在指定的恢复目标之后停止（true），或者仅在恢复目标之前停止（false）

9.进行数据恢复

pg_ctl start

三、恢复日志

2016-08-16 13:03:02.607 CST,,,14064,,57b29e86.36f0,1,,2016-08-16  13:03:02 CST,,0,LOG,00000,"ending log output to  stderr",,"Future log output will go to log destination  ""csvlog"".",,,,,,,""

2016-08-16 13:03:02.620 CST,,,14066,,57b29e86.36f2,1,,2016-08-16  13:03:02 CST,,0,LOG,00000,"database system was interrupted; last known  up at 2016-08-16 12:57:33 CST",,,,,,,,,""

2016-08-16 13:03:02.701 CST,,,14066,,57b29e86.36f2,2,,2016-08-16  13:03:02 CST,,0,LOG,00000,"starting point-in-time recovery to 2016-08-16  12:57:00+08",,,,,,,,,""

2016-08-16 13:03:02.751 CST,,,14066,,57b29e86.36f2,3,,2016-08-16  13:03:02 CST,,0,LOG,00000,"restored log file  ""00000001000000000000000F"" from  archive",,,,,,,,,""

2016-08-16 13:03:02.758 CST,,,14066,,57b29e86.36f2,4,,2016-08-16  13:03:02 CST,1/0,0,LOG,00000,"redo starts at  0/F000060",,,,,,,,,""

2016-08-16 13:03:02.761 CST,,,14066,,57b29e86.36f2,5,,2016-08-16  13:03:02 CST,1/0,0,LOG,00000,"consistent recovery state reached at  0/F000168",,,,,,,,,""

2016-08-16 13:03:02.762 CST,,,14064,,57b29e86.36f0,2,,2016-08-16  13:03:02 CST,,0,LOG,00000,"database system is ready to accept read only  connections",,,,,,,,,""

2016-08-16 13:03:02.811 CST,,,14066,,57b29e86.36f2,6,,2016-08-16  13:03:02 CST,1/0,0,LOG,00000,"restored log file  ""000000010000000000000010"" from  archive",,,,,,,,,""

2016-08-16 13:03:02.818 CST,,,14066,,57b29e86.36f2,7,,2016-08-16  13:03:02 CST,1/0,0,LOG,00000,"redo done at  0/10000060",,,,,,,,,""

2016-08-16 13:03:02.871 CST,,,14066,,57b29e86.36f2,8,,2016-08-16  13:03:02 CST,1/0,0,LOG,00000,"restored log file  ""000000010000000000000010"" from  archive",,,,,,,,,""

2016-08-16 13:03:02.882 CST,,,14066,,57b29e86.36f2,9,,2016-08-16  13:03:02 CST,1/0,0,LOG,00000,"selected new timeline ID:  2",,,,,,,,,""

2016-08-16 13:03:03.172 CST,,,14066,,57b29e86.36f2,10,,2016-08-16  13:03:02 CST,1/0,0,LOG,00000,"archive recovery  complete",,,,,,,,,""

2016-08-16 13:03:03.289  CST,,,14066,,57b29e86.36f2,11,,2016-08-16 13:03:02  CST,1/0,0,LOG,00000,"MultiXact member wraparound protections are now  enabled",,,,,,,,,""

2016-08-16 13:03:03.292 CST,,,14064,,57b29e86.36f0,3,,2016-08-16  13:03:02 CST,,0,LOG,00000,"database system is ready to accept  connections",,,,,,,,,""

2016-08-16 13:03:03.294 CST,,,14077,,57b29e87.36fd,1,,2016-08-16  13:03:03 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""

另外，恢复完成后，backup_label文件会更名为backup_label.old。

 至此，能够基于时间点恢复的数据库已经搭建完成了，是不是还挺简单呀~赶紧试试吧~


postgresql
文章转载自漠南的工作笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论