MaterializedPostgreSQL 테이블 엔진

MaterializedPostgreSQL 테이블 엔진

ClickHouse Cloud 사용자는 PostgreSQL을 ClickHouse로 복제할 때 ClickPipes를 사용할 것을 권장해요. 이는 PostgreSQL을 위한 고성능 Change Data Capture(CDC)를 네이티브로 지원해요.

이 엔진은 PostgreSQL 테이블의 초기 데이터 덤프와 함께 ClickHouse 테이블을 만들고 복제 프로세스를 시작해요. 즉, 원격 PostgreSQL 데이터베이스의 PostgreSQL 테이블에 새 변경 사항이 발생할 때마다 적용하는 백그라운드 작업을 실행해요.

이 테이블 엔진은 실험적이에요. 사용하려면 구성 파일에서 enable_materialized_postgresql_table을 1로 설정하거나 SET 명령을 사용해요:

SET enable_materialized_postgresql_table=1

테이블이 두 개 이상 필요하다면 테이블 엔진 대신 MaterializedPostgreSQL 데이터베이스 엔진을 사용하고, 복제할 테이블을 지정하는 materialized_postgresql_tables_list 설정을 사용하는 것이 매우 권장돼요 (데이터베이스 schema도 추가 가능). CPU 측면에서 훨씬 좋고, 연결과 원격 PostgreSQL 데이터베이스 내부의 복제 슬롯도 더 적게 사용해요.

출처: 문서

본문

테이블 생성하기

CREATE TABLE postgresql_db.postgresql_replica (key UInt64, value UInt64)
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgresql_table', 'postgres_user', 'postgres_password')
PRIMARY KEY key;

엔진 매개변수

  • host:port — PostgreSQL 서버 주소
  • database — 원격 데이터베이스 이름
  • table — 원격 테이블 이름
  • user — PostgreSQL 사용자
  • password — 사용자 비밀번호

TLS/SSL

TLS/SSL 매개변수는 libpq로 전달되며 named collection 또는 엔진의 뒤따르는 키-값 인자로 제공할 수 있어요: sslmode(disable, allow, prefer, require, verify-ca, verify-full; 설정하지 않으면 libpq 기본값 prefer 적용), 그리고 두 가지 형태 중 하나로 인증서와 키. sslrootcert(CA 인증서), sslcert(클라이언트 인증서), sslkey(클라이언트 개인 키)는 서버 로컬 파일의 경로로, 서버 구성 파일에 정의된 named collection에서만 받아들여져요. sslrootcert_pem, sslcert_pem, sslkey_pem은 대신 해당 파일의 리터럴 내용을 받아들이고 SQL에서 지정할 수 있으며 비밀번호처럼 로그와 SHOW 쿼리에서 마스킹돼요.

CREATE TABLE postgresql_db.postgresql_replica (key UInt64, value UInt64)
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgresql_table', 'postgres_user', 'postgres_password',
                                sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----')
PRIMARY KEY key;

TLS/SSL 매개변수는 테이블이 생성될 때 고정되는 PostgreSQL 연결 매개변수의 일부예요.

요구 사항

  • PostgreSQL 구성 파일에서 wal_level 설정이 logical 값이어야 하고 max_replication_slots 매개변수가 최소 2여야 해요
  • MaterializedPostgreSQL 엔진의 테이블은 PostgreSQL 테이블의 replica identity index(기본적으로 primary key)와 같은 primary key를 가져야 해요 (replica identity index 세부 사항)
  • 데이터베이스 Atomic만 허용돼요
  • MaterializedPostgreSQL 테이블 엔진은 구현이 pg_replication_slot_advance PostgreSQL 함수를 요구하므로 PostgreSQL 버전 >= 11에서만 동작해요

가상 컬럼

  • _version — 트랜잭션 카운터. 타입: UInt64
  • _sign — 삭제 표시. 타입: Int8. 가능한 값:
    • 1 — 행이 삭제되지 않음
    • -1 — 행이 삭제됨

이 컬럼들은 테이블 생성 시 추가할 필요가 없어요. SELECT 쿼리에서 항상 접근할 수 있어요. _version 컬럼은 WAL의 LSN 위치와 같으므로 복제가 얼마나 최신인지 확인하는 데 사용할 수 있어요.

CREATE TABLE postgresql_db.postgresql_replica (key UInt64, value UInt64)
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgresql_replica', 'postgres_user', 'postgres_password')
PRIMARY KEY key;

SELECT key, value, _version FROM postgresql_db.postgresql_replica;

TOAST 값은 복제돼요. PostgreSQL이 업데이트 중에 변경되지 않은 TOAST 참조를 보내면 기존 값이 보존돼요. 변경되지 않은 TOAST replica identity 컬럼은 PostgreSQL이 이전 키 튜플을 보내야 해요. 그렇지 않으면 행을 식별할 수 없어요.

더 알아보기 (Learn more)