跳至主要内容
跳至主要内容
编辑此页

PostgreSQL

关于从 PostgreSQL 迁移到 ClickHouse 的完整迁移指南，包括数据建模和等效概念的建议，可以在 这里 找到。以下描述了如何连接 ClickHouse 和 PostgreSQL。

将 ClickHouse 连接到 PostgreSQL

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

  • 使用 PostgreSQL 表引擎，用于从 PostgreSQL 表读取数据
  • 使用实验性的 MaterializedPostgreSQL 数据库引擎，用于将 PostgreSQL 中的数据库与 ClickHouse 中的数据库同步
提示

请查看我们的 托管 Postgres 服务。它由与计算物理共置的 NVMe 存储提供支持，与使用网络附加存储（如 EBS）的替代方案相比，对于受磁盘限制的工作负载，性能提升高达 10 倍，并允许您使用 ClickPipes 中的 Postgres CDC 连接器将您的 Postgres 数据复制到 ClickHouse。

使用 PostgreSQL 表引擎​

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

1. 设置 PostgreSQL​

  1. 在 postgresql.conf 中，添加以下条目以启用 PostgreSQL 侦听网络接口
listen_addresses = '*'
  1. 创建一个用户从 ClickHouse 连接。为了演示目的，此示例授予完整的超级用户权限。
CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';
  1. 在 PostgreSQL 中创建一个新数据库
CREATE DATABASE db_in_psg;
  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 文件中。使用子网或 PostgreSQL 服务器的 IP 地址更新地址行
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    db_in_psg             clickhouse_user 192.168.1.0/24          password
  1. 重新加载 pg_hba.conf 配置（根据您的版本调整此命令）
/usr/pgsql-12/bin/pg_ctl reload
  1. 验证新的 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!
  1. 让我们创建一个新的数据库
CREATE DATABASE db_in_ch;
  1. 创建一个使用 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:端口主机名或 IP 和端口postgres-host.domain.com:5432
数据库PostgreSQL 数据库名称db_in_psg
用户连接到 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     │
└────┴─────────┘
  1. 在 PostgreSQL 中，向表中添加几行
INSERT INTO table1
  (id, column1)
VALUES
  (3, 'ghi'),
  (4, 'jkl');
  1. 这两行新数据应出现在您的 ClickHouse 表中
SELECT * FROM db_in_ch.table1

响应应为

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

┌─id─┬─column1─┐
│  1 │ abc     │
│  2 │ def     │
│  3 │ ghi     │
│  4 │ jkl     │
└────┴─────────┘
  1. 让我们看看向 ClickHouse 表添加行会发生什么
INSERT INTO db_in_ch.table1
  (id, column1)
VALUES
  (5, 'mno'),
  (6, 'pqr');
  1. 在 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 - a match made in data heaven - part 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:端口主机名或 IP 和端口postgres-host.domain.com:5432
数据库PostgreSQL 数据库名称db1
用户连接到 postgres 的用户名clickhouse_user
password连接到 postgres 的密码ClickHouse_123
设置引擎的附加设置materialized_postgresql_tables_list = 'table1'
参考

有关 PostgreSQL 数据库引擎的完整指南，请参阅 https://clickhouse.ac.cn/docs/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 命令，但该引擎可以设置为检测更改并在进行结构更改时重新加载表。

参考

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

    © . This site is unofficial and not affiliated with ClickHouse, Inc.