Introduction
In today’s dynamic corporate climate, data migration between database systems is a deliberate move that coincides with changing organizational demands. This block describes the migration of PostgreSQL to MySQL. This change necessitates meticulous preparation to handle possible issues and guarantee smooth interaction with existing business procedures.
Basic Differences
PostgreSQL
- Advanced Feature Set: PostgreSQL is known for its rich feature set, including support for complex data types, advanced indexing, and strong compliance with SQL standards.
- Robust Concurrency: Utilizes MVCC (Multi-Version Concurrency Control) to manage high concurrency with minimal locking.
- Extensible: Highly extensible with support for custom datatypes, indexes, and procedural languages.
MySQL
- Performance-Oriented: MySQL is optimized for high performance, especially in read-heavy applications, making it a popular choice for web applications.
- Cost-Effective: Often associated with lower licensing fees and operational costs.
- Extensive Ecosystem: Supported by a large community and numerous plugins for enhanced functionality.
Data Type Differences
Understanding the data type differences between PostgreSQL and MySQL is critical for a successful move. Here are some important distinctions:
| PostgreSQL | MySQL | Notes |
| SERIAL | INT AUTO_INCREMENT | MySQL uses AUTO_INCREMENT for auto-incrementing fields. |
| BOOLEAN | TINYINT(1) | MySQL doesn’t have a BOOLEAN type; uses TINYINT(1) instead. |
| BYTEA | BLOB / BINARY | MySQL uses BLOB or BINARY for binary data; careful attention needed for conversions. |
| TEXT | TEXT | Both systems support TEXT, but handling and performance may vary. |
| JSON | JSON | Both support a JSON type but with different manipulation functions. |
| ARRAY | N/A | MySQL lacks native ARRAY type; use JSON or join tables for similar functionality. |
| UUID | VARCHAR(36) | MySQL typically stores UUIDs as VARCHAR(36). |
| TIMESTAMP” | TIMESTAMP | Both support TIMESTAMP, but default behaviors and timezone handling may differ. |
Migration Tools from PostgreSQL to MySQL:
Several solutions can help with the conversion from PostgreSQL to MySQL, preserving data integrity while decreasing manual labor. Here are some popular options:
SQLines:
SQLines’ features include data transfer, database schema (DDL) conversion, procedures, and scripting. It supports a large number of PostgreSQL and MySQL versions .
It may require tweaking for complicated schema configurations. Performance might vary according to data amount.
MySQL Workbench Migration Wizard:
Features: A step-by-step wizard guides you through database conversion, and it supports a variety of RDBMS systems, including PostgreSQL .
Limitations: On big datasets, GUI difficulties and performance bottlenecks might degrade the user experience. Some manual interventions may be necessary.
Hevo Data:
Features: Provides an automated pipeline for data migration that requires little setup. It also allows for continuous data integration between PostgreSQL and MySQL via live sync.
Limitations: A paid tool requiring license fees. There may be limits for certain sophisticated data types or formats.
pg2mysql:
Features: A script for converting PostgreSQL dump files to MySQL format, including CREATE TABLE, INSERT INTO, CREATE INDEX, and ALTER TABLE commands.
Limitations include known concerns with complicated schema items such as triggers and stored procedures. Requires manual intervention for certain data type conversions.
In this blog we are going to discuss the pg2mysql tool. It’s an open source migration tool.
Data Migration using pg2mysql:
Installing pg2mysql from github repository.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [postgres@mysql-01 ~]$ wget https://github.com/dolthub/pg2mysql/archive/refs/heads/main.zip --2024-12-31 12:07:12-- https://github.com/dolthub/pg2mysql/archive/refs/heads/main.zip Resolving github.com (github.com)... 20.207.73.82 Connecting to github.com (github.com)|20.207.73.82|:443... connected. HTTP request sent, awaiting response... 302 Found Location: https://codeload.github.com/dolthub/pg2mysql/zip/refs/heads/main [following] --2024-12-31 12:07:15-- https://codeload.github.com/dolthub/pg2mysql/zip/refs/heads/main Resolving codeload.github.com (codeload.github.com)... 20.207.73.88 Connecting to codeload.github.com (codeload.github.com)|20.207.73.88|:443... connected. HTTP request sent, awaiting response... 200 OK Length: unspecified [application/zip] Saving to: 'main.zip' main.zip [ <=> ] 18.72K --.-KB/s in 0.04s 2024-12-31 12:07:16 (531 KB/s) - 'main.zip' saved [19169] |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | [postgres@mysql-01 ~]$ unzip main.zip Archive: main.zip dd956ff207178dc67750568a375f50025cc37b38 creating: pg2mysql-main/ inflating: pg2mysql-main/LICENSE inflating: pg2mysql-main/README.md inflating: pg2mysql-main/pg2mysql.pl creating: pg2mysql-main/test/ inflating: pg2mysql-main/test/auto-increment.bats inflating: pg2mysql-main/test/basic-types.bats inflating: pg2mysql-main/test/begin-end.bats inflating: pg2mysql-main/test/cli-args.bats inflating: pg2mysql-main/test/common.bash inflating: pg2mysql-main/test/indexes-keys-constraints.bats inflating: pg2mysql-main/test/unsupported-types.bats [postgres@mysql-01 ~]$ cd pg2mysql-main/ [postgres@mysql-01 pg2mysql-main]$ ll total 36 -rw-r--r-- 1 postgres postgres 11358 Oct 9 2023 LICENSE -rwxr-xr-x 1 postgres postgres 20249 Oct 9 2023 pg2mysql.pl -rw-r--r-- 1 postgres postgres 1778 Oct 9 2023 README.md drwxr-xr-x 2 postgres postgres 186 Oct 9 2023 test |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | postgres=# create database dummy; CREATE DATABASE postgres=# \c dummy You are now connected to database "dummy" as user "postgres". dummy=#CREATE TABLE employees ( dummy=# employee_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, dummy=# first_name VARCHAR(50) NOT NULL, dummy=# last_name VARCHAR(50) NOT NULL, dummy=# email VARCHAR(100) UNIQUE NOT NULL, dummy=# hire_date DATE NOT NULL, dummy=# salary NUMERIC(10, 2) CHECK(salary > 0) dummy=#); CREATE TABLE dummy=#CREATE TABLE departments ( dummy=# department_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, dummy=# department_name VARCHAR(100) NOT NULL dummy=#); CREATE TABLE dummy=#CREATE TABLE project_assignments ( dummy=# employee_id INT, dummy=# project_id INT, dummy=# assignment_date DATE NOT NULL, dummy=# PRIMARY KEY (employee_id, project_id) dummy=#); CREATE TABLE dummy=#CREATE TABLE products ( dummy=# product_id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, dummy=# product_name VARCHAR(100) NOT NULL, dummy=# price NUMERIC(10, 2) DEFAULT 0.00, dummy=# created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP dummy=#); CREATE TABLE |
| 1 2 3 4 5 6 7 8 9 | dummy=# \dt+ List of relations Schema | Name | Type | Owner | Persistence | Access method | Size | Description --------+---------------------+-------+----------+-------------+---------------+------------+------------- public | departments | table | postgres | permanent | heap | 8192 bytes | public | employees | table | postgres | permanent | heap | 8192 bytes | public | products | table | postgres | permanent | heap | 8192 bytes | public | project_assignments | table | postgres | permanent | heap | 8192 bytes | (4 rows) |
| 1 2 3 4 5 6 7 8 9 | dummy=# \d List of relations Schema | Name | Type | Owner --------+---------------------+-------+---------- public | departments | table | postgres public | employees | table | postgres public | products | table | postgres public | project_assignments | table | postgres (4 rows) |
Taking backup of database in postgres & convert to MySQL schema:
Here I used the pg_dump to take the backup in the SQL format and convert the file to MySQL compatible format using the pg2mysql script. It’s written in perl.
| 1 2 | [postgres@mysql-01 ~]$ pg_dump -U postgres -p 5432 -d dummy -f /var/lib/pgsql/download/dummy.sql [postgres@mysql-01 ~]$ /var/lib/pgsql/pg2mysql-main/./pg2mysql.pl < /var/lib/pgsql/download/dummy.sql > /var/lib/pgsql/download/dummy1.sql 2>warnings.txt |
Restoring database in mysql:
Execute the SQL file in MySQL to restore the database.
| 1 2 | [root@mysql-01 pgsql]# mysql -u root -p < /var/lib/pgsql/download/dummy1.sql Enter password: |
Verification Steps:
Note: In mysql schema is called database.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | mysql> \u public Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------+ | Tables_in_public | +---------------------+ | departments | | employees | | products | | project_assignments | +---------------------+ 4 rows in set (0.00 sec) mysql> desc departments; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | department_id | int | NO | PRI | NULL | | | department_name | varchar(100) | NO | | NULL | | +-----------------+--------------+------+-----+---------+-------+ 2 rows in set (0.07 sec) mysql> desc employees; +---------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------+-------+ | employee_id | int | NO | PRI | NULL | | | first_name | varchar(50) | NO | | NULL | | | last_name | varchar(50) | NO | | NULL | | | email | varchar(100) | NO | UNI | NULL | | | hire_date | date | NO | | NULL | | | salary | decimal(10,2) | YES | | NULL | | | department_id | int | YES | MUL | NULL | | +---------------+---------------+------+-----+---------+-------+ 7 rows in set (0.00 sec) mysql> desc products; +--------------+---------------+------+-----+-------------------+-------------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+-------------------+-------------------+ | product_id | int | NO | PRI | NULL | | | product_name | varchar(100) | NO | | NULL | | | price | decimal(10,2) | YES | | 0.00 | | | created_at | timestamp | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED | +--------------+---------------+------+-----+-------------------+-------------------+ 4 rows in set (0.00 sec) mysql> desc project_assignments; +-----------------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------+------+-----+---------+-------+ | employee_id | int | NO | PRI | NULL | | | project_id | int | NO | PRI | NULL | | | assignment_date | date | NO | | NULL | | +-----------------+------+------+-----+---------+-------+ 3 rows in set (0.00 sec) |
Limitations of pg2mysql
pg2mysql is a useful tool for basic migrations, however it has a few limitations:
- Conversion Scope: Supports just tables. Triggers, views, and stored procedures are not transformed, potentially leading to incomplete migrations.
- Data Type Handling: Some data types are not correctly transformed and may necessitate manual modifications. For example, a character with no defined length changes to LONGTEXT, impacting key definitions.
- Performance: huge datasets can be slow to process, and the tool may struggle with huge files or complicated schema dependencies.
- Manual Adjustments: Significant manual intervention is required to fix SQL syntax discrepancies and data type concerns. It is significantly dependent on pre-processing and post-processing processes.
- This blog is to enlighten on various available tools or options to perform the migration of PostgreSQL to MySQL. The pg2mysql is not a fully matured product and it needs more changes to make the migration seamless.
Credit:Credit goes to:James Grant Lightbox Technolgoies http://www.lightbox.org/ , https://github.com/ChrisLundquist/pg2mysql
