连接到 PostgreSQL

介绍将 PostgreSQL 连接到 ClickHouse 的各种方法

​

将 ClickHouse 连接到 PostgreSQL

本页介绍了将 PostgreSQL 与 ClickHouse 集成的以下方式：

• 使用 PostgreSQL 表引擎读取 PostgreSQL 表中的数据
• 使用 Experimental 的 MaterializedPostgreSQL 数据库引擎，将 PostgreSQL 中的数据库与 ClickHouse 中的数据库同步

了解我们的 Managed Postgres 服务。该服务采用与计算资源物理同置的 NVMe 存储；与使用 EBS 等网络附加存储的替代方案相比，对于受磁盘 IO 限制的工作负载，性能最高可提升 10 倍，并支持通过 ClickPipes 中的 Postgres CDC connector 将 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 server 所在子网或其 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	用于连接 Postgres 的用户名	clickhouse_user
password	用于连接 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');

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 的表中：

  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 表引擎文档 了解更多功能，例如指定 schema、仅返回部分列，以及连接到多个副本。另请参阅博客 ClickHouse and PostgreSQL - 数据世界中的天作之合 - 第 1 部分。

​

使用 MaterializedPostgreSQL 数据库引擎

PostgreSQL 数据库引擎使用 PostgreSQL 的复制功能，为数据库中全部或部分 schema 和表创建副本。 本文旨在说明使用一个数据库、一个 schema 和一张表进行集成的基本方法。

在以下步骤中，将使用 PostgreSQL 命令行客户端 (psql) 和 ClickHouse 命令行客户端 (clickhouse-client)。PostgreSQL 服务器安装在 Linux 上。如果 PostgreSQL 数据库是全新的测试安装，则以下内容为最小配置。

​

1. 在 PostgreSQL 中

1. 在 postgresql.conf 中，设置最小监听级别、复制 wal level 和 replication slots：

添加以下配置项：

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

*ClickHouse 至少需要将 wal level 设为 logical，并且至少需要 2 个 replication slots

2. 使用管理员账户，创建一个供 ClickHouse 连接的用户：

CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';

*仅为演示起见，已授予完整的 superuser 权限。

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 文件中的最简条目：

# TYPE  DATABASE        USER            ADDRESS                 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 命令行客户端

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

最少选项：

parameter	描述	示例
host:port	主机名或 IP 地址及端口	postgres-host.domain.com:5432
database	PostgreSQL 数据库名称	db1
user	连接到 Postgres 的用户名	clickhouse_user
password	连接到 Postgres 的密码	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. 总结

本集成指南主要通过一个简单示例说明了如何复制包含单个表的 database，不过也提供了更高级的选项，例如复制整个 database，或向现有复制配置中添加新的表和 schema。虽然这种复制方式不支持 DDL 命令，但可以将该引擎配置为检测变更，并在发生结构性变更时重新加载表。

有关高级选项的更多功能，请参阅参考文档。