PostgreSQL至ClickHouse同步复制配置问题求助

问题根源 #

你给PostgreSQL添加的synchronous_standby_names: "'*'"存在两个关键问题：一是多了额外的单引号，属于语法错误；二是Materialized PostgreSQL引擎是通过逻辑复制槽拉取PG数据，并非PG的物理备库，用通配符*会让PG等待所有备库确认，但ClickHouse不属于PG的标准备库范畴，直接这么配置会导致PG一直等不到确认信号，复制流程彻底卡住。

正确配置步骤 #

1. 修正PostgreSQL的同步配置 #

去掉synchronous_standby_names里的多余单引号，并且指定ClickHouse对应的逻辑复制槽名称（不要用通配符）：

synchronous_commit = on
synchronous_standby_names = 'clickhouse_replication_slot'

注意：clickhouse_replication_slot要替换成你创建Materialized PostgreSQL表时实际指定的复制槽名称

2. 配置ClickHouse表的同步确认 #

创建Materialized PostgreSQL表时，必须显式指定复制槽，同时开启同步提交开关：

CREATE MATERIALIZED TABLE ch_db.ch_target_table
ENGINE = MaterializedPostgreSQL('pg_host:5432', 'pg_source_db', 'pg_source_table', 'pg_user', 'pg_password', 'clickhouse_replication_slot')
SETTINGS materialized_postgresql_synchronous_commit = 1;

materialized_postgresql_synchronous_commit = 1的作用是让ClickHouse写完数据后，向PG发送确认信号，这样PG的写入事务就会等待该确认返回后再完成，正好满足你“主库写入等待ClickHouse同步完成再提交”的需求。

3. 验证配置是否生效 #

在PG中执行插入或更新语句，观察事务是否会等待片刻（等待ClickHouse完成写入）后才返回，同时检查ClickHouse目标表的数据同步情况。另外可以通过PG的复制状态视图确认：

SELECT usename, application_name, state, sync_state FROM pg_stat_replication;

如果sync_state列显示sync，说明同步提交配置已正常生效。

排查要点 #

• 确认PG的postgresql.conf中wal_level已设置为logical，这是逻辑复制的必要前提
• 检查PG的操作用户是否拥有REPLICATION权限，以及对源表的读写权限
• 查看ClickHouse日志，排查是否存在连接失败、复制槽不存在等相关报错

内容的提问来源于stack exchange，提问作者casablanca