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时,主端的事务提交需要等待相应的延迟时间后才会成功
--