ClickHouse: MaterializedPostgreSQL: Failure during '[END COPY]': ERROR: publication "xxx" does not exist

I have created logical replication slot in my pg and opened transaction to get snapshot id:

select * from pg_create_logical_replication_slot('clickhouse_02', 'pgoutput');

BEGIN;
SELECT pg_export_snapshot();

Then I have created db in clickhouse:

CREATE DATABASE IF NOT EXISTS dbname
    ENGINE = MaterializedPostgreSQL('host', 'pgdb', 'username', 'pass')
        SETTINGS
            materialized_postgresql_replication_slot = 'clickhouse_02',
            materialized_postgresql_snapshot = 'snapshotid',
            materialized_postgresql_tables_list = 'table1';

+ ended transaction in pg.

and it gives the error above about publication not existing.

How to reproduce

  • Which ClickHouse server version to use 22.11.2.30
  • Which interface to use, if matters
  • Non-default settings, if any
  • CREATE TABLE statements for all tables involved
  • Sample data for all these tables, use clickhouse-obfuscator if necessary
  • Queries to run that lead to unexpected result

Expected behavior

Working pg-clickhouse replication

Error message and/or stacktrace

2023.01.31 19:14:16.127953 [ 3376541 ] {} <Error> bool DB::MaterializedPostgreSQLConsumer::readFromReplicationSlot(): std::exception. Code: 1001, type: pqxx::sql_error, e.what() = Failure during '[END COPY]': ERROR:  publication "dbname_ch_publication" does not exist
CONTEXT:  slot "clickhouse_02", output plugin "pgoutput", in the change callback, associated LSN 1D0/4BE496B8
, Stack trace (when copying this message, always include the lines below):

0. pqxx::sql_error::sql_error(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, char const*) @ 0x18270359 in /usr/bin/clickhouse
1. pqxx::result::throw_sql_error(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>> const&) const @ 0x18272a66 in /usr/bin/clickhouse
2. pqxx::result::check_status(std::__1::basic_string_view<char, std::__1::char_traits<char>>) const @ 0x18272f19 in /usr/bin/clickhouse
3. pqxx::connection::make_result(pg_result*, std::__1::shared_ptr<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>> const&, std::__1::basic_string_view<char, std::__1::char_traits<char>>) @ 0x18265e92 in /usr/bin/clickhouse
4. pqxx::connection::read_copy_line() @ 0x1826a2cc in /usr/bin/clickhouse
5. pqxx::stream_from::get_raw_line() @ 0x18277200 in /usr/bin/clickhouse
6. pqxx::stream_from::parse_line() @ 0x18277537 in /usr/bin/clickhouse
7. DB::MaterializedPostgreSQLConsumer::readFromReplicationSlot() @ 0x12d3ef43 in /usr/bin/clickhouse
8. DB::MaterializedPostgreSQLConsumer::consume(std::__1::vector<std::__1::pair<int, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>>, std::__1::allocator<std::__1::pair<int, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char>>>>>&) @ 0x12d3fa39 in /usr/bin/clickhouse
9. DB::PostgreSQLReplicationHandler::consumerFunc() @ 0x12d228e5 in /usr/bin/clickhouse
10. DB::BackgroundSchedulePoolTaskInfo::execute() @ 0x1241fb61 in /usr/bin/clickhouse
11. DB::BackgroundSchedulePool::threadFunction() @ 0x1242310a in /usr/bin/clickhouse
12. ? @ 0x12423f2c in /usr/bin/clickhouse
13. ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0xdac43f6 in /usr/bin/clickhouse
14. ? @ 0xdac9561 in /usr/bin/clickhouse
15. start_thread @ 0x7ea7 in /usr/lib/x86_64-linux-gnu/libpthread-2.31.so
16. clone @ 0xfcaef in /usr/lib/x86_64-linux-gnu/libc-2.31.so
 (version 22.11.2.30 (official build))

About this issue

  • Original URL
  • State: open
  • Created a year ago
  • Comments: 20 (13 by maintainers)

Most upvoted comments

In my own test, the problem was because of case sensitivity, I name the db databaseName in postgres, but used databasename in the MaterializedPostgreSQL

@kssenii sorry, but still the same error… maybe this line says something?

PostgreSQLReplicationHandler: Publication publication_name already exists, but it is a CREATE query, not ATTACH. Publication will be dropped

I created the publication manually, and still it is deleted and recreated… but then it is not visible to CH