万万没想到，PostgreSQL备库把主库干翻了！

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：PGCM、OCP、YCP
Skill：Oracle、Mysql、PostgreSQL、国产数据库
Platform：CSDN、墨天轮、公众号（呆呆的私房菜）

备库配置：
postgres=# show hot_standby_feedback;
 hot_standby_feedback
----------------------
 on
(1 row)


主库配置：设置较小的唤醒时间以及垃圾回收阈值
postgres=# show autovacuum_naptime;
-[ RECORD 1 ]------+---
autovacuum_naptime | 1s


postgres=# show autovacuum_vacuum_scale_factor;
-[ RECORD 1 ]------------------+-------
autovacuum_vacuum_scale_factor | 0.0002


1. 创建表并插入数据
postgres=# create table tt1(id int, name text, current timestamp);
CREATE TABLE
postgres=# insert into tt1 select 1, md5(random()::text), now() from generate_series(1,10000000);
INSERT 0 10000000


2. 在备库上开启一个repeatable read事务，，执行查询
postgres=# begin transaction isolation level repeatable read;
BEGIN
postgres=# select count(*) from tt1;
  count
----------
 10000000
(1 row)


3. 主库执行更新tt1表
postgres=# update tt1 set name = 'name_test';


4. 查询tt1表当前统计信息，有1000w条dead tuple
postgres=#  select * from pg_stat_all_tables where relname = 'tt1';
-[ RECORD 1 ]-------+------------------------------
relid               | 41343
schemaname          | public
relname             | tt1
seq_scan            | 1
seq_tup_read        | 10000000
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 10000000
n_tup_upd           | 10000000
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 10000000
n_dead_tup          | 10000000
n_mod_since_analyze | 0
last_vacuum         |
last_autovacuum     | 2024-11-05 00:01:52.069311+08
last_analyze        |
last_autoanalyze    | 2024-11-04 23:29:39.84811+08
vacuum_count        | 0
autovacuum_count    | 47
analyze_count       | 0
autoanalyze_count   | 2

-- 关闭从库hot_standby_feedback 
postgres=# alter system set hot_standby_feedback = off;
ALTER SYSTEM


pg_ctl reload
server signaled


-- 垃圾已经被回收
postgres=# select * from pg_stat_all_tables where relname = 'tt1';
-[ RECORD 1 ]-------+------------------------------
relid               | 41343
schemaname          | public
relname             | tt1
seq_scan            | 1
seq_tup_read        | 10000000
idx_scan            |
idx_tup_fetch       |
n_tup_ins           | 10000000
n_tup_upd           | 10000000
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 10000000
n_dead_tup          | 0
n_mod_since_analyze | 0
last_vacuum         | 
last_autovacuum     | 2024-11-05 00:10:23.788943+08
last_analyze        |
last_autoanalyze    | 2024-11-04 23:29:39.84811+08
vacuum_count        | 1
autovacuum_count    | 59
analyze_count       | 0
autoanalyze_count   | 2


postgres=# select count(*) from tt1;
FATAL:  terminating connection due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.