Converting PostgreSQL Database to Mysql Database

I am writing this tutorial because I couldn’t find out how to do this successfully on the internet by doing a Google search and had to figure it out on my own. Sometimes, when doing web development, we have to do that regardless of how much time and how many tries it takes. In the end here is the solution I found to convert my PostgreSQL database into a MySQL database. I am not saying this is the best or only solution in the world to do this, but it is the only one that worked for me. There was online converters that did not work in my case and there are complicated database management utilities you can download and user from the desktop, but they are nearly impossible to use if you don’t have a doctorate in Computer science and about 20 years of experience behind your belt. Anyway, Here is how I did it:

 

  1. I used phppgadmin to export the tables one at a time in CSV format. To do so, simply open your phppgadmin, go to the table you want, click on “Export” then select “data only” and for “Format”, select “CSV” from the drop-down. Then tick the download option near the bottom and click the “Export” button to download the CSV file to your local machine.
  2. Now open phpmyadmin in your browser and select a database to add the table to or create a new database and select it.
  3. with the database selected, click on the “Import” tab.
  4. Click “Browse” and select the dump file created in step one. If the dump file is larger than 2,000 KiB see the section below for special instructions for larger files.
  5. For “Format of imported file” select “CSV”.
  6. For “Fields terminated by”, enter a comma.
  7. Place a check in the box that says “column names in first row”.
  8. Leave the rest of the options set to default and click the “Go” button.
  9. You will see it created a new table and named it something like Table1. Change the table name according to your needs and you are done. You may need to view the structure and make some changes, but it will work as-is for most situations.

Importing Large files bigger than 2,000 KiB

To import larger files, you have to break up the dump CSV file into sections. Here is how:

  1. Start from the bottom and copy the last 500-3000 lines to the clipboard, depending on how much data is in them. Try 2000 and if it is still over 2,000 KiB, do the math and try again.
  2. Paste the data into a new CSV file.
  3. From the original dump file, go to the first line and copy it and paste it to the first line of the new CSV file you have created.
  4. Save the file as somename.csv.
  5. Follow the above instructions to import the file into a table and repeat as many times as needed.

 

Leave a Reply

Your email address will not be published. Required fields are marked *