Skip to content
ClickHouse Docs
ClickHouse DocsClickHouse Docs
49.6kGet Started

PostgreSQL

View as MarkdownEdit this pageReport an issueSet up docs MCP

可在此处查看从 PostgreSQL 迁移到 ClickHouse 的完整指南，其中包括有关数据建模和对应概念的建议。以下将介绍如何连接 ClickHouse 和 PostgreSQL。

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

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

使用 PostgreSQL 表引擎

PostgreSQL 表引擎允许在 ClickHouse 中对存储于远程 PostgreSQL 服务器上的数据执行 SELECT 和 INSERT 操作。 本文以单个表为例，说明集成的基本方法。

配置 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 中定义表

  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:port 主机名或 IP 地址及端口 postgres-host.domain.com:5432
database PostgreSQL 数据库名称 db_in_psg
user 连接到 PostgreSQL 的用户名 clickhouse_user
password 连接到 PostgreSQL 的密码 ClickHouse_123

测试集成

  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 表引擎文档页面，了解更多功能，例如指定 schema、仅返回部分列，以及连接多个副本。另请参阅博客 ClickHouse and PostgreSQL - a match made in data heaven - part 1。

使用 MaterializedPostgreSQL 数据库引擎

ClickHouse Cloud 不支持此功能
Experimental 功能

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

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

在 PostgreSQL 中

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

添加以下配置项：

listen_addresses = '*'
max_replication_slots = 10
wal_level = logical

*ClickHouse 至少需要将 wal level 设为 logical，并至少配置 2 个 replication slot

  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 文档，将 address 行更新为子网或服务器地址

  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>

在 ClickHouse 中

  1. 登录 ClickHouse 命令行客户端
clickhouse-client --user default --password ClickHouse123!
  1. 为 database engine 启用 PostgreSQL Experimental 功能：
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';

最小选项：

parameter 描述 示例
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'
  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     │
└────┴─────────┘

测试基本复制

  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     │
└────┴─────────┘

摘要

本集成指南以复制包含单个表的数据库这一简单示例为重点；不过，还提供了更高级的选项，包括复制整个数据库，或者向现有复制中添加新表和 schema。尽管此复制不支持 DDL 命令，但可以将引擎设置为检测变更，并在结构发生变更时重新加载表。

Navigation