Connecting ClickHouse to PostgreSQL

```
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
```

*\*for demonstration purposes, this is using clear text password authentication method. update the address line with either the subnet or the address of the server per PostgreSQL documentation*

8. reload the `pg_hba.conf` configuration with something like this (adjust for your version):

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

9. Test the login with new `clickhouse_user`:

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

### 2. In ClickHouse[​](#2-in-clickhouse "Direct link to 2. In ClickHouse")

1. log into the ClickHouse CLI

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

2. Enable the PostgreSQL experimental feature for the database engine:

```
SET allow_experimental_database_materialized_postgresql=1
```

3. Create the new database to be replicated and define the initial table:

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

minimum options:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| parameter Description example|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | host:port hostname or IP and port postgres-host.domain.com:5432|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | database PostgreSQL database name db1|  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | | user username to connect to postgres clickhouse\_user|  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | | password password to connect to postgres ClickHouse\_123|  |  |  | | --- | --- | --- | | settings additional settings for the engine materialized\_postgresql\_tables\_list = 'table1' | | | | | | | | | | | | | | | | | |

References

For complete guide to the PostgreSQL database engine, refer to <https://clickhouse.com/docs/engines/database-engines/materialized-postgresql/#settings>

4. Verify the initial table has data:

```
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. Test basic replication[​](#3-test-basic-replication "Direct link to 3. Test basic replication")

1. In PostgreSQL, add new rows:

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

2. In ClickHouse, verify the new rows are visible:

```
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. Summary[​](#4-summary "Direct link to 4. Summary")

This integration guide focused on a simple example on how to replicate a database with a table, however, there exist more advanced options which include replicating the whole database or adding new tables and schemas to the existing replications. Although DDL commands aren't supported for this replication, the engine can be set to detect changes and reload the tables when there are structural changes made.

References

For more features available for advanced options, please see the [reference documentation](/docs/engines/database-engines/materialized-postgresql).

- [Using the PostgreSQL table engine](#using-the-postgresql-table-engine)
  - [1. Setting up PostgreSQL](#1-setting-up-postgresql)- [2. Define a Table in ClickHouse](#2-define-a-table-in-clickhouse)- [3 Test the Integration](#3-test-the-integration)- [Using the MaterializedPostgreSQL database engine](#using-the-materializedpostgresql-database-engine)
    - [1. In PostgreSQL](#1-in-postgresql)- [2. In ClickHouse](#2-in-clickhouse)- [3. Test basic replication](#3-test-basic-replication)- [4. Summary](#4-summary)

Was this page helpful?