Migrating From MySQL To PostgreSQL in Five Simple Steps

There are many reasons you may want to migrate from MySQL to PostgreSQL, which we will skip over for brevity.  I see many questions about moving tables and data, and sadly, the answers range from sloppy to incredibly complicated.  Each database migration is unique, as issues range from heavily relying on a vendor's feature to extremely complex schemas that require attention to minute detail.

Step 1

This may seem obvious, but the first step is access to the original 'source' database. You will need FULL admin access to the database.  If you do not have full access for security, administrative, or other reasons, you will most likely not receive all the desired data. 

We will use DBeaver Enterprise to connect to the source database.

We need to choose a MySQL database driver











Configure the connection














We will be porting the World database.

















In this example, we will port the MySQL World database. This dataset has been used for decades in MySQL documentation, training, and examples. You probably installed it when you installed MySQL, and it is a good example for you to experiment with on your own.

Step 2

You need to connect to the 'target' database. Again, you will need those administrative access rights.

Step 3

You need to highlight the tables you want and use Export Data

Pick the desired tables.











Pick the target transfer type, in this case, a database.









We are using a PG 17 instance.













There are options for the extraction and loading. Stick to the defaults until you become familiar with them.

There are options for the extraction; use the defaults.














And options for the loading; again, use the defaults.













Now, you can back out at this point. But we want to convert, so proceed.

The moment of truth!













The tables and their data are now in a PostgreSQL instance.

And now we have the World data in PostgreSQL.













Step 4

We can now use DBeaver to compare the data. 





















Did we get all the data?
















You can compare differences side by side.
But in this case, there were no differences. And that is what we wanted!



Step 5  

Now you get to decide on indexes.  PostgreSQL is much richer in index choices, and you can explore them at your leisure. 

Remember, the hard work has been done for you. And you can use the MySQL index creation code if you want to stick with B+ Tree indexes.

Bonus

For those who feel that five steps are too many, there is a 'migrate/compare' option that we can cover later, which reduces the steps.  This blog entry was created to demonstrate clear, orthogonal steps, so I did not use this option, hoping that extra clarity would be worth the extra step.

Conclusion

DBeaver is a fantastic database tool. It is handy when you have to move between versions or vendors. You can use it to change data types on the fly. The ability to move and compare data afterward gives you confidence. BTW, you can get a free two-week trial on any version by simply asking.

If you are moving from the Dolphin to the Elephant, please follow the above steps. 







Comments

Post a Comment

Popular posts from this blog

Does Artificial Intelligence Query MySQL Better Than You?

Image
 The signal-to-noise ratio in the hype for Artificial Intelligence is incredibly high. There are many claims that AI will replace software developers. Many years ago, developers were told to find other jobs as a new programming language was designed to be written by nonprogrammers, replacing expensive coders. The Common Business Orientated Language, or COBOL, was a big hit and is still very popular in some sectors. But it did not replace programmers. But can an AI write Structured Query Language (SQL) as well for use with MySQL, or better than you?  I decided to use the MySQL World and Sakila databases because they have been around for decades. Nearly everyone who has had tangential exposure to MySQL has used them, and their tables are easy to understand. These tried and true schemas have been used for documentation, blogs, webinars, and demos for years, so I had little doubt that some of that had been scraped and fed into the AI. I randomly picked Grok as the AI and will try ...
Read more

Database Administration Query Repository

 I have started a repository of handy SQL Queries for Database Administration, which I have collected over the years. Finding unused or duplicate indexes or queries without indexes and other information can speed up response times. Knowing statistics like cache hit rates can aid in planning upgrades.  I am currently populating this repo as I run across the queries as part of my 'spring cleaning. ' I welcome your participation if you have a handy bit of SQL for an open-source database you'd like to share. The repo is at https://github.com/davestokes/HandySQL , and I will be adding to it over the next few months. If you have a question that you would like to see addressed, please don't hesitate to let me know. I will try to accommodate you. 
Read more

Tracking MySQL Query Plans Over Time Part One

 Did you ever need to determine the performance of a query over time? One of the problems with database query optimizations is that the underlying data is constantly churning. Add in an increase in the number of users, expanding server demand use, and other items that impact your query. What was optimized is no longer performing as expected. But how do you tell if there is a change in the query plan or the query performance? Well, the first step is gathering the needed information. MySQL added a JSON format option to the output of the EXPLAIN command quite a while ago. More recently, Oracle added the feature of saving the output into a variable (see https://dev.mysql.com/doc/refman/9.0/en/mysql-nutshell.html and https://dev.mysql.com/blog-archive/explain-into-and-explain-for-schema-in-mysql-81-and-82 ). This provides a way to access the data in that variable and plop it into a database. The saved result can be compared to current or other past responses for analysis.  mysql...
Read more