A little while ago I decided to switch from MySQL to Postgres for my development database. Although I love how command line friendly MySQL is and think group_concat is the bomb, I’ve been burned my MySQL’s loosey-goosey nature and had it corrupt some data on me. I decided that it was time to switch to Postgres. The transition was a little aggravating at times but it doesn’t take long to get used to it. Installing Postgres is easy enough on Ubuntu:

mike@sleepycat:~$ sudo aptitude install postgresql postgresql-server-dev-8.4

If you need to start at new Rails project you can specify Postgres right from square one:

mike@sleepycat:~/test$ rails myapp -d postgresql

After installing the database I started to look for the gems that provide the driver. I found myself sorting through a bewildering number of gems. “Postgres”, “postgres-pr”, a few minutes search turned up several options each of which proved to be the wrong thing. The gem you need is actually called ruby-pg and is written by a group of people that includes the actual creator of the Ruby language, Yukihiro Matsumoto. Although it is called ruby-pg if you ever want to find it on the internet, you install it using the name pg:

sudo gem install pg

The next stumbling block for me was my assumption that the naming of the Rails adapter would be similar to MySQL. Since adapter: mysql is what I have been using in my project already, I assumed adapter: postgres would be fine. It’s not.

development:
adapter: postgresql
database: myapp_development
username: postgres
password: password
host: localhost

Coming from MySQL, it took a fair bit of reading to collect the Postgres equivalent of all the commands I use most often. Just to save you some looking here is my “Cole’s Notes” version:

Setting a password for the default user ‘postgres’:

mike@sleepycat:~$ sudo -u postgres psql postgres
[sudo] password for mike:
psql (8.4.2)
Type “help” for help.
postgres=# \password postgres
Enter new password:
Enter it again:

Create a database as user ‘postgres’:

mike@sleepycat:~$ sudo -u postgres createdb myapp_development

Drop a database as user ‘postgres’:

mike@sleepycat:~$ sudo -u postgres dropdb myapp_development

Get to the command line of the database myapp_development as user ‘postgres’:

mike@sleepycat:~$ sudo -u postgres psql myapp_development

Load the file ‘pgdump.sql’ into the myapp_development database as the user ‘postgres’:

mike@sleepycat:~/$ sudo -u postgres psql -f pgdump.sql myapp_development

Dump a Postgres database to a file:

mike@sleepycat:~/$ sudo -u postgres pg_dump myapp_development > ~/myapp_dev_dump.sql

Show tables:

postgres=# \dt

show a table:

postgres=# \d tablename

quit:

postgres=# \q

Thats what worked for me. In general I feel like using postgres is forcing me to write more portable code. Pretty much anything I write in Rails that runs in postgres will run on MySQL, but the reverse is really not true. Little things like using ’1′ for true. MySQL is fine with that but for Postgres with a real boolean datatype, true is true. 1 is not. I stumbled arcoss that when I was using validates_acceptance_of. I feel like its also forcing me to write better SQL as well. If you’ve written a bunch of “find_by_sql” in an existing app you might not appreciate its pickyness though. However once the initial pain of the switch passes, Postgres is pretty great.