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)
In my own test, the problem was because of case sensitivity, I name the db
databaseName
in postgres, but useddatabasename
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