MaterializedPostgreSQL table engine

ClickHouse Cloud users are recommended to use ClickPipes for PostgreSQL replication to ClickHouse. This natively supports high-performance Change Data Capture (CDC) for PostgreSQL.

Creates ClickHouse table with an initial data dump of PostgreSQL table and starts the replication process, i.e. it executes a background job to apply new changes as they happen on PostgreSQL table in the remote PostgreSQL database.

This table engine is experimental. To use it, set allow_experimental_materialized_postgresql_table to 1 in your configuration files or by using the SET command:

SET allow_experimental_materialized_postgresql_table=1

If more than one table is required, it is highly recommended to use the MaterializedPostgreSQL database engine instead of the table engine and use the materialized_postgresql_tables_list setting, which specifies the tables to be replicated (will also be possible to add database schema). It will be much better in terms of CPU, fewer connections and fewer replication slots inside the remote PostgreSQL database.

Creating a table

CREATE TABLE postgresql_db.postgresql_replica (key UInt64, value UInt64)
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgresql_table', 'postgres_user', 'postgres_password')
PRIMARY KEY key;

Engine Parameters

host:port — PostgreSQL server address.
database — Remote database name.
table — Remote table name.
user — PostgreSQL user.
password — User password.

TLS/SSL

TLS/SSL parameters are forwarded to libpq and can be supplied through a named collection or as trailing key-value arguments of the engine: sslmode (disable, allow, prefer, require, verify-ca or verify-full; when unset, the libpq default of prefer applies), and the certificates and the key in one of two forms. sslrootcert (CA certificate), sslcert (client certificate) and sslkey (client private key) are paths to server-local files, accepted only from a named collection defined in the server configuration file. sslrootcert_pem, sslcert_pem and sslkey_pem accept the literal contents of the corresponding file instead, can be specified from SQL, and are masked in logs and SHOW queries like a password.

CREATE TABLE postgresql_db.postgresql_replica (key UInt64, value UInt64)
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgresql_table', 'postgres_user', 'postgres_password',
                                sslmode = 'verify-full', sslrootcert_pem = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----')
PRIMARY KEY key;

The TLS/SSL parameters are part of the PostgreSQL connection parameters, which are fixed when the table is created.

Requirements

The wal_level setting must have a value logical and max_replication_slots parameter must have a value at least 2 in the PostgreSQL config file.
A table with MaterializedPostgreSQL engine must have a primary key — the same as a replica identity index (by default: primary key) of a PostgreSQL table (see details on replica identity index).
Only database Atomic is allowed.
The MaterializedPostgreSQL table engine only works for PostgreSQL versions >= 11 as the implementation requires the pg_replication_slot_advance PostgreSQL function.

Virtual columns

_version — Transaction counter. Type: UInt64.
_sign — Deletion mark. Type: Int8. Possible values:
- 1 — Row is not deleted,
- -1 — Row is deleted.

These columns do not need to be added when a table is created. They are always accessible in SELECT query. _version column equals LSN position in WAL, so it might be used to check how up-to-date replication is.

CREATE TABLE postgresql_db.postgresql_replica (key UInt64, value UInt64)
ENGINE = MaterializedPostgreSQL('postgres1:5432', 'postgres_database', 'postgresql_replica', 'postgres_user', 'postgres_password')
PRIMARY KEY key;

SELECT key, value, _version FROM postgresql_db.postgresql_replica;

Replication of TOAST values is not supported. The default value for the data type will be used.

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

Creating a table

TLS/SSL

Requirements

Virtual columns

​Creating a table

​TLS/SSL

​Requirements

​Virtual columns

Creating a table

TLS/SSL

Requirements

Virtual columns