Problems with MaterializedPostgreSQL to Neon (LakeBase) #84431
Description
Company or project name
Databricks, Inc.
Neon (LakeBase)
Describe the unexpected behaviour
Hello!
We have errors while using MaterializedPostgreSQL with Neon starting from ClickHouse v. 24.9.
The replication slot is not created in a reasonable time. The requests to a Matherialized PgSQL table fail.
If you need access to a Neon instance for testing, we will kindly provide it.
Thank you!
Which ClickHouse versions are affected?
24.9 and above
How to reproduce
First, create a table on the Neon side and insert some data:
CREATE TABLE table1 (id integer primary key, column1 varchar(10));
INSERT INTO table1 (id, column1) VALUES (1, 'abc'), (2, 'def');
Then, raise a Docker container with ClickHouse 24.9+
% docker create --name clickhouse -p 9000:9000 -p 8123:8123 -e "CLICKHOUSE_PASSWORD=xIYh8UvrjJkMKAhe6mc=" clickhouse/clickhouse-server:24.9
fb1d38dbe757ceb4b32624b2b6e631be7a1b947bef9001821f7bdb425704a3c7
% docker start fb1d38dbe757ceb4b32624b2b6e631be7a1b947bef9001821f7bdb425704a3c7
fb1d38dbe757ceb4b32624b2b6e631be7a1b947bef9001821f7bdb425704a3c7
% docker exec -it fb1d38dbe757ceb4b32624b2b6e631be7a1b947bef9001821f7bdb425704a3c7 bash
root@fb1d38dbe757:/# clickhouse-client
ClickHouse client version 24.9.3.128 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 24.9.3.
Warnings:
* Linux transparent hugepages are set to "always". Check /sys/kernel/mm/transparent_hugepage/enabled
* Delay accounting is not enabled, OSIOWaitMicroseconds will not be gathered. You can enable it using `echo 1 > /proc/sys/kernel/task_delayacct` or by using sysctl.
fb1d38dbe757 :) SET allow_experimental_database_materialized_postgresql = 1
SET allow_experimental_database_materialized_postgresql = 1
Query id: f9dd581b-8f71-403a-b941-f03153f01758
Ok.
0 rows in set. Elapsed: 0.004 sec.
fb1d38dbe757 :) CREATE DATABASE db1_postgres ENGINE = MaterializedPostgreSQL('ep-delicate-frost-w398f84c-pooler.eastus2.azure.neon.build', 'neondb', 'neondb_owner', 'npg_YuTO52PHFMSV') SETTINGS materialized_postgresql_tables_list = 'table1';
CREATE DATABASE db1_postgres
ENGINE = MaterializedPostgreSQL('ep-delicate-frost-w398f84c-pooler.eastus2.azure.neon.build', 'neondb', 'neondb_owner', '***')
SETTINGS materialized_postgresql_tables_list = 'table1'
Query id: b4a4c8dd-64bf-4033-932e-9abbbe99cced
Ok.
0 rows in set. Elapsed: 0.008 sec.
fb1d38dbe757 :) select * from db1_postgres.table1 order by 1;
SELECT *
FROM db1_postgres.table1
ORDER BY 1 ASC
Query id: 37d076ac-3df2-4e70-9abc-671f94a25dcb
Elapsed: 0.003 sec.
Received exception from server (version 24.9.3):
Code: 60. DB::Exception: Received from localhost:9000. DB::Exception: Unknown table expression identifier 'db1_postgres.table1' in scope SELECT * FROM db1_postgres.table1 ORDER BY 1 ASC. (UNKNOWN_TABLE)
fb1d38dbe757 :) Bye.
(retries with a reasonable time don't help)
Expected behavior
The same on ClickHouse v. 24.8 works ok:
% docker create --name clickhouse -p 9000:9000 -p 8123:8123 -e "CLICKHOUSE_PASSWORD=xIYh8UvrjJkMKAhe6mc=" clickhouse/clickhouse-server:24.8
a271e8573c79a781ddec50ebc5e5c3902d10517356f162e105327325d4ed1ec1
% docker start a271e8573c79a781ddec50ebc5e5c3902d10517356f162e105327325d4ed1ec1
a271e8573c79a781ddec50ebc5e5c3902d10517356f162e105327325d4ed1ec1
% docker exec -it a271e8573c79a781ddec50ebc5e5c3902d10517356f162e105327325d4ed1ec1 bash
root@a271e8573c79:/# clickhouse-client
ClickHouse client version 24.8.14.39 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 24.8.14.
Warnings:
* Linux transparent hugepages are set to "always". Check /sys/kernel/mm/transparent_hugepage/enabled
* Delay accounting is not enabled, OSIOWaitMicroseconds will not be gathered. You can enable it using `echo 1 > /proc/sys/kernel/task_delayacct` or by using sysctl.
a271e8573c79 :) SET allow_experimental_database_materialized_postgresql = 1
SET allow_experimental_database_materialized_postgresql = 1
Query id: ad5e2655-a8b0-443b-aece-4b48c36e49cd
Ok.
0 rows in set. Elapsed: 0.002 sec.
a271e8573c79 :) CREATE DATABASE db1_postgres ENGINE = MaterializedPostgreSQL('ep-delicate-frost-w398f84c-pooler.eastus2.azure.neon.build', 'neondb', 'neondb_owner', 'npg_YuTO52PHFMSV') SETTINGS materialized_postgresql_tables_list = 'table1';
CREATE DATABASE db1_postgres
ENGINE = MaterializedPostgreSQL('ep-delicate-frost-w398f84c-pooler.eastus2.azure.neon.build', 'neondb', 'neondb_owner', '***')
SETTINGS materialized_postgresql_tables_list = 'table1'
Query id: ccdefeba-d7d6-484e-8d70-4a713e211413
Ok.
0 rows in set. Elapsed: 0.008 sec.
a271e8573c79 :) select * from db1_postgres.table1 order by 1;
SELECT *
FROM db1_postgres.table1
ORDER BY 1 ASC
Query id: 4a4437a8-24ed-4ce3-9406-57ebb5a0bf78
┌─id─┬─column1─┐
1. │ 1 │ abc │
2. │ 2 │ def │
└────┴─────────┘
2 rows in set. Elapsed: 0.002 sec.
Error message and/or stacktrace
/var/log/clickhouse-server/clickhouse-server.err.log:
2025.07.25 07:07:24.712280 [ 248 ] {} <Error> DatabaseMaterializedPostgreSQL (db1_postgres): Unable to load replicated tables list
2025.07.25 07:07:24.712321 [ 248 ] {} <Error> DatabaseMaterializedPostgreSQL (db1_postgres): Failed to start replication from PostgreSQL, will retry. Error: std::exception. Code: 1001, type: pqxx::broken_connection, e.what() = connection to server at "ep-delicate-frost-w398f84c-pooler.eastus2.azure.neon.build" (4.152.100.207), port 5432 failed: could not open certificate file "/root/.postgresql/postgresql.crt": Permission denied
connection to server at "ep-delicate-frost-w398f84c-pooler.eastus2.azure.neon.build" (4.152.100.207), port 5432 failed: ERROR: connection is insecure (try using `sslmode=require`)
, Stack trace (when copying this message, always include the lines below):
0. pqxx::connection::complete_init() @ 0x000000001486fae4
1. postgres::Connection::updateConnection() @ 0x000000000e34372c
2. DB::PostgreSQLReplicationHandler::fetchRequiredTables() @ 0x000000000eb609f0
3. void std::__function::__policy_invoker<void ()>::__call_impl<std::__function::__default_alloc_func<DB::DatabaseMaterializedPostgreSQL::DatabaseMaterializedPostgreSQL(std::shared_ptr<DB::Context const>, String const&, StrongTypedef<wide::integer<128ul, unsigned int>, DB::UUIDTag>, bool, String const&, String const&, postgres::ConnectionInfo const&, std::unique_ptr<DB::MaterializedPostgreSQLSettings, std::default_delete<DB::MaterializedPostgreSQLSettings>>)::$_0, void ()>>(std::__function::__policy_storage const*) @ 0x000000000eb50f88
4. DB::BackgroundSchedulePool::threadFunction() @ 0x000000000e6acd2c
5. void std::__function::__policy_invoker<void ()>::__call_impl<std::__function::__default_alloc_func<ThreadFromGlobalPoolImpl<false, true>::ThreadFromGlobalPoolImpl<DB::BackgroundSchedulePool::BackgroundSchedulePool(unsigned long, StrongTypedef<unsigned long, CurrentMetrics::MetricTag>, StrongTypedef<unsigned long, CurrentMetrics::MetricTag>, char const*)::$_0>(DB::BackgroundSchedulePool::BackgroundSchedulePool(unsigned long, StrongTypedef<unsigned long, CurrentMetrics::MetricTag>, StrongTypedef<unsigned long, CurrentMetrics::MetricTag>, char const*)::$_0&&)::'lambda'(), void ()>>(std::__function::__policy_storage const*) @ 0x000000000e6add0c
6. void* std::__thread_proxy[abi:v15007]<std::tuple<std::unique_ptr<std::__thread_struct, std::default_delete<std::__thread_struct>>, void ThreadPoolImpl<std::thread>::scheduleImpl<void>(std::function<void ()>, Priority, std::optional<unsigned long>, bool)::'lambda0'()>>(void*) @ 0x000000000b971c34
7. start_thread @ 0x0000000000007624
8. ? @ 0x00000000000d162c
(version 24.9.3.128 (official build))
Additional context
No response