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

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示例：

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

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:添加逻辑复制所需的信息。

一个配置示例：

archive_mode = on
archive_command = 'cp %p /path/to//archive_dir/%f'
wal_level = replica

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选项。

示例：

# 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"

# 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中的时间线Timeline

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。

该文件的内容：

postgres> cat /home/postgres/archivelogs/00000002.history
1    0/A000198  before 2021-5-10 12:05:00.861324+00

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） 命名的还原点
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 备份的频率可能更方便，但对于大数据库就需要了。

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

先以 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 表恢复回来了。

先以 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、 使用 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、验证数据的完整性：
在此事务号以后创建的表都没有了，已经恢复成功。

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 快照方式备份与恢复

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、验证数据的完整性：
发现快照备份后的的事务能够恢复回来。

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

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 目录下的日志给删除了，但是进行备份的时候就报错，无法备份：

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 即可正常备份。

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恢复

整个示例恢复流程：

配置归档


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');

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');

做基础备份

-- 备份
pg_basebackup -h127.0.0.1 -Upostgres -p5433  -Fp -Pv -Xf  -D /bk

 tailf /pg13/pgdata/pg_log/postgresql-Thu.log

-- 备份

pg_basebackup -h127.0.0.1 -Upostgres -p5433 -Fp -Pv -Xf -D /bk

tailf /pg13/pgdata/pg_log/postgresql-Thu.log

备份过程：

[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

[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

做增量数据

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

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：

pg_ctl stop
mv /pg13/pgdata /pg13/pgdata_bk1
cp -R /bk  /pg13/pgdata
chmod 0700 /pg13/pgdata

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，表示数据库进入恢复模式，这一步是关键，恢复完成后会自动删除。

touch /pg13/pgdata/recovery.signal

1	touch /pg13/pgdata/recovery.signal

修改postgresql.auto.conf文件

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

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表。

启动数据库并验证数据

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)

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

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

(1 row)

查看数据，可知tl1_t1未被恢复。

告警日志内容：

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.

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继续恢复：

vi /pg13/pgdata/postgresql.auto.conf

recovery_target_time = '2022-03-10 12:11:51.280597+08'

-- 修改完成后重启
pg_ctl restart

vi /pg13/pgdata/postgresql.auto.conf

recovery_target_time = '2022-03-10 12:11:51.280597+08'

-- 修改完成后重启

pg_ctl restart

告警日志：

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.

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.

查看数据：

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

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

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

(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();结束恢复状态：

[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 ~]$

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

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

(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