PostgreSQL and MySQL are two popular open-source relational database management systems (RDBMS) that offer unique features and strengths. While both databases are widely used, PostgreSQL has distinct advantages over MySQL for corporate scale data warehouses. These benefits include high availability and fault tolerance in distributed environments, faster query execution, better performance in large databases, and better scalability.
However, migrating from MySQL to PostgreSQL can be challenging, and organizations need to use specialized automation tools to mitigate the risk of data loss or corruption due to human error. There are free and commercial tools available for MySQL to Postgres database migration, but the free tools often require a lot of prerequisites and preparation steps as it is specified below.
For example, using the Pgloader tool requires users to install and compile from sources, create target PostgreSQL databases and roles, create a dedicated MySQL user, and manage certificates if necessary. Similarly, using Py-mysql2pgsql requires users to prepare the environment, install Python and various packages, create a YAML configuration file, and run the migration process.
Commercial tools like MySQL-to-PostgreSQL, developed by Intelligent Converters, are a better option for users who are not comfortable with the command line or lack expertise in system administration tasks. MySQL-to-PostgreSQL implements direct reading and writing of data without using middleware libraries or components, making it a popular choice for migrating data from MySQL to PostgreSQL.
MySQL-to-PostgreSQL is compatible with all versions of MySQL and PostgreSQL, including forks such as MariaDB and Percona. It also works with popular PaaS variations of DBMS such as Azure for MySQL, Amazon RDS, Google cloud, and Heroku. The product offers a user-friendly graphic interface, command-line support for scripting and scheduling, and several useful options for migrating data.
For example, MySQL-to-PostgreSQL allows users to merge or sync existing PostgreSQL databases with MySQL data, filter particular records for migration using ‘Select’ queries, and rename, change the type and attributes, or exclude some columns from migration. If the PostgreSQL server does not allow remote connections, MySQL-to-PostgreSQL also provides an option to export the MySQL database into an SQL script, which can be imported to PostgreSQL using standard tools like pgAdmin or psql.
When you launch the MySQL to PostgreSQL converter in GUI wizard-style mode, it will lead you through a series of steps to configure the database migration as it is specified below.
Step 1. Select Migration Scenario. On this step you need to choose: either to migrate to PostgreSQL server directly or to export into PostgreSQL script file. The second option may be suitable for those cases where the target PostgreSQL server does not allow remote connections.
Step 2. Connect to MySQL Server. On this step user must provide all necessary details to connect to the source MySQL database server. The tool can connect to local (launched on the same machine where you run MySQL-to-PostgreSQL from) or remote MySQL server. If you choose ‘Remote’, if is necessary to specify MySQL host that can be either network name or IP address of the server machine. Entering port number is required only if it differs from default 3306.
Step 3. Connect to PostgreSQL Server. If you select ‘Migrate to PostgreSQL server directly’ scenario on the 1st step, then you need to specify the necessary settings to establish connection with the target server.MySQL-to-PostgreSQL can connect to local or remote PostgreSQL servers. For remote server specify its IP address or network name in the “Host” field.It is necessary to enter PostgreSQL port only if it differs from the default value 5432. Otherwise, you may leave this field empty. By default, PostgreSQL server connects to database with the same name as the username specified. If there is no such database or PostgreSQL server does not allow access to it, you can specify custom database on this wizard page.
Step 4. Select the Databases. On this step enter source MySQL and destination PostgreSQL database names. You can enter MySQL database name directly or drop down the combo box to see all available databases. If you specify the name of existing PostgreSQL database, you will be able to select how to process it – overwrite, merge or synchronize. Also, you can specify a log file to make the tool writing execution traces that may help to analyze possible errors and get more control over the database migration. Finally, point the appropriate PostgreSQL encoding to storethe resulting data correctly.
Step 5. Specify Conversion Settings. On this step you can configure the database migration using the following settings:
- Convert table definitions only – select this checkbox to migrate only MySQL table structures only, no data will be migrated.
- Create tables – select how to create PostgreSQL tables: LOGGED or UNLOGGED. Unlogged tables do not require writing data into write-ahead log that makes them considerably faster but not crash-safe.
- Insert data using – this option allows to select PostgreSQL statement for writing data: INSERT or COPY. INSERT statement loads data row by row while COPY loads all the rows in one command.
- Make names case sensitive. Note that case sensitive names must be enclosed in quotes when composing query.
Also, you can specify the target PostgreSQL schema – either existing or new one.
Step 6. Select Tables to Convert.On this step you can manage MySQL tables to migrate. To add new MySQL table for migrating to PostgreSQL server you should select it in “Available tables” list box and click “Add” button (or double-click the selected item). Click “Add all” to select all available MySQL tables. To remove some tables from “Selected tables” either highlightthe corresponding items and click “Remove” orclick “Remove all” to emptythe entire list box. Click “Add Query” to add SELECT-query filtering data that will be migrated to PostgreSQL server. To modifythe target table or query, double click the corresponding item in “Selected tables” list box.
Step 7. Select Views. On this step you can manage MySQL views to migrate to PostgreSQL. To add new MySQL view for converting into PostgreSQL you should select it in “Available queries” list box and click “Add” button (or double-click the selected item). Also you can use “Add all” button to add all available PostgreSQL views. To remove some views from “Selected views” list,either highlightthe corresponding items and click “Remove” button or click “Remove all” empty the entire list box.
Some views may depend on another views that must be created first. Use the up and down buttons to control the order in which views are migrated. Highlight an item in “Selected views” list and then you will be able to move it up and down using those buttons.
After all these steps are passed, you should see the progress indication screen and the database migration will be completed soon.
Any specialist responsible for database migration should remember about the important steps required for any approach has been chosen:
- Organizations need to back up their databases before database migration as it might impact the data integrity.
- Migration results must be validated through performance and functional tests before releasing in production.
- Even after the database migration is completed, recurrent backup procedures are also reasonable to prepare for possible data loss disasters.
In conclusion, PostgreSQL offers significant benefits for corporate scale data warehouses compared to MySQL. However, organizations need to carefully plan and execute the migration process using specialized tools like MySQL-to-PostgreSQL to ensure that data is migrated safely and accurately. By doing so, organizations can leverage the advantages offered by PostgreSQL for high availability, fault tolerance, faster query execution, and better scalability.