Chief Product Officer, Ispirer Systems
If you are planning a migration from MySQL to PostgreSQL or are only thinking about data migration, you have come to the right place.
To handle data migration properly, you need to grasp its complexity and select the right tool for the job. This article explains the details of a specific type of migration. It also provides guidance and tips for a successful migration. In the article we consider utilizing two well-known instruments: pgloader and Ispirer Toolkit.
But before that, some prerequisites. Before starting, it’s important to understand both databases, their differences, and potential migration results. Let’s consider why PostgreSQL is the right choice.
Why PostgreSQL?
If you are considering migrating data from MySQL to another database, PostgreSQL is definitely the right choice. Here are several reasons why you should consider migrating to Postgres:
- ACID Compliance. PostgreSQL is ACID compliant, ensuring reliable and consistent transactions. This is crucial for applications that require data integrity and consistency.
- Extensibility. PostgreSQL allows you to define your own data types, operators, and functions. This flexibility is helpful when switching from MySQL. It lets you customize PostgreSQL to fit your data and business logic.
- Data Types and Indexing: PostgreSQL supports a wide range of data types and indexing options. This can be advantageous when migrating data that involves complex data structures or requires specific indexing strategies.
- Rich Ecosystem. PostgreSQL has a rich ecosystem of tools and extensions for various tasks. But if you need to facilitate the migration process, such solutions as Ispirer Toolkit or pgLoader are great options.
- Community support. PostgreSQL has a supportive community and commercial vendors that offer help and services with extra support options.
Migration using pgloader
Pgloader is an open-source tool that helps users migrate databases from MySQL, SQLite, and SQL Server to PostgreSQL.
Prerequisites:
- Access to servers with a firewall setup and a non-root user with sudo privileges.
- Root MySQL user authenticates using a password and supports encrypted connections.
- Utilize PostgreSQL server as the MySQL client machine.
Now let’s review the steps for migrating data using pgloader:
1. Install Pgloader.
You can install Pgloader from the Ubuntu APT repositories using the apt command. To use the useSSL option in v3.5.1 and later versions, you should install from the GitHub repository using the source code.
a. Update the package index of the Postgres server.
postgres-server$
sudo apt update
b. Enter the following commands and hit Enter to install Pgloader dependencies.
postgres-server$
sudo apt update
c. Go to the release page of the GitHub project, locate the most recent version> Assets> copy the Source code (tar.gz) link, insert it into the curl command, and substitute the https URL.
postgres-server$ curl -fsSLO https://github.com/dimitri/pgloader/releases/download/v3.6.9/pgloader-bundle-3.6.9.tgz
d. Unpack the tarball.
postgres-server$ tar xvf v3.6.9.tar.gz
e. Navigate to the newly created Pgloader root directory.
postgres-server$ cd pgloader-3.6.9/
f. Build the Pgloader binary.
postgres-server$ make pgloader
g. Relocate the binary file to the /usr/local/bin directory.
postgres-server$ sudo mv ./build/bin/pgloader /usr/local/bin/
h. Verify the version of Pgloader is installed.
postgres-server$ pgloader –version
2. Establish a PostgreSQL role and database.
The ident protocol/ident (by default) and password can verify PostgreSQL database users. Although the default method provides enhanced security, it can cause issues for external connections.
Pgloader can import data into a Postgres database if the role is verified using the ident method. The role must also have the same name as the Linux user profile running the pgloader command. This guide will show you how to configure a PostgreSQL role with password verification.
a. Generate a new role. The -P flag asks for a password for the new role.
postgres-server$ sudo -u postgres createuser --interactive -P
b. Next, the script will ask for a new role name.
Input name of role to add:
c. Input and reconfirm the password for the role.
Input password for new role: Re-enter it:
d. Assign the new role as a superuser due to its extensive permissions. Press y and hit ENTER.
Output
. . .
Should the new role be a superuser? (y/n) y
e. Establish a destination database to import the original data.
postgres-server$ sudo -u postgres createdb xxx
3. Set up a MySQL specific user and handle certificates.
a. Access the MySQL prompt.
mysql -u root -p
b. Generate a new MySQL user. Substitute the your_postgres_server_ip with the public PostgreSQL IP address and password with a secure one or passphrase.
mysql> CREATE USER 'new_mysql_name'@'your_postgres_server_ip' IDENTIFIED BY 'password' REQUIRE SSL;
c. Provide the newly formed MySQL user permission to the destination.
GRANT ALL ON source_db.* TO ' new_mysql_name'@'your_postgresql_server_ip';
d. Refresh the grant tables to apply privilege modifications.
mysql> FLUSH PRIVILEGES;
e. Exit the prompt.
mysql> exit
f. Incorporate the ca.pem and client-cert.pem files into Ubuntu's trusted certificate repository to activate SSL for Pgloader. Remember to modify the filenames to include the .crt extension.
postgres-server$ sudo cp ~/client-ssl/ca.pem /usr/local/share/ca-certificates/ca.pem.crt
postgres-server$ sudo cp ~/client-ssl/client-cert.pem /usr/local/share/ca-certificates/client-cert.pem.crt
h. Search for certificates.
postgres-server$ sudo update-ca-certificates
4. Transfer data from MySQL to PostgreSQL.
$ pgloader mysql://mysql_username:password@mysql_server_ip_/source_database_name? useSSL=true postgresql:// postgresql://postgresql_role_name:password@postgresql_server_ip/target_database_name?option_1=value&option_n=value
5. Verify the data migration.
a. Launch the PostgreSQL prompt.
postgres-server$ sudo -i -u postgres psql
b. Establish a connection to the target database that receives the data.
postgres-server$ \c xxx
c. Verify if the transferred data is present in your PostgreSQL database:
xxx=# SELECT * FROM source_db.sample_table;
6. Perform MySQL to PostgreSQL migration on the same system. Execute the command as a Linux user who has root MySQL user access.
$ pgloader mysql://root@localhost/source_dbpgsql://sammy:postgresql_password@localhost/target_db
7. Carry out migration from the CSV file.
$ pgloader load.csv pgsql://sammy:password@localhost/target_db
Migration with Ispirer Toolkit
Ispirer Toolkit is another tool that allows you to easily migrate data from MySQL to PostgreSQL. Why is it worth taking a closer look at this tool? There are a number of reasons for this:
- GUI and Command Line Options. Ispirer Toolkit has a GUI and a command-line mode, giving users flexibility based on their preferences. The GUI caters to those who prefer a visual approach, while the command-line mode allows for scheduled tasks. This flexibility is a notable aspect to consider, depending on the user's comfort level with either interface.
- Extensive Source and Target Support. Ispirer Toolkit supports migration of more than 20 source and target RDMBSs, making it adaptable to diverse database environments. This diversity is an advantage for projects with complex data structures, allowing Ispirer Toolkit to handle various scenarios effectively.
- Dependency Management. The tool system tracks and manages dependencies between objects, ensuring that it loads parent objects first. This feature minimizes the risk of reference-related issues during the migration process, contributing to a smoother transition.
- Mapping Capabilities. Ispirer Toolkit helps switch between MySQL and PostgreSQL databases by mapping data types effectively. Customizing column, table, and schema names allows for flexibility to match various database structures.
- Selective Data Migration. Ispirer Toolkit allows users to migrate selected data from specific tables. This feature is useful for big databases when only some data needs to be moved, making the migration process more efficient.
- Constraints and Indexes. Ispirer Toolkit allows the independent migration of constraints and indexes. This feature enables users to fine-tune the migration process by selecting specific components based on project requirements.
- Character Encoding Capabilities. The tool helps transfer data between MySQL and PostgreSQL, supporting different languages and character sets.
- Customization Option. Recognizing the uniqueness of each migration project, Ispirer Toolkit offers customization options. Customization implies adding necessary conversion rules to Ispirer Toolkit which enhances conversion quality.
- Support of SQL. Ispirer Toolkit provides a cost-effective solution for migrating SQL objects. Flexible pricing for smaller data sets makes it a cost-effective option, especially compared to manual conversion methods. Users can adjust SQL object transfers and tailor the migration process according to specific project requirements.
Conclusion
In summary, data migration is a complex process, and selecting the right tool is crucial for its success. Having explored two prominent solutions, the decision ultimately rests on the unique needs of each project. Consider the specific requirements, volume, and complexity before making a choice between the available solutions.
For those seeking a robust one-size-fits-all solution, Ispirer Toolkit stands ready for exploration. Get a free 30-day demo version on the website to try out its features and make an informed decision. It's an opportunity to evaluate its compatibility with your project's intricacies and make an educated decision tailored to your data migration endeavors.