Porting to Postgres on Rails

At inEvo, we recently changed a lot of Rails applications to run with Postgres. It sure is a high quality database and has some useful features that mysql just doesn’t has, like Views (mysql 5 has it, but Postgres as a better support for it and it’s free!).

Porting MySql to Postgres

First, we had a lot of work porting the application to Postgres on the server. You cannot simply create an SQL export from your PhpMyAdmin and import it to Postgres. There are several post about how-to do this but you always hit a lot of walls like string encoding, special chars, relations, foreign keys, primary keys, etc..

What saved us was this little wonder plug-in that exports and imports data from/to your app named Yaml DB. The only “hard” part is to create the schema, but rails already does that, so, just create the Postgres database, configure the database.yml and import your schema with Rake (rake db_schema_import).

Now, put back the old Mysql configuration anduse the yaml_db pluging to export the data (rake db:data:dump). Your data is now all at db/data.yml!

Change the DB config again to Postgres and import the data using the same plugin (rake db:data:load).

You’re good to start your application!

Changing Application queries

You shouldn’t get much trouble here. There are some differences between the SQL implementation of Postgres and Mysql, but remember that SQL is a standard and rails does almost all the magic. So, everything should work well.

Hmm.. there are some things to take care. Firstly, the place you have to be more careful is on the :conditions=>”" and :joins=>”" elements as these will pass right to your sql query. One of the only things you have to change though is all your “like” to “ilike” because “like” in Postgres is case sensitive and that can bring you some trouble.

The other thing you have to pay attention is your rder option on associations. On Mysql, the default order it by Id number, but on Postgres is by internal Id number.. is that any different ?? sure!.. the internal Id number changes every time you update a row, so if you update an object, it will be first in the association array, instead of showing up as they were created like on Mysql. So, just set your orders properly and you are good to go.

Well, that’s almost it. You shouldn’t have any trouble for most of your app unless you have really Mysql specifics.

Setting you development application on Windows

Ok, so now we have our application working perfectly on our linux server on Postgres.. That’s great and it seems to behave pretty well. You don’t have phpMyAdmin to play with.. but you have great replacements! So you won’t miss it.. almost..

Now, you need to continue to develop the application and you are set with a Windows laptop. Well, no problem.. or almost!

First, do the checkout, and install Postgres (8.3, maybe). Create a dump using Postgres tools from your server (so you have the latest application data) and import it to your local DB using PSQL to restore it – we are going to use this technique only to learn something more -

Basicly you can use the server admin tool to dump the database or use pg_dump dbname > outfile. Then, on your local command line use the psql < outfile to restore it (more info on the above link).

You now have your data on the DB and are ready to go. As you run mongrel (or something else.. ) and try to access your app, you notice that some dlls are missing!.. This is not a problem, is just your current path that doesn’t have all the need Dll’s and Postgres windows installation didn’t take care of that. So, the easier (not prettiest) step is to copy all DLL’s from Postgres bin dir to you Ruby dir!

Finally, everything is working .. but something strange appear when you are navigating around your site:

PSQLException:: ERROR: operator does not exist: character varying  = integer

This problem occurs because Postgres (version 8.3 at least) cannot compare strings (varchar) with numbers (integer), so, operations like >, <, <>, = between strings and numbers on query just won’t work!…

But.. why is my application doing this!? Well, somewhere along the way some foreign key on your table changed to var char (character varying). For me, it happened mostly on enumeration relations like, for instance if you have a class Employs and a class Employ_Status (to see if he is on vacations or working or sick or etc..). my employ_status_id field on Employs table is var char and not integer as the id field on Employs_Status.

But, don’t worry, this isn’t a problem! Just alter the column and cast it to Integer.

Doing this on Postgres is tricky .. as a simple alter table column won’t work as it can’t cast strings to integer.
First drop the default, that do proper alter and then set you wished default.. like this:

ALTER TABLE employs ALTER COLUMN employ_status_id DROP DEFAULT;
ALTER TABLE employs ALTER employ_status_id TYPE integer USING employ_status_id::integer;

You maybe have to change all empty rows with “” on employ_status_id to null (or something else if the field is not null) before you go.

Ok, there you have it, the steps to port your server and local development application from MySql to Postgres.

Hope it helps.