How to convert MySQL database to PostgreSQL
Aug 12th, 2021,
If you are migrating from one database management system to another it needs some serious work and effort, but with the help of ERBuilder Data Modeler, you will be able to convert your database to several supported DBMS easily and surely.
ERBuilder Data Modeler support conversion from and to the most popular database management system such as SQL Server, Oracle, PostgreSQL, MySQL, MariaDB, SQLite, Firebird, Azure, SQL Database, Amazon Redshift, and Amazon RDS.
In this article, we will walk you through the process of migration of a MySQL database to PostgreSQL. In this tutorial, We will use one the features of ERBuilder Data modeler to convert MySQL database to PostgreSQL.
1. Reverse engineer MySQL database
Firstly, download and install ERBuilder Data Modeler (Download for Free), then run it and follow the steps below to reverse engineer your MySQL database:
Step1
Click the file menu then chose the reverse engineer menu item.
Step2
Create a connection to your MySQL database by Clicking on the new connection button
Step3
Choose a name for the connection, then specify the database management system and its version. In this case, we will select MySQL DBMS, type the server's name and port, enter the username, and specify the password for the user account. The last thing to do is selecting the database you want to convert. Once you are done with all this, validate your entries and test your connection.
Step4
Now that the connection is established, all you need to do is click on the Next button
Step5
All database objects will be loaded. Select the objects you want to include in your database conversion process, then click the Import button.
On the screen below you will see that the database reverse engineering is done and now we can proceed with the next step to change the DBMS.
2. Convert MySQL database to PostgreSQL
ERBuilder Data Modeler is designed in such a way that converting the DBMS to another one is a straightforward process. Now let's see how this can be done
Step1
Click on the Tools button then choose Change DBMS from the drop-down menu.
Step2
Select PostgreSQL from the list of supported target DBMS, when you are done click on the Ok button.
The database management system now is successfully changed from MySQL to PostgreSQL. It's important to note that the "Change DBMS" feature will only convert data types. The views, triggers, and stored procedures implementation code is kept as is.
3. Generate the PostgreSQL database
Now that we have the new model for the PostgreSQL database just a few steps are left and the job will be done.
Before we proceed with generating the database, we need to set up a new PostgreSQL connection. To do that, click on the File menu then chose Manage Connections from menu items.
On the screen below click on the Plus button to add a new connection and from there it will be the same as reverse engineer step 3.
Once we are done setting up the PostgreSQL connection, we will start generating the converted database.
Step1
Click on the tools menu then select Generate database.
Step2
For this step select the PostgreSQL connection previously step up then make sure to select all objects of the database then click on the Generate database button
This step will take some time, depending on the size of the database, and when it’s done you will see the screen below.
The conversion from the MySQL database to PostgreSQL is now done.
In conclusion, we can say that the process of converting a mysql database becomes a simple quick task with erbuilder data modeler. In addition to that, you can also directly generate the target database, populate it with realistic test data or generate database documentation.
How ERBuilder Helps
ERBuilder Data Modeler is a GUI data modeling tool that allows you to visualize, design and model databases by using entity relationship diagrams and automatically generates the most popular SQL databases including Amazon Redshift. Generate and share the data Model documentation with your team. Optimize your data model by using advanced features such as test data generation, schema compare and schema synchronization. Try now ERBuilder for 15 days FREE
Subscribe To Our Newsletter
Subscribe to our email newsletter today to receive updates of the latest news, tutorials and special offers!
