Skip to main content

Connecting ClickHouse to PostgreSQL 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. 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>

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:

    parameterDescriptionexample
    host:porthostname or IP and portpostgres-host.domain.com:5432
    databasePostgreSQL database namedb_in_psg
    userusername to connect to postgresclickhouse_user
    passwordpassword to connect to postgresClickHouse_123
    note

    View the PostgreSQL table engine doc page for a complete list of parameters.

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');
  3. 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 │
    └────┴─────────┘
  4. 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');
  5. 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)

Summary​

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 for more features, such as specifying schemas, returning only a subset of columns, and connecting to multiple replicas.