Skip to main content

PostgreSQL to ClickHouse Data Migration | NineData

Use this workflow when you need to move PostgreSQL data into ClickHouse for analytics, reporting, or downstream analysis. NineData Data Replication helps create the ClickHouse target structure and load the selected PostgreSQL data.

This page explains the scenario workflow. For detailed prerequisites, permissions, system columns, data type mapping, and precheck items, see PostgreSQL to ClickHouse.

When to use this workflow​

Use this workflow when you need to:

  • Move PostgreSQL tables into ClickHouse for analytics.
  • Initialize ClickHouse table structures from PostgreSQL.
  • Run large analytical queries in ClickHouse instead of the transactional PostgreSQL database.
  • Validate replicated data before it is used by reporting or downstream systems.

Overview​

PostgreSQL and ClickHouse use different table models. In a typical NineData workflow, schema replication creates the ClickHouse table structures and full replication loads the selected PostgreSQL data.

If you do not use schema replication, prepare the ClickHouse target tables manually. The table structures must match the PostgreSQL source objects, and the target tables must include the required system columns described in the canonical replication guide.

Before you begin​

Prepare the following items:

  • Access to the NineData console.
  • Connection information and accounts for the PostgreSQL source and ClickHouse target.
  • Required permissions on both systems.
  • The required ClickHouse account configuration, including access_management=1.
  • Network connectivity from NineData to both systems.

Step 1: Add the source and target data sources​

  1. In the NineData console, click Data Source Management > Data Sources, and then click Create Data Source.

    Create a data source in NineData

  2. Follow the page prompts, test the connection, and click Create Data Source.

    Complete data source configuration

  3. Repeat the process for the PostgreSQL source and the ClickHouse target.

Step 2: Create the migration task​

  1. In the NineData console, click Data Replication > Data Replication, and then click Create Replication.

    Create a replication task

  2. Configure the PostgreSQL source and ClickHouse target.

  3. Select the replication types required for the task. For PostgreSQL to ClickHouse, use Schema Replication and Full Replication to create target structures and load existing data.

    Configure PostgreSQL to ClickHouse replication

  4. Select the source objects, configure mapping if needed, and run the precheck.

  5. After the precheck passes, start the task.

  6. Monitor the task until full replication completes.

    Monitor migration progress

Step 3: Validate replicated data​

  1. Open the replication task created in Step 2.

    Open task details

  2. Click the Data Comparison tab, and click Start Data Comparison if comparison results are not already available.

    Start data comparison

    tip

    After comparison starts, NineData compares the selected source and target objects and displays the comparison result.

    View data comparison results

  3. Click Re-compare when you need to validate the latest result after another run.

    Run data comparison again

Step 4: Configure alerts​

Configure alerts so operators are notified if the migration task fails.

  1. Open the replication task details page.

    Open task details

  2. Click Configure Alerts.

    Configure task alerts

  3. Enter a Policy Name, review the alert rules, and click Save Configuration.

    Save alert policy

Result​

After the task completes, ClickHouse contains the selected PostgreSQL table structures and full data. You can use the ClickHouse copy for analytical queries and reporting while keeping the PostgreSQL source focused on transactional workloads.