ClickHouse Cloud 사용자는 PostgreSQL을 ClickHouse로 복제할 때 ClickPipes를 사용하는 것이 좋습니다. ClickPipes는 PostgreSQL에 대한 고성능 CDC(Change Data Capture)를 네이티브로 지원합니다. MaterializedPostgreSQL 엔진을 사용하는 데이터베이스가 PostgreSQL 데이터베이스의 스냅샷을 생성하고 필요한 테이블을 로드합니다. 필요한 테이블에는 지정된 데이터베이스의 스키마 하위 집합에 포함된 테이블 하위 집합을 자유롭게 지정할 수 있습니다. 스냅샷과 함께 데이터베이스 엔진은 LSN을 획득하며, 테이블의 초기 덤프가 완료되면 WAL에서 업데이트를 가져오기 시작합니다. 데이터베이스가 생성된 후 PostgreSQL 데이터베이스에 새로 추가된 테이블은 복제 대상에 자동으로 포함되지 않습니다. 이러한 테이블은 ATTACH TABLE db.table 쿼리를 사용해 수동으로 추가해야 합니다.
복제는 PostgreSQL 논리적 복제 프로토콜을 통해 구현됩니다. 이 프로토콜은 DDL 복제는 허용하지 않지만, 복제를 중단시키는 변경이 발생했는지는 확인할 수 있습니다(컬럼 유형 변경, 컬럼 추가/삭제). 이러한 변경이 감지되면 해당 테이블은 업데이트 수신을 중지합니다. 이 경우 ATTACH/ DETACH PERMANENTLY 쿼리를 사용해 테이블 전체를 다시 로드해야 합니다. DDL이 복제를 중단시키지 않는 경우에는(예: 컬럼 이름 변경) 테이블은 계속 업데이트를 수신합니다(삽입은 위치 기준으로 수행됨).
이 데이터베이스 엔진은 Experimental 상태입니다. 사용하려면 설정 파일에서 allow_experimental_database_materialized_postgresql을 1로 설정하거나 SET 명령을 사용하십시오:SET allow_experimental_database_materialized_postgresql=1
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedPostgreSQL('host:port', 'database', 'user', 'password') [SETTINGS ...]
host:port — PostgreSQL 서버 엔드포인트.database — PostgreSQL 데이터베이스 이름.user — PostgreSQL 사용자 이름.password — 사용자 비밀번호.
CREATE DATABASE postgres_db
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password');
SHOW TABLES FROM postgres_db;
┌─name───┐
│ table1 │
└────────┘
SELECT * FROM postgresql_db.postgres_table;
MaterializedPostgreSQL 데이터베이스를 생성한 후에는 연결된 PostgreSQL 데이터베이스의 새 테이블이 자동으로 감지되지 않습니다. 이러한 테이블은 수동으로 추가할 수 있습니다:
ATTACH TABLE postgres_database.new_table;
버전 22.1 이전에는 테이블을 복제에 추가할 때 삭제되지 않는 임시 replication slot(이름: {db_name}_ch_replication_slot_tmp)이 남았습니다. 22.1 이전 버전의 ClickHouse에서 테이블을 ATTACH하는 경우 이 슬롯을 수동으로 삭제해야 합니다(SELECT pg_drop_replication_slot('{db_name}_ch_replication_slot_tmp')). 그렇지 않으면 디스크 사용량이 증가합니다. 이 문제는 22.1에서 수정되었습니다.
DETACH TABLE postgres_database.table_to_remove PERMANENTLY;
MaterializedPostgreSQL 데이터베이스 엔진 1개당 스키마 1개를 사용합니다. 이 경우 materialized_postgresql_schema 설정을 사용해야 합니다.
테이블은 테이블 이름만으로 액세스합니다:
CREATE DATABASE postgres_database
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_schema = 'postgres_schema';
SELECT * FROM postgres_database.table1;
- 하나의
MaterializedPostgreSQL 데이터베이스 엔진에 대해, 지정된 테이블 집합이 있는 스키마를 여러 개 사용할 수 있습니다. 이 경우 materialized_postgresql_tables_list 설정을 사용해야 합니다. 각 테이블은 해당 스키마와 함께 지정됩니다.
테이블에 액세스할 때는 스키마 이름과 테이블 이름을 함께 사용합니다:
CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_tables_list = 'schema1.table1,schema2.table2,schema1.table3',
materialized_postgresql_tables_list_with_schema = 1;
SELECT * FROM database1.`schema1.table1`;
SELECT * FROM database1.`schema2.table2`;
materialized_postgresql_tables_list의 모든 테이블은 스키마 이름을 포함해 작성해야 합니다.
materialized_postgresql_tables_list_with_schema = 1이 필요합니다.
경고: 이 경우 테이블 이름에 점(.)을 사용할 수 없습니다.
- 하나의
MaterializedPostgreSQL 데이터베이스 엔진에 대해 전체 테이블 집합을 포함하는 여러 스키마를 사용할 수 있습니다. 이 경우 설정 materialized_postgresql_schema_list를 사용해야 합니다.
CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_schema_list = 'schema1,schema2,schema3';
SELECT * FROM database1.`schema1.table1`;
SELECT * FROM database1.`schema1.table2`;
SELECT * FROM database1.`schema2.table2`;
-
PostgreSQL 구성 파일에서 wal_level 설정 값은
logical이어야 하며, max_replication_slots 매개변수 값은 최소 2여야 합니다.
-
각 복제된 테이블(Replicated Table)에는 다음 레플리카 아이덴티티 중 하나가 설정되어 있어야 합니다.
-
기본 키(primary key) (기본값)
-
인덱스
postgres# CREATE TABLE postgres_table (a Integer NOT NULL, b Integer, c Integer NOT NULL, d Integer, e Integer NOT NULL);
postgres# CREATE unique INDEX postgres_table_index on postgres_table(a, c, e);
postgres# ALTER TABLE postgres_table REPLICA IDENTITY USING INDEX postgres_table_index;
postgres# SELECT CASE relreplident
WHEN 'd' THEN 'default'
WHEN 'n' THEN 'nothing'
WHEN 'f' THEN 'full'
WHEN 'i' THEN 'index'
END AS replica_identity
FROM pg_class
WHERE oid = 'postgres_table'::regclass;
TOAST 값의 복제는 지원되지 않습니다. 대신 해당 데이터 타입의 기본값이 사용됩니다. materialized_postgresql_tables_list
materialized_postgresql_tables_list = 'table1(co1, col2),table2,table3(co3, col5, col7)
materialized_postgresql_schema
materialized_postgresql_schema_list
materialized_postgresql_max_block_size
65536.
materialized_postgresql_replication_slot
materialized_postgresql_snapshot과 함께 사용해야 합니다.
materialized_postgresql_snapshot
materialized_postgresql_replication_slot과 함께 사용해야 합니다.
CREATE DATABASE database1
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS materialized_postgresql_tables_list = 'table1,table2,table3';
SELECT * FROM database1.table1;
materialized_postgresql_tables_list 설정은 변경할 수 없습니다. 이 설정의 테이블 목록을 업데이트하려면 ATTACH TABLE 쿼리를 사용하십시오.
ALTER DATABASE postgres_database MODIFY SETTING materialized_postgresql_max_block_size = <new_size>;
materialized_postgresql_use_unique_replication_consumer_identifier
0입니다.
1로 설정하면 동일한 PostgreSQL 테이블을 가리키는 여러 MaterializedPostgreSQL 테이블을 설정할 수 있습니다.
논리적 replication slot의 장애 조치
materialized_postgresql_replication_slot 설정으로 전달해야 하며, EXPORT SNAPSHOT 옵션으로 내보내야 합니다. 스냅샷 식별자는 materialized_postgresql_snapshot 설정으로 전달해야 합니다.
이 기능은 실제로 필요한 경우에만 사용해야 한다는 점에 유의하십시오. 이를 꼭 사용해야 할 실질적인 이유가 없거나 그 이유를 충분히 이해하지 못한 경우에는 테이블 엔진이 자체 replication slot을 생성하고 관리하도록 두는 편이 더 낫습니다.
예시(@bchrobot 제공)
-
PostgreSQL에서 replication slot을 구성합니다.
apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
name: acid-demo-cluster
spec:
numberOfInstances: 2
postgresql:
parameters:
wal_level: logical
patroni:
slots:
clickhouse_sync:
type: logical
database: demodb
plugin: pgoutput
-
replication slot이 준비될 때까지 기다린 다음 트랜잭션을 시작하고, 트랜잭션 스냅샷 식별자를 내보냅니다.
BEGIN;
SELECT pg_export_snapshot();
-
ClickHouse에서 데이터베이스를 생성합니다.
CREATE DATABASE demodb
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password')
SETTINGS
materialized_postgresql_replication_slot = 'clickhouse_sync',
materialized_postgresql_snapshot = '0000000A-0000023F-3',
materialized_postgresql_tables_list = 'table1,table2,table3';
-
ClickHouse DB로의 복제가 확인되면 PostgreSQL 트랜잭션을 종료합니다. 장애 조치 후에도 복제가 계속되는지 확인합니다.
kubectl exec acid-demo-cluster-0 -c postgres -- su postgres -c 'patronictl failover --candidate acid-demo-cluster-1 --force'
-
CREATE PUBLICATION — 쿼리 생성 권한.
-
CREATE_REPLICATION_SLOT — 복제 권한.
-
pg_drop_replication_slot — 복제 권한 또는 superuser 권한.
-
DROP PUBLICATION — publication의 소유자(MaterializedPostgreSQL engine 자체의
username).
2 및 3 명령을 실행하지 않고 해당 권한 없이도 진행할 수 있습니다. materialized_postgresql_replication_slot 및 materialized_postgresql_snapshot 설정을 사용하십시오. 단, 매우 주의해서 사용해야 합니다.
테이블 액세스:
-
pg_publication
-
pg_replication_slots
-
pg_publication_tables
