PostgreSQL 13 异步流复制+延迟备库（#2.2）-202103

PostgreSQL 13 异步流复制+延迟备库（#2.2）-202103

 

延迟备库原理：

炒股的童鞋都理解T+1业务，可以按照这个思路来理解延迟备考。

股市业务中T+1：T时间点交易结束，资金需要延迟1个交易日才会到账（假设下一个交易日是工作日）。

延迟备库中T+N：T时间点交易结束，备库需要延迟N时间后才会在备库进行wal日志流重做。

# 主库：数据时间切片为T

# 延迟备库：数据时间切片为T+N

 

场景简述：

这是巨量数据库备份恢复的补充方案，显然这个成本较低。

若主库T时间点误操作，我们可以在N时间段内轻松的完成T时间点内的数据恢复，因为备库延迟至T+N时间点才会开始WAL日志流重做。

 

## 异步延迟备库配置步骤

环境：

OS：RedHat 8.3

DB：PostgreSQL 13.1

 

1.异步流复制配置

# 参考《PostgreSQL 13 异步流复制（#2.1）-202103》

 

2.延迟参数设置

2.1 主备保证时间一致性

# 延迟应用会根据时间差进行校验同步，故需要主备时间一致。

2.2 主库添加监听和wal日志模式

# postgresql.conf参数配置

# Asynchronous stream replication

listen_addresses ='*'

wal_level = replica

# 参考《PostgreSQL 13 异步流复制（#2.1）-202103》

2.3 备库添加延迟应用参数

# postgresql.conf配置

recovery_min_apply_delay=1min

 

3.重启备库参数生效

pg_ctl -D $PGDATA stop -m fast

pg_ctl -D $PGDATA -l logfile start

 

4.延迟备库应用验证

4.1 主库

Create table test_delay(id int4,create_time timestamp(0) without time zone);

Insert into test_delay(id,create_time) values(1,now());

4.2 备库

Select now(),create_time from test_delay;

# 备库持续查询test_delay表

postgres=# Select now(),create_time from test_delay;

 now | create_time 

-----+-------------

(0 rows)

 

postgres=# Select now(),create_time from test_delay;

 now | create_time 

-----+-------------

(0 rows)

 

postgres=# Select now(),create_time from test_delay;

             now              |     create_time     

------------------------------+---------------------

 2021-03-31 14:16:58.12312+08 | 2021-03-31 14:15:58

(1 row)

# 最新业务数据在备库备应用的时间，正好与主库相差1分钟。

# 延迟备库生效！

 

4.3 删除需求测试

4.3.1 主库drop表

postgres=# drop table test_delay;

DROP TABLE

4.3.2 备库查看延迟时间内是否被删除

# 主库删除后，备库立即查看，此时hang住，直至备库test_delay被删除后数据库报错如下：

postgres=# Select now(),create_time from test_delay; <=hang

ERROR:  relation "test_delay" does not exist <=test_delay was drop.

LINE 1: Select now(),create_time from test_delay;

## 主库drop table，备库hang的原因：

## 第一个是锁机制问题：ACCESS SHARE仅与ACCESS EXCLUSIVE冲突；操作上select仅会被ACCESS EXCLUSIVE锁模式阻挡。

## 第二个是WAL日志流是如何记录锁和备库应用锁：exclusive lock 活动记录会被记录在WAL中，并且备库重放时保持hang住read replica。

参考：https://aws.amazon.com/cn/blogs/database/best-practices-for-amazon-rds-postgresql-replication/

Avoid exclusive lock on source instance tables

At the source instance, whenever you run commands such as DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY), Postgres processes an Access Exclusive lock.

ACCESS EXCLUSIVE is the most restrictive lock mode (conflicts with all other lock modes). This lock prevents all other transactions from accessing the table for the lock’s hold duration. Generally, the table remains locked until the transaction ends. This lock activity is recorded in WAL and is replayed and held by Read Replica. The longer the table remains under an ACCESS EXCLUSIVE lock, the longer the replication lag.

 

号外：

Oracle physical dataguard 是通过block-for-block进行复制，故不存在锁冲突的问题。若是用sql层复制均受锁冲突的限制。