ClickHouse/ClickHouse
MaterializedPostgreSQL Database with ON CLUSTER
Aperta
#58.726 aperta il 11 gen 2024
experimental featurehelp wantedunfinished code
Metriche repository
- Star
- (47.419 stelle)
- Metriche merge PR
- (Merge medio 2g 2h) (1000 PR mergiate in 30 g)
Descrizione
Versions ClickHouse 23.12 PostgreSQL 16
Describe the expected behaviour
A cluster 'all-replicated' with 2 replicas clickhouse-01 and clickhouse-02
If executing this statement in each replica independently without ON CLUSTER we get the expected behaviour
CREATE DATABASE pg_mat_test
ENGINE = MaterializedPostgreSQL('postgres:5432', 'pg_test', 'postgres', 'postgres')
SETTINGS materialized_postgresql_schema = 'public', materialized_postgresql_use_unique_replication_consumer_identifier = 1;
Both databases are created in each replica and there is a publication with 1 replication slot.
PostgreSQL logs:
2024-01-11 20:06:27.355 UTC [54] LOG: statement: SELECT exists (SELECT 1 FROM pg_publication WHERE pubname = 'pg_test_ch_publication')
2024-01-11 20:06:27.355 UTC [54] LOG: statement: COPY (SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public') TO STDOUT
2024-01-11 20:06:27.362 UTC [55] LOG: statement: SET default_transaction_isolation='repeatable read'
2024-01-11 20:06:27.362 UTC [55] LOG: statement: SELECT exists (SELECT 1 FROM pg_publication WHERE pubname = 'pg_test_ch_publication')
2024-01-11 20:06:27.362 UTC [55] LOG: statement: CREATE PUBLICATION "pg_test_ch_publication" FOR TABLE ONLY "public"."person_of_interest"
2024-01-11 20:06:27.363 UTC [55] LOG: statement: SELECT active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name = 'cb63eaab_a619_4067_a29b_898cde1e2806'
2024-01-11 20:06:27.368 UTC [55] LOG: logical decoding found consistent point at 0/413EAF0
2024-01-11 20:06:27.368 UTC [55] DETAIL: There are no running transactions.
2024-01-11 20:06:27.368 UTC [55] STATEMENT: CREATE_REPLICATION_SLOT "cb63eaab_a619_4067_a29b_898cde1e2806" LOGICAL pgoutput EXPORT_SNAPSHOT
2024-01-11 20:06:27.368 UTC [55] LOG: exported logical decoding snapshot: "00000003-0000002A-1" with 0 transaction IDs
2024-01-11 20:06:27.368 UTC [55] STATEMENT: CREATE_REPLICATION_SLOT "cb63eaab_a619_4067_a29b_898cde1e2806" LOGICAL pgoutput EXPORT_SNAPSHOT
2024-01-11 20:06:27.374 UTC [56] LOG: statement: BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY
2024-01-11 20:06:27.374 UTC [56] LOG: statement: SET TRANSACTION SNAPSHOT '00000003-0000002A-1'
2024-01-11 20:06:27.374 UTC [56] LOG: statement: COPY (SELECT attname AS name, format_type(atttypid, atttypmod) AS type, attnotnull AS not_null, attndims AS dims, atttypid as type_id, atttypmod as type_modifier, attgenerated as generated FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'person_of_interest' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')) AND NOT attisdropped AND attnum > 0 ORDER BY attnum ASC) TO STDOUT
2024-01-11 20:06:27.375 UTC [56] LOG: statement: COPY (SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'person_of_interest' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')) AND i.indisprimary) TO STDOUT
Now second database is created in replica clickhouse-02
2024-01-11 20:08:59.834 UTC [56] LOG: logical decoding found consistent point at 0/413EAF0
2024-01-11 20:08:59.834 UTC [56] DETAIL: There are no running transactions.
2024-01-11 20:08:59.834 UTC [56] STATEMENT: COPY (select lsn, data FROM pg_logical_slot_peek_binary_changes('cb63eaab_a619_4067_a29b_898cde1e2806', NULL, 65536, 'publication_names', 'pg_test_ch_publication', 'proto_version', '1')) TO STDOUT
2024-01-11 20:09:07.929 UTC [69] LOG: statement: SELECT exists (SELECT 1 FROM pg_publication WHERE pubname = 'pg_test_ch_publication')
2024-01-11 20:09:07.929 UTC [69] LOG: statement: DROP PUBLICATION IF EXISTS pg_test_ch_publication
2024-01-11 20:09:07.930 UTC [69] LOG: statement: COPY (SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public') TO STDOUT
2024-01-11 20:09:07.936 UTC [70] LOG: statement: SET default_transaction_isolation='repeatable read'
2024-01-11 20:09:07.936 UTC [70] LOG: statement: SELECT exists (SELECT 1 FROM pg_publication WHERE pubname = 'pg_test_ch_publication')
2024-01-11 20:09:07.937 UTC [70] LOG: statement: CREATE PUBLICATION "pg_test_ch_publication" FOR TABLE ONLY "public"."person_of_interest"
2024-01-11 20:09:07.937 UTC [70] LOG: statement: SELECT active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name = 'f9fdcb42_a89f_4483_93e9_d82b4c701062'
2024-01-11 20:09:07.941 UTC [70] LOG: logical decoding found consistent point at 0/41456B0
2024-01-11 20:09:07.941 UTC [70] DETAIL: There are no running transactions.
2024-01-11 20:09:07.941 UTC [70] STATEMENT: CREATE_REPLICATION_SLOT "f9fdcb42_a89f_4483_93e9_d82b4c701062" LOGICAL pgoutput EXPORT_SNAPSHOT
2024-01-11 20:09:07.941 UTC [70] LOG: exported logical decoding snapshot: "00000003-0000004C-1" with 0 transaction IDs
2024-01-11 20:09:07.941 UTC [70] STATEMENT: CREATE_REPLICATION_SLOT "f9fdcb42_a89f_4483_93e9_d82b4c701062" LOGICAL pgoutput EXPORT_SNAPSHOT
2024-01-11 20:09:07.948 UTC [71] LOG: statement: BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY
2024-01-11 20:09:07.948 UTC [71] LOG: statement: SET TRANSACTION SNAPSHOT '00000003-0000004C-1'
2024-01-11 20:09:07.949 UTC [71] LOG: statement: COPY (SELECT attname AS name, format_type(atttypid, atttypmod) AS type, attnotnull AS not_null, attndims AS dims, atttypid as type_id, atttypmod as type_modifier, attgenerated as generated FROM pg_attribute WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'person_of_interest' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')) AND NOT attisdropped AND attnum > 0 ORDER BY attnum ASC) TO STDOUT
2024-01-11 20:09:07.949 UTC [71] LOG: statement: COPY (SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type FROM pg_index i JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey) WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'person_of_interest' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')) AND i.indisprimary) TO STDOUT
A new publication is created and new replication slot. Both databases are assigned to them and both replicate correct results if inserting in postgres:
pg_test=# INSERT INTO person_of_interest VALUES (1, 'Albert', 'Einstein');
pg_test=# select * from person_of_interest;
id | name | surname
----+--------+----------
1 | Albert | Einstein
SELECT from clickhouse-01 and clickhouse-02
SELECT *
FROM pg_mat_test.person_of_interest
┌─id─┬─name───┬─surname──┐
│ 1 │ Albert │ Einstein │
└────┴────────┴──────────┘
Insert again in postgres:
pg_test=# INSERT INTO person_of_interest VALUES (2, 'Ada', 'Lovelace');
pg_test=# select * from person_of_interest;
id | name | surname
----+---------+----------
1 | Albert | Einstein
2 | Ada | Lovelace
(2 rows)
SELECT from clickhouse-01 and clickhouse-02
SELECT *
FROM pg_mat_test.person_of_interest
┌─id─┬─name───┬─surname──┐
│ 1 │ Albert │ Einstein │
│ 2 │ Ada │ Lovelace │
└────┴────────┴──────────┘
2 rows in set. Elapsed: 0.001 sec.
Describe the Unexpected behaviour
If using ON CLUSTER in clickhouse-01
CREATE DATABASE pg_mat_test ON CLUSTER 'all-replicated'
ENGINE = MaterializedPostgreSQL('postgres:5432', 'pg_test', 'postgres', 'postgres')
SETTINGS materialized_postgresql_schema = 'public', materialized_postgresql_use_unique_replication_consumer_identifier = 1;
It creates the publication and replication slot but only the latest replica clickhouse-02 replicates the data correctly if doing INSERTS:
clickhouse-01 :) SELECT * FROM pg_mat_test.person_of_interest;
SELECT *
FROM pg_mat_test.person_of_interest
┌─id─┬─name───┬─surname──┐
│ 1 │ Albert │ Einstein │
│ 2 │ Ada │ Lovelace │
│ 3 │ Alan │ Turing │
└────┴────────┴──────────┘
3 rows in set. Elapsed: 0.001 sec.
clickhouse-02 :) SELECT * FROM pg_mat_test.person_of_interest;
SELECT *
FROM pg_mat_test.person_of_interest
┌─id─┬─name─┬─surname───┐
│ 5 │ Rene │ Descartes │
└────┴──────┴───────────┘
┌─id─┬─name────┬─surname──┐
│ 1 │ Albert │ Einstein │
│ 2 │ Ada │ Lovelace │
│ 3 │ Alan │ Turing │
│ 4 │ Charles │ Babbage │
└────┴─────────┴──────────┘
Here logs from clickhouse-01:
2024.01.11 20:16:18.753260 [ 38 ] {25d386f1-5bb6-4e68-a0cb-9c2759305c6c} <Debug> executeQuery: (from 172.18.0.1:54332) CREATE DATABASE pg_mat_test ON CLUSTER `all-replicated` ENGINE = MaterializedPostgreSQL('postgres:5432', 'pg_test', 'postgres', '[HIDDEN]') SETTINGS materialized_postgresql_schema = 'public', materialized_postgresql_use_unique_replication_consumer_identifier = 1 (stage: Complete)
2024.01.11 20:16:18.753313 [ 38 ] {25d386f1-5bb6-4e68-a0cb-9c2759305c6c} <Trace> ContextAccess (default): Access granted: CREATE DATABASE ON pg_mat_test.*
2024.01.11 20:16:18.753325 [ 38 ] {25d386f1-5bb6-4e68-a0cb-9c2759305c6c} <Trace> ContextAccess (default): Access granted: POSTGRES ON *.*
2024.01.11 20:16:18.755019 [ 730 ] {} <Debug> DDLWorker: Scheduling tasks
2024.01.11 20:16:18.755031 [ 727 ] {} <Debug> DDLWorker: Cleaning queue
2024.01.11 20:16:18.755177 [ 730 ] {} <Trace> DDLWorker: scheduleTasks: initialized=true, size_before_filtering=3, queue_size=3, entries=query-0000000004..query-0000000006, first_failed_task_name=none, current_tasks_size=1, last_current_task=query-0000000005, last_skipped_entry_name=none
2024.01.11 20:16:18.755203 [ 730 ] {} <Debug> DDLWorker: Will schedule 1 tasks starting from query-0000000006
2024.01.11 20:16:18.755209 [ 730 ] {} <Trace> DDLWorker: Checking task query-0000000006
2024.01.11 20:16:18.756118 [ 730 ] {} <Debug> DDLWorker: Processing task query-0000000006 (query: CREATE DATABASE pg_mat_test UUID '1a594d6f-85cb-468d-be0f-c8dc6a2e6a03' ENGINE = MaterializedPostgreSQL('postgres:5432', 'pg_test', 'postgres', '[HIDDEN]') SETTINGS materialized_postgresql_schema = 'public', materialized_postgresql_use_unique_replication_consumer_identifier = 1, backup restore: false)
2024.01.11 20:16:18.756746 [ 730 ] {} <Debug> DDLWorker: Executing query: CREATE DATABASE pg_mat_test UUID '1a594d6f-85cb-468d-be0f-c8dc6a2e6a03' ENGINE = MaterializedPostgreSQL('postgres:5432', 'pg_test', 'postgres', '[HIDDEN]') SETTINGS materialized_postgresql_schema = 'public', materialized_postgresql_use_unique_replication_consumer_identifier = 1
2024.01.11 20:16:18.756833 [ 730 ] {76aa9a9c-eac3-484b-a113-206f0a45f8b3} <Debug> executeQuery: (from 0.0.0.0:0, user: , initial_query_id: 25d386f1-5bb6-4e68-a0cb-9c2759305c6c) CREATE DATABASE pg_mat_test UUID '1a594d6f-85cb-468d-be0f-c8dc6a2e6a03' ENGINE = MaterializedPostgreSQL('postgres:5432', 'pg_test', 'postgres', '[HIDDEN]') SETTINGS materialized_postgresql_schema = 'public', materialized_postgresql_use_unique_replication_consumer_identifier = 1 (stage: Complete)
2024.01.11 20:16:18.757925 [ 730 ] {76aa9a9c-eac3-484b-a113-206f0a45f8b3} <Information> DatabaseMaterializedPostgreSQL (pg_mat_test): Metadata processed, database pg_mat_test has 0 tables and 0 dictionaries in total.
2024.01.11 20:16:18.757937 [ 730 ] {76aa9a9c-eac3-484b-a113-206f0a45f8b3} <Information> TablesLoader: Parsed metadata of 0 tables in 1 databases in 4.4223e-05 sec
2024.01.11 20:16:18.757943 [ 730 ] {76aa9a9c-eac3-484b-a113-206f0a45f8b3} <Trace> ReferentialDeps: No tables
2024.01.11 20:16:18.757949 [ 730 ] {76aa9a9c-eac3-484b-a113-206f0a45f8b3} <Trace> LoadingDeps: No tables
2024.01.11 20:16:18.758018 [ 124 ] {} <Information> PostgreSQLReplicationHandler: Using replication slot 1a594d6f_85cb_468d_be0f_c8dc6a2e6a03 and publication pg_test_ch_publication
2024.01.11 20:16:18.758041 [ 730 ] {76aa9a9c-eac3-484b-a113-206f0a45f8b3} <Debug> DDLWorker: Executed query: /* ddl_entry=query-0000000006 */ CREATE DATABASE pg_mat_test UUID '1a594d6f-85cb-468d-be0f-c8dc6a2e6a03' ENGINE = MaterializedPostgreSQL('postgres:5432', 'pg_test', 'postgres', '[HIDDEN]') SETTINGS materialized_postgresql_schema = 'public', materialized_postgresql_use_unique_replication_consumer_identifier = 1
2024.01.11 20:16:18.758897 [ 730 ] {} <Debug> DDLWorker: Waiting for queue updates
2024.01.11 20:16:18.763210 [ 124 ] {} <Debug> PostgreSQLConnection: New connection to postgres:5432
2024.01.11 20:16:18.763684 [ 124 ] {} <Debug> PostgreSQLReplicationHandler: Publication exists: false, is attach: false
2024.01.11 20:16:18.764472 [ 124 ] {} <Debug> PostgreSQLReplicationHandler: Tables list was fetched from PostgreSQL directly: person_of_interest
2024.01.11 20:16:18.764540 [ 124 ] {} <Trace> DatabaseMaterializedPostgreSQL (pg_mat_test): Loaded 1 tables. Starting synchronization
2024.01.11 20:16:18.769429 [ 124 ] {} <Debug> PostgreSQLConnection: New connection to postgres:5432
2024.01.11 20:16:18.770437 [ 124 ] {} <Debug> PostgreSQLReplicationHandler: Created publication pg_test_ch_publication with tables list: "public"."person_of_interest"
2024.01.11 20:16:18.770943 [ 124 ] {} <Debug> PostgreSQLReplicationHandler: Starting tables sync load
2024.01.11 20:16:18.775948 [ 124 ] {} <Trace> PostgreSQLReplicationHandler: Created replication slot: 1a594d6f_85cb_468d_be0f_c8dc6a2e6a03, start lsn: 0/414BCE0, snapshot: 00000003-00000099-1
2024.01.11 20:16:18.780510 [ 124 ] {} <Debug> PostgreSQLConnection: New connection to postgres:5432
2024.01.11 20:16:18.781868 [ 124 ] {} <Debug> PostgreSQLReplicationHandler: Loading PostgreSQL table pg_test."public"."person_of_interest"
2024.01.11 20:16:18.782026 [ 124 ] {} <Debug> StorageMaterializedPostgreSQL(pg_test.person_of_interest): Creating clickhouse table for postgresql table pg_mat_test.person_of_interest (ast: CREATE TABLE pg_mat_test.person_of_interest (`id` Int32, `name` Nullable(String), `surname` Nullable(String), `_sign` Int8() MATERIALIZED 1, `_version` UInt64() MATERIALIZED 1) ENGINE = ReplacingMergeTree(_version) ORDER BY tuple(id))
and logs from clickhouse-02:
2024.01.11 20:16:18.755877 [ 685 ] {} <Trace> DDLWorker: scheduleTasks: initialized=true, size_before_filtering=3, queue_size=3, entries=query-0000000004..query-0000000006, first_failed_task_name=none, current_tasks_size=0, last_current_task=none, last_skipped_entry_name=query-0000000005
2024.01.11 20:16:18.755888 [ 685 ] {} <Debug> DDLWorker: Will schedule 1 tasks starting from query-0000000006
2024.01.11 20:16:18.755893 [ 685 ] {} <Trace> DDLWorker: Checking task query-0000000006
2024.01.11 20:16:18.756759 [ 685 ] {} <Debug> DDLWorker: Processing task query-0000000006 (query: CREATE DATABASE pg_mat_test UUID '1a594d6f-85cb-468d-be0f-c8dc6a2e6a03' ENGINE = MaterializedPostgreSQL('postgres:5432', 'pg_test', 'postgres', '[HIDDEN]') SETTINGS materialized_postgresql_schema = 'public', materialized_postgresql_use_unique_replication_consumer_identifier = 1, backup restore: false)
2024.01.11 20:16:18.757397 [ 685 ] {} <Debug> DDLWorker: Executing query: CREATE DATABASE pg_mat_test UUID '1a594d6f-85cb-468d-be0f-c8dc6a2e6a03' ENGINE = MaterializedPostgreSQL('postgres:5432', 'pg_test', 'postgres', '[HIDDEN]') SETTINGS materialized_postgresql_schema = 'public', materialized_postgresql_use_unique_replication_consumer_identifier = 1
2024.01.11 20:16:18.757553 [ 685 ] {3e3c6075-85fc-47a1-bcb3-a9094d042893} <Debug> executeQuery: (from 0.0.0.0:0, user: , initial_query_id: 25d386f1-5bb6-4e68-a0cb-9c2759305c6c) CREATE DATABASE pg_mat_test UUID '1a594d6f-85cb-468d-be0f-c8dc6a2e6a03' ENGINE = MaterializedPostgreSQL('postgres:5432', 'pg_test', 'postgres', '[HIDDEN]') SETTINGS materialized_postgresql_schema = 'public', materialized_postgresql_use_unique_replication_consumer_identifier = 1 (stage: Complete)
2024.01.11 20:16:18.758435 [ 685 ] {3e3c6075-85fc-47a1-bcb3-a9094d042893} <Information> DatabaseMaterializedPostgreSQL (pg_mat_test): Metadata processed, database pg_mat_test has 0 tables and 0 dictionaries in total.
2024.01.11 20:16:18.758443 [ 685 ] {3e3c6075-85fc-47a1-bcb3-a9094d042893} <Information> TablesLoader: Parsed metadata of 0 tables in 1 databases in 4.8852e-05 sec
2024.01.11 20:16:18.758448 [ 685 ] {3e3c6075-85fc-47a1-bcb3-a9094d042893} <Trace> ReferentialDeps: No tables
2024.01.11 20:16:18.758452 [ 685 ] {3e3c6075-85fc-47a1-bcb3-a9094d042893} <Trace> LoadingDeps: No tables
2024.01.11 20:16:18.758522 [ 166 ] {} <Information> PostgreSQLReplicationHandler: Using replication slot 1a594d6f_85cb_468d_be0f_c8dc6a2e6a03 and publication pg_test_ch_publication
2024.01.11 20:16:18.758548 [ 685 ] {3e3c6075-85fc-47a1-bcb3-a9094d042893} <Debug> DDLWorker: Executed query: /* ddl_entry=query-0000000006 */ CREATE DATABASE pg_mat_test UUID '1a594d6f-85cb-468d-be0f-c8dc6a2e6a03' ENGINE = MaterializedPostgreSQL('postgres:5432', 'pg_test', 'postgres', '[HIDDEN]') SETTINGS materialized_postgresql_schema = 'public', materialized_postgresql_use_unique_replication_consumer_identifier = 1
2024.01.11 20:16:18.759469 [ 685 ] {} <Debug> DDLWorker: Waiting for queue updates
2024.01.11 20:16:18.763658 [ 166 ] {} <Debug> PostgreSQLConnection: New connection to postgres:5432
2024.01.11 20:16:18.763989 [ 166 ] {} <Debug> PostgreSQLReplicationHandler: Publication exists: false, is attach: false
2024.01.11 20:16:18.764685 [ 166 ] {} <Debug> PostgreSQLReplicationHandler: Tables list was fetched from PostgreSQL directly: person_of_interest
2024.01.11 20:16:18.764731 [ 166 ] {} <Trace> DatabaseMaterializedPostgreSQL (pg_mat_test): Loaded 1 tables. Starting synchronization
2024.01.11 20:16:18.769519 [ 166 ] {} <Debug> PostgreSQLConnection: New connection to postgres:5432
2024.01.11 20:16:18.770842 [ 166 ] {} <Error> DatabaseMaterializedPostgreSQL (pg_mat_test): Failed to start replication from PostgreSQL, will retry. Error: std::exception. Code: 1001, type: pqxx::unique_violation, e.what() = ERROR: duplicate key value violates unique constraint "pg_publication_pubname_index"
DETAIL: Key (pubname)=(pg_test_ch_publication) already exists.
, Stack trace (when copying this message, always include the lines below):
0. pqxx::sql_error::sql_error(String const&, String const&, char const*) @ 0x0000000015e312a3 in /usr/bin/clickhouse
1. pqxx::unique_violation::unique_violation(String const&, String const&, char const*) @ 0x0000000015e32729 in /usr/bin/clickhouse
2. pqxx::result::throw_sql_error(String const&, String const&) const @ 0x0000000015e324f1 in /usr/bin/clickhouse
3. pqxx::result::check_status(std::basic_string_view<char, std::char_traits<char>>) const @ 0x0000000015e32a39 in /usr/bin/clickhouse
4. pqxx::connection::make_result(pg_result*, std::shared_ptr<String> const&, std::basic_string_view<char, std::char_traits<char>>) @ 0x0000000015e2c3b5 in /usr/bin/clickhouse
5. pqxx::connection::exec(std::shared_ptr<String>, std::basic_string_view<char, std::char_traits<char>>) @ 0x0000000015e2e198 in /usr/bin/clickhouse
6. pqxx::transaction_base::exec(std::basic_string_view<char, std::char_traits<char>>, std::basic_string_view<char, std::char_traits<char>>) @ 0x0000000015e3a664 in /usr/bin/clickhouse
7. DB::PostgreSQLReplicationHandler::startSynchronization(bool) @ 0x0000000010a25ed7 in /usr/bin/clickhouse
8. 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*) @ 0x0000000011181b96 in /usr/bin/clickhouse
9. DB::BackgroundSchedulePool::threadFunction() @ 0x00000000103500bf in /usr/bin/clickhouse
10. void std::__function::__policy_invoker<void ()>::__call_impl<std::__function::__default_alloc_func<ThreadFromGlobalPoolImpl<false>::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*) @ 0x0000000010351111 in /usr/bin/clickhouse
11. void* std::__thread_proxy[abi:v15000]<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*) @ 0x000000000c7c00d8 in /usr/bin/clickhouse
12. ? @ 0x00007fcf4b33c609 in ?
13. ? @ 0x00007fcf4b261353 in ?
(version 23.12.2.59 (official build))
2024.01.11 20:16:23.770984 [ 171 ] {} <Information> PostgreSQLReplicationHandler: Using replication slot 1a594d6f_85cb_468d_be0f_c8dc6a2e6a03 and publication pg_test_ch_publication
2024.01.11 20:16:23.776744 [ 171 ] {} <Debug> PostgreSQLConnection: New connection to postgres:5432
2024.01.11 20:16:23.777272 [ 171 ] {} <Debug> PostgreSQLReplicationHandler: Publication exists: true, is attach: false
2024.01.11 20:16:23.777282 [ 171 ] {} <Warning> PostgreSQLReplicationHandler: Publication pg_test_ch_publication already exists, but it is a CREATE query, not ATTACH. Publication will be dropped
2024.01.11 20:16:23.779081 [ 171 ] {} <Debug> PostgreSQLReplicationHandler: Dropped publication: pg_test_ch_publication
2024.01.11 20:16:23.780027 [ 171 ] {} <Debug> PostgreSQLReplicationHandler: Tables list was fetched from PostgreSQL directly: person_of_interest
2024.01.11 20:16:23.780098 [ 171 ] {} <Trace> DatabaseMaterializedPostgreSQL (pg_mat_test): Loaded 1 tables. Starting synchronization
2024.01.11 20:16:23.785016 [ 171 ] {} <Debug> PostgreSQLConnection: New connection to postgres:5432
2024.01.11 20:16:23.785812 [ 171 ] {} <Debug> PostgreSQLReplicationHandler: Created publication pg_test_ch_publication with tables list: "public"."person_of_interest"
2024.01.11 20:16:23.786242 [ 171 ] {} <Debug> PostgreSQLReplicationHandler: Replication slot 1a594d6f_85cb_468d_be0f_c8dc6a2e6a03 already exists (active: false). Restart lsn position: 0/414BCA8, confirmed flush lsn: 0/414BCE0
2024.01.11 20:16:23.787032 [ 171 ] {} <Debug> PostgreSQLReplicationHandler: Dropped replication slot: 1a594d6f_85cb_468d_be0f_c8dc6a2e6a03
2024.01.11 20:16:23.787038 [ 171 ] {} <Debug> PostgreSQLReplicationHandler: Starting tables sync load
2024.01.11 20:16:23.791899 [ 171 ] {} <Trace> PostgreSQLReplicationHandler: Created replication slot: 1a594d6f_85cb_468d_be0f_c8dc6a2e6a03, start lsn: 0/414C670, snapshot: 00000003-000000A8-1
2024.01.11 20:16:23.796931 [ 171 ] {} <Debug> PostgreSQLConnection: New connection to postgres:5432
2024.01.11 20:16:23.798068 [ 171 ] {} <Debug> PostgreSQLReplicationHandler: Loading PostgreSQL table pg_test."public"."person_of_interest"
2024.01.11 20:16:23.798193 [ 171 ] {} <Debug> StorageMaterializedPostgreSQL(pg_test.person_of_interest): Creating clickhouse table for postgresql table pg_mat_test.person_of_interest (ast: CREATE TABLE pg_mat_test.person_of_interest (`id` Int32, `name` Nullable(String), `surname` Nullable(String), `_sign` Int8() MATERIALIZED 1, `_version` UInt64() MATERIALIZED 1) ENGINE = ReplacingMergeTree(_version) ORDER BY tuple(id))
There is an interesting Stack Trace in clickhouse-02 it tryes to start synchronization but there is a constraint violation and does a retry recreating the publication and new replication slot id. It seems pqxx::transaction_base is failing preparing the transaction?.
It would be nice if we could use ON CLUSTER to avoid repeating the same command in each replica. This could be tedious in big clusters.
@kssenii 😄