PostgreSQL参数hot_standby_feedback的理解

流复制集群：

主库：192.168.13.31
备库：192.168.13.32

数据库版本：

PostgreSQL 14.5

结论：

没有开启hot_standby_feedback的时候，主库在做vacuum的时候，不能感知备库的查询；
开启hot_standby_feedback的时候，主库在做vacuum的时候，会感知备库的查询，对备库要访问的数据的vacuum操作会延后；

测试过程：

主库

[postgres@duqk01 ~]$ psql
psql (14.5)
Type "help" for help.

postgres=# show synchronous_commit;
 synchronous_commit 
--------------------
 on
(1 row)

postgres=# show max_standby_streaming_delay;
 max_standby_streaming_delay 
-----------------------------
 -1
(1 row)

postgres=# 
postgres=# show autovacuum;
 autovacuum 
------------
 off
(1 row)

postgres=# show old_snapshot_threshold;
 old_snapshot_threshold 
------------------------
 -1
(1 row)

postgres=# show hot_standby_feedback;
 hot_standby_feedback 
----------------------
 off
(1 row)

postgres=# show wal_receiver_status_interval;
 wal_receiver_status_interval 
------------------------------
 1s
(1 row)

postgres=#

postgres=# create table t(id int,name varchar(20));
CREATE TABLE
postgres=# insert into t select id,'test'||id from generate_series(1,1000000) id;
INSERT 0 1000000
postgres=# 
postgres=# create table test(id int,name varchar(20));                              
CREATE TABLE
postgres=# insert into test select id,'test'||id from generate_series(1,10) id;          
INSERT 0 10

postgres=# insert into test select id,'test'||id from generate_series(1,1000) id;
INSERT 0 1000
postgres=# 

备库

[postgres@duqk02 ~]$ psql
psql (14.5)
Type "help" for help.

postgres=# begin;
BEGIN
postgres=*# select * from t where id=200;
 id  |  name   
-----+---------
 200 | test200
(1 row)

postgres=*# select count(*) from test;
 count 
-------
  1010
(1 row)

postgres=*#

主库

postgres=# drop table t;
DROP TABLE
postgres=# delete from test where id>2;
DELETE 1006
postgres=# 

备库

postgres=*# select * from t where id=200;
 id  |  name   
-----+---------
 200 | test200
(1 row)

postgres=*# select count(*) from test;   
 count 
-------
  1010
(1 row)

postgres=*# 

主库

postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 11666
usesysid         | 16388
usename          | replica
application_name | walreceiver
client_addr      | 192.168.13.32
client_hostname  | 
client_port      | 34830
backend_start    | 2022-10-17 20:24:46.059948+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/49B49910
write_lsn        | 0/49B49910
flush_lsn        | 0/49B49910
replay_lsn       | 0/49B2F5C8
write_lag        | 00:00:00.000672
flush_lag        | 00:00:00.001138
replay_lag       | 00:00:43.282024
sync_priority    | 0
sync_state       | async
reply_time       | 2022-10-17 21:14:53.652254+08

postgres=# \x
Expanded display is off.
postgres=# 
postgres=# vacuum (verbose,full) test;
INFO:  vacuuming "public.test"
INFO:  "test": found 85 removable, 4 nonremovable row versions in 6 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
postgres=# 

备库

postgres=*# commit;
COMMIT
postgres=#

修改参数重启备库
[postgres@duqk02 ~]$ psql
psql (14.5)
Type "help" for help.

postgres=# show hot_standby_feedback;
 hot_standby_feedback 
----------------------
 on
(1 row)

postgres=#

主库

postgres=# truncate table test;
TRUNCATE TABLE
postgres=# insert into test select id,'test'||id from generate_series(1,1000) id; 
INSERT 0 1000
postgres=#
postgres=# create table t(id int,name varchar(20));
CREATE TABLE
postgres=# insert into t select id,'test'||id from generate_series(1,1000000) id; 
INSERT 0 1000000
postgres=# 

备库

postgres=# begin;
BEGIN
postgres=*# select * from t where id=200;
 id  |  name   
-----+---------
 200 | test200
(1 row)

postgres=*# select count(*) from test;
 count 
-------
  1000
(1 row)

postgres=*#

主库

postgres=# drop table t;
DROP TABLE
postgres=# delete from test where id>2;
DELETE 998
postgres=# 

备库

postgres=*# select * from t where id=200;
 id  |  name   
-----+---------
 200 | test200
(1 row)

postgres=*# select count(*) from test;   
 count 
-------
  1000
(1 row)

postgres=*# 

主库

postgres=# vacuum (verbose,full) test;
INFO:  vacuuming "public.test"
INFO:  "test": found 0 removable, 1000 nonremovable row versions in 6 pages
DETAIL:  998 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
postgres=#

备库

postgres=*# end;
COMMIT
postgres=#

主库

postgres=*# end;
COMMIT
postgres=#