PG中使用pg_basebackup实现时间点恢复( Point-in-Time Recovery (PITR))
Tags: PGpg_basebackupPITR不完全恢复基于时间点的恢复备份恢复
pg_basebackup备份工具简介
参考:https://www.xmmup.com/pg_basebackupbubeifentemporary-tablehe-unlogged-table.html
该备份工具自动执行 pg_start_backup()和 pg_stop_backup()函数,而且备份速度和数据都比手动的备份快。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 1、 数据库处于归档模式 2、 备份: a) 产生压缩的 tar 包,-Ft 参数指定: pg_basebackup -D bk1 -Ft -z -P 此备份花的时间比较长,但是节省空间。 b) 产生跟源文件一样的格式,即原样格式,-Fp 参数指定: pg_basebackup -D bk2 -Fp -P 此备份方式很快,但是不节省空间。 3、 恢复: a) 关闭数据库或者 kill 服务器主进程模拟主机断电 pg_ctl stop b) 删除 data 目录下所有的文件,(如果是删除这个 data 目录,则下一次创建该目录时要求该目录的权限是 750,否则启动数据库时会报错): rm –rf $PGDATA/* c) 使用 tar 包进行恢复: tar -zvxf bk1/base.tar.gz -C /usr/local/pg12.2/data tar -zvxf bk1/pw_wal.tar.gz -C /usr/local/pg12.2/data/pg_wal d)或者使用原样文件备份进行恢复: cp –rf bk2/* $PGDATA e)在 postgres.conf 文件中添加如下 2 行: restore_command = 'cp /home/postgres/arch/%f %p' recovery_target_timeline = 'latest' f) 在$PGDATA 目录下 touch 一个空文件,告诉 pg 需要做 recovery: touch recovery.signal g) 启动数据库: pg_ctl start h) 登录数据库,执行函数(否则 pg 数据库处于只读状态): select pg_wal_replay_resume(); g)验证数据的完整性: testdb=# select count(*) from t1; count 524288 |
注意:
把数据库文件转储出来后,在启动数据库前 postgresql.conf 一定要添加恢复参数,否则只恢复到备份的时间点。
经过测试,第一次恢复后,数据库能够恢复到最新状态,t1 表的数据能够恢复到归档的最后位置。对数据库进行操作,切换几个日志后,再用之前的备份对数据库进行恢复,结果能够恢复到最新的状态,能够自动应用归档,此恢复方式比 mysql 要智能。
如果当前的日志丢失,pg 实际上做的是不完全恢复,但是恢复的时候没有提示,而且不需要显式进行 recovery,在启动数据库的时候自动运行,类似 oracle 的实例恢复。
PITR简介
1、什么是PITR ?
PITR: 全称是Point-In-Time-Recover (时间点恢复),是PG从8.0版本开始引入的一个特性,该特性可以使用基础备份和连续归档日志将数据库集群恢复到任意时间点。
2、什么是基础备份?怎么获取基础备份?
在pg中,也是从8.0版本中引入了在线物理全备份,运行中的整个数据库集群的快照(即物理备份数据)被称为基础备份。
在pg中可以由两种方法获取基础备份:
- 使用系统low-level函数:pg_start_backup、pg_stop_backup以及一些shell命令(如cp、tar、rsync等)。
- 使用pg_basebackup工具
3、pg_start_backup做了什么?
pg_start_backup和pg_stop_backup命令定义见这里:
src/backend/access/transam/xlogfuncs.c
链接:https://github.com/postgres/postgres/blob/master/src/backend/access/transam/xlogfuncs.c
pg_start_backup()是一个用来启动基本备份的函数。它是PostgreSQL 8.0中引入的原始物理备份API的一部分。
pg_start_backup主要执行以下几个操作:
- 强制服务器进入全页写模式(即使你设置了full_page_writes=off)。
- 执行checkpoint(我在前面checkpoint的文章中提过,数据库恢复的过程需要从一个REDO位置开始,所以pg_start_backup需要做checkpoint来创建一个REDO点)。
- 创建一个backup_label文件。
4、backup_label文件的内容和作用什么?
一个backup_label示例:
1 2 3 4 5 6 | START WAL LOCATION: 0/9000028 (file 000000010000000000000009) CHECKPOINT LOCATION: 0/9000060 BACKUP METHOD: pg_start_backup BACKUP FROM: master START TIME: 2021-5-10 11:45:19 GMT LABEL: Weekly Backup |
其中:
CHECKPOINT LOCATION: 这是该函数创建的检查点被记录的LSN位置。当使用这个基础备份恢复数据库时,PostgreSQL从backup_label文件中获取“CHECKPOINT LOCATION”,并开始恢复过程。
BACKUP METHOD: 标示用于进行基础备份的方法(pg_start_backup或pg_basebackup)。
START TIME: 执行pg_start_backup时的时间戳。
LABEL: 这是在调用pg_start_backup(LABEL)中指定的标签。
5、此时你可能有疑问,为什么checkpoint的检查点的位置保存在了backup_label,而不是我们已知的pg_control里?
因为数据库的备份时间可能很长的,期间可能会多次执行常规checkpoint,如果放在pg_control里,那这个位置就可能会常规checkpoint被覆盖了。
6、pg_stop_backup做了什么?
pg_stop_backup主要完成以下工作:
如果pg_start_backup强制打开了full_page_write,pg_stop_backup将会将其重置为非全页写模式。
创建备份历史文件(timeline.hostory)。
删除backup_label文件。(注:从基础备份恢复时,需要backup_label文件,其被复制到归档目录之后,在原始数据库集中就不再需要了。)
7、什么是数据库归档?
一般情况下,PostgreSQL数据库将wal文件保存在$PGDATA的pg_wal下。但是这些wal文件可能会被回收。为了避免这种情况,我们可以将wal文件的副本保存在除$PGDATA之外的单独目录中。在PG中,将wal文件复制到其他位置被称为归档(让服务器读取wal文件并应用它称为恢复)。
8、怎么开启归档?
在pg中开启归档,依赖三个配置:archive_mode、archive_command和wal_level
archive_mode:表示是否要启用wal归档。
archive_command:指定如何归档。此选项接受shell命令或shell脚本。
wal_level:可选项:
Minimal:只添加崩溃服务或立即关闭所需的信息。它不能用于复制或归档目的。
Replica: 表示wal将有足够的信息用于wal归档和复制。
Logical:添加逻辑复制所需的信息。
一个配置示例:
1 2 3 | archive_mode = on archive_command = 'cp %p /path/to//archive_dir/%f' wal_level = replica |
其中:
%p: 被替换为WAL文件的路径名。
%f: 被替换为WAL文件的文件名。
9、怎么做PITR(按时间点恢复)?
PITR工作需要两个重要的先决条件:
基础备份的可用性。
连续归档日志。
有了这两个为前提,然后需要配置restore_command和recovery_target选项。
示例:
1 2 3 4 5 | # Place archive logs under /mnt/server/archivedir directory. restore_command = 'cp /mnt/server/archivedir/%f %p' recovery_target_time = "2021-5-10 12:05 GMT" |
restore_command:指定从何处查找要在该服务器上重播的WAL文件。这个命令接受与archive_command相同的占位符。
recovery_target_time:该选项告诉服务器何时停止恢复或重放进程。一旦到达给定的时间戳,进程就会停止。
注:pg11.0以及之前,这两个参数在recovery.conf中进行设置。pg12.0以及之后在postgresql.conf中进行配置(12.0已经废除了recovery.conf,所有与recovery相关的参数都移到了postgresql.conf中)。除此之后,在版本12或更高版本中,当进行恢复时,还需要创建一个名为recovery.signal的空文件。
10、PITR的工作流程?
(1)PostgreSQL从backup_label文件中读取CHECKPOINT LOCATION的值,即REDO点。
(2) PostgreSQL从recovery.conf(版本11或更早)或PostgreSQL .conf(版本12或更高)中读取restore_command和recovery_target_time参数值。
(3) PostgreSQL从REDO点开始重放WAL数据,通过参数resotere_command,从归档日志中读取WAL数据,将归档日志从归档区复制到临时区。(临时区域中复制的日志文件使用后会被删除。)
注:如果没有配置recovery_target_time,PG将重放日志直到归档结束。
(4)恢复过程结束后,生成时间线历史文件,如00000002.History,该文件被创建在pg_xlog子目录(在版本10或更高版本,pg_wal子目录),如果启用了归档日志特性,归档目录下也会创建相同的命名文件。
11、PITR与正常的数据库恢复有何不同?
正常复模式是从base目录下的pg_wal目录中获取wal文件,而PITR模式是从archive_command中设置的归档目录中获取。
正常恢复模式从pg_control文件获取检查点位置,而PITR模式从backup_label文件中获取检查点位置。
12、什么是时间线?什么时间线历史文件?
PG中的时间线用于区分原始的数据库集群和恢复后的数据库集群,是PITR的核心概念。
每个数据库集群分配一个单独的timelineId。initdb实用程序创建的原始数据库集群的timelineId为1。当数据库集群恢复时,timelineId将增加1。
当我们启动PostgreSQL服务器时,初始时间线被设置为1,从pg_start_backup创建的REDO点到恢复目标,在归档日志中回放WAL数据,然后,一个新的timelineId 2被分配给恢复的数据库集,PostgreSQL将在新的时间线2上运行。如下图所示:
前面提到,当一个PITR进程完成时,会生时间线历史文件。这个文件的命名规则为:
“8位的新timelineid.history”,比如此例中,就是00000002.history。
该文件的内容:
1 2 | postgres> cat /home/postgres/archivelogs/00000002.history 1 0/A000198 before 2021-5-10 12:05:00.861324+00 |
第一列:timelineId—timelineId用于恢复的归档日志的id。
第二列:LSN WAL段发生切换的位置。
13、PITR有哪些参数可以配置?
默认情况下,恢复将会一直恢复到 WAL 日志的末尾。 在 recovery_target 、recovery_target_lsn、recovery_target_name、recovery_target_time 和 recovery_target_xid 中, 最多只能使用一个,如果在配置文件中使用了多个,将使用最后一个。
- recovery_target = 'immediate' :这个参数指定恢复应该在达到一个一致状态后尽快结束, 即尽早结束。在从一个在线备份中恢复时,这意味着备份结束的那个点。
- recovery_target_name (string) :指定(pg_create_restore_point()所创建)的已命名的恢复点,进行恢复。
- recovery_target_time (timestamp) :这个参数指定按时间戳恢复。
- recovery_target_xid (string) :这个参数指定按事务 ID 进行恢复。
- recovery_target_lsn (pg_lsn) :这个参数指定按继续进行的预写日志位置的 LSN 进行恢复。
- recovery_target_inclusive (boolean):指定我们是否仅在指定的恢复目标之后停止(true),
或者仅在恢复目标之前停止(false)。 适用于 recovery_target_lsn、recovery_target_time 或者 recovery_target_xid 被指定的情况。 这个设置分别控制事务是否有准确的目标 WAL 位置(LAN)、提交时间或事务 ID 将被包括在该恢复中。 默认值为 true。 - recovery_target_timeline (string) :指定恢复到一个特定的时间线中。默认值是沿着基础备份建立时的当前时间线恢复。将这个参数设置为 latest 会恢复到该归档中能找到的最新的时间线。
- recovery_target_action (enum) :指定在达到恢复目标时服务器应该立刻采取的动作,包括 pause(暂停)、promote(接受连接)、shutdown(停止服务器),其中 pause 为默认动作。
14、PITR类型有哪些?
1、基于命名还原点恢复
数据库的 PITR 是一般数据库都必须满足的技术;
其原理是依据之前的物理备份文件加上 wal 的预写日志模式备份做的恢复; 该技术支持 8.*及以上版本。
recovery.conf 文件还原点控制参数 --12.0 版本以下的
postgresql.conf 文件还原点控制参数 12.0 版本以上
1 2 3 4 5 6 7 8 9 10 11 | 1) 命名的还原点 recovery_target_name = 'first-pt' 指 pg_create_restore_point(text)创建的还原点,如果数据库中有多个重复命名的还原点, 遇到第一个则停止。因 为 它 不 需 要 abort 或 chepoint 判 断 结 束 点 , 不 需 要 判断 参 数 recovery_target_inclusive 的。 2) 目标时间还原点 recovery_target_time = '2020-02-27 06:53:42' 指 WAL 中 记 录 recordXtime(xl_xact_commit_compact->xact_time); 如果在同一个时间点有多个事务回滚或提交: 其值为 false 则恢复到这个时间点第一个回滚或提交的事务(含) 其值为 true 则恢复到这个时间点最后一个回滚或提交的事务(含) 如果时间点上刚好只有 1 个事务回滚或提交:那么其值为 true 和 false 一样, 恢复将处理到这个事务包含的 wal 信息(含) 如果时间点没有匹配的事务提交或回滚信息:那么其值 true 和 false 一样, 恢复将处理到这个时间后的下一个事务回滚或提交的wal 信息 3) ID 事务还原点 recovery_target_xid, 指 WalRecord->xl_xid, 可以配合 recovery_target_inclusive 使用, 但是 recovery_target_inclusive 只影响日志的输出, 并不影响恢复进程截至点的选择,截止于这个 xid 的 wal 位置. 也就是说无论如何都包含了这个事务的 wal 信息的recovery. 这里需要特别注意 xid 的信息体现在结束时, 而不是分配 xid 时. 所以恢复到xid=100 提交回滚点, 可能 xid=102 已经先提交了. 那么包含 xid=102 的 wal 信息会被recovery. 结论:PITR 技术对于 7*24 小时支撑是至关重要的,但是如果数据库非常小,增大pg_dump 备份的频率可能更方便,但对于大数据库就需要了。 |
2、基于 recovery_target_name
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | 先以 pg_basebackup 的方法完成一个基础备份,参考指令如下: mkdir /home/postgres/bk/`date +%F` pg_basebackup -F t –D /home/postgres/bk/`date +%F` 如果要压缩,则加一个参数-z 即可: pg_basebackup -F t -z -D /home/postgres/bk/`date +%F` 1、生成的备份文件如下: [postgres@PostgreSQL01]$ ls -l t ls bk/2021-07-11/ base.tar pg_wal.tar 2、继续插入数据: testdb=# insert into t1 select * from t1; testdb=# select count(*) from t1; count - 1048576 3、创建存储点: testdb# select pg_create_restore_point('first_pt'); 2021-07-11 02:07:05.782 EST [3684] LOG: restore point 'first_pt' created at 0/260001E0 2021-07-11 02:07:05.782 EST [3684] STATEMENT: select pg_create_restore_point('first_pt'); pg_create_restore_point - 0/260001E0 4、删除表: testdb=# drop table test_copy; 5、切换日志, 同时查看归档文件的名字 : testdb=# select pg_walfile_name(pg_switch_wal()); pg_walfile_name 00000001000000000000002A 6、关闭数据库pg_ctl stop -m fast 7、删除$PGDATA 目录下的所有文件: rm -rf $PGDATA/* 8、恢复数据库: tar -vxf /home/postgres/bk/2021-07-11/base.tar -C $PGDATA 9、修改 postgresql.conf 文件,添加如下一行: restore_command = 'cp /home/postgres/arch/%f %p' recovery_target_name = 'first_pt' 10、在$PGDATA 目录下创建空文件recovery.signal,这一步是关键,恢复完成后会自动删除。 touch recovery.signal 10、启动数据库: pg_ctl start 11、登录数据库执行函数: psql postgres=# select pg_wal_replay_resume(); 12、验证数据完整性: 检查发现 copy_test 表恢复回来了。 |
3、基于 recovery_target_xid 恢复方式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | 1、 使用 pg_basebackup 或者快照对数据库进行备份。 2、 查询当前的事务号(XID): SELECT CAST(txid_current() AS text); txid_current 954 3、 创建新的表: testdb=# create table t6_956 as select * from t1; testdb=# create table t6_957 as select * from t1; 4、 切换几个日志: testdb=# select pg_switch_wal(); pg_switch_wal - 0/56040D10 5、 关闭数据库: pg_ctl stop -D /datadir 6、 删除$PGDATA 目录下的所有文件: rm -rf /datadir 7、 恢复备份的数据库(这里用了快照备份的数据库): tar -zvxf /root/pgdatadir.tgz -C /datadir 8、 编辑 postgresql.conf 文件,添加如下两行: restore_command = 'cp /home/postgres/arch/%f %p' recovery_target_xid ='954' 9、 创建 recovery.signal 文件: touch recovery.signal 10、启动数据库: pg_ctl start -D /datadir 11、执行 pg_wal_replay_resume()函数: postgres=# select pg_wal_replay_resume(); pg_wal_replay_resume - (1 row) 12、验证数据的完整性: 在此事务号以后创建的表都没有了,已经恢复成功。 |
基于 recovery_target_time 恢 复 方 式 与 基 于 recovery_target_name 以 及 基 于
recovery_target_lsn(在创建存储点的时候会显示 lsn 号)恢复的方式一样。
4、基于 time 恢复方式
(recovery_target_lsn='0/50000140')基于 lsn 恢复时的提示:PostgreSQL 快照方式备份与恢复
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | root 用户操作: 1、创建一个文件: dd if=/dev/zero of=/var/local/pgdisk bs=1M count=2000 2、链接上一个 loop 设备: losetup /dev/loop2 /var/local/pgdisk 3、创建卷组 vgcreate VG_PG /dev/loop2 4、创建逻辑卷 lvcreate -L 800M -n lv_datadir VG_PG 5、创建文件系统 mkfs.ext4 /dev/VG_PG/lv_datadir 6、创建挂接点mkdir -p /datadir 7、挂接: mount /dev/VG_PG/lv_datadir /datadir 8、删除没有必要的目录rmdir /datadir/lost+found 9、修改权限 chown postgres:postgres /datadir 9、把 pg 数据库的所有数据复制到 datadir(postgres 用户操作) cp -a $PGDATA/* /datadir 10、启动 PG 数据库 pg_ctl -D /datadir start 11、创建快照备份需要的逻辑卷 lvcreate -s -n lv_datadirbackup -L 500M /dev/VG_PG/lv_datadir 12、创建快照文件系统挂接点mkdir /root/snapshot 13、挂接快照文件系统 mount /dev/VG_PG/lv_datadirbackup /root/snapshot 14、进入快照文件系统查看当前的内容,就可以看到 PG 数据库所有的数据文件。 cd /root/snapshot ls backup_label.old pg_dynshmem pg_multixact pg_snapshots pg_tblspc pg_xact postmaster.pid base pg_hba.conf pg_notify pg_stat pg_twophase postgresql.auto.conf serverlog 15、基于快照做备份 tar -czf /root/pgdatadir.tgz . 16、备份结束后卸载快照文件系统cd umount /root/snapshot 17、如果不需要,删除快照逻辑卷 lvremove VG_PG/lv_datadirbackup -f 18、使用快照备份做恢复 (postgres 用户) pg_ctl -D /datadir stop (root 用户) rm -rf /datadir/* chmod 755 /root (postgres 用户) tar -zvxf /root/pgdatadir.tgz -C /datadir/ 19、创建 recovery.signal,否则只会恢复到快照备份的状态,如果创建了则会恢复到最新状态,看来这个文件是提醒 pg 要做 recovery,这一步是关键: touch recovery.signal 20、编辑 postgressql.conf 添加如下 2 行: restore_command = 'cp /home/postgres/arch/%f %p' recovery_target_timeline = 'latest' 21、启动数据库 pg_ctl -D /datadir start 22、验证数据的完整性: 发现快照备份后的的事务能够恢复回来。 |
故障排除:
1) 由于之前做了一些恢复操作,日志太多,所以把 pg_wal 目录下的日志给删除了,但是进行备份的时候就报错,无法备份:
1 2 3 | pg_basebackup -D bk2 -Fp pg_basebackup: error: could not send replication command 'TIMELINE_HISTORY': ERROR: could not open file 'pg_wal/0000000A.history': No such file or directory 解决方法:随便找了一个日志改名为 0000000A.history 即可正常备份。 |
示例:按照recovery_target_time恢复
整个示例恢复流程:
配置归档
1 2 3 4 5 6 7 8 9 10 11 12 13 | cat >> /pg13/pgdata/postgresql.conf <<"EOF" wal_level='replica' archive_mode='on' archive_command='test ! -f /pg13/archive/%f && cp %p /pg13/archive/%f' restore_command='cp /pg13/archive/%f %p' archive_timeout=10 EOF pg_ctl restart select * from pg_settings where name in ('wal_level','archive_mode','archive_command'); |
做基础备份
1 2 3 4 5 | -- 备份 pg_basebackup -h127.0.0.1 -Upostgres -p5433 -Fp -Pv -Xf -D /bk tailf /pg13/pgdata/pg_log/postgresql-Thu.log |
备份过程:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | [pg13@lhrpgall ~]$ pg_basebackup -h127.0.0.1 -Upostgres -p5433 -Fp -Pv -Xf -D /bk Password: pg_basebackup: initiating base backup, waiting for checkpoint to complete WARNING: skipping special file "./.s.PGSQL.5433" pg_basebackup: checkpoint completed pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1 WARNING: skipping special file "./.s.PGSQL.5433"abel ) 39796/39796 kB (100%), 1/1 tablespace pg_basebackup: write-ahead log end point: 0/2000100 pg_basebackup: syncing data to disk ... pg_basebackup: renaming backup_manifest.tmp to backup_manifest pg_basebackup: base backup completed [pg13@lhrpgall ~]$ ll /pg13/pgdata/pg_wal/ total 32776 -rw------- 1 pg13 postgres 16777216 Mar 10 12:08 000000010000000000000002 -rw------- 1 pg13 postgres 337 Mar 10 12:08 000000010000000000000002.00000028.backup -rw------- 1 pg13 postgres 16777216 Mar 10 12:08 000000010000000000000003 drwx------ 1 pg13 postgres 4096 Mar 10 12:08 archive_status [pg13@lhrpgall ~]$ ll /pg13/archive/ total 32772 -rw------- 1 pg13 postgres 16777216 Mar 10 12:08 000000010000000000000001 -rw------- 1 pg13 postgres 16777216 Mar 10 12:08 000000010000000000000002 -rw------- 1 pg13 postgres 337 Mar 10 12:08 000000010000000000000002.00000028.backup [pg13@lhrpgall ~]$ more /pg13/archive/000000010000000000000002.00000028.backup START WAL LOCATION: 0/2000028 (file 000000010000000000000002) STOP WAL LOCATION: 0/2000100 (file 000000010000000000000002) CHECKPOINT LOCATION: 0/2000060 BACKUP METHOD: streamed BACKUP FROM: master START TIME: 2022-03-10 12:08:01 CST LABEL: pg_basebackup base backup START TIMELINE: 1 STOP TIME: 2022-03-10 12:08:01 CST STOP TIMELINE: 1 |
做增量数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | create table tl1_t1(a int); insert into tl1_t1 values (generate_series(1,10000)); select now(); -- 2022-03-10 12:11:29.371353+08 SELECT pg_walfile_name(pg_current_wal_lsn()); -- 000000010000000000000005 select pg_switch_wal(); create table tl1_t2(a int); insert into tl1_t2 values (generate_series(1,1000)); select now(); -- 2022-03-10 12:11:51.280597+08 SELECT pg_walfile_name(pg_current_wal_lsn()); -- 000000010000000000000007 select pg_switch_wal(); checkpoint; select pg_switch_wal(); -- 确保日志已经归档 [pg13@lhrpgall ~]$ ll /pg13/archive/ total 147460 -rw------- 1 pg13 postgres 16777216 Mar 10 12:08 000000010000000000000001 -rw------- 1 pg13 postgres 16777216 Mar 10 12:08 000000010000000000000002 -rw------- 1 pg13 postgres 337 Mar 10 12:08 000000010000000000000002.00000028.backup -rw------- 1 pg13 postgres 16777216 Mar 10 12:09 000000010000000000000003 -rw------- 1 pg13 postgres 16777216 Mar 10 12:10 000000010000000000000004 -rw------- 1 pg13 postgres 16777216 Mar 10 12:11 000000010000000000000005 -rw------- 1 pg13 postgres 16777216 Mar 10 12:11 000000010000000000000006 -rw------- 1 pg13 postgres 16777216 Mar 10 12:12 000000010000000000000007 -rw------- 1 pg13 postgres 16777216 Mar 10 12:12 000000010000000000000008 -rw------- 1 pg13 postgres 16777216 Mar 10 12:12 000000010000000000000009 |
数据库故障
假定数据库故障,停止数据库,备份故障data并将基础备份的back替换为新data:
1 2 3 4 | pg_ctl stop mv /pg13/pgdata /pg13/pgdata_bk1 cp -R /bk /pg13/pgdata chmod 0700 /pg13/pgdata |
不完全恢复到表tl1_t2
创建recovery.signal
在$PGDATA 目录下创建空文件recovery.signal,表示数据库进入恢复模式,这一步是关键,恢复完成后会自动删除。
1 | touch /pg13/pgdata/recovery.signal |
修改postgresql.auto.conf文件
1 2 3 4 | cat >> /pg13/pgdata/postgresql.auto.conf << "EOF" # restore_command = 'cp /pg13/archive/%f %p' recovery_target_time = '2022-03-10 12:11:29.371353+08' EOF |
recovery_target_time表示要恢复到的时间点。
我们先恢复到tl1_t1表,然后再恢复到tl1_t2表。
启动数据库并验证数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | pg_ctl start postgres=# \dt List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | tl1_t1 | table | postgres (1 row) postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) |
查看数据,可知tl1_t1未被恢复。
告警日志内容:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 2022-03-10 12:26:00.149 CST [3418] LOG: starting PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit 2022-03-10 12:26:00.150 CST [3418] LOG: listening on IPv4 address "0.0.0.0", port 5433 2022-03-10 12:26:00.150 CST [3418] LOG: listening on IPv6 address "::", port 5433 2022-03-10 12:26:00.207 CST [3418] LOG: listening on Unix socket "/pg13/pgdata/.s.PGSQL.5433" 2022-03-10 12:26:00.260 CST [3420] LOG: database system was interrupted; last known up at 2022-03-10 12:08:00 CST cp: cannot stat ‘/pg13/archive/00000002.history’: No such file or directory 2022-03-10 12:26:00.325 CST [3420] LOG: starting point-in-time recovery to 2022-03-10 12:11:29.371353+08 2022-03-10 12:26:00.360 CST [3420] LOG: restored log file "000000010000000000000002" from archive 2022-03-10 12:26:00.528 CST [3420] LOG: redo starts at 0/2000028 2022-03-10 12:26:00.552 CST [3420] LOG: consistent recovery state reached at 0/2000100 2022-03-10 12:26:00.553 CST [3418] LOG: database system is ready to accept read only connections 2022-03-10 12:26:00.604 CST [3420] LOG: restored log file "000000010000000000000003" from archive 2022-03-10 12:26:00.748 CST [3420] LOG: restored log file "000000010000000000000004" from archive 2022-03-10 12:26:00.907 CST [3420] LOG: restored log file "000000010000000000000005" from archive 2022-03-10 12:26:01.068 CST [3420] LOG: restored log file "000000010000000000000006" from archive 2022-03-10 12:26:01.163 CST [3420] LOG: recovery stopping before commit of transaction 490, time 2022-03-10 12:11:42.132026+08 2022-03-10 12:26:01.164 CST [3420] LOG: pausing at the end of recovery 2022-03-10 12:26:01.164 CST [3420] HINT: Execute pg_wal_replay_resume() to promote. |
继续增量恢复
由于备份文件中存在recovery.signal文件,所以,此时数据库仍然处于恢复状态,可以修改recovery_target_time继续恢复:
1 2 3 4 5 6 | vi /pg13/pgdata/postgresql.auto.conf recovery_target_time = '2022-03-10 12:11:51.280597+08' -- 修改完成后重启 pg_ctl restart |
告警日志:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | 2022-03-10 12:27:14.151 CST [3418] LOG: received fast shutdown request 2022-03-10 12:27:14.226 CST [3418] LOG: aborting any active transactions 2022-03-10 12:27:14.227 CST [3423] LOG: shutting down 2022-03-10 12:27:14.333 CST [3418] LOG: database system is shut down 2022-03-10 12:27:14.432 CST [3500] LOG: starting PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit 2022-03-10 12:27:14.433 CST [3500] LOG: listening on IPv4 address "0.0.0.0", port 5433 2022-03-10 12:27:14.433 CST [3500] LOG: listening on IPv6 address "::", port 5433 2022-03-10 12:27:14.492 CST [3500] LOG: listening on Unix socket "/pg13/pgdata/.s.PGSQL.5433" 2022-03-10 12:27:14.578 CST [3502] LOG: database system was shut down in recovery at 2022-03-10 12:27:14 CST cp: cannot stat ‘/pg13/archive/00000002.history’: No such file or directory 2022-03-10 12:27:14.585 CST [3502] LOG: starting point-in-time recovery to 2022-03-10 12:11:51.280597+08 2022-03-10 12:27:14.619 CST [3502] LOG: restored log file "000000010000000000000002" from archive 2022-03-10 12:27:14.830 CST [3502] LOG: redo starts at 0/2000028 2022-03-10 12:27:14.865 CST [3502] LOG: restored log file "000000010000000000000003" from archive 2022-03-10 12:27:15.029 CST [3502] LOG: restored log file "000000010000000000000004" from archive 2022-03-10 12:27:15.172 CST [3502] LOG: restored log file "000000010000000000000005" from archive 2022-03-10 12:27:15.323 CST [3502] LOG: restored log file "000000010000000000000006" from archive 2022-03-10 12:27:15.456 CST [3502] LOG: consistent recovery state reached at 0/60069C8 2022-03-10 12:27:15.456 CST [3500] LOG: database system is ready to accept read only connections 2022-03-10 12:27:15.506 CST [3502] LOG: restored log file "000000010000000000000007" from archive 2022-03-10 12:27:15.639 CST [3502] LOG: restored log file "000000010000000000000008" from archive 2022-03-10 12:27:15.728 CST [3502] LOG: recovery stopping before commit of transaction 493, time 2022-03-10 12:12:42.04038+08 2022-03-10 12:27:15.728 CST [3502] LOG: pausing at the end of recovery 2022-03-10 12:27:15.728 CST [3502] HINT: Execute pg_wal_replay_resume() to promote. |
查看数据:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | postgres=# \dt List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | tl1_t1 | table | postgres public | tl1_t2 | table | postgres (2 rows) postgres=# select count(*) from tl1_t2; count ------- 1000 (1 row) postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) postgres=# create table tl2_t1(id int); ERROR: cannot execute CREATE TABLE in a read-only transaction postgres=# postgres=# \q [pg13@lhrpgall pg13]$ pg_controldata | grep TimeLine Latest checkpoint's TimeLineID: 1 Latest checkpoint's PrevTimeLineID: 1 |
表tl1_t2已恢复,时间线处于1。
恢复到RW状态
数据库操作select pg_wal_replay_resume();
结束恢复状态:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 | [pg13@lhrpgall pg13]$ psql psql (13.3) Type "help" for help. postgres=# select pg_wal_replay_resume(); pg_wal_replay_resume ---------------------- (1 row) postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row) postgres=# create table tl2_t1 (id int); CREATE TABLE postgres=# postgres=# insert into tl2_t1 values (generate_series(1,10000)); INSERT 0 10000 postgres=# select now(); now ------------------------------- 2022-03-10 12:29:50.131739+08 (1 row) postgres=# SELECT pg_walfile_name(pg_current_wal_lsn()); pg_walfile_name -------------------------- 000000020000000000000008 (1 row) postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/80B8450 (1 row) postgres=# \dt List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | tl1_t1 | table | postgres public | tl1_t2 | table | postgres public | tl2_t1 | table | postgres (3 rows) postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/9000078 (1 row) postgres=# \q [pg13@lhrpgall pg13]$ pg_controldata | grep TimeLine Latest checkpoint's TimeLineID: 2 Latest checkpoint's PrevTimeLineID: 1 [pg13@lhrpgall pg13]$ ll ./pgdata/recovery.signal ls: cannot access ./pgdata/recovery.signal: No such file or directory [pg13@lhrpgall ~]$ psql psql (13.3) Type "help" for help. postgres=# checkpoint; CHECKPOINT postgres=# select pg_switch_wal(); pg_switch_wal --------------- 0/B000078 (1 row) postgres=# exit [pg13@lhrpgall ~]$ pg_controldata | grep TimeLine Latest checkpoint's TimeLineID: 2 Latest checkpoint's PrevTimeLineID: 2 -- 确保日志已经归档 [pg13@lhrpgall ~]$ ll /pg13/pgdata/pg_wal/ total 131080 -rw------- 1 pg13 postgres 16777216 Mar 10 12:31 00000002000000000000000A -rw------- 1 pg13 postgres 16777216 Mar 10 12:31 00000002000000000000000B -rw------- 1 pg13 postgres 16777216 Mar 10 12:32 00000002000000000000000C -rw------- 1 pg13 postgres 16777216 Mar 10 12:27 00000002000000000000000D -rw------- 1 pg13 postgres 16777216 Mar 10 12:27 00000002000000000000000E -rw------- 1 pg13 postgres 16777216 Mar 10 12:30 00000002000000000000000F -rw------- 1 pg13 postgres 16777216 Mar 10 12:30 000000020000000000000010 -rw------- 1 pg13 postgres 16777216 Mar 10 12:27 000000020000000000000011 -rw------- 1 pg13 postgres 49 Mar 10 12:29 00000002.history drwx------ 2 pg13 postgres 4096 Mar 10 12:31 archive_status [pg13@lhrpgall ~]$ ll /pg13/archive/ total 229384 -rw------- 1 pg13 postgres 16777216 Mar 10 12:08 000000010000000000000001 -rw------- 1 pg13 postgres 16777216 Mar 10 12:08 000000010000000000000002 -rw------- 1 pg13 postgres 337 Mar 10 12:08 000000010000000000000002.00000028.backup -rw------- 1 pg13 postgres 16777216 Mar 10 12:09 000000010000000000000003 -rw------- 1 pg13 postgres 16777216 Mar 10 12:10 000000010000000000000004 -rw------- 1 pg13 postgres 16777216 Mar 10 12:11 000000010000000000000005 -rw------- 1 pg13 postgres 16777216 Mar 10 12:11 000000010000000000000006 -rw------- 1 pg13 postgres 16777216 Mar 10 12:12 000000010000000000000007 -rw------- 1 pg13 postgres 16777216 Mar 10 12:12 000000010000000000000008 -rw------- 1 pg13 postgres 16777216 Mar 10 12:12 000000010000000000000009 -rw------- 1 pg13 postgres 16777216 Mar 10 12:13 00000001000000000000000A -rw------- 1 pg13 postgres 16777216 Mar 10 12:30 000000020000000000000008 -rw------- 1 pg13 postgres 16777216 Mar 10 12:30 000000020000000000000009 -rw------- 1 pg13 postgres 16777216 Mar 10 12:31 00000002000000000000000A -rw------- 1 pg13 postgres 16777216 Mar 10 12:31 00000002000000000000000B -rw------- 1 pg13 postgres 49 Mar 10 12:29 00000002.history [pg13@lhrpgall ~]$ |
日志:
1 2 3 4 5 6 7 | 2022-03-10 12:29:00.842 CST [3502] LOG: redo done at 0/8004C10 2022-03-10 12:29:00.842 CST [3502] LOG: last completed transaction was at log time 2022-03-10 12:11:48.381625+08 cp: cannot stat ‘/pg13/archive/00000002.history’: No such file or directory 2022-03-10 12:29:00.912 CST [3502] LOG: selected new timeline ID: 2 2022-03-10 12:29:01.128 CST [3502] LOG: archive recovery complete cp: cannot stat ‘/pg13/archive/00000001.history’: No such file or directory 2022-03-10 12:29:01.577 CST [3500] LOG: database system is ready to accept connections |
recovery.signal文件自动被删除了,时间线进入了2。
在timeline2回退到tl1_t1表
这时候,如果发现恢复错误,我又想回到tl1_t1表的时间:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | -- 删除 pg_ctl stop mv /pg13/pgdata /pg13/pgdata_bk2 cp -R /bk /pg13/pgdata chmod 0700 /pg13/pgdata -- 恢复 touch /pg13/pgdata/recovery.signal cat >> /pg13/pgdata/postgresql.auto.conf << "EOF" # restore_command = 'cp /pg13/archive/%f %p' recovery_target_time = '2022-03-10 12:11:29.371353+08' EOF pg_ctl start postgres=# \dt List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | tl1_t1 | table | postgres (1 row) postgres=# select count(*) from tl1_t1; count ------- 10000 (1 row) postgres=# select pg_wal_replay_resume(); pg_wal_replay_resume ---------------------- (1 row) checkpoint ; create table tl3_t1 (id int); insert into tl3_t1 values (generate_series(1,10000)); select now(); -- 2022-03-10 12:40:04.243063+08 SELECT pg_walfile_name(pg_current_wal_lsn()); -- 000000030000000000000009 select pg_switch_wal(); checkpoint ; [pg13@lhrpgall ~]$ pg_controldata | grep TimeLine Latest checkpoint's TimeLineID: 3 Latest checkpoint's PrevTimeLineID: 3 [pg13@lhrpgall ~]$ ll /pg13/archive/ total 360460 -rw------- 1 pg13 postgres 16777216 Mar 10 12:08 000000010000000000000001 -rw------- 1 pg13 postgres 16777216 Mar 10 12:08 000000010000000000000002 -rw------- 1 pg13 postgres 337 Mar 10 12:08 000000010000000000000002.00000028.backup -rw------- 1 pg13 postgres 16777216 Mar 10 12:09 000000010000000000000003 -rw------- 1 pg13 postgres 16777216 Mar 10 12:10 000000010000000000000004 -rw------- 1 pg13 postgres 16777216 Mar 10 12:11 000000010000000000000005 -rw------- 1 pg13 postgres 16777216 Mar 10 12:11 000000010000000000000006 -rw------- 1 pg13 postgres 16777216 Mar 10 12:12 000000010000000000000007 -rw------- 1 pg13 postgres 16777216 Mar 10 12:12 000000010000000000000008 -rw------- 1 pg13 postgres 16777216 Mar 10 12:12 000000010000000000000009 -rw------- 1 pg13 postgres 16777216 Mar 10 12:13 00000001000000000000000A -rw------- 1 pg13 postgres 16777216 Mar 10 12:30 000000020000000000000008 -rw------- 1 pg13 postgres 16777216 Mar 10 12:30 000000020000000000000009 -rw------- 1 pg13 postgres 16777216 Mar 10 12:31 00000002000000000000000A -rw------- 1 pg13 postgres 16777216 Mar 10 12:31 00000002000000000000000B -rw------- 1 pg13 postgres 16777216 Mar 10 12:34 00000002000000000000000C -rw------- 1 pg13 postgres 49 Mar 10 12:29 00000002.history -rw------- 1 pg13 postgres 16777216 Mar 10 12:39 000000030000000000000006 -rw------- 1 pg13 postgres 16777216 Mar 10 12:39 000000030000000000000007 -rw------- 1 pg13 postgres 16777216 Mar 10 12:40 000000030000000000000008 -rw------- 1 pg13 postgres 16777216 Mar 10 12:40 000000030000000000000009 -rw------- 1 pg13 postgres 16777216 Mar 10 12:40 00000003000000000000000A -rw------- 1 pg13 postgres 16777216 Mar 10 12:40 00000003000000000000000B -rw------- 1 pg13 postgres 16777216 Mar 10 12:41 00000003000000000000000C -rw------- 1 pg13 postgres 100 Mar 10 12:38 00000003.history |
告警日志:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 2022-03-10 12:37:15.780 CST [4110] LOG: starting PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit 2022-03-10 12:37:15.780 CST [4110] LOG: listening on IPv4 address "0.0.0.0", port 5433 2022-03-10 12:37:15.780 CST [4110] LOG: listening on IPv6 address "::", port 5433 2022-03-10 12:37:15.855 CST [4110] LOG: listening on Unix socket "/pg13/pgdata/.s.PGSQL.5433" 2022-03-10 12:37:15.942 CST [4112] LOG: database system was interrupted; last known up at 2022-03-10 12:08:00 CST 2022-03-10 12:37:16.000 CST [4112] LOG: restored log file "00000002.history" from archive cp: cannot stat ‘/pg13/archive/00000003.history’: No such file or directory 2022-03-10 12:37:16.006 CST [4112] LOG: starting point-in-time recovery to 2022-03-10 12:11:29.371353+08 2022-03-10 12:37:16.012 CST [4112] LOG: restored log file "00000002.history" from archive 2022-03-10 12:37:16.091 CST [4112] LOG: restored log file "000000010000000000000002" from archive 2022-03-10 12:37:16.843 CST [4112] LOG: redo starts at 0/2000028 2022-03-10 12:37:16.875 CST [4112] LOG: consistent recovery state reached at 0/2000100 2022-03-10 12:37:16.875 CST [4110] LOG: database system is ready to accept read only connections 2022-03-10 12:37:16.938 CST [4112] LOG: restored log file "000000010000000000000003" from archive 2022-03-10 12:37:17.126 CST [4112] LOG: restored log file "000000010000000000000004" from archive 2022-03-10 12:37:18.377 CST [4112] LOG: restored log file "000000010000000000000005" from archive 2022-03-10 12:37:19.631 CST [4112] LOG: restored log file "000000010000000000000006" from archive 2022-03-10 12:37:20.985 CST [4112] LOG: recovery stopping before commit of transaction 490, time 2022-03-10 12:11:42.132026+08 2022-03-10 12:37:20.985 CST [4112] LOG: pausing at the end of recovery 2022-03-10 12:37:20.985 CST [4112] HINT: Execute pg_wal_replay_resume() to promote. 2022-03-10 12:38:13.042 CST [4112] LOG: redo done at 0/60069C8 2022-03-10 12:38:13.042 CST [4112] LOG: last completed transaction was at log time 2022-03-10 12:10:52.21124+08 cp: cannot stat ‘/pg13/archive/00000003.history’: No such file or directory 2022-03-10 12:38:13.085 CST [4112] LOG: selected new timeline ID: 3 2022-03-10 12:38:14.445 CST [4112] LOG: archive recovery complete 2022-03-10 12:38:14.452 CST [4112] LOG: restored log file "00000002.history" from archive 2022-03-10 12:38:14.948 CST [4110] LOG: database system is ready to accept connections |
时间线进入了3.
在timeline3恢复到tl2_t1表
此时,由于tl2_t1表在timeline2中,所以,必须配置recovery_target_timeline参数。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | -- 删除 pg_ctl stop mv /pg13/pgdata /pg13/pgdata_bk3 cp -R /bk /pg13/pgdata chmod 0700 /pg13/pgdata -- 恢复 touch /pg13/pgdata/recovery.signal cat >> /pg13/pgdata/postgresql.auto.conf << "EOF" # restore_command = 'cp /pg13/archive/%f %p' recovery_target_timeline = 2 recovery_target_time = '2022-03-10 12:29:50.131739+08' EOF pg_ctl start postgres=# \dt List of relations Schema | Name | Type | Owner --------+--------+-------+---------- public | tl1_t1 | table | postgres public | tl1_t2 | table | postgres public | tl2_t1 | table | postgres (3 rows) postgres=# select count(*) from tl2_t1; count ------- 10000 (1 row) postgres=# select pg_wal_replay_resume(); pg_wal_replay_resume ---------------------- (1 row) create table tl4_t1 (id int); insert into tl4_t1 values (generate_series(1,10000)); select now(); -- 2022-03-10 12:47:59.856196+08 SELECT pg_walfile_name(pg_current_wal_lsn()); -- 00000001000000000000001B select pg_switch_wal(); [pg13@lhrpgall ~]$ pg_controldata | grep TimeLine Latest checkpoint's TimeLineID: 4 Latest checkpoint's PrevTimeLineID: 2 [pg13@lhrpgall ~]$ psql psql (13.3) Type "help" for help. postgres=# checkpoint; CHECKPOINT postgres=# exit [pg13@lhrpgall ~]$ pg_controldata | grep TimeLine Latest checkpoint's TimeLineID: 4 Latest checkpoint's PrevTimeLineID: 4 |
可以看到,成功恢复,时间线从2直接到4。
总结
1、做实验时,在删除$PGDATA目录时,最好选择mv操作,因为有一些在线日志来不急归档,那么恢复时就会报错。
2、建表之后的时间做一次checkpoint操作,确保相关表的操作的日志已经归档,否则恢复会报错。
参考
https://www.xmmup.com/pg_basebackupbubeifentemporary-tablehe-unlogged-table.html
麦老师请教一下,恢复后数据是有了
但select pg_is_in_recovery();结果是t,
select pg_wal_replay_resume();执行了表也还是只读状态,
recovery.signal也还在
不知道是不是我漏掉什么操作?还是哪个步骤搞错?
那你得看告警日志有没有啥输出,告警日志很重要