Connecting ClickHouse to PostgreSQL

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/dbms/postgresql/connecting-to-postgresql.md)

This page covers following options for integrating PostgreSQL with ClickHouse:

- using the `PostgreSQL` table engine, for reading from a PostgreSQL table
- using the experimental `MaterializedPostgreSQL` database engine, for syncing a database in PostgreSQL with a database in ClickHouse

Tip

Check out our [Managed Postgres](/docs/cloud/managed-postgres) service. Backed by NVMe storage that is physically collocated with compute, it delivers up to 10x faster performance for workloads that are disk-bound compared to alternatives using network-attached storage like EBS and allows you to replicate your Postgres data to ClickHouse using the Postgres CDC connector in ClickPipes.

## Using the PostgreSQL table engine[​](#using-the-postgresql-table-engine "Direct link to Using the PostgreSQL table engine")

The `PostgreSQL` table engine allows **SELECT** and **INSERT** operations on data stored on the remote PostgreSQL server from ClickHouse.
This article is to illustrate basic methods of integration using one table.

### 1. Setting up PostgreSQL[​](#1-setting-up-postgresql "Direct link to 1. Setting up PostgreSQL")

1. In `postgresql.conf`, add the following entry to enable PostgreSQL to listen on the network interfaces:

```
listen_addresses = '*'
```

2. Create a user to connect from ClickHouse. For demonstration purposes, this example grants full superuser rights.

```
CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';
```

3. Create a new database in PostgreSQL:

```
CREATE DATABASE db_in_psg;
```

4. Create a new table:

```
CREATE TABLE table1 (
    id         integer primary key,
    column1    varchar(10)
);
```

5. Let's add a few rows for testing:

```
INSERT INTO table1
  (id, column1)
VALUES
  (1, 'abc'),
  (2, 'def');
```

6. To configure PostgreSQL to allow connections to the new database with the new user for replication, add the following entry to the `pg_hba.conf` file. Update the address line with either the subnet or IP address of your PostgreSQL server:

```
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    db_in_psg             clickhouse_user 192.168.1.0/24          password
```

7. Reload the `pg_hba.conf` configuration (adjust this command depending on your version):

```
/usr/pgsql-12/bin/pg_ctl reload
```

8. Verify the new `clickhouse_user` can login:

```
psql -U clickhouse_user -W -d db_in_psg -h <your_postgresql_host>
```

Note

If you're using this feature in ClickHouse Cloud, you may need the to allow the ClickHouse Cloud IP addresses to access your PostgreSQL instance.
Check the ClickHouse [Cloud Endpoints API](/docs/cloud/get-started/query-endpoints) for egress traffic details.

### 2. Define a Table in ClickHouse[​](#2-define-a-table-in-clickhouse "Direct link to 2. Define a Table in ClickHouse")

1. Login to the `clickhouse-client`:

```
clickhouse-client --user default --password ClickHouse123!
```

2. Let's create a new database:

```
CREATE DATABASE db_in_ch;
```

3. Create a table that uses the `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');
```

The minimum parameters needed are:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| parameter Description example|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | host:port hostname or IP and port postgres-host.domain.com:5432|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | database PostgreSQL database name db\_in\_psg|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | user username to connect to postgres clickhouse\_user|  |  |  | | --- | --- | --- | | password password to connect to postgres ClickHouse\_123 | | | | | | | | | | | | | | |

Note

View the [PostgreSQL table engine](/docs/engines/table-engines/integrations/postgresql) doc page for a complete list of parameters.

### 3 Test the Integration[​](#3-test-the-integration "Direct link to 3 Test the Integration")

1. In ClickHouse, view initial rows:

```
SELECT * FROM db_in_ch.table1
```

The ClickHouse table should automatically be populated with the two rows that already existed in the table in PostgreSQL:

```
Query id: 34193d31-fe21-44ac-a182-36aaefbd78bf

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

2. Back in PostgreSQL, add a couple of rows to the table:

```
INSERT INTO table1
  (id, column1)
VALUES
  (3, 'ghi'),
  (4, 'jkl');
```

4. Those two new rows should appear in your ClickHouse table:

```
SELECT * FROM db_in_ch.table1
```

The response should be:

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

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

5. Let's see what happens when you add rows to the ClickHouse table:

```
INSERT INTO db_in_ch.table1
  (id, column1)
VALUES
  (5, 'mno'),
  (6, 'pqr');
```

6. The rows added in ClickHouse should appear in the table in PostgreSQL:

```
db_in_psg=# SELECT * FROM table1;
id | column1
----+---------
  1 | abc
  2 | def
  3 | ghi
  4 | jkl
  5 | mno
  6 | pqr
(6 rows)
```

This example demonstrated the basic integration between PostgreSQL and ClickHouse using the `PostrgeSQL` table engine.
Check out the [doc page for the PostgreSQL table engine](/docs/engines/table-engines/integrations/postgresql) for more features, such as specifying schemas, returning only a subset of columns, and connecting to multiple replicas. Also check out the [ClickHouse and PostgreSQL - a match made in data heaven - part 1](https://clickhouse.com/blog/migrating-data-between-clickhouse-postgres) blog.

## Using the MaterializedPostgreSQL database engine[​](#using-the-materializedpostgresql-database-engine "Direct link to Using the MaterializedPostgreSQL database engine")

Not supported in ClickHouse Cloud

Experimental feature. [Learn more.](/docs/beta-and-experimental-features#experimental-features)

The PostgreSQL database engine uses the PostgreSQL replication features to create a replica of the database with all or a subset of schemas and tables.
This article is to illustrate basic methods of integration using one database, one schema and one table.

***In the following procedures, the PostgreSQL CLI (psql) and the ClickHouse CLI (clickhouse-client) are used. The PostgreSQL server is installed on linux. The following has minimum settings if the postgresql database is new test install***

### 1. In PostgreSQL[​](#1-in-postgresql "Direct link to 1. In PostgreSQL")

1. In `postgresql.conf`, set minimum listen levels, replication wal level and replication slots:

add the following entries:

```
listen_addresses = '*'
max_replication_slots = 10
wal_level = logical
```

*\*ClickHouse needs minimum of `logical` wal level and minimum `2` replication slots*

2. Using an admin account, create a user to connect from ClickHouse:

```
CREATE ROLE clickhouse_user SUPERUSER LOGIN PASSWORD 'ClickHouse_123';
```

*\*for demonstration purposes, full superuser rights have been granted.*

3. create a new database:

```
CREATE DATABASE db1;
```

4. connect to the new database in `psql`:

```
\connect db1
```

5. create a new table:

```
CREATE TABLE table1 (
    id         integer primary key,
    column1    varchar(10)
);
```

6. add initial rows:

```
INSERT INTO table1
(id, column1)
VALUES
(1, 'abc'),
(2, 'def');
```

7. Configure PostgreSQL allow connections to the new database with the new user for replication. Below is the minimum entry to add to the `pg_hba.conf` file:

```
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    db1             clickhouse_user 192.168.1.0/24          password
```