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

Converting MySQL to PostgreSQL

One thought on “Converting MySQL to PostgreSQL

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s