MySQL Migration to PostgreSQL
UDTS supports migration from MySQL to MAXIR. Supported MySQL versions include MySQL (including Percona edition) 5.5/5.6/5.7/8.x. Supported PostgreSQL versions range from 9.4 to 16.x.
1. Functional Limitations
1.1 Source MySQL Limitations
- For incremental/full+incremental migration, the source database needs to enable binlog and set the format to ROW, with the image set to FULL.
Query Method:
show global variables like 'binlog_format';
show global variables like 'binlog_row_image';
Configuration Method:
set global binlog_format = "ROW";
set global binlog_row_image = "FULL";2. Migration Contents
| Migration Content | Description |
|---|---|
| Migration Structure | 1. Database, Table structure, and data 2. Existing databases and tables in the target database will not be cleared before migration starts. If automatic table creation does not meet requirements, the client can manually create tables in the target database in advance. |
| Migration Range | Only databases and tables that can be queried at the time of task creation will be migrated. Newly added tables during task operation will not be automatically migrated for the time being. |
| DDL | Not supported |
| DML | insert/update/delete |
3. Form Filling
Data Source Form
| Parameter Name | Description |
|---|---|
| Address Type | Supports three methods: intranet address, external address, and dedicated line address. Intranet addresses require VPC and subnet information; external addresses support both IP and domain name; dedicated line addresses support both IP and domain name, but using domain names requires an external network outlet. |
| Port | MySQL connection port |
| Username | MySQL connection username |
| Password | Password for the MySQL database user |
| Database Name | MySQL database name. Only single-database migration is supported. |
| Table Name | MySQL transfer table name. Only effective when “Database Name” specifies a single database. If not filled in, all tables in the specified database will be migrated by default. To transfer a specific table, please fill in the table name; to transfer multiple tables, enter multiple table names in sequence, separated by commas. |
| Maximum Rate | The rate range for external/dedicated line is 1-56 MB/s; the rate range for intranet is 1-1024 MB/s. |
Target Transfer Form
| Parameter Name | Description |
|---|---|
| Address Type | Currently only intranet is supported for the target. |
| Port | PostgreSQL connection port |
| Username | PostgreSQL connection username |
| Password | Password for the PostgreSQL database user |
4. Permission Requirements
| Category | Permission Description |
|---|---|
| Source MySQL | SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT |
| Target PostgreSQL | Must have owner privileges for the database to be migrated, or rolcreatedb privileges |
5. Data Type Mapping
| MySQL Data Type | PostgreSQL Data Type |
|---|---|
| BIT(1) TINYINT(1) |
BOOL |
| TINYINT | INT2 |
| TINYINT UNSIGNED SMALLINT |
INT2 |
| SMALLINT UNSIGNED MEDIUMINT MEDIUMINT UNSIGNED INT INTEGER YEAR |
INT4 |
| INT UNSIGNED INTEGER UNSIGNED BIGINT |
INT8 |
| BIGINT UNSIGNED | NUMERIC(20,0) |
| DECIMAL(p, s) DECIMAL(p, s) UNSIGNED NUMERIC(p, s) NUMERIC(p, s) UNSIGNED |
NUMERIC(p,s) |
| FLOAT FLOAT UNSIGNED |
FLOAT4 |
| DOUBLE DOUBLE UNSIGNED REAL REAL UNSIGNED |
FLOAT8 |
| CHAR VARCHAR TINYTEXT MEDIUMTEXT TEXT LONGTEXT ENUM JSON ENUM |
VARCHAR/TEXT |
| DATE | DATE |
| TIME(s) | TIME(s) |
| DATETIME TIMESTAMP(s) |
TIMESTAMP(s) |
| BINARY VARBINAR BIT(p) TINYBLOB MEDIUMBLOB BLOB LONGBLOB GEOMETRY |
BYTEA |