连接 ClickHouse 和 PostgreSQL
本页面涵盖了将 PostgreSQL 与 ClickHouse 集成的以下选项:
- 使用 ClickPipes,由 PeerDB 提供支持的 ClickHouse Cloud 的托管集成服务 - 现已公开测试版!
- 使用 PeerDB,一个专门设计用于 PostgreSQL 数据库复制到自管理的 ClickHouse 和 ClickHouse Cloud 的开源 CDC 工具。
- 使用
PostgreSQL
表引擎,从 PostgreSQL 表中读取数据 - 使用实验性的
MaterializedPostgreSQL
数据库引擎,将 PostgreSQL 中的数据库与 ClickHouse 中的数据库同步
使用 PostgreSQL 表引擎
PostgreSQL
表引擎允许在 ClickHouse 中对存储在远程 PostgreSQL 服务器上的数据执行 SELECT 和 INSERT 操作。
本文旨在说明使用一个表的集成基本方法。
1. 设置 PostgreSQL
- 在
postgresql.conf
中,添加以下条目以启用 PostgreSQL 监听网络接口:
- 创建一个用户以便从 ClickHouse 连接。为了演示,这个例子授予完全的超级用户权限。
- 在 PostgreSQL 中创建一个新数据库:
- 创建一个新表:
- 让我们添加几行用于测试:
- 要配置 PostgreSQL 以允许使用新用户对新数据库进行复制的连接,请将以下条目添加到
pg_hba.conf
文件中。用 PostgreSQL 服务器的子网或 IP 地址更新地址行:
- 重新加载
pg_hba.conf
配置(根据您的版本调整此命令):
- 验证新用户
clickhouse_user
是否可以登录:
如果您在 ClickHouse Cloud 中使用此功能,您可能需要允许 ClickHouse Cloud IP 地址访问您的 PostgreSQL 实例。 查看 ClickHouse Cloud Endpoints API 获取出站流量详情。
2. 在 ClickHouse 中定义一个表
- 登录到
clickhouse-client
:
- 让我们创建一个新数据库:
- 创建一个使用
PostgreSQL
的表:
所需的最小参数为:
参数 | 描述 | 示例 |
---|---|---|
host:port | 主机名或 IP 和端口 | postgres-host.domain.com:5432 |
database | PostgreSQL 数据库名称 | db_in_psg |
user | 连接到 PostgreSQL 的用户名 | clickhouse_user |
password | 连接到 PostgreSQL 的密码 | ClickHouse_123 |
查看 PostgreSQL 表引擎 文档页面以获取完整的参数列表。
3 测试集成
- 在 ClickHouse 中查看初始行:
ClickHouse 表应自动填充 PostgreSQL 表中已存在的两行:
- 返回 PostgreSQL,向表中添加几行:
- 这两行新数据应出现在您的 ClickHouse 表中:
响应应为:
- 让我们看看在 ClickHouse 表中添加行会发生什么:
- 在 ClickHouse 中添加的行应该出现在 PostgreSQL 表中:
这个例子演示了使用 PostgreSQL
表引擎在 PostgreSQL 和 ClickHouse 之间的基本集成。
查看 PostgreSQL 表引擎文档页面 以了解更多功能,例如指定模式、仅返回子集列和连接多个副本。还可以查看 ClickHouse 和 PostgreSQL - 数据的天堂似的匹配 - 第 1 部分 博客。
使用 MaterializedPostgreSQL 数据库引擎
PostgreSQL 数据库引擎利用 PostgreSQL 的复制功能创建一个数据库的副本,包含所有或部分的模式和表。 本文旨在说明使用一个数据库、一个模式和一个表的集成基本方法。
在以下过程中,使用 PostgreSQL CLI (psql) 和 ClickHouse CLI (clickhouse-client)。PostgreSQL 服务器安装在 Linux 上。如果 PostgreSQL 数据库是新的测试安装,则以下具有最低设置
1. 在 PostgreSQL 中
- 在
postgresql.conf
中,设置最小监听级别、复制 wal 级别和复制槽:
添加以下条目:
*ClickHouse 需要最低 logical
wal 级别和最低 2
个复制槽
- 使用管理员账户创建一个用户以便从 ClickHouse 连接:
_为了演示,授予了完全的超级用户权限。
- 创建一个新数据库:
- 在
psql
中连接新数据库:
- 创建一个新表:
- 添加初始行:
- 配置 PostgreSQL 以允许使用新用户对新数据库进行复制的连接。以下是需要添加到
pg_hba.conf
文件中的最小条目:
_为了演示,使用的是明文密码身份验证方法。根据 PostgreSQL 文档更新地址行,使用子网或服务器地址
- 使用如下命令重新加载
pg_hba.conf
配置(根据您的版本进行调整):
- 测试新用户
clickhouse_user
的登录:
2. 在 ClickHouse 中
- 登录到 ClickHouse CLI
- 为数据库引擎启用 PostgreSQL 实验功能:
- 创建要复制的新数据库并定义初始表:
最小选项:
参数 | 描述 | 示例 |
---|---|---|
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
- 验证初始表是否有数据:
3. 测试基本复制
- 在 PostgreSQL 中,添加新行:
- 在 ClickHouse 中,验证新行是否可见:
4. 总结
本集成指南专注于如何简单地复制一个具有表的数据库,然而,还有更多高级选项,包括复制整个数据库或向现有复制中添加新表和模式。虽然此复制不支持 DDL 命令,但引擎可以设置为检测更改,并在进行结构更改时重新加载表。
有关高级选项可用的更多功能,请参见 参考文档.