Skip to main content

PostgreSQL to Doris Real-Time Synchronization | NineData

PostgreSQL often runs transactional workloads, while Apache Doris is used for analytics, reporting, and data warehouse queries. NineData Data Replication helps replicate PostgreSQL data to Doris through schema replication, full replication, and incremental replication.

Use this workflow to initialize Doris from PostgreSQL, keep Doris updated with source-side changes, and validate the target before it is used by downstream systems.

When to use this workflow​

Use this workflow to:

  • Build an analytics layer in Doris from PostgreSQL business data.
  • Reduce analytical query load on the PostgreSQL source.
  • Initialize Doris table structures and data from PostgreSQL.
  • Keep the target updated with incremental PostgreSQL changes.
  • Validate data consistency before handing the Doris target to analytics users.

For detailed permissions, WAL settings, Doris table model rules, data type mapping, and precheck items, see PostgreSQL to Doris.

Before you begin​

Prepare the following items:

  • Access to the NineData console.
  • Connection information and accounts for the PostgreSQL source and Doris target.
  • Required permissions on both systems.
  • PostgreSQL WAL settings required for incremental replication, including wal_level=logical.
  • 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 Doris target.

Step 2: Create the synchronization 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 Doris target.

  3. Select the replication types required for the task:

    • Schema Replication to create target structures.
    • Full Replication to load existing source data.
    • Incremental Replication to synchronize new source-side changes after the full load.

    Configure PostgreSQL to Doris replication

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

  5. After the precheck passes, start the task.

  6. Monitor the task until the full load finishes and incremental delay reaches 0 seconds.

    Monitor synchronization delay

Step 3: Validate synchronized data​

Use data comparison to confirm that Doris contains the expected 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 to validate the latest synchronized data.

    Run data comparison again

Step 4: Configure alerts​

For a long-running synchronization task, configure alerts for task failure and replication delay.

  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 starts, NineData initializes the selected PostgreSQL data in Doris and then keeps Doris updated through incremental replication. When synchronization delay is 0 seconds and validation passes, the Doris target can be used for analytics, reporting, and downstream warehouse workloads.