> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL

> 介绍如何将 Postgres 与 ClickHouse 集成的页面

> 可在[此处](/zh/get-started/migrate/postgres/overview)查看从 PostgreSQL 迁移到 ClickHouse 的完整指南，其中包括有关数据建模和对应概念的建议。以下将介绍如何连接 ClickHouse 和 PostgreSQL。

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

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

<Tip>
  了解我们的 [Managed Postgres](/zh/products/managed-postgres/overview) 服务。该服务基于与计算资源物理共置的 NVMe 存储，相比采用 EBS 等网络附加存储的替代方案，对于受磁盘 I/O 限制的工作负载，性能最高可提升 10 倍；并且你还可以使用 ClickPipes 中的 Postgres CDC 连接器，将 Postgres 数据复制到 ClickHouse。
</Tip>

<div id="using-the-postgresql-table-engine">
  ## 使用 PostgreSQL 表引擎
</div>

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

<div id="1-setting-up-postgresql">
  ### 1. 配置 PostgreSQL
</div>

1. 在 `postgresql.conf` 中添加以下配置项，使 PostgreSQL 监听网络接口：

```text theme={null}
  listen_addresses = '*'
```

2. 创建一个供 ClickHouse 连接使用的用户。为便于演示，此示例授予其完整的超级用户权限。

```sql theme={null}
  CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';
```

3. 在 PostgreSQL 中创建一个新数据库：

```sql theme={null}
  CREATE DATABASE db_in_psg;
```

4. 创建新表：

```sql theme={null}
  CREATE TABLE table1 (
      id         integer primary key,
      column1    varchar(10)
  );
```

5. 添加几行数据来测试：

```sql theme={null}
  INSERT INTO table1
    (id, column1)
  VALUES
    (1, 'abc'),
    (2, 'def');
```

6. 要将 PostgreSQL 配置为允许新用户出于复制目的连接到新数据库，请将以下条目添加到 `pg_hba.conf` 文件中。请将地址行更新为你的 PostgreSQL 服务器所在的子网或其 IP 地址：

```text theme={null}
  # TYPE  DATABASE        USER            ADDRESS                 METHOD
  host    db_in_psg             clickhouse_user 192.168.1.0/24          password
```

7. 重新加载 `pg_hba.conf` 配置文件 (请根据你的版本调整此命令) ：

```text theme={null}
  /usr/pgsql-12/bin/pg_ctl reload
```

8. 验证新的 `clickhouse_user` 是否能够登录：

```text theme={null}
  psql -U clickhouse_user -W -d db_in_psg -h <your_postgresql_host>
```

<Note>
  如果你在 ClickHouse Cloud 中使用此功能，可能需要允许 ClickHouse Cloud 的 IP 地址访问你的 PostgreSQL 实例。
  有关出站流量的详细信息，请参阅 ClickHouse [Cloud Endpoints API](/zh/products/cloud/guides/sql-console/query-endpoints)。
</Note>

<div id="2-define-a-table-in-clickhouse">
  ### 2. 在 ClickHouse 中定义表
</div>

1. 登录 `clickhouse-client`：

```bash theme={null}
  clickhouse-client --user default --password ClickHouse123!
```

2. 创建一个新的数据库：

```sql theme={null}
  CREATE DATABASE db_in_ch;
```

3. 创建一个使用 `PostgreSQL` 的表：

```sql theme={null}
  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');
```

所需的最少参数如下：

| parameter | Description        | example                       |
| --------- | ------------------ | ----------------------------- |
| host:port | 主机名或 IP 地址和端口      | postgres-host.domain.com:5432 |
| database  | PostgreSQL 数据库名称   | db\_in\_psg                   |
| user      | 用于连接 Postgres 的用户名 | clickhouse\_user              |
| password  | 用于连接 Postgres 的密码  | ClickHouse\_123               |

<Note>
  完整参数列表请参阅 [PostgreSQL 表引擎](/zh/reference/engines/table-engines/integrations/postgresql) 文档页面。
</Note>

<div id="3-test-the-integration">
  ### 3 测试集成
</div>

1. 在 ClickHouse 中，查看初始数据行：

```sql theme={null}
  SELECT * FROM db_in_ch.table1
```

ClickHouse 表应会自动写入 PostgreSQL 中该表里原有的两行数据：

```response theme={null}
  Query id: 34193d31-fe21-44ac-a182-36aaefbd78bf

  ┌─id─┬─column1─┐
  │  1 │ abc     │
  │  2 │ def     │
  └────┴─────────┘
```

2. 回到 PostgreSQL，向表中添加几行数据：

```sql theme={null}
  INSERT INTO table1
    (id, column1)
  VALUES
    (3, 'ghi'),
    (4, 'jkl');
```

4. 这两行新数据应该会显示在你的 ClickHouse 表中：

```sql theme={null}
  SELECT * FROM db_in_ch.table1
```

返回结果应为：

```response theme={null}
  Query id: 86fa2c62-d320-4e47-b564-47ebf3d5d27b

  ┌─id─┬─column1─┐
  │  1 │ abc     │
  │  2 │ def     │
  │  3 │ ghi     │
  │  4 │ jkl     │
  └────┴─────────┘
```

5. 我们来看看向 ClickHouse 表中添加行时会发生什么：

```sql theme={null}
  INSERT INTO db_in_ch.table1
    (id, column1)
  VALUES
    (5, 'mno'),
    (6, 'pqr');
```

6. 在 ClickHouse 中添加的行应会出现在 PostgreSQL 的表中：

```sql theme={null}
  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 表引擎文档页面](/zh/reference/engines/table-engines/integrations/postgresql)，了解更多功能，例如指定 schema、仅返回部分列，以及连接多个副本。另请参阅博客 [ClickHouse and PostgreSQL - a match made in data heaven - part 1](https://clickhouse.com/blog/migrating-data-between-clickhouse-postgres)。

<div id="using-the-materializedpostgresql-database-engine">
  ## 使用 MaterializedPostgreSQL 数据库引擎
</div>

<CloudNotSupportedBadge />

<ExperimentalBadge />

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

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

<div id="1-in-postgresql">
  ### 1. 在 PostgreSQL 中
</div>

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

添加以下配置项：

```text theme={null}
listen_addresses = '*'
max_replication_slots = 10
wal_level = logical
```

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

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

```sql theme={null}
CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';
```

\**仅为演示起见，已授予完整的超级用户权限。*

3. 创建一个新数据库：

```sql theme={null}
CREATE DATABASE db1;
```

4. 在 `psql` 中连接到新的数据库：

```text theme={null}
\connect db1
```

5. 创建新表：

```sql theme={null}
CREATE TABLE table1 (
    id         integer primary key,
    column1    varchar(10)
);
```

6. 插入初始行：

```sql theme={null}
INSERT INTO table1
(id, column1)
VALUES
(1, 'abc'),
(2, 'def');
```

7. 配置 PostgreSQL，允许新用户连接到新数据库以进行复制。下面是需要添加到 `pg_hba.conf` 文件中的最简条目：

```text theme={null}
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    db1             clickhouse_user 192.168.1.0/24          password
```

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

8. 重新加载 `pg_hba.conf` 配置，类似如下 (请根据你的版本调整) ：

```text theme={null}
/usr/pgsql-12/bin/pg_ctl reload
```

9. 使用新的 `clickhouse_user` 测试登录：

```text theme={null}
 psql -U clickhouse_user -W -d db1 -h <your_postgresql_host>
```

<div id="2-in-clickhouse">
  ### 2. 在 ClickHouse 中
</div>

1. 登录 ClickHouse 命令行客户端

```bash theme={null}
clickhouse-client --user default --password ClickHouse123!
```

2. 为该数据库引擎启用 PostgreSQL Experimental 功能：

```sql theme={null}
SET allow_experimental_database_materialized_postgresql=1
```

3. 创建要启用复制的新数据库，并定义初始表：

```sql theme={null}
CREATE DATABASE db1_postgres
ENGINE = MaterializedPostgreSQL('postgres-host.domain.com:5432', 'db1', 'clickhouse_user', 'ClickHouse_123')
SETTINGS materialized_postgresql_tables_list = 'table1';
```

最低必填选项：

| parameter | Description         | example                                           |
| --------- | ------------------- | ------------------------------------------------- |
| 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' |

<Info>
  有关 PostgreSQL 数据库引擎的完整指南，请参阅 [https://clickhouse.com/docs/engines/database-engines/materialized-postgresql/#settings](https://clickhouse.com/docs/engines/database-engines/materialized-postgresql/#settings)
</Info>

4. 验证初始表中是否已有数据：

```sql theme={null}
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     │
└────┴─────────┘
```

<div id="3-test-basic-replication">
  ### 3. 测试基本复制功能
</div>

1. 在 PostgreSQL 中插入新行：

```sql theme={null}
INSERT INTO table1
(id, column1)
VALUES
(3, 'ghi'),
(4, 'jkl');
```

2. 在 ClickHouse 中，验证是否可以看到新行：

```sql theme={null}
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     │
└────┴─────────┘
```

<div id="4-summary">
  ### 4. 总结
</div>

本集成指南重点讲解了一个简单示例，说明如何复制一个包含单个表的数据库。不过，也有更高级的选项，包括复制整个数据库，或向现有复制中添加新的表和 schema。虽然这种复制不支持 DDL 命令，但可以将该引擎设置为检测变更，并在发生结构变更时重新加载这些表。

<Info>
  有关高级选项提供的更多功能，请参阅[参考文档](/zh/reference/engines/database-engines/materialized-postgresql)。
</Info>