PostgreSQL

Postgres를 ClickHouse와 통합하는 방법을 설명하는 문서

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

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

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

Managed Postgres 서비스도 살펴보십시오. 컴퓨트와 물리적으로 동일한 위치에 배치된 NVMe storage를 기반으로 하므로, EBS와 같은 network-attached storage를 사용하는 대안과 비교해 디스크 입출력에 병목이 있는 워크로드에서 최대 10배 더 빠른 성능을 제공하며, ClickPipes의 Postgres CDC 커넥터를 사용해 Postgres 데이터를 ClickHouse로 복제할 수 있습니다.

​

PostgreSQL 테이블 엔진 사용

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

​

1. PostgreSQL 설정

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

  listen_addresses = '*'

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

  CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

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

  CREATE DATABASE db_in_psg;

4. 새 테이블을 만듭니다:

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

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

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

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

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

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

  /usr/pgsql-12/bin/pg_ctl reload

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

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

ClickHouse Cloud에서 이 기능을 사용하는 경우, PostgreSQL 인스턴스에 ClickHouse Cloud IP 주소의 접근을 허용해야 할 수 있습니다. 송신 트래픽에 대한 자세한 내용은 ClickHouse Cloud Endpoints API에서 확인하십시오.

​

2. ClickHouse에서 테이블 정의하기

1. clickhouse-client에 로그인합니다:

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

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

  CREATE DATABASE db_in_ch;

3. 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	postgres에 연결할 사용자 이름	clickhouse_user
password	postgres에 연결할 비밀번호	ClickHouse_123

전체 매개변수 목록은 PostgreSQL 테이블 엔진 문서 페이지에서 확인하십시오.

​

3 통합 테스트

1. ClickHouse에서 초기 행을 조회합니다:

  SELECT * FROM db_in_ch.table1

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

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

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

2. PostgreSQL로 돌아가서 테이블에 행 몇 개를 추가합니다:

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

4. 새로 추가한 두 개의 행이 ClickHouse 테이블에 나타나야 합니다:

  SELECT * FROM db_in_ch.table1

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

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

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

5. ClickHouse 테이블에 행을 추가했을 때 어떤 일이 일어나는지 살펴보겠습니다:

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

6. ClickHouse에 추가된 행이 PostgreSQL의 테이블(table)에 나타나야 합니다:

  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 데이터베이스 엔진 사용하기

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

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

​

1. PostgreSQL에서

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

다음 항목을 추가합니다:

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

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

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

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

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

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

CREATE DATABASE db1;

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

\connect db1

5. 새 테이블을 만듭니다:

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

6. 초기 행을 추가합니다:

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

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

# 유형  데이터베이스        USER            주소                 METHOD
host    db1             clickhouse_user 192.168.1.0/24          password

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

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

/usr/pgsql-12/bin/pg_ctl reload

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

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

​

2. ClickHouse에서

1. ClickHouse CLI에 로그인합니다

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

2. 데이터베이스 엔진용 PostgreSQL 실험적 기능을 활성화하세요:

SET allow_experimental_database_materialized_postgresql=1

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

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’

PostgreSQL 데이터베이스 엔진의 전체 가이드는 https://clickhouse.com/docs/engines/database-engines/materialized-postgresql/#settings 를 참조하십시오

4. 초기 테이블에 데이터가 있는지 확인하십시오:

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     │
└────┴─────────┘

​

3. 기본적인 복제 테스트

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

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

2. 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     │
└────┴─────────┘

​

4. 요약

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

고급 옵션에서 사용할 수 있는 더 많은 기능은 참고 문서를 참조하십시오.