ClickHouse: Clickhouse continuously generates error logs:"PostgreSQLReaplicaConsumer: connection error: Lost connection to the database server"

Hi， I’m new to clickhouse , here I got a problom:

clickhouse server version: 21.9.1.7503 (testing version) what did I do：

install clickhouse server 21.9.1.7503 with rpm packages and configure the root password.
start up clickhouse server
create a database test
use database test, and then create table with MaterializedPostgreSQL table engine CREATE TABLE test.tb1 (id Int32, … ) ENGINE = MaterializedPostgreSQL(‘xxxx:5432’, ‘dbName’, ‘tableName’, ‘root’, ‘xxx’) ORDER BY id PARTITION by (id % 20) PRIMARY key id; So far, everything is normal. but after I created tbl2, errors log came:
CREATE TABLE test.tb2 (id Int32, … ) ENGINE = MaterializedPostgreSQL(‘xxxx:5432’, ‘dbName’, ‘tableName2’, ‘root’, ‘xxx’) ORDER BY id PARTITION by (id % 20) PRIMARY key id; Even if I drop tbl2 now, the error log won’t go away， Unless I drop tbl1 as well ( That is, all tables created using the MaterializedPostgreSQL table engine are deleted ). btw, Synchronizing data from pg to clickhouse is ok. logs (Keep outputting ) looks like :

What’s the problem?

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 16 (6 by maintainers)

Most upvoted comments

I am also getting this error! I’m using the database engine, not the table engine, and I’m only syncing a single table.

Clickhouse version 21.8.4.51, PostgreSQL version PostgreSQL 13.3.

I’m a bit nervous that the Clickhouse engine is going to abandon its WAL slots and let the WAL space on the disk grow, causing problems with the production instance.

What you’re worried about is what I’m going through today. This morning, I received an O&M alarm. The disk space of the PG is used up by wal logs. Query the slots of the PG and find that many slots are used by clickhouse. (I think when the Materialized PostgreSQL database on the clickhouse side dropped or renamed , it is no longer used by clickhouse.)

PG slots: select * from pg_replication_slots;

column slot name :

We have created two MaterializedPostgreSQL datebase engine databases: syn_bepcloud_yellow and syn_bepcloud_yellow_v1. The former has been dropped, but the used slots still exist. I’m not sure if clickhouse should clean up the corresponding slot after dropping the database.

Another question is that there is a slot named xxx_tmp in the preceding figure. I do not know what its function is, but I observed it for a minute and found that its catalog_xmin has not been updated.

ohhhYang on Dec 24, 2021