Along with the migration that I have done from SVN to Git and deploying my projects using Capistrano recipes, I was also faced with one task that was not very easy. I had to move my MySQL databases to PostgreSQL.

Why move?

Well, I gave it a thought, and I feel that I am now more comfortable with using Rails with PostgreSQL. Another factor is that I've already used MySQL in my early programming days and seemed fit for small scale projects. Since I have been dealing more and more with bigger databases, I'm getting the hang of using PostgreSQL over MySQL. It didn't really have to be very technical, but the simple preference mattered much.

What to do?

For the impatient:

  1. Export your current database using PhpMyAdmin;. Select the database you want to export and click on the export tab.

  2. Export using SQL format and configure your options as seen below:

  • export options

  • Download the mysql to pgsql script from pgfoundry or from lanyard's github;. It will at least do for you some stuffs that are needed and leave out only some more small stuffs to watch out for/needs fixing.

  • Once you have the script, make it executable by doing this:

    • chmod 777 mysql2pgsql.perl
    1. Now, convert your exported mysql.sql file to pgsql.sql by using the converter, like this:
      2. ./mysql2pgsql.perl yourinputfile.sql youroutputfile.sql

    2. Your new youroutputfile.sql is not supposedly a postgres format. But wait! There's more! Your outputfile.sql is not yet totally ready. The magic is over, its time to make some moves for yourself. Don't be lazy! ;)

    3. Given the code: http://pastie.org/330839

    
DROP TABLE "audits" CASCADE\\g
DROP SEQUENCE "audits_id_seq" CASCADE ;

CREATE SEQUENCE "audits_id_seq" START WITH 63 ;

CREATE TABLE "audits" (
 "id" integer DEFAULT nextval('"audits_id_seq"') NOT NULL,
 "auditable_id" int default NULL, 
 "auditable_type" varchar(255) default NULL, 
 "user_id" int default NULL, 
 "user_type" varchar(255) default NULL, 
 "username" varchar(255) default NULL, 
 "action" varchar(255) default NULL, 
 "changes" text, 
 "version" int default '0', 
 "created_at" timestamp without time zone default NULL, 
 primary key ("id")
 ) ;

--

-- Dumping data for table 'audits'

INSERT INTO "audits (id", "auditable_id", "auditable_type", "user_id", "user_type", "username", "action", "changes", "version", "created_at)" VALUES 
(1, 2, 'Feedback', NULL, NULL, NULL, 'create', '--- \\nname: \\n- \\n- chris\\nmessage: \\n- \\n- testing lang po\\ntopic: \\n- \\n- some topic\\nemail: \\n- \\n- coderkitty@sweetperceptions.com\\n', 1, '2008-01-27 11:34:10');

    remove all lines that look like these:

    
DROP TABLE "audits" CASCADE\\g
DROP SEQUENCE "audits_id_seq" CASCADE ;

    and then make sure that your INSERTs don't have the double quotes, so that they'll look like this:

    INSERT INTO audits (id, auditable_id, auditable_type, user_id, user_type, username, action, changes, version, created_at) VALUES

    That's it for the quick and short version. The long version though would have to be reading more of the documentations of each databases. You'd have to watch out several items like:

    • ENGINE=MyISAM
    • IF NOT EXISTS
    • mediumint to int
    • tinyint to smallint

    If your database is already huge, consider doing it manually. You may miss some data if you don't go through the nitty gritty details of it. But for small databases like the ones I have, this solution is pretty neat.

    You can get some more info here: