PostgreSQL | ClickHouse Docs

데이터 모델링과 대응 개념에 대한 조언을 포함한 PostgreSQL에서 ClickHouse로의 전체 migration 가이드는 여기에서 확인할 수 있습니다. 아래에서는 ClickHouse와 PostgreSQL을 연결하는 방법을 설명합니다.

이 페이지에서는 PostgreSQL을 ClickHouse와 통합하는 다음 옵션을 설명합니다.

PostgreSQL 테이블에서 데이터를 읽기 위해 PostgreSQL 테이블 엔진을 사용하는 방법
PostgreSQL의 데이터베이스를 ClickHouse의 데이터베이스와 동기화하기 위해 실험적 MaterializedPostgreSQL 데이터베이스 엔진을 사용하는 방법

PostgreSQL 테이블 엔진 사용

PostgreSQL 테이블 엔진을 사용하면 ClickHouse에서 원격 PostgreSQL 서버에 저장된 데이터에 대해 SELECT 및 INSERT 작업을 수행할 수 있습니다. 이 문서에서는 하나의 테이블을 사용해 통합하는 기본 방법을 설명합니다.

PostgreSQL 설정

postgresql.conf에 다음 항목을 추가하여 PostgreSQL이 네트워크 인터페이스에서 연결을 수신 대기하도록 설정합니다:

listen_addresses = '*'

ClickHouse에서 연결할 사용자를 생성합니다. 데모용으로, 이 예시에서는 슈퍼유저 전체 권한을 부여합니다.

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

PostgreSQL에서 새 데이터베이스를 생성하세요:

CREATE DATABASE db_in_psg;

새 테이블을 만듭니다:

CREATE TABLE table1 (
    id         integer primary key,
    column1    varchar(10)
);

테스트용으로 몇 개의 행을 추가하겠습니다:

INSERT INTO table1
  (id, column1)
VALUES
  (1, 'abc'),
  (2, 'def');

PostgreSQL이 새 사용자로 새 데이터베이스에 대한 복제 연결을 허용하도록 구성하려면 pg_hba.conf 파일에 다음 항목을 추가하십시오. 주소 줄은 PostgreSQL 서버의 서브넷 또는 IP 주소에 맞게 업데이트하십시오:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    db_in_psg             clickhouse_user 192.168.1.0/24          password

pg_hba.conf 설정을 다시 로드합니다(사용 중인 버전에 따라 이 명령을 조정하세요):

/usr/pgsql-12/bin/pg_ctl reload

새 clickhouse_user로 로그인할 수 있는지 확인하십시오:

psql -U clickhouse_user -W -d db_in_psg -h <your_postgresql_host>

ClickHouse에 테이블 정의

clickhouse-client에 로그인합니다:

clickhouse-client --user default --password ClickHouse123!

새 데이터베이스를 생성하겠습니다:

CREATE DATABASE db_in_ch;

PostgreSQL을 사용하는 테이블을 생성합니다:

CREATE TABLE db_in_ch.table1
(
    id UInt64,
    column1 String
)
ENGINE = PostgreSQL('postgres-host.domain.com:5432', 'db_in_psg', 'table1', 'clickhouse_user', 'ClickHouse_123');

필요한 최소 매개변수는 다음과 같습니다:

매개변수	설명	예시
host:port	호스트명 또는 IP 주소와 포트	postgres-host.domain.com:5432
database	PostgreSQL 데이터베이스 이름	db_in_psg
user	PostgreSQL에 연결할 사용자 이름	clickhouse_user
password	PostgreSQL에 연결할 비밀번호	ClickHouse_123

통합 테스트

ClickHouse에서 첫 몇 개의 행을 확인합니다:

SELECT * FROM db_in_ch.table1

ClickHouse 테이블은 PostgreSQL 테이블에 이미 존재하는 2개의 행으로 자동으로 채워져야 합니다:

Query id: 34193d31-fe21-44ac-a182-36aaefbd78bf

┌─id─┬─column1─┐
│  1 │ abc     │
│  2 │ def     │
└────┴─────────┘

PostgreSQL로 돌아가 테이블에 몇 개의 행을 추가합니다:

INSERT INTO table1
  (id, column1)
VALUES
  (3, 'ghi'),
  (4, 'jkl');

새로 추가한 두 행이 ClickHouse 테이블에 표시됩니다:

SELECT * FROM db_in_ch.table1

응답은 다음과 같아야 합니다:

Query id: 86fa2c62-d320-4e47-b564-47ebf3d5d27b

┌─id─┬─column1─┐
│  1 │ abc     │
│  2 │ def     │
│  3 │ ghi     │
│  4 │ jkl     │
└────┴─────────┘

ClickHouse 테이블에 행을 추가하면 어떤 일이 발생하는지 살펴보겠습니다:

INSERT INTO db_in_ch.table1
  (id, column1)
VALUES
  (5, 'mno'),
  (6, 'pqr');

ClickHouse에 추가된 행이 PostgreSQL 테이블에 표시되어야 합니다:

db_in_psg=# SELECT * FROM table1;
id | column1
----+---------
  1 | abc
  2 | def
  3 | ghi
  4 | jkl
  5 | mno
  6 | pqr
(6 rows)

이 예시에서는 PostrgeSQL 테이블 엔진을 사용해 PostgreSQL과 ClickHouse를 기본적으로 통합하는 방법을 보여주었습니다. 스키마를 지정하거나, 일부 컬럼만 반환하거나, 여러 레플리카에 연결하는 등 더 많은 기능은 PostgreSQL 테이블 엔진 문서 페이지에서 확인하십시오. 또한 ClickHouse and PostgreSQL - a match made in data heaven - part 1 블로그도 확인하십시오.

MaterializedPostgreSQL 데이터베이스 엔진 사용하기

ClickHouse Cloud에서 지원되지 않음

실험 기능

PostgreSQL 데이터베이스 엔진은 PostgreSQL 복제 기능을 사용하여 데이터베이스 전체 또는 일부 스키마와 테이블의 레플리카를 생성합니다. 이 문서에서는 하나의 데이터베이스, 하나의 스키마, 하나의 테이블을 사용한 기본적인 통합 방법을 설명합니다.

다음 절차에서는 PostgreSQL CLI (psql)와 ClickHouse CLI (clickhouse-client)를 사용합니다. PostgreSQL 서버는 Linux에 설치되어 있습니다. PostgreSQL 데이터베이스를 새로 테스트 설치한 경우 아래 내용은 최소 설정입니다.

PostgreSQL에서

postgresql.conf에서 최소 리슨 수준, 복제용 WAL 수준, 그리고 replication slot을 설정합니다:

다음 항목을 추가합니다:

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

*ClickHouse에는 최소 logical WAL 수준과 최소 2개의 replication slot이 필요합니다

관리자 계정을 사용해 ClickHouse에서 연결할 사용자를 생성합니다:

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

*데모를 위해 전체 슈퍼유저 권한을 부여했습니다.

새 데이터베이스를 생성합니다:

CREATE DATABASE db1;

psql에서 새 데이터베이스에 연결합니다:

\connect db1

새 테이블을 만듭니다:

CREATE TABLE table1 (
    id         integer primary key,
    column1    varchar(10)
);

초기 행을 추가합니다:

INSERT INTO table1
(id, column1)
VALUES
(1, 'abc'),
(2, 'def');

복제를 위해 새 사용자가 새 데이터베이스에 연결할 수 있도록 PostgreSQL을 구성합니다. 아래는 pg_hba.conf 파일에 추가할 최소 항목입니다:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    db1             clickhouse_user 192.168.1.0/24          password

*데모 목적으로 평문 비밀번호 인증 메서드를 사용합니다. PostgreSQL 문서에 따라 address 행을 서브넷 또는 서버 주소로 업데이트하십시오

다음과 같이 pg_hba.conf 구성을 다시 로드합니다(사용 중인 버전에 맞게 조정):

/usr/pgsql-12/bin/pg_ctl reload

새 clickhouse_user로 로그인 테스트를 수행합니다:

 psql -U clickhouse_user -W -d db1 -h <your_postgresql_host>

ClickHouse에서

ClickHouse CLI에 로그인합니다

clickhouse-client --user default --password ClickHouse123!

데이터베이스 엔진에 PostgreSQL 실험적 기능을 활성화합니다:

SET allow_experimental_database_materialized_postgresql=1

복제할 새 데이터베이스를 생성하고 초기 테이블을 정의합니다:

CREATE DATABASE db1_postgres
ENGINE = MaterializedPostgreSQL('postgres-host.domain.com:5432', 'db1', 'clickhouse_user', 'ClickHouse_123')
SETTINGS materialized_postgresql_tables_list = 'table1';

최소 옵션:

매개변수	설명	예시
host:port	호스트명 또는 IP 주소 및 포트	postgres-host.domain.com:5432
database	PostgreSQL 데이터베이스 이름	db1
user	PostgreSQL에 연결할 사용자 이름	clickhouse_user
password	PostgreSQL에 연결할 비밀번호	ClickHouse_123
settings	엔진의 추가 설정	materialized_postgresql_tables_list = 'table1'

초기 테이블에 데이터가 있는지 확인합니다:

ch_env_2 :) select * from db1_postgres.table1;

SELECT *
FROM db1_postgres.table1

Query id: df2381ac-4e30-4535-b22e-8be3894aaafc

┌─id─┬─column1─┐
│  1 │ abc     │
└────┴─────────┘
┌─id─┬─column1─┐
│  2 │ def     │
└────┴─────────┘

기본 복제 테스트

PostgreSQL에서 새 행을 추가합니다:

INSERT INTO table1
(id, column1)
VALUES
(3, 'ghi'),
(4, 'jkl');

ClickHouse에서 새 행이 표시되는지 확인합니다:

ch_env_2 :) select * from db1_postgres.table1;

SELECT *
FROM db1_postgres.table1

Query id: b0729816-3917-44d3-8d1a-fed912fb59ce

┌─id─┬─column1─┐
│  1 │ abc     │
└────┴─────────┘
┌─id─┬─column1─┐
│  4 │ jkl     │
└────┴─────────┘
┌─id─┬─column1─┐
│  3 │ ghi     │
└────┴─────────┘
┌─id─┬─column1─┐
│  2 │ def     │
└────┴─────────┘

요약

이 통합 가이드에서는 테이블이 포함된 데이터베이스를 복제하는 간단한 예시를 다루었지만, 전체 데이터베이스를 복제하거나 기존 복제에 새 테이블과 스키마를 추가하는 등 더 고급 옵션도 사용할 수 있습니다. 이 복제에서는 DDL 명령이 지원되지 않지만, 구조 변경이 발생하면 엔진이 변경 사항을 감지하고 테이블을 다시 로드하도록 설정할 수 있습니다.