While looking for better scalability, performance, and support for complex data types, companies and developers often decide to switch from MySQL to PostgreSQL. PostgreSQL is the recommended option for high-performance database due to its sophisticated features (such as powerful query planner and optimizer, extensive support for custom data types and functions, better handling of concurrent transactions) and compliance with SQL standards.
This whitepaper covers all the necessary steps to convert MySQL to PostgreSQL and miscellaneous tools, such as pgLoader, that can guarantee a seamless database migration. All those approaches will be ranged by capabilities and ease of use, so every user can choose the ideal option for particular migration project.
pgLoader
The goal of the open-source database migration tool pgLoader is to make the PostgreSQL migration process easier. It facilitates migrations to PostgreSQL from a variety of file formats and RBDMSs, such as MySQL and SQLite. pgLoader uses high performance COPY command to migrate data from the source database or comma-separated values (CSV) file into the target database.
The tool can work on most popular platforms, but installation procedure may differ. More information about installing pgLoader can be found at https://pgloader.readthedocs.io/en/latest/install.html.
In order to convert MySQl to PostgreSQL, pgloader copies the source data either from a file or directly from a database and inserts it directly into a PostgreSQL database. This requires either Linux user with sufficient privileges over Postgres database or PostgreSQL role with enough permissions to create tables and load the data.
The next step of setting up pgloader is configuring SSL for MySQL. The CA certificate or client certificate loaded to database server is not visible to pgLoader since it cannot read configuration files. However, when SSL is required to connect to MySQL, pgLoader mandates the use of trusted certificates rather than disregarding SSL requirements. Adding the ca.pem and client-cert.pem files to Ubuntu’s trusted certificate store /usr/local/share/ca-certificates/ will therefore fix this problem.
Now everything is ready to convert MySQl to PostgreSQL. pgLoader can migrate an entire database through a single command-line statement. This statement includes two connection strings, one for the source databases and the other for the target databases, and other conversion settings. The declaration for both connection strings includes the type of DBMS, username and password (separated by a colon), IP address or network name of the server hosting the database, database name and other options that affect how pgLoader behaves. For example:
pgloader
mysql://mysql_user: mysql_password@mysql_server/mysql_database_name?option_1=value&option_n=value
postgresql://postgres_user: postgres_password@postgres_server/ postgres_database?option_1=value&option_n=value
Foreign Data Wrapper
Foreign data wrapper is an extension that to work with an external data source (connecting, reading and writing). To convert MySQL to PostgreSQL through this approach, you must take the following steps:
- Create a Foreign Data Wrapper extension for MySQL using CREATE EXTENSION statement on the target PostgreSQL server. Extension name must be unique. PostgreSQL user created the extension will become its owner. For example:
- CREATE EXTENSION mysql_fdw;
- Set up connection to MySQL using CREATE SERVER statement. Connection settings such as host, port may be specified via ‘OPTIONS’ keyword. For example:
- CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw [OPTIONS (host ’127.0.0.1’, port ’3306 ’) ;
- Configure mapping associating PostgreSQL role with the server using CREATE USER MAPPING statement. For example:
- CREATE USER MAPPING FOR public SERVER mysql_server OPTIONS ( username ’test’, password ’secret ’) ;
- Define every table in the PostgreSQL database that corresponds to migrated MySQL table using CREATE FOREIGN TABLE statement. Or import multiple remote tables in the local schema using IMPORT FOREIGN SCHEMA statement. For example:
- CREATE FOREIGN TABLE warehouse (warehouse_id INT, warehouse_name TEXT, warehouse_created TIMESTAMP) SERVER mysql_server OPTIONS (dbname ‘db’, table_name ‘warehouse’);
MySQL to PostgreSQL Converter
As the third option to convert MySQL to PostgreSQL we consider commercial software product designed by Intelligent Converters to automate database migration procedure via intuitive wizard-style interface. It has full support for install and uninstall and comes with comprehensive context sensitive help.
The working cycle of the converter includes six steps specified below.
Step 1: select to migrate to PostgreSQL server directly or export into local SQL-file containing statements to create all database entries and copy the data
Step 2: provide connection details for MySQL server
Step 3 (optional, if you choose direct migration): provide connection details for PostgreSQLserver
Step 4: select the source and target databases, specify the log file (optional) to track the program execution
Step 5: customize migration via the following settings
- Specify PostgreSQL schema
- Create tables LOGGED/UNLOGGED. Unlogged tables do not require writing data into write-ahead log that makes them considerably faster but not crash-safe.
- Migrate data via INSERT/COPY statements. INSERT statement loads data row by row while COPY loads all the rows in one command. COPY is faster, while INSERT may give more diagnostic information in case of an issue.
- Do not migrate the data – use this option if you need to convert only table definitions from MySQL to PostgreSQL
- Make names case sensitive – use this option to make table and column names case sensitive. In this case, names must be enclosed in quotes when composing a query.
Step 6: select tables to migrate
After those steps are passed, the user is redirected to progress screen that shows up progress of the database migration. The final screen of the program tells if the migration succeeded or failed.
Conclusion
In this article we consider three options to convert MySQL to PostgreSQL: phLoader, foreign data wrapper and commercial converter. Two of them are free and require some efforts to install and configure. Third solution provides easy to use wizard-style interface that allows to automate database migration with just a few clicks of mouse button.