PostgreSQL to ClickHouse

BladePipe supports data replication from PostgreSQL to ClickHouse. View supported migration, sync, verification, and connector capabilities.

Target DataSource：

ClickHouse

Connection

Basic Functions

Function	Description
Schema Migration	If the target does not have the selected table, it will automatically generate and execute the creation statement on the target based on the source metadata and mapping.
Full Data Migration	Logical migration that sequentially scans the table data and writes it in batches to the target database.
Incremental Real-time Sync	Supports common DML sync for INSERT, UPDATE, and DELETE.
Data Verification	Performs full data verification and supports scheduling.
Modify Subscription	Add, delete, or modify subscription tables, with support for historical data migration. Documentation: Modify Subscription.
Reset Position	Supports rewinding the position based on file position or timestamp, allowing re-consumption of incremental logs from a specified Binlog file and position or a past period.
Table Name Mapping	Supports keep consistent with the source, convert to lowercase, convert to uppercase, and truncate with '_number' suffix.
DDL Sync	PostgreSQL DDL sync is realized by triggers. The user should have the permissions on triggers and tables. For more information, see Permissions Required for PostgreSQL ALTER TABLE ADD COLUMN, DROP COLUMN, MODIFY COLUMN, CHANGE COLUMN CREATE TABLE (full database sync)
Metadata Retrieval	Queries the target from the source table, with support for filter conditions and primary key settings.

Advanced Functions

Function	Description
Write in Append Mode	INSERT and UPDATE statements are written in batches in append mode, and DELETE statements are executed individually through ALTER statements.
Scheduled Table Optimization	By setting the parameter autoOptimizeThresholdSec, the tables are optimized regularly.
Scheduled Full Migration	Documentation 1: Create Scheduled Full Task Documentation 2: Use Scheduled Full Migration for Incremental Data.
Custom Code	Documentation 1: Create a Custom Code Task Documentation 2: Debug Custom Code Task Documentation 3: Log in Custom Code.
Data Filtering Conditions	Supports WHERE conditions for data filtering, using SQL 92 subset. Documentation: Create Data Filter Task.
Set Target Primary Key	Change the primary key to another field for easier data aggregation and operations.
Add Virtual Columns	Supports adding custom virtual columns with fixed values, such as region or ID.

Limits

Limit	Description
Special Operation	Too many DELETE operations (>50 records/second) significantly affect data synchronization performance.
Target Table Engine	Only the following table engines and corresponding source table types are supported: MergeTree(tables without primary keys) ReplacingMergeTree(tables with primary keys) ReplicatedMergeTree(tables without primary keys) ReplicatedReplacingMergeTree(tables with primary keys)

---

Source

Prerequisites

Prerequisite	Description
Permissions for Account	Required permissions (taking a self-managed database as an example): GRANT ALL PRIVILEGES ON DATABASE sync_db TO sync_user (or SELECT permission on all views in the sync_db information_schema, and SELECT permission on tables, indexes, constraints to be synchronized) ALTER USER sync_user REPLICATION
Incremental Data Sync Preparation	Prepare as follows: Modify postgresql.conf, set wal_level=logical and wal_log_hints=on Modify pg_hba.conf, set host replication sync_user CIDR netmask md5, host sync_db sync_user CIDR netmask md5, host postgres sync_user CIDR netmask md5 Restart PostgreSQL
Port Preparation	Allow the migration and sync node (Worker) to connect to the PostgreSQL port (e.g., port 5432).

Parameters

Parameter	Description
fullFetchSize	Fetch size for scaning full data.
eventStoreSize	Cache size for parsed incremental events.
ignoreGisSRID	Whether to ignore SRID when parsing GIS data types.
defaultGisSRID	Set the SRID for GIS data types.

Tips: To modify the general parameters, see General Parameters and Functions.

---

Target

Prerequisites

Prerequisite	Description
Permissions for Account	SELECT, INSERT and common DDL permissions.
Port Preparation	Allow the migration and sync node (Worker) to connect to the ClickHouse port (e.g., 8123).

Parameters

Parameter	Description
multiReplica	Whether there are multiple replicas in a cluster.
clusterName	Cluster name. When multiReplica is true, the ON CLUSTER clusterName clause is automatically added to DDL/DML.
ckTableEngine	The following table engines are currently supported: MergeTree ReplacingMergeTree ReplicatedMergeTree ReplicatedReplacingMergeTree
autoOptimizeThresholdSec	Interval of scheduled table optimization (optimize table final). If the value <=0, it means the feature is disabled.
enableTimeRangeClamping	Whether to enable time range clamping. Forces date and time values to be constrained within the valid ClickHouse JDBC range. Values outside this range will be clamped to the minimum or maximum values. Disabled by default (false). Ranges after clamping(UTC)： Date：[1970-01-01, 2149-06-06] Date32：[1925-01-01, 2283-11-11] Timestamp：[1970-01-01 00:00:00, 2106-02-07 14:28:15] Timestamp64：[1925-01-01 08:00:00.000, 2283-11-12 07:59:59.000]

Tips: To modify the general parameters, see General Parameters and Functions.