MySQL to PostgreSQL Data Replication

NineData Data Replication supports full replication and incremental replication from MySQL-compatible data sources to PostgreSQL.

Overview

Use this workflow to migrate MySQL data to PostgreSQL and keep the target database updated with incremental source changes.

Before you begin

• Add both data sources to NineData. For details, see Add data source.

• The supported MySQL source versions range from 5.1 to 9.0.

• The target data source must be PostgreSQL 15, 14, 13, 12, 11, or 10.

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

  Replication type	Source data source	Target data source
  Schema Replication	SELECT permission	SELECT, INSERT, UPDATE, DELETE permissions
  Full Replication	SELECT permission	SELECT, INSERT, UPDATE, DELETE permissions
  Incremental Replication	SELECT, REPLICATION CLIENT, REPLICATION SLAVE permissions	SELECT, INSERT, UPDATE, DELETE permissions

• For incremental replication, enable Binlog on the source data source and set the following parameters:

  • binlog_format=ROW

  • binlog_row_image=FULL

    tip

    If the source data source is a standby database, enable log_slave_updates to capture the full Binlog.

• If the source and target data sources are in different regions, add each NineData server IP address to the other side's whitelist. Find both IP addresses on the "Source & Target" tab when you create the task.

Restrictions

• Incremental replication does not support synchronizing source-side DDL operations.
• Data replication applies only to user databases. System databases are not replicated. For MySQL data sources, system databases include information_schema, mysql, performance_schema, and sys.
• Check source and target performance before you start. Run synchronization during off-peak hours when possible because full initialization reads and writes both data sources and increases database load.
• Make sure each replicated table has a primary key or unique constraint, and that column names are unique. Otherwise, the same data may be synchronized more than once.

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 in the page header.

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

   Parameter	Description
   Name	Enter a task name that is easy to recognize later. Up to 64 characters are supported.
   Source	Select the source data source.
   Target	Select the target data source.
   Type	Choose what to copy to the target data source. Schema: Copy only the source table structure, not the data. Full: Copy all objects and data from the source. This is full replication. Incremental: After full synchronization finishes, sync changes from the source logs.
   If target table already exists (Required when Schema is selected)	Pre-Check Error and Stop Task: Stop the task if the precheck finds a table with the same name. Skip and Continue Task: If the precheck finds a table with the same name, continue the task after a message appears. During schema replication, NineData skips that table. If data replication is also enabled, NineData appends data to the table with the same name without overwriting existing data. Delete Objects and Rewrite: If the precheck finds a table with the same name, continue the task after a message appears. During schema replication, NineData deletes the table with the same name in the target database and copies the schema from the source again. If data replication is also enabled, NineData writes data after the schema is copied. Keep Schema and Clear Data, then Rewrite (Optional when both schema and data replication are performed): If the precheck finds a table with the same name, continue the task after a message appears. During schema replication, NineData keeps the target table schema. When data replication starts, NineData clears the data in the table with the same name and then copies data from the source again.
   Target Table Exists Data (Required when Schema is not selected)	Pre-Check Error and Stop Task: Stop the task if the precheck finds existing data in the target table. Ignore existing target data and append to it.: Ignore the existing data in the target table and append new data. Clear target existing data before write: Delete the existing data in the target table and write new data.

5. On the Objects tab, set the following fields, then click Next.

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

6. On the Mapping tab, configure mapping based on the selected replication type.

   • If Schema is included: Set the target table name after synchronization, and click Save and Pre-Check.

     tip

     Use Mapping & Filtering to rename columns after synchronization. Set Data Filter. Only rows that match the filter condition are synchronized to the target data source. For example, if the filter condition is emp_no>=10005, rows whose emp_no value is less than 10005 are not synchronized.

   • If "Schema" is not included: NineData selects the database with the same name in the target data source by default. If the database does not exist, 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

   • Select Enable data consistency comparison to compare target data with source data after the replication task completes. The start time of Enable data consistency comparison depends on the selected Type:
     • Schema: Starts after schema replication completes.
     • Schema+Full, Full: Starts after full replication completes.
     • Schema+Full+Incremental, Incremental: Starts when incremental data matches the source data 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 to view the cause. After fixing the issue, click Check Again to run the precheck again.
   • For items whose Result is Warning, fix the issue or ignore the warning when appropriate.

8. On the Launch page, after Launch Successfully appears, the replication task starts running. Use these actions to review the task:

   • Click View Details to view the status of each replication phase.
   • Click Back to list to return to the Replication task list.

Result

1. Sign in to the NineData Console.

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

3. On the Replication page, click the Task ID of the target synchronization task. The task details page shows the following information.

   

   Number	Function	Description
   1	Sync Delay	The synchronization delay between the source and target data sources. 0 seconds means the target has caught up with the source, so you can switch traffic based on your migration plan.
   2	Configure Alerts	When the task fails, the system notifies the selected channel through the configured alert.
   3	More	Pause: Pause the task. Only tasks with Running status are selectable. Duplicate: Create a new replication task with the same configuration as the current task. Terminate: Terminate tasks that are incomplete or still listening (that is, in incremental synchronization). After a task is terminated, it cannot be restarted, so proceed with caution. If the synchronization objects contain triggers, trigger replication options appear for selection. Delete: Delete the task. Once deleted, it cannot be recovered, so proceed with caution.
   4	Structure Replication (displayed in scenarios involving structure replication)	Displays the progress and detailed information of structure replication. Click Log to view the execution logs of structure replication. Click the to view the latest information. Click View DDL in the Actions column for the target object in the list to view SQL replay.
   5	Full Replication (displayed in scenarios involving full replication)	Displays the progress and detailed information of full replication. Click Monitor to view various monitoring indicators during full replication. During full replication, you can also click Flow Control Settings on the monitoring page to limit the rate of writing to the target data source per second. The unit is rows/second. Click Log to view the execution logs of full replication. Click the to view the latest information.
   6	Incremental Replication (displayed in scenarios involving incremental replication)	Displays incremental replication indicators. Click View Threads to view the operations currently being executed by the current replication task, including: Thread ID: Replication tasks are executed in multiple threads, and this shows the current thread number. Execute SQL: Details of the SQL statement currently being executed by the current thread. Response Time: The response time of the current thread. If this value increases, it indicates that the current thread may be stuck for some reason. Event Time: The timestamp when the current thread was started. Status: The status of the current thread. Click Flow Control Settings to limit the rate of writing to the target data source per second. The unit is rows/second. Click Log to view the execution logs of incremental replication. Click the to view the latest information.
   7	Modify Object	Displays the modification records of synchronization objects. Click Modify Objects to configure the synchronization objects. Click the to view the latest information.
   8	Data Comparison	Displays the comparison results between the source and target data sources. If data comparison is not enabled, click Enable Comparison on the page to enable it. Click Re-compare to rerun the comparison for the current source and target data sources. Click Stop to stop the comparison task immediately after it starts. Click Log to view the execution logs of consistency comparison. Click Monitor (displayed only in data comparison) to view the trend chart of RPS (records per second) comparison. Click Details to view earlier records. Click the in the Actions column in the comparison list (displayed under the Data tab only when inconsistencies are found) to view details of the comparison between the source and target data sources. Click the in the Actions column in the comparison list (displayed only when inconsistencies are found): Generate change SQL. You can copy this SQL to the target data source and run it to fix the mismatch.
   9	Expand	Displays detailed information of the current replication task. Common actions: Export table configuration: Export the current task's database and table configuration so you can quickly import it when creating another replication task with the same objects. Alert Rules: Configure alerts for the current task.

Appendix 1: MySQL to PostgreSQL data type mapping

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

Category	MySQL Data Type	PostgreSQL Data Type
Numeric	TINYINT	SMALLINT
	TINYINT UNSIGNED	SMALLINT
	SMALLINT	SMALLINT
	SMALLINT UNSIGNED	INTEGER
	MEDIUMINT	INTEGER
	MEDIUMINT UNSIGNED	INTEGER
	INT	INTEGER
	INT UNSIGNED	BIGINT
	BIGINT	BIGINT
	BIGINT UNSIGNED	BIGINT
	BIT(M)	BIT(N)
	DECIMAL()	DECIMAL
	NUMERIC	DECIMAL
	FLOAT	FLOAT
	DOUBLE	FLOAT8
	BOOL, BOOLEAN	SMALLINT
Date and time	DATE	DATE
	DATETIME[(fsp)]	TIMESTAMP[(p)]
	TIMESTAMP[(fsp)]	TIMESTAMP[(p)] with time zone
	TIME[(fsp)]	TEXT
	YEAR[(4)]	INTEGER
String	CHAR	CHAR[(n)]
	VARCHAR	VARCHAR
	BINARY/VARBINARY	BYTEA
	TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT	TEXT
	TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB	BYTEA
	ENUM	VARCHAR
	SET	VARCHAR
JSON	JSON	JSON

Appendix 2: Precheck items

Check item	What NineData checks
Source data source connection check	Checks the source gateway status, instance accessibility, and username and password accuracy.
Target data source connection check	Checks the target gateway status, instance accessibility, and username and password accuracy.
Target database permission check	Checks whether the target database account permissions meet the requirements.
Source database permission check	Checks whether the source database account permissions meet the requirements.
Source database log_slave_updates support check	Checks whether log_slave_updates is set to ON when the source database is a standby database.
Source and target data source version check	Checks whether the source and target database versions are compatible.
Source database Binlog enabled check	Checks whether Binlog is enabled in the source database.
Source database Binlog format support check	Checks whether the source database binlog format is ROW.
Source database binlog_row_image support check	Checks whether the source database binlog_row_image is FULL.
Target database data existence check	Checks whether data already exists in the target database for the objects to replicate.
Target database same-name object existence check	Checks whether objects with the same names already exist in the target database.

Next steps

• Introduction to Data Replication