techbuzzireland.com

Database migration between advanced DBMS such as MySQL and PostgreSQL can be a complicated procedure. However, the benefits of PostgreSQL, such as better support for advanced features, superior performance for certain use cases, and compliance with SQL standards, make it an appealing option for many developers and organizations. Below is a comprehensive guide on why and how to migrate from MySQL to PostgreSQL.

Why Migrate to PostgreSQL?

• • SQL Standards Compliance: PostgreSQL is known for its adherence to SQL standards, making it more predictable and portable. While MySQL has made improvements over the years, it is not as fully compliant with SQL standards as PostgreSQL.
  • Data Integrity: PostgreSQL supports advanced features like full ACID compliance, foreign keys, joins, and subqueries more robustly than MySQL. 
  • Complex Queries: PostgreSQL has support for complex queries, indexing, and powerful optimization techniques that MySQL does not always handle well. 
  • JSON and JSONB: PostgreSQL’s JSONB type provides more efficient storage and querying capabilities for JSON data compared to MySQL’s JSON support. 
  • Concurrency and MVCC: PostgreSQL provides better concurrency control and uses Multi-Version Concurrency Control (MVCC), which ensures better read consistency under heavy load, compared to MySQL’s default InnoDB engine. 

• Extensibility: PostgreSQL supports custom data types, operators, and functions, allowing for much more flexibility and extensibility.

• Optimized for Read and Write Operations: PostgreSQL handles heavy read and write loads more efficiently in certain applications compared to MySQL.

• Better Support for OLAP and OLTP: PostgreSQL shines in handling both Online Analytical Processing (OLAP) and Online Transaction Processing (OLTP) workloads. MySQL generally performs better for simple OLTP workloads, but PostgreSQL outperforms MySQL in analytics-heavy applications.

Challenges of Migration

MySQL and PostgreSQL have different default data types. For example, MySQL TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB must be mapped in PostgreSQL BYTEA. Integer or BIGINT types with AUTO_INCREMENT attribute in MySQL are mapped to SERIAL or BIGSERIAL in PostgreSQL.

SQL syntax in MySQL and PostgreSQL can differ, especially for advanced queries. Queries or functions written for MySQL may need to be rewritten for PostgreSQL. Certain MySQL-specific functions and features (like AUTO_INCREMENT, GROUP_CONCAT, etc.) do not exist in PostgreSQL, requiring adjustments.

MySQL and PostgreSQL use different procedural languages for stored procedures and triggers (MySQL uses SQL/PSM while PostgreSQL uses PL/pgSQL). This means you might need to rewrite complex stored procedures, triggers, or functions.

Migrate from MySQL to PostgreSQL Using pgLoader

pgLoader is an open-source, command-line tool to load data from various sources into a PostgreSQL database. The tool uses COPY command of PostgreSQL to load the source data from database or CSV file into the target database. It automates the process of converting and transferring databases from one format to another, handling schema and data migration.

 

On Ubuntu pgLoader is available in the default repository and can be installed via apt. However, to migrate from MySQL over an SSL connection, we need particular version of pgLoader (3.5.1 and newer). This can only be installed from GitHub repository.

Before proceeding with the installation of pgLoader, we have to install prerequisites:

• sbcl: Common Lisp compiler
• unzip: decompressor for .zip files
• gawk: pattern scanning and processing language
• make: tool to manage package compilation
• libzip-dev: A library for managing zip archives

 

Install these dependencies as follows:

sudo apt install sbcl unzip libsqlite3-dev gawk curl make freetds-dev libzip-dev

Then download and unpack pgLoader itself: 

1. curl -fsSLO https://github.com/dimitri/pgloader/archive/v3.6.9.tar.gz
2. tar xvf v3.6.9.tar.gz

Build the pgloader executable from sources via make pgloader. After building is completed, move the binary file into the standard location of binary files sudo mv ./build/bin/pgloader /usr/local/bin

Once pgLoader is installed, you need to configure access to PostgreSQL and MySQL instances.

Create a Postgres Role and Database

pgLoader extracts data from the source file or database and loads it into a PostgreSQL database. To successfully execute this operation, you must either run pgLoader as a Linux user who has the sufficient privileges for PostgreSQL database or specify a PostgreSQL role with the necessary grants in the load command.

In PostgreSQL, database access is controlled through roles, which can be thought of as either individual database users or groups of users, depending on the configuration. While most relational databases use a CREATE USER SQL command to create a user, PostgreSQL provides a convenient createuser script that acts as a wrapper around this command, allowing you to create users directly from the console.

Note: By default, PostgreSQL uses the ident authentication method, which maps the client’s Linux username to the PostgreSQL database username, rather than requiring a password. While this method offers increased security in many scenarios, it can present challenges when an external program, like pgLoader, needs to connect to a PostgreSQL database.

If you’re using pgLoader, you can migrate data to the PostgreSQL database through the role authenticated through the ident method, as long as the role’s name matches the Linux user profile executing the pgLoader command. However, for clarity and ease of use, this guide recommends setting up a separate PostgreSQL role that authenticates using a password instead of the ident method.

To create this new role, run the following command on your PostgreSQL server:

sudo -u postgres createuser –interactive -P

Confirm that new role should have superuser permissions as it is required for using pgLoader. Then you can create new empty PostgreSQL database as follows:

sudo -u postgres createdb new_db

Create a MySQL User and Manage Certificates

Protecting data from unauthorized access is extremely important during the database migration, since there’s a risk that malicious actors could intercept the data transferring across the network if the connection isn’t encrypted. To prevent this, we will create special MySQL user that pgLoader will use to perform the migrate securely over an SSL-encrypted channel.

Run MySQL command line client: mysql -u root -p and create a new MySQL user as follows:

CREATE USER ‘pgloader’@‘postgres_server_ip’ IDENTIFIED BY ‘password’ REQUIRE SSL;

Of course, ‘postgres_server_ip’ must be replaced by actual IP address of the PostgreSQL server. REQUIRE SSL clause at the end of the statement restricts the user ‘pgloader’ to access the database through SSL connection only. 

Now we have to grant user ‘pgloader’ access to the target database ‘mydb’ in this example:

GRANT ALL ON mydb.* TO ‘pgloader’@‘postgresql_server_ip’;

Execute FLUSH PRIVILEGES statement to renew the grant tables and exit from the MySQL prompt. 

Then attempt to connect to MySQL as new user ‘pgloader’ from PostgreSQL server:

mysql -u pgloader -p -h mysql_server_ip

If you see the MySQL prompt, the command succeeded. Now we have a special MySQL user who is able to connect the source database from PostgreSQL machine. Afterall pgloader will fail to migrate using SSL since it cannot read MySQL config files and does not know where to look for necessary certificates

Instead of bypassing SSL requirements, pgLoader enforces the use of trusted certificates when SSL is required to connect to MySQL. To address this, you need to add the ca.pem and client-cert.pem files to Ubuntu trusted certificate store by copying the ca.pem and client-cert.pem files into the /usr/local/share/ca-certificates. Be sure to rename the files with a .crt extension, as this is necessary for your system to recognize the new certificates. 

Now everything is ready to migrate from MySQL to PostgreSQL.

Migrating the Database

pgLoader enables users to migrate MySQL database to a PostgreSQL server using this command: 

pgloader mysql://mysql_username:password@mysql_server_ip_/source_database_name?option_1=value&option_n=value postgresql://postgresql_role_name:password@postgresql_server_ip/target_database_name?option_1=value&option_n=value

This command line includes 2 connection strings – for MySQL and PostgreSQL databases. Each connection string starts by DBMS type followed by the username and password, the host address of the database server, the database name and miscellaneous options that configure migration. MySQL connection string must include option useSSL=true for secured connection to the database. 

If this command succeeded, you will see an output table indicating the migration progress.

Migrate Using Foreign Data Wrapper

Migrating from MySQL to PostgreSQL using Foreign Data Wrappers (FDW) allows you to access MySQL data directly within PostgreSQL without fully importing it. This method is useful for hybrid systems where you want to gradually transition or integrate MySQL data into PostgreSQL without moving everything at once.

1. Install the PostgreSQL MySQL FDW Extension

First, ensure that the mysql_fdw extension is installed on your PostgreSQL server. This extension allows PostgreSQL to interact with MySQL databases via Foreign Data Wrappers. Once the FDW extension is installed, you need to enable it in PostgreSQL:

CREATE EXTENSION mysql_fdw;

2. Create a Foreign Server for the MySQL Database

Now you need to define the MySQL database as a foreign server in PostgreSQL. CREATE SERVER statement provides connection information for a Foreign Data Wrapper to access external data source:

1. CREATE SERVER mysql_server
2.     FOREIGN DATA WRAPPER mysql_fdw
3.     OPTIONS (host ‘mysql_host’, port ‘3306’, dbname ‘mysql_db’);

Replace mysql_host with the address of your MySQL server, mysql_db – with the name of your MySQL database. You can also specify the port if it’s different from the default 3306.

 

3. Create a User Mapping for MySQL

Create a user mapping in PostgreSQL to allow it to authenticate with the MySQL database. It includes the connection details required by the Foreign Data Wrapper, along with the information from the foreign server to access an external data source:

1. CREATE USER MAPPING FOR postgres
2. SERVER mysql_server
3. OPTIONS (username ‘mysql_user’, password ‘mysql_password’);

Replace mysql_user and mysql_password with the appropriate MySQL credentials.

 

4. Create Foreign Tables

Once the foreign server and user mapping are set up, you can create foreign tables in PostgreSQL that map to the MySQL tables:

1. CREATE FOREIGN TABLE my_table (
2.     id integer,
3.     name text,
4.     — other columns as in the MySQL table
5. )
6. SERVER mysql_server
7. OPTIONS (tablename ‘mysql_table’);

Replace mysql_table with the actual table name in MySQL.

5. Migrate Data

To migrate data from MySQL to PostgreSQL, you can copy the data from the foreign table to a native PostgreSQL table. Create the PostgreSQL table:

1. CREATE TABLE pg_table (
2.     id integer,
3.     name text,
4.     — other columns
5. );

Insert Data from Foreign Table:

INSERT INTO pg_table SELECT * FROM my_table;

This will copy the data from MySQL (through the foreign data wrapper) into the local PostgreSQL table. Repeat the process of creating foreign tables and migrating data for all the relevant tables you need to migrate.

Once all the data has been successfully transferred and you’re confident that PostgreSQL is ready to take over, you can stop using the FDW and migrate all remaining data directly into PostgreSQL. You may choose to drop the foreign tables and foreign server when done.

Migrate Using Intelligent Converters Software

As you may see two previous methods require plenty of manual effort for installing and configuring tools. For those who look for more automated solutions, it is suggested to consider dedicated commercial converters. 

One of these tools is MySQL-to-PostgreSQL developed by Intelligent Converters. This converter works with all modern versions of MySQL and PostgreSQL including such forks as MariaDB, Percona and DBaaS platforms such as Azure for MySQL, Heroku, Amazon RDS, ClearDB, Google Cloud.

Other features:

• schemas, tables, data, indexes, constraints and views are migrated
• option to merge or synchronize PostgreSQL database with MySQL data
• option to filter data via SELECT-queries
• target tables can be fully customized (modify name, type, default values for every column, exclude columns from migration)
• conversion settings are serialized into profile
• command line support

Conclusion

Database migration from MySQL to PostgreSQL can be a straightforward process with the right tools and careful planning. It’s extremely important to take care on differences in data types, indexing, and SQL dialects between the two databases. Tools like MySQL-to-PostgreSQL by Intelligent Converters streamline the migration of both schema and data, reducing manual effort. Thorough testing post-migration is crucial to ensure data integrity, application compatibility, and performance. By following the outlined steps and leveraging the appropriate migration tools, you can successfully transition from MySQL to PostgreSQL, taking advantage of PostgreSQL’s advanced features and reliability for your applications.