postgres实现任意时间点恢复（使用pg_basebackup）

全量备份和增量备份时间线概念时间线可以理解为版本或者分支有了增量备份已经可以实现按照时间点恢复那为什么还要引入时间线比如在周三12点删错了一个表但是周五才发现这时候我们根据增量备份可以将数据库恢复到失误操作发生前的状态比如周三11点但是后来又根据实际情况需要恢复到周四早上8点。那么这是做不到的。因为在数据库不断运行中会产生与旧的WAL文件重名的文件这些文件进入归档目录时会覆盖原来的旧日志导致恢复数据库需要的 WAL文件丢失。为了避免这种情况需要区分原始数据库历史生成的WAL文件和完成恢复之后继续运行产生的重名的新WAL文件。为了解决这个问题PostgreSQL引入了时间线的概念。每当归档文件恢复完成后创建一个新的时间线用来区别新生成的WAL记录。时间线ID号是WAL文件名组成之一因此一个新的时间线不会覆盖由以前的时间线生成的WAL每个时间线类似一个分支在当前时间线的操作不会对其他时间线WAL造成影响有了时间线我们就可以恢复到之前的任何时间点。环境centos 7系统postgresql 12docker 20.10.6试验步骤配置postgresql.conf文件归档命令中的路径要事先创建好wal_level(枚举类型)pg10版本中待选的值为minimal、replica、logical。minimal --不能通过基础备份和wal日志恢复数据库。replica 9.6版本以前的archive和hot_standby --该级别支持wal归档和复制。logical --在replica级别的基础上添加了支持逻辑解码所需的信息归档命令%p 恢复文件的路径 拼上 恢复文件的文件名 用“/”拼接%f 只有恢复文件的文件名下面的归档命令实际上就是cp pg_wal/000000010000000000000001 /home/test/archive/000000010000000000000001 echo 000000010000000000000001 /home/test/archive/archive.listwal_keep_segmentspg_wal目录下所能保留的过去日志文件段的数目。每个段通常是 16 兆字节/home/test/archive/该目录必须为docker容器内的目录,如果想将其放到外部,需要使用docker挂载外面的目录.#日志级别 wal_level replica #归档开关 archive_mode on #归档命令 archive_command cp %p /home/test/archive/%f echo %f /home/test/archive/archive.list #用于指定pg_wal目录中保存的过去的wal文件wal 段的最小数量 wal_keep_segments1024重启pgdocker-compose stop查看归档目录此后数据库的操作都会记录wal日志文件到/home/test/archive目录下ls /home/test/archive/ 000000010000000000000001 000000010000000000000002 000000010000000000000003 000000010000000000000004 archive.list如果归档目录中没有内容需要执行强制日志归档命令select pg_switch_wal();如果还是没有查看数据库日志如果报错cpcan not create XXX permission denied给创建的归档目录授权777 然后再次重启 并查看 日志 看该报错是否消失如果消失则再去查看归档目录 看是否有上面类似内容如果没有 则执行强制归档命令select pg_switch_wal();chmod 777 /home/test/archive使用pg_basebackup进行基础备份pg_basebackup的参数-F, p|t指定输出格式p原样输出即把主数据库中的各个数据文件配置文件、目录结构都完全一样的写到备份目录t 把输出的备份文件打包到一个tar文件中。-z, --gzip使用gzip压缩仅能能与tar输出模式配合使用。-Z, --compress0-9指定压缩级别-x, --xlog (fetch mode)备份时会把备份中产生的xlog文件也自动备份出来这样才能在恢复数据库时应用这些xlog文件把数据库推到一个一致点然后真正打开这个备份的数据库这个选项与 -X fetch是完全一样的。使用这个选项需要设置“wal_keep_segments参数以保证在备份过程中需要的WAL日志文件不会被覆盖。-P, --progress在备份过程中实时打印备份进度-v, --verbose详细模式使用了-P后还会打印出正在备份的具体文件的信息。-D路径参数,指的依旧是容器内的路径,可以使用docker挂载docker exec -u postgres -it test_db_1 pg_basebackup -h 127.0.0.1 -U odoo -p 5432 -Ft -Pv -Xf -z -Z5 -D /var/lib/postgresql/data/pgdata_bk备份完成后对应备份目录会出现基础备份 是一个压缩包准备试验数据在2022-03-10 16:40:00 创建表dog 并随意插入几条数据等待适当时间在16:43:00创建表cat 并随意插入几条数据恢复时间点 2022-03-10 16:42:00 创建两张表之间的时间点就行如果恢复后数据库中存在dog表不存在cat表 则试验成功模拟数据库故障模拟数据库故障停掉数据库备份原来的data目录cp -r _data data_bk #删除原来data目录内容 rm -rf _data/* #将备份的压缩包复制到data目录中并解压 cp data_bk/pgdata_bk/base.tar.gz _data/ tar -zxvf base.tar.gz #删除备份 rm -rf base.tar.gz修改postgresql.conf设置归档命令和恢复时间点设置恢复时间点的时候要注意时区问题212 1 其实都是linux中的符号 包括0也是0代表标准输入1代表标准输出2代表标准错误输出代表重定向整体含义将标准错误输出重定向到标准输出这个路径还是容器内的路径restore_command cp /home/test/archive/%f %p /home/test/archive/recovery.log 21 recovery_target_time 2022-03-10 16:42:00添加空白文件recovery.signal文件添加该文件 提醒postgresql要做recoverytouch recovery.signal重启查看数据查看数据是否符合预期恢复后的数据库为只读模式 需要执行select pg_wal_replay_resume()使用到时间线的场景还是时间线概念标题的例子我们在周五恢复到周三完成之后又要恢复到周四显然没有时间线是不可行的因为周三和周四在同一条时间线上所以这个时候想恢复到周四就要指定时间线。指定哪条时间线指定周三那条时间线。每次恢复完成后数据库会自动创建一条时间线所以我们第二次恢复就要指定前面一条时间线。查看当前时间线的命令root69a829dac2b6:/# pg_controldata |grep TimeLineID Latest checkpoints TimeLineID: 8 Latest checkpoints PrevTimeLineID: 8例如指定时间线参数#指定时间线的时候 配的值为时间线的ID recovery_target_timeline 8