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
