跳至主要内容

将 ClickHouse 连接到 PostgreSQL

此页面涵盖了以下将 PostgreSQL 与 ClickHouse 集成的选项

  • 使用 ClickHouse 的 PeerDB,这是一种专门为 PostgreSQL 数据库复制到自托管 ClickHouse 和 ClickHouse Cloud 而设计的 CDC 工具。
  • 使用 PostgreSQL 表引擎,用于从 PostgreSQL 表读取数据
  • 使用实验性的 MaterializedPostgreSQL 数据库引擎,用于将 PostgreSQL 中的数据库与 ClickHouse 中的数据库同步

使用 ClickHouse 的 PeerDB

PeerDB 与 ClickHouse 携手合作,提供最快的 Postgres CDC!要开始使用,请在 PeerDB 云 上创建一个帐户,并参考 文档 以获取设置说明。

使用 PostgreSQL 表引擎

PostgreSQL 表引擎允许从 ClickHouse 对存储在远程 PostgreSQL 服务器上的数据执行 SELECTINSERT 操作。本文旨在说明使用一个表的基本集成方法。

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 云端点 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
    数据库PostgreSQL 数据库名称db_in_psg
    用户连接到 postgres 的用户名clickhouse_user
    密码连接到 postgres 的密码ClickHouse_123
    注意

    查看 PostgreSQL 表引擎 文档页面以获取完整参数列表。

3 测试集成

  1. 在 ClickHouse 中,查看初始行

    SELECT * FROM db_in_ch.table1

    ClickHouse 表应自动填充已存在于 PostgreSQL 中表的两行

    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');
  3. 这两行新数据应该出现在您的 ClickHouse 表中

    SELECT * FROM db_in_ch.table1

    响应应为

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

    ┌─id─┬─column1─┐
    │ 1 │ abc │
    │ 2 │ def │
    │ 3 │ ghi │
    │ 4 │ jkl │
    └────┴─────────┘
  4. 让我们看看向 ClickHouse 表中添加行时会发生什么

    INSERT INTO db_in_ch.table1
    (id, column1)
    VALUES
    (5, 'mno'),
    (6, 'pqr');
  5. 在 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 和 PostgreSQL - 数据领域的完美组合 - 第 1 部分 博客。

使用 MaterializedPostgreSQL 数据库引擎

ClickHouse Cloud 中不支持
实验性功能。 了解更多。

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 个复制槽

  1. 使用管理员帐户创建一个用户以从 ClickHouse 连接
CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

*出于演示目的,已授予完全的超级用户权限。

  1. 创建一个新数据库
CREATE DATABASE db1;
  1. psql 中连接到新数据库
\connect db1
  1. 创建一个新表
CREATE TABLE table1 (
id integer primary key,
column1 varchar(10)
);
  1. 添加初始行
INSERT INTO table1
(id, column1)
VALUES
(1, 'abc'),
(2, 'def');
  1. 配置 PostgreSQL 以允许使用新用户连接到新数据库进行复制:以下是添加到 pg_hba.conf 文件中的最小条目
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host db1 clickhouse_user 192.168.1.0/24 password

*出于演示目的,这里使用的是明文密码身份验证方法。根据 PostgreSQL 文档更新地址行,使用子网或服务器地址

  1. 使用类似以下内容重新加载 pg_hba.conf 配置(根据您的版本调整)
/usr/pgsql-12/bin/pg_ctl reload
  1. 使用新的 clickhouse_user 测试登录
 psql -U clickhouse_user -W -d db1 -h <your_postgresql_host>

2. 在 ClickHouse 中

  1. 登录到 ClickHouse CLI
clickhouse-client --user default --password ClickHouse123!
  1. 为数据库引擎启用 PostgreSQL 实验功能
SET allow_experimental_database_materialized_postgresql=1
  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
数据库PostgreSQL 数据库名称db1
用户连接到 postgres 的用户名clickhouse_user
密码连接到 postgres 的密码ClickHouse_123
设置引擎的其他设置materialized_postgresql_tables_list = 'table1'
信息

有关 PostgreSQL 数据库引擎的完整指南,请参阅 https://clickhouse.ac.cn/docs/en/engines/database-engines/materialized-postgresql/#settings

  1. 验证初始表是否有数据
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');
  1. 在 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 命令,但可以将引擎设置为检测更改并在进行结构更改时重新加载表。

信息

有关高级选项可用的更多功能,请参阅参考文档:https://clickhouse.ac.cn/docs/en/engines/database-engines/materialized-postgresql/