Converting MySQL to PostgreSQL

It has been a long time that i did not write a post into my blog, well it’s time, working with bacula, i got a need to upgrade my database from MySQL to PostgreSQL. If you have to do so, well you can do it simpley using command line and tools given with these database applications.

First of all dump your tables with

$ mysqldump –compatible=postgresql databasename > outputfile.sql

but even then you will have to change quote escaping:

$ sed “s/\\\’/\’\’/g” outputfile.sql

You also have to manually modify the data types when /int.* unsigned/ “bigint” when /bigint/ “bigint” when “bit(1)” “boolean” when “tinyint(1)” “boolean” when /tinyint/ “tinyint” when /int/ “integer” when /varchar/ “varchar” when /char/ “char” when /(float|decimal)/ “decimal” when /double/ “double precision”

After you convert your tables, import them the same way you were used to in MySQL, that is

$ psql -h server -d databasename -U username -W < data.sql


Author: Ali MEZGANI

My name is MEZGANI Ali. I was born back in 1978 in Rabat Morocco. My interests are Debian Linux , programming , science and music.

One thought on “Converting MySQL to PostgreSQL”

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s