Migrate MySQL to PostgreSQL

[MySQL to PostgreSQL Converter] [How to Control Migration Results] [Types Mapping] [Converting Queries]

Why Migrate to PostgreSQL?

Both MySQL and PostgreSQL are popular open-source RDBMS enriched by wide range of administration and development tools. Both systems are ported on every popular OS and have large communities of experts. However, PostgreSQL provides multiple advantages that may be important for certain projects:

full compliance with ANSI SQL standard
support for multiple indexing models (partial, bitmap, and expression)
synchronous and asynchronous replication are supported
support for Common Table Expressions (CTE)
full outer joins are supported
support for materialized views and table inheritance
unlike MySQL, PostgreSQL works with arrays, hstore and other advanced data types

On the other hand, PostgreSQL is more complex than MySQL and it can be difficult for beginners. So, if you work on simple database project that is covered by MySQL capabilities and do not plan to scale it, there is no much sense to migrate it from MySQL to Postgres.

Migration Strategies

1. Manual Migration. The steps described below are required to migrate database from MySQL to PostgreSQL manually:

All table definitions are extracted from the source database as DDL SQL statements. It can be done like this:
phpMyAdmin - highlight the table, go to 'Export' tab, select 'Custom' option, set format to 'SQL' and make sure that radio-button 'Structure' is selected

MySQL console client - use the statement
```
mysqldump -d -h (host) -u (user) -p(password) (databasename) > (dumpifle)
```
All patterns in round brackets must be replaced by actual values
Translate those DDL statements according to PostgreSQL format and load into the destination database. The main challenge of this step is to properly convert column types from MySQL to Postgres. Read article Types Mapping for detailed information about types conversion.
Data of every MySQL table is exported into an intermediate format such as CSV file. It can be done as follows:
phpMyAdmin - highlight the table, go to 'Export' tab, select 'Custom' option, set format to 'CSV' and make sure that radio-button 'Data' is selected

MySQL console client - use the statement
```
SELECT * INTO OUTFILE ('table.csv')
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' FROM (table)
```
All patterns in round brackets must be replaced by actual values
The data in CSV files has to be transformed according to PostgreSQL format (if it is necessary) and then loaded into the destination database.
Finally views, stored procedures and triggers are extracted from MySQL database in form of SQL statements and source code. In both phpMyAdmin and MySQL console client it can be done using these SQL-statements:
views
```
SELECT table_name, view_definition FROM information_schema.views 
WHERE table_schema='(your database name)'
```
stored procedures
```
SHOW PROCEDURE STATUS WHERE Db = 'your database name'
```
triggers
```
SHOW TRIGGERS
```
The resulting statements and source codes are converted into PostgreSQL format and loaded into the target database. Note that this step requires deep knowledge in MySQL and PostgreSQL dialects of SQL and database development. This article explains how to convert views from MySQL to PostgreSQL.

2. Pgloader. See .... for details.

3. Connect MySQL to PostgreSQL Using Foreign Data Wrapper.

This method is based on the PostgreSQL foreign data wrapper feature and includes the following steps.

Create an Extension. The initial step is to add a new extension to the current database utilizing the CREATE EXTENSION command. It's crucial to ensure that there are no previously loaded extensions with the same name. The CREATE EXTENSION command generates new SQL objects like operators, data types, functions, and index support methods. The user who executes the CREATE EXTENSION command becomes the owner of the newly created extension.
To establish a connection between PostgreSQL and MySQL, you must create the mysql_fdw extension. To achieve this, you need to input mysql_fdw as the extension_name while creating the extension. This allows you to query the MySQL server from the Postgres database:
```
CREATE EXTENSION [IF NOT EXISTS] extension_name [WITH] [SCHEMA schema_name];
```
Create a Server. By executing the CREATE SERVER command, you can create a new foreign server and establish its connections. This command provides the necessary connection information that Foreign Data Wrapper requires to access external data sources.
The user who creates the server becomes its owner, and it's mandatory to ensure that the server's name is unique within the database:
```
CREATE SERVER [ IF NOT EXISTS ] server_name[TYPE server_type]
FOREIGN DATA WRAPPER fdw_name [OPTIONS (option 'value' [, ...] ) ]
```
Create User Mapping. User mapping is created to link a Postgres role with a foreign server, such as a MySQL server. This mapping includes the connection information required by Foreign Data Wrapper to access an external data source, as well as information from the foreign server:
```
CREATE USER MAPPING [ IF NOT EXISTS ] FOR { user_name | USER | 
CURRENT_USER | PUBLIC } SERVER server_name [OPTIONS ( option 'value' [, ...] ) ]
```

Create a Foreign Table. A foreign table serves as a reference to a table located on the MySQL host. As such, you must create a foreign table definition on the Postgres Server, which establishes a connection to a table on the MySQL Server. In this process, the columns in the foreign table map to the columns on the Postgres Server:

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name( 
[ { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] 
  [ COLLATE collation] [ column_constraint[ ... ] ]
    | table_constraint }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

As example, assume we have MySQL table with the following structure:

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

Then we need to execute the following statement to create equivalent table in the PostgreSQL database:

CREATE FOREIGN TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
) SERVER mysql_server
OPTIONS (dbname 'db name', table_name 'Persons');

Import Foreign Schema. This command transfers table definitions from a foreign server, such as the MySQL Server, to PostgreSQL. It generates foreign tables that mirror the tables on the foreign server.
Usually, IMPORT FOREIGN SCHEMA imports all the views and tables present in a specific schema on the foreign server. However, it's possible to specify a list of tables limited to a subset or exclude certain tables.
```
IMPORT FOREIGN SCHEMA remote_schema
    [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
    FROM SERVER server_name
    INTO local_schema
    [ OPTIONS ( option 'value' [, ... ] ) ]
```

4. Commercial Tools. For those who consider the tree previous migration methods too complicated, it is reasonable to use special commercial tools that can fully automate database migration procedure through intuitive graphical interface within just a few button clicks. One of these tools is MySQL to PostgreSQL converter developed by Intelligent Converters. It has all necessary features to handle large and complex database migration projects and does not require users to have deep technical skills to use it.

Have questions? Contact us