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

# Connecting ClickHouse to PostgreSQL

> Page describing the various ways to connect PostgreSQL to ClickHouse

export const ExperimentalBadge = () => {
  return <div className="experimentalBadge">
            <div className="experimentalIcon">
            <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                <path strokeWidth="1.25" d="M5.5 2H10.5" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.25" d="M9.50015 2V6.19625L13.4283 12.7425C13.4738 12.8183 13.4985 12.9049 13.4996 12.9934C13.5008 13.0818 13.4785 13.169 13.435 13.246C13.3914 13.323 13.3283 13.3871 13.2519 13.4317C13.1755 13.4764 13.0886 13.4999 13.0002 13.5H3.00015C2.91164 13.5 2.8247 13.4766 2.74822 13.432C2.67174 13.3874 2.60847 13.3233 2.56487 13.2463C2.52126 13.1693 2.49889 13.082 2.50004 12.9935C2.50119 12.905 2.52582 12.8184 2.5714 12.7425L6.50015 6.19625V2" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.25" d="M4.47656 9.56754C5.30344 9.41254 6.47656 9.47942 7.99969 10.25C10.0153 11.2707 11.4216 11.0569 12.2184 10.7282" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
            </svg>
        </div>
            Experimental feature. <u><a href="/docs/beta-and-experimental-features#experimental-features">Learn more.</a></u>
        </div>;
};

export const CloudNotSupportedBadge = () => {
  return <div className="cloudNotSupportedBadge">
            <div className="cloudNotSupportedIcon">
            <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                <path strokeWidth="1.5" d="M6.33366 12.6666L12.3739 12.6667C13.6593 12.6667 14.7073 11.6187 14.7073 10.3334C14.7073 9.04804 13.6593 8.00003 12.3739 8.00003C12.3739 8.00003 12.3337 7.66659 12.0003 7.33325M10.667 5.33322C8.00033 2.33325 4.45395 4.78537 4.14195 6.68203C2.55728 6.7627 1.29395 8.06203 1.29395 9.6667C1.29395 11.3234 2.66699 12.6666 4.00033 12.6666" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.5" d="M2.66699 14L12.0003 4.66663" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
            </svg>

        </div>
            Not supported in ClickHouse Cloud
        </div>;
};

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](/products/managed-postgres/overview) 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.
</Tip>

<h2 id="using-the-postgresql-table-engine">
  Using the PostgreSQL table engine
</h2>

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.

<h3 id="1-setting-up-postgresql">
  1. Setting up PostgreSQL
</h3>

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

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

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

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

3. Create a new database in PostgreSQL:

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

4. Create a new table:

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

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

```sql theme={null}
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:

```text theme={null}
# 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):

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

8. Verify the new `clickhouse_user` can login:

```text theme={null}
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](/products/cloud/guides/sql-console/query-endpoints) for egress traffic details.
</Note>

<h3 id="2-define-a-table-in-clickhouse">
  2. Define a Table in ClickHouse
</h3>

1. Login to the `clickhouse-client`:

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

2. Let's create a new database:

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

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

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](/reference/engines/table-engines/integrations/postgresql) doc page for a complete list of parameters.
</Note>

<h3 id="3-test-the-integration">
  3 Test the Integration
</h3>

1. In ClickHouse, view initial rows:

```sql theme={null}
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:

```response theme={null}
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:

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

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

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

The response should be:

```response theme={null}
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:

```sql theme={null}
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:

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

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](/reference/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.

<h2 id="using-the-materializedpostgresql-database-engine">
  Using the MaterializedPostgreSQL database engine
</h2>

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

<h3 id="1-in-postgresql">
  1. In PostgreSQL
</h3>

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

add the following entries:

```text theme={null}
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:

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

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

3. create a new database:

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

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

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

5. create a new table:

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

6. add initial rows:

```sql theme={null}
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:

```text theme={null}
# 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):

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

9. Test the login with new `clickhouse_user`:

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

<h3 id="2-in-clickhouse">
  2. In ClickHouse
</h3>

1. log into the ClickHouse CLI

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

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

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

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

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

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

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

4. Verify the initial table has data:

```sql theme={null}
ch_env_2 :) select * from db1_postgres.table1;

SELECT *
FROM db1_postgres.table1
```

```response theme={null}
Query id: df2381ac-4e30-4535-b22e-8be3894aaafc

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

<h3 id="3-test-basic-replication">
  3. Test basic replication
</h3>

1. In PostgreSQL, add new rows:

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

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

```sql theme={null}
ch_env_2 :) select * from db1_postgres.table1;

SELECT *
FROM db1_postgres.table1
```

```response theme={null}
Query id: b0729816-3917-44d3-8d1a-fed912fb59ce

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

<h3 id="4-summary">
  4. Summary
</h3>

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.

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