pg流复制_延迟备库

延迟备库与常规流复制备库搭建上没有区别，区别在于备端是否设置了recovery_min_apply_delay参数；
延迟备库可用于延迟应用主的变化，提供了源端误操作等的恢复功能。

--备设置recovery_min_apply_delay参数为1min，并重启生效（只reload可以查到参数但不生效）
postgres@postgres:alter system set recovery_min_apply_delay='1min';
ALTER SYSTEM
postgres@postgres:select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

postgres@postgres:show recovery_min_apply_delay;
 recovery_min_apply_delay 
--------------------------
 1min
(1 row)

[postgres@pg93 log]$ pg_ctl restart -D /pgdata/
waiting for server to shut down.... done
server stopped
waiting for server to start....2021-01-29 21:39:45.819 CST [9409] LOG:  starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
2021-01-29 21:39:45.819 CST [9409] LOG:  listening on IPv4 address "192.168.150.130", port 6000
2021-01-29 21:39:45.831 CST [9409] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.6000"
2021-01-29 21:39:45.884 CST [9409] LOG:  redirecting log output to logging collector process
2021-01-29 21:39:45.884 CST [9409] HINT:  Future log output will appear in directory "log".
 done
server started
--
--经测试如果主备两端系统时间不一致，则延迟备库并不会按配置的延迟来应用变更
--比如主的时间比备晚一天，备设置1天内的延迟都会被立即应用
--
--主插入一条数据
findb=# create table delay(c1 timestamp);
CREATE TABLE
findb=# insert into delay values(now());
INSERT 0 1
findb=# select now(),* from delay;
              now              |             c1             
-------------------------------+----------------------------
 2021-01-29 21:41:10.728824+08 | 2021-01-29 21:40:45.140996
(1 row)
--备端需要等待一分钟才能查到该条数据
fin_er@findb:select now(),* from delay;
 now | c1 
-----+----
(0 rows)

fin_er@findb:select now(),* from delay;
 now | c1 
-----+----
(0 rows)

fin_er@findb:select now(),* from delay;
              now              |             c1             
-------------------------------+----------------------------
 2021-01-29 21:41:48.207225+08 | 2021-01-29 21:40:45.140996
(1 row)
--因为延迟备库是实时接收WAL日志的，只是延迟应用，延迟时间需要根据主生成日志量和备端WAL存放目录大小来综合决定
--
--延迟备库在配置了synchronous_commit同步参数为on时，还是正常的延迟应用
--如果synchronous_commit为remote_apply时，主端的事务提交需要等待相应的延迟时间后才会成功
--