MaterializedPostgreSQL 데이터베이스 엔진

MaterializedPostgreSQL 데이터베이스 엔진

PostgreSQL 데이터베이스의 테이블로 ClickHouse 데이터베이스를 만드는 엔진이에요. 먼저 PostgreSQL 데이터베이스의 스냅샷을 만들고 필요한 테이블을 불러온 뒤, WAL에서 업데이트를 가져와 복제해요. 이 데이터베이스 엔진은 실험적이에요.

출처: 문서

본문

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

PostgreSQL 데이터베이스의 테이블로 ClickHouse 데이터베이스를 만들어요. 먼저 MaterializedPostgreSQL 엔진의 데이터베이스가 PostgreSQL 데이터베이스의 스냅샷을 만들고 필요한 테이블을 불러와요. 필요한 테이블은 지정된 데이터베이스의 어떤 스키마의 어떤 부분집합의 테이블이든 될 수 있어요. 스냅샷과 함께 데이터베이스 엔진은 LSN을 획득하고 테이블의 초기 덤프가 수행되면 WAL에서 업데이트를 끌어오기 시작해요. 데이터베이스가 만들어진 후 PostgreSQL 데이터베이스에 새로 추가된 테이블은 복제에 자동으로 추가되지 않아요. ATTACH TABLE db.table 쿼리로 수동으로 추가해야 해요.

복제는 PostgreSQL 논리 복제 프로토콜(Logical Replication Protocol)로 구현되며, 이는 DDL을 복제하지 않지만 복제를 깨뜨리는 변경(컬럼 타입 변경, 컬럼 추가/제거)이 일어났는지는 알 수 있게 해줘요. 그런 변경은 감지되고 해당 테이블은 업데이트 수신을 중단해요. 이 경우 ATTACH/DETACH PERMANENTLY 쿼리로 테이블을 완전히 다시 불러와야 해요. DDL이 복제를 깨뜨리지 않으면(예: 컬럼 이름 변경) 테이블은 여전히 업데이트를 받아요(삽입은 위치별로 수행돼요).

이 데이터베이스 엔진은 실험적이에요. 사용하려면 설정 파일이나 SET 명령으로 allow_experimental_database_materialized_postgresql을 1로 설정해요.

SET allow_experimental_database_materialized_postgresql=1

데이터베이스 만들기 (Creating a database)

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MaterializedPostgreSQL('host:port', 'database', 'user', 'password') [SETTINGS ...]

엔진 매개변수 (Engine Parameters)

• host:port — PostgreSQL 서버 엔드포인트.
• database — PostgreSQL 데이터베이스 이름.
• user — PostgreSQL 사용자.
• password — 사용자 비밀번호.

사용 예시 (Example of use)

CREATE DATABASE postgres_db
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgres_user', 'postgres_password');

SHOW TABLES FROM postgres_db;

┌─name───┐
│ table1 │
└────────┘

SELECT * FROM postgres_db.postgres_table;

복제에 새 테이블 동적으로 추가 (Dynamically adding new tables to replication)

MaterializedPostgreSQL 데이터베이스가 만들어진 후에는 해당 PostgreSQL 데이터베이스의 새 테이블을 자동으로 감지하지 않아요. 그런 테이블은 수동으로 추가할 수 있어요.

ATTACH TABLE postgres_database.new_table;

22.1 버전 이전에는 복제에 테이블을 추가하면 제거되지 않는 임시 복제 슬롯({db_name}_ch_replication_slot_tmp 이름)이 남았어요. 22.1 이전 ClickHouse 버전에서 테이블을 붙인다면 수동으로 삭제하는지 확인해요(SELECT pg_drop_replication_slot('{db_name}_ch_replication_slot_tmp')). 그렇지 않으면 디스크 사용량이 늘어나요. 이 문제는 22.1에서 고쳐졌어요.

복제에서 테이블 동적으로 제거 (Dynamically removing tables from replication)

복제에서 특정 테이블을 제거할 수 있어요.

DETACH TABLE postgres_database.table_to_remove PERMANENTLY;

PostgreSQL 스키마 (PostgreSQL schema)

PostgreSQL 스키마는(21.12 버전부터) 3가지 방식으로 구성할 수 있어요.

• 하나의 MaterializedPostgreSQL 데이터베이스 엔진에 대한 한 스키마. 설정 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`;

경고: 이 경우 테이블 이름에 점이 허용되지 않아요.

요구 사항 (Requirements)

• wal_level 설정의 값이 logical이어야 하고, PostgreSQL 설정 파일에서 max_replication_slots 매개변수의 값이 적어도 2여야 해요.
• 각 복제 테이블은 다음 replica identity 중 하나를 가져야 해요.
  • 기본 키(기본값)
  • 인덱스

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;

기본 키가 항상 먼저 검사돼요. 없으면 replica identity 인덱스로 정의된 인덱스가 검사돼요. 인덱스가 replica identity로 사용되면 테이블에 그런 인덱스가 하나만 있어야 해요. 특정 테이블에 어떤 유형이 사용되는지는 다음 명령으로 확인할 수 있어요.

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 값은 복제돼요. PostgreSQL이 업데이트 중에 변경되지 않은 TOAST 참조를 보내면 기존 값이 보존돼요. 변경되지 않은 TOAST replica identity 컬럼은 PostgreSQL이 이전 키 튜플을 보내도록 요구하며, 그렇지 않으면 행을 식별할 수 없어요.

설정 (Settings)

materialized_postgresql_tables_list

MaterializedPostgreSQL 데이터베이스 엔진을 통해 복제될 PostgreSQL 데이터베이스 테이블의 쉼표로 구분된 목록을 설정해요. 각 테이블은 대괄호 안에 복제될 컬럼 부분집합을 가질 수 있어요. 컬럼 부분집합을 생략하면 테이블의 모든 컬럼이 복제돼요.

materialized_postgresql_tables_list = 'table1(co1, col2),table2,table3(co3, col5, col7)

기본값: 빈 목록 — 전체 PostgreSQL 데이터베이스가 복제됨을 뜻해요.

materialized_postgresql_schema

기본값: 빈 문자열. (기본 스키마가 사용돼요.)

materialized_postgresql_schema_list

기본값: 빈 목록. (기본 스키마가 사용돼요.)

materialized_postgresql_max_block_size

PostgreSQL 데이터베이스 테이블로 플러시하기 전에 메모리에 모이는 행 수를 설정해요. 가능한 값: 양의 정수. 기본값: 65536.

materialized_postgresql_replication_slot

사용자가 만든 복제 슬롯. materialized_postgresql_snapshot과 함께 사용해야 해요.

materialized_postgresql_snapshot

PostgreSQL 테이블의 초기 덤프가 수행될 스냅샷을 식별하는 텍스트 문자열. 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;

설정은 필요하면 DDL 쿼리로 바꿀 수 있어요. 하지만 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 테이블을 구성할 수 있어요.

materialized_postgresql_use_extended_date_and_time_types

PostgreSQL의 date와 timestamp/timestamptz 타입을 PostgreSQL 타입의 더 넓은 값 범위를 덮는 ClickHouse Date32와 DateTime64로 매핑해요. 기본값: 1. 0으로 설정하면 더 좁은 Date와 DateTime 타입이 대신 사용돼요(그 범위 밖이거나 서브초 정밀도의 값은 표현할 수 없어요). 이 설정은 중첩 테이블이 생성될 때 타입 추론으로 선택되는 컬럼 타입만 제어하므로, CREATE DATABASE 시점에 지정해야 해요. 이후 ALTER DATABASE ... MODIFY SETTING으로 바꿀 수 없어요(이미 생성된 중첩 테이블은 고정된 컬럼 타입을 유지하고, 그런 변경은 거부돼요). 바꾸려면 데이터베이스를 다시 만들어요. 컬럼 타입이 명시적으로 선언되는 MaterializedPostgreSQL 테이블 엔진에는 적용되지 않아요.

TLS/SSL

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

TLS를 강제하는 PostgreSQL 서버에 연결해 서버 인증서를 검증하는 예시:

CREATE DATABASE postgres_db
ENGINE = MaterializedPostgreSQL('postgres-host:5432', 'postgres_database', 'postgres_user', 'postgres_password',
                                sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----');

TLS/SSL 매개변수는 데이터베이스가 생성될 때 고정되는 PostgreSQL 연결 매개변수의 일부예요. 바꾸려면 데이터베이스를 다시 만들어요.

참고 (Notes)

논리 복제 슬롯의 장애 조치 (Failover of the logical replication slot)

프라이머리에 존재하는 논리 복제 슬롯(Logical Replication Slots)은 스탠바이 복제본에서 사용할 수 없어요. 그래서 장애 조치(failover)가 발생하면 새 프라이머리(이전 물리적 스탠바이)는 이전 프라이머리와 존재하던 슬롯들을 인식하지 못해요. 이것은 PostgreSQL로부터의 복제가 깨지는 결과를 낳아요. 해결책은 복제 슬롯을 직접 관리하고 영구 복제 슬롯을 정의하는 것이에요(일부 정보는 여기에서 찾을 수 있어요). materialized_postgresql_replication_slot 설정으로 슬롯 이름을 전달해야 하고, EXPORT SNAPSHOT 옵션으로 내보내야 해요. 스냅샷 식별자는 materialized_postgresql_snapshot 설정으로 전달해야 해요.

이 기능은 정말 필요할 때만 사용해야 한다는 점을 유의해 주세요. 실제 필요가 없거나 이유를 완전히 이해하지 못한다면 테이블 엔진이 자체 복제 슬롯을 만들고 관리하도록 두는 것이 더 좋아요.

예시 (@bchrobot 제공)

• PostgreSQL에서 복제 슬롯 구성.

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

• 복제 슬롯이 준비될 때까지 기다린 다음 트랜잭션을 시작하고 트랜잭션 스냅샷 식별자를 내보내요.

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'

필요한 권한 (Required permissions)

• CREATE PUBLICATION — 쿼리 생성 권한.
• CREATE_REPLICATION_SLOT — 복제 권한.
• pg_drop_replication_slot — 복제 권한 또는 superuser.
• DROP PUBLICATION — 발행(publication)의 소유자(MaterializedPostgreSQL 엔진 자체의 username).

2와 3 명령을 실행하고 그 권한을 갖지 않아도 가능해요. 설정 materialized_postgresql_replication_slot과 materialized_postgresql_snapshot을 사용해요. 단, 매우 주의해서요.

테이블 접근:

• pg_publication
• pg_replication_slots
• pg_publication_tables

백업과 복원 (Backup and restore)

MaterializedPostgreSQL 데이터베이스를 백업할 수 있어요. 복제된 각 테이블의 데이터는 중첩 ReplacingMergeTree 테이블에 살므로, BACKUP DATABASE는 중첩 테이블에 위임하여 그 데이터를 캡처해요.

BACKUP DATABASE postgres_db TO Disk('backups', 'postgres_db.zip');

MaterializedPostgreSQL 데이터베이스나 테이블을 제자리에서 복원하는 것은 지원되지 않아요. 복원된 MaterializedPostgreSQL 객체는 즉시 라이브 PostgreSQL 소스에서 복제를 시작하므로, 그 위에 백업 스냅샷을 복원하면 스냅샷과 현재 원격 상태가 섞이게 돼요. 그래서 이 경우 RESTORE는 안전하게 실패(fail closed)해요. 캡처된 데이터를 대신 일반 ReplacingMergeTree 테이블로 복원해요.

• 데이터베이스 백업에서 각 테이블의 저장된 정의는 이미 합성 중첩 ReplacingMergeTree(MaterializedPostgreSQL 엔진이 아님)이므로, 각 테이블은 새롭고 아직 존재하지 않는 테이블로 바로 복원될 수 있어요.

RESTORE TABLE postgres_db.table1 AS restored_db.table1
FROM Disk('backups', 'postgres_db.zip')
SETTINGS allow_different_table_def = 1;

• 독립형 MaterializedPostgreSQL 테이블 백업의 경우 저장된 정의는 MaterializedPostgreSQL 엔진 자체예요. 중첩 테이블과 같은 구조(_sign, _version 컬럼 포함)의 ReplacingMergeTree 테이블을 미리 만들고 그 안으로 복원해요.

RESTORE TABLE src AS existing_replacing_mergetree
FROM Disk('backups', 'table.zip')
SETTINGS allow_different_table_def = 1;

더 알아보기 (Learn more)

• MaterializedPostgreSQL 테이블 엔진
• ClickPipes