Relational database management systems (RDBMS) have widespread use across industries including e-commerce, website development, financial services, and geographical designs. MySQL and PostgreSQL are two popular open-source RDBMS options and are ranked among the top 5 databases worldwide.
Depending on the business’s requirements, if they have complex, longer-running queries, require advanced query features like joins, analytics, window, or need ACID-consistent data, they may prefer PostgreSQL over MySQL.
MySQL is typically used for small web databases in organizations that have carefully tuned and reviewed relatively simple application-generated queries, especially those that can use PRIMARY KEYs, resulting in faster query execution.
On the other hand, PostgreSQL is suitable for analytics, data mining environments, and data warehouses since the database has better querying, query optimizing, and query answering abilities than MySQL.
This whitepaper introduces three conversion methods for migrating from MySQL to PostgreSQL. The first method is using Pgloader, a free database migration tool that enables continuous migration from MySQL, SQLite, and MS SQL Server to PostgreSQL.
To use this tool, you need access to servers that have a firewall and a non-root user with sudo rights. You also need to authenticate the root MySQL user with a password and accept encrypted connections, and use the PostgreSQL server as the client machine of MySQL.
To use Pgloader, you need to install it first. You can install it from the Ubuntu APT repositories using the apt command, but for versions 3.5.1 and later, you must install it from the GitHub repository using the source code.
Once installed, you must create a PostgreSQL role and database, then create a MySQL dedicated user and manage certificates. Afterward, you can migrate data from MySQL to PostgreSQL using the pgloader command. Finally, you can check the migration by connecting to PostgreSQL’s prompt.
Second option of data from MySQL to PostgreSQL can be achieved using Py-mysql2pgsql tool. Py-mysql2pgsql is a Python 2.7-based tool that can write a PostgreSQL-compliant dump file or stream data into a PostgreSQL server from MySQL 5.x. The tool, however, does not support the importation of spatial data from MySQL. Before proceeding with the conversion, ensure that the following dependencies are installed: MySQL-python, psycopg2, PyYAML, termcolor (except on Windows), and pytz.
To install Py-mysql2pgsql, use the following command on Windows after installing the driver dependencies:
pip install py-mysql2pgsql
Alternatively, you can clone the source code and run:
git clone git://github.com/philipsoutham/py-mysql2pgsql.git
cd py-mysql2pgsql
python setup.py install
After installation, verify the installation using the command:
py-mysql2pgsql -h
Edit the migration configuration file, mysql2pgsql.yml, to specify the migration configuration options, such as the MySQL hostname, port, socket, username, password, and database, as well as the PostgreSQL hostname, port, username, password, and database. The file also has options to choose tables to transform, suppress data or schema definition, force table truncation, and specify index prefix.
Finally, migrate the data from MySQL to PostgreSQL using the following command, specifying the location of the migration configuration file:
py-mysql2pgsql -v -f mysql2pgsql.yml
Both of those partially automated options demonstrate that migrating a database from MySQL to PostgreSQL can be a complex task. Attempting to do it manually may result in data loss or corruption due to human error. Therefore, it is recommended to use suitable software tools to automate the conversion process. MySQL to PostgreSQL migration tool by Intelligent Converters is an example of such a software product that has all the required features to make large migration projects simple and error-free. The tool supports the migration of tables, data, indexes, and constraints from MySQL to PostgreSQL. It is compatible with all versions of PostgreSQL from v9.0 onwards, as well as MySQL. Furthermore, it has been verified to work seamlessly with Heroku, Azure, MariaDB, and Percona.
The tool also supports SSL connection for PostgreSQL, enabling secure data transfer during the migration process. Users have the flexibility to customize field mapping, which allows them to modify the name, type, and default values for each column.
In addition, the tool provides an option to merge or synchronize PostgreSQL database with MySQL data, export into SQL script file, and filter data using SELECT-queries. It can also convert MySQL views into PostgreSQL format.
Users can leverage the command line support offered by the tool for automation of the conversion process. The conversion settings can be saved into a profile for future use. All these features combined make MySQL to PostgreSQL converter a reliable and efficient tool for large and complex migration projects.