ClickHouse를 PostgreSQL에 연결하기

ClickHouse를 PostgreSQL에 연결하는 다양한 방법을 설명하는 페이지

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

• PostgreSQL 테이블에서 읽기 위해 PostgreSQL 테이블 엔진을 사용하는 방법
• PostgreSQL의 데이터베이스를 ClickHouse의 데이터베이스와 동기화하기 위해 experimental 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에서 사용하는 경우, ClickHouse Cloud IP 주소에서 PostgreSQL 인스턴스에 액세스할 수 있도록 허용해야 할 수 있습니다. 아웃바운드 트래픽에 대한 자세한 내용은 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	PostgreSQL에 연결할 사용자 이름	clickhouse_user
password	PostgreSQL에 연결할 비밀번호	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로 돌아가 테이블에 행 2개를 추가합니다:

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

4. 새로 추가된 2개의 행이 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 테이블에 표시됩니다:

  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 수준, 복제 슬롯을 설정합니다:

다음 항목을 추가합니다:

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';

최소 옵션:

매개변수	Description	example
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 명령이 지원되지 않지만, 구조 변경이 발생할 때 변경 사항을 감지하고 테이블을 다시 로드하도록 엔진을 설정할 수 있습니다.

고급 옵션에서 사용할 수 있는 추가 기능은 참고 문서에서 확인하십시오.