PostgreSQL to StarRocks Data Replication

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

Overview

Use StarRocks for reporting, data warehouse, and ad hoc query workloads. This guide shows how to move PostgreSQL data into StarRocks, initialize the target, keep incremental changes in sync, and verify the result before downstream systems use it.

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 database type is PostgreSQL.

• The target database is StarRocks 3.1, 3.0, 2.5, 2.4, 2.3, 2.2, 2.1, or 2.0.

• 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.

  • Set wal_level to logical.
  • Set wal_sender_timeout to 0. This parameter stops replication connections that stay inactive 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.
  • Set max_wal_senders to a value greater than 1.

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

  Replication Type	Source Data Source	Target Data Source
  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)

Restrictions

• Check the performance of the source and target data sources before you start the task. Run full replication during off-peak hours when possible because the initial load consumes read and write resources.
• Make sure each replicated table has a primary key or unique constraint, and that column names are unique. Otherwise, duplicate rows may be replicated.

Procedure

1. Sign in to the NineData Console.

2. Go to Replication.

3. On the Replication page, click Create Replication.

4. On the Source & Target tab, configure the fields in the table, and 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 StarRocks data source that receives the replicated objects.
   Type	Choose what to replicate to StarRocks. 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 precheck finds a table with the same name. Skip and Continue Task: Continue the task after a prompt. During structure 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 structure 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 structure replication completes. Keep Schema and Clear Data, then Rewrite(Optional when both structure and data replication are selected): Continue the task after a prompt. During structure 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 precheck finds 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 field, 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 database and table names after structure replication, and then click Save and Pre-Check.

     tip

     Click Mapping & Filtering to customize target column names after replication. Set Data Filter so that only rows that match the filter are replicated. For example, if the filter condition is emp_no>=10005, rows whose emp_no value is less than 10005 are not 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 finish 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 structure replication completes.
     • Schema+Full, Full: Starts after full replication completes.
     • Schema+Full+Incremental, Incremental: Starts when incremental data matches the source for the first time and Delay is 0 seconds. Click View Details to view the 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.

Result

1. Sign in to the NineData Console.

2. Go to Replication.

3. On the Replication page, click the Task ID of the target synchronization task. 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	Structure Replication (Displayed when structure replication is included)	Shows structure replication progress and details. Click Logs to view structure 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 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 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 StarRocks data type mapping

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

Category	PostgreSQL Data Type	StarRocks 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)	If the width of (n) is less than 11000: use VARCHAR(3*n). If the width of (n) is greater than or equal to 11000: use STRING.
	CHAR(n)	If the width of (n) is less than 80: use CHAR(3*n). If the width of (n) is greater than or equal to 80: use STRING.
	TEXT	STRING
Binary	BYTEA	STRING
Date and Time	TIMESTAMP [(p)] [without time zone]	DATETIME (PostgreSQL and StarRocks must use the same session-level time_zone value)
	TIMESTAMP [(p)] with time zone	DATETIME (PostgreSQL and StarRocks must use the same session-level time_zone value)
	DATE	DATE
	TIME [(p)] [without time zone]	VARCHAR
	TIME [(p)] with time zone	VARCHAR
	INTERVAL [fields] [(p)]	VARCHAR(100)
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	JSON

Next steps

• Data Replication