PostgreSQL to Doris Data Replication

NineData Data Replication supports schema replication, full replication, and incremental replication from PostgreSQL to Doris.

Overview

Apache Doris is used for analytics, reporting, and data warehouse workloads. Use this guide to replicate PostgreSQL data into Doris, initialize target tables, keep incremental changes synchronized, and validate the result before downstream systems use the target.

NineData data replication supports schema, full data, and incremental data replication between data sources. For supported data sources, it also supports bidirectional replication for geo-distributed active-active architectures.

• Schema replication: Replicates object structures between homogeneous and heterogeneous data sources.
• Full data replication: Uses data sharding and row-level concurrent batch replication to improve throughput. Breakpoint resume helps preserve data accuracy, including for tables without primary keys.
• Incremental data replication: Replicates DML and DDL changes for supported object types. Row-level concurrency and hotspot merge processing help maintain replication throughput.
• Bidirectional real-time data replication (only between MySQL instances): Replicates changes in both directions between nodes so data can stay current across participating nodes.

Use these capabilities for full or incremental data replication, migration, synchronization, data integration, and low-downtime migration workflows.

Before you begin

• Add the source and target data sources to NineData. For instructions, see Add Data Source.

• The source data source is PostgreSQL, and the target data source is Doris.

• Make sure the source and target accounts have the following permissions.

  Replication Type	Source Data Source	Target Data Source
  Schema Replication	CONNECT, SELECT	DDL
  Full Replication	CONNECT, SELECT	TABLE-related permissions (ALTER, DROP, SELECT, INSERT, UPDATE, DELETE)
  Incremental Replication	SUPERUSER	TABLE-related permissions (ALTER, DROP, SELECT, INSERT, UPDATE, DELETE)

• For incremental replication, open postgresql.conf and configure the following parameters. If you do not know the file location, run SHOW config_file; in the psql client.

  • The wal_level parameter of the source data source must be logical.
  • Set wal_sender_timeout to 0. This parameter interrupts replication connections that stay inactive for longer than the configured number of milliseconds. The default value is 60000. Setting it to 0 disables the timeout.
  • Set max_replication_slots to a value greater than 1. This parameter specifies the maximum number of replication slots that the server supports. The default value is 10.
  • Set max_wal_senders to a value greater than 1. This parameter specifies the maximum number of concurrent WAL sender connections. The default value is 10.

Restrictions

• Before you run a replication task, assess the performance of the source and target data sources. Run the task during off-peak hours when possible because full initialization consumes read and write resources.
• Each replicated table must have a primary key or unique constraint, and column names must be unique. Otherwise, duplicate rows may be replicated.

Procedure

1. Sign in to the NineData Console.

2. In the navigation menu, click Replication > Data Replication.

3. On the Replication page, click Create Replication.

4. On the Source & Target tab, set the following fields, then click Next.

   Parameter	Description
   Name	Enter a meaningful task name. Up to 64 characters are supported.
   Source	Select the PostgreSQL data source that contains the objects to replicate.
   Target	Select the Doris data source that receives the replicated objects.
   Type	Select the content to replicate to Doris. Schema: Replicate only source database and table schemas. Data is not replicated. Full: Replicate all selected source objects and data. This is full data replication. Incremental: After full replication completes, replicate incremental changes from source logs.
   If target table already exists(Required when Schema is selected)	Pre-Check Error and Stop Task: Stop the task if the precheck detects a table with the same name. Skip and Continue Task: Continue the task after a prompt. During schema replication, skip the same-name table. If data replication is also enabled, append data to the existing table without overwriting existing data. Delete Objects and Rewrite: Continue the task after a prompt. During schema replication, delete the same-name table in the target database and replicate the table structure from the source again. If data replication is also enabled, write data after schema replication completes. Keep Schema and Clear Data, then Rewrite(Optional when both schema and data replication are selected): Continue the task after a prompt. During schema replication, keep the target table structure. When data replication starts, clear the data in the same-name table and then replicate data from the source table again.
   Target Table Exists Data(Required when Schema is not selected)	Pre-Check Error and Stop Task: Stop the task if the precheck detects existing data in the target table. Ignore existing target data and append to it.: Ignore existing data in the target table and append other data. Clear target existing data before write: Delete existing data in the target table before writing new data.

5. On the Objects tab, configure the following parameter, and then click Next.

   Parameter	Description
   Replication Objects	Select the objects to replicate. Select All Objects to replicate all objects in the source database, or select Customized Object, choose objects in the Source Object list, and click > to move them to the Target Object list.

6. On the Mapping tab, choose the action that matches the selected replication type.

   • If Schema is included: Configure the target table name, Key, and Distribute Key after the table schema is replicated to Doris, then click Save and Pre-Check. The table below shows the default Doris data model and key definitions for each PostgreSQL table type.

     PostgreSQL Table Type	Doris Data Model	Doris Key Definition	Doris Distribute Key Definition
     Table with Primary Key	Unique Model	All primary key columns, in the order defined by the source database.	All primary key columns.
     Table without Primary Key but with Unique Key	Unique Model	All unique key columns, in the order defined by the source database.	All unique key columns.
     Table without Primary Key and without Unique Key	Duplicate Model	The first two columns in the table are selected by default.	Same as Key.

   tip

   Click Mapping & Filtering to customize target column names after replication. Set Data Filter so that only rows that match the filter are replicated.

   :::

   • If Schema is not included: NineData selects the database with the same name in the target data source by default. If no same-name database exists, select the target database manually. The table and column names in the target database must match the replication objects. If they do not match, map them manually.

7. On the Pre-check tab, wait for NineData to complete the precheck. After the precheck passes, click Launch.

   tip

   • To compare target data with source data after synchronization, enable Enable data consistency comparison. Depending on the selected Type, Enable data consistency comparison starts as follows:
     • Schema: Starts after schema replication completes.
     • Schema+Full, Full: Starts after full replication completes.
     • Schema+Full+Incremental, Incremental: Starts when incremental data is consistent with the source for the first time and Delay is 0 seconds. Click View Details to view synchronization delay on the Details page.
   • If the precheck fails, click Details in the Actions column for the failed item, fix the issue, and then click Check Again to run the precheck again.
   • If Result is Warning, fix the issue or ignore the warning.

8. On the Launch page, the Launch Successfully message appears and the synchronization task starts. Use these actions:

   • Click View Details to view each synchronization stage.
   • Click Back to list to return to the Replication task list.

View replication results

1. Sign in to the NineData Console.

2. In the navigation menu, click Replication > Data Replication.

3. Click the Task ID of the target synchronization task on the Replication page. The task details page contains the following areas.

   

   No.	Function	Description
   1	Synchronization Delay	Shows the synchronization delay between the source and target data sources. 0 seconds means the target has caught up with the source, which is the expected state before a planned traffic switch.
   2	Configure Alerts	Notifies the selected contacts when the task fails. For more information, see Introduction to Operations Monitoring.
   3	More	Pause: Pause a task whose status is Running. Terminate: End an unfinished task or a task that is running incremental synchronization. After a task is terminated, it cannot be restarted. Proceed with caution. If the synchronization objects contain triggers, a trigger replication option appears. Delete: Delete the task. Deleted tasks cannot be restored. Proceed with caution.
   4	Structural Replication (Displayed when structural replication is included)	Shows schema replication progress and details. Click Logs to view schema replication logs. Click the icon to refresh the information. Click View DDL in the Operation column of a target object to view SQL replay.
   5	Full Replication (Displayed when full replication is included)	Shows full replication progress and details. Click Monitoring to view full replication metrics. On the monitoring page, click Throttle Settings to limit the write rate to the target data source. Click Logs to view full replication logs. Click the icon to refresh the information.
   6	Incremental Replication (Displayed when incremental replication is included)	Shows incremental replication metrics. Click View Threads to view operations currently running in the replication task, including: Thread ID: The current thread ID. A replication task can use multiple threads. Execute SQL: Details of the SQL statement that the current thread is executing. Response Time: The response time of the current thread. If this value increases, the thread may be blocked. Event Time: The timestamp when the current thread started. Status: The current thread status. Click Throttle Settings to limit the write rate to the target data source. The unit is rows per second. Click Logs to view incremental replication logs. Click the icon to refresh the information.
   7	Modify Object	Shows modification records for synchronization objects. Click Modify Synchronization Object to update the synchronization scope. Click the icon to refresh the information.
   8	Data Comparison	Shows structure and data comparison results between the source and target data sources. If data comparison is not enabled, click Enable Data Comparison. Click Re-compare to compare the current source and target data again. Click a date to view all comparison result records, and click a result item to open its details. Click Logs to view consistency comparison logs. Click Monitoring (displayed only for data comparison) to view the comparison RPS trend chart. Click Details to view earlier records. Click in the Operation column of a comparison row to view source and target comparison details. Click in the Operation column when inconsistent data exists to generate change SQL. Copy the SQL to the target data source to fix inconsistent data.
   9	Expand	Shows current replication task details, including Replication Type, Replication Object, and Start Time.

Appendix 1: PostgreSQL to Doris data type mapping

During replication, PostgreSQL data types are mapped to the corresponding Doris data types.

Category	PostgreSQL Data Type	Doris Data Type
NUMERIC	SMALLINT	SMALLINT
	INTEGER	INT
	BIGINT	BIGINT
	DECIMAL	DECIMAL
	REAL	DOUBLE
	DOUBLE	DOUBLE (up to 16 decimal places)
	SMALLSERIAL	SMALLINT
	SERIAL	INT
	BIGSERIAL	BIGINT
MONETARY	MONEY	DECIMAL
CHARACTER	VARCHAR(N)	To avoid data loss, use the following conversion rules: If the width of VARCHAR(N) in the source is less than 11000: use VARCHAR(3*N). If the width of VARCHAR(N) in the source is greater than 11000: use STRING.
	CHAR(N)	To avoid data loss, use the following conversion rules: If the width of CHAR(N) in the source is less than 80: use CHAR(3*N). If the width of CHAR(N) in the source is greater than 80: use STRING.
	TEXT	STRING
BINARY	BYTEA	STRING
DATE AND TIME	TIMESTAMP [(P)] [WITHOUT TIME ZONE]	Doris version >= 1.2.0: DATATIME (without fsp) or DATATIMEV2 (with fsp). Doris version < 1.2.0: DATATIME
	TIMESTAMP [(P)] WITH TIME ZONE	Doris version >= 1.2.0: DATATIME (without fsp) or DATATIMEV2 (with fsp). Doris version < 1.2.0: DATATIME
	DATE	DATE
	TIME [(P)] [WITHOUT TIME ZONE]	VARCHAR
	TIME [(P)] WITH TIME ZONE	VARCHAR
	INTERVAL [FIELDS] [(P)]	INT
BOOLEAN	BOOLEAN	BOOLEAN
GEOMETRIC	POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE	VARCHAR
NETWORK ADDRESS	CIDR, INET, MACADDR, MACADDR8	VARCHAR
TEXT SEARCH	TSVECTOR	VARCHAR
XML	XML	VARCHAR
JSON	JSON	Doris version >= 1.2.0: JSONB. Doris version < 1.2.0: STRING.

Appendix 2: Pre-check items

Check item	Check content
Object name compliance check	Checks whether the database name and table name follow the Doris naming convention
Source data source connection check	Checks the gateway status of the source data source, whether the instance is reachable, and whether the username and password are correct
Target data source connection check	Checks the gateway status of the target data source, whether the instance is reachable, and whether the username and password are correct
Target database permission check	Checks whether the target database account has the required permissions
Source database permission check	Checks whether the source database account has the required permissions
Target database data existence check	Checks whether the target database already contains data for the object to be replicated
Target database same-name object existence check	Checks whether an object with the same name already exists in the target database
Check wal_level	Checks whether wal_level of the source data source is logical

Related links

• Data Replication