Rails on PostgreSQL

My roving Google bots turn up various notes on folks moving from MySQL to PostgreSQL. Here are a few accumulated entries:

• Yangthaman describes the process of converting his app's database from MySQL to PostgreSQL. He notes that 'Postgres does not allow option for “text” data type' - in Rails 3 with the latest pg gem this works fine, though. He cites Heroku's PostgreSQL requirement as a motivator - this line of reasoning turned up a couple of times.


• A detailed post from Mike Castleman recommends ensuring that config.active_record.schema_format is set to :ruby and has a pointer to SQL Fairy as a solution for complex schemas. He notes differences in quoting styles (PostgreSQL uses double quotes) and reminds the reader of ActiveRecord's quote_column_name, e.g., Book.connection.quote_column_name('when') #=> "\"when\"". He notes that PostgreSQL has a native boolean type, so if code was dependent on MySQL's tinyint columns it will need to be cleaned up. He mentions a few other changes they needed to make; definitely worth a read. Finally, they open sourced their data conversion rake task; good stuff!


• minlev converted his database using Michael Siebert's AR-DBCopy. This utility creates ActiveRecord classes on the fly to copy records from one AR connection to another. Mindlev noted that this took 5 minutes for 100K records, so it's not something for anything other than smallish databases... but, it has its place.


• Mike Williamson explores some PostgreSQL interactions using the various command line tools - dropdb, psql, pg_dump, and so forth. He also notes the PostgreSQL native boolean type vs MySQL's tinyint.


• StackOverflow has a long article with lots of resources on moving from MySQL to PostgreSQL. It includes a pointer to the PostgreSQL wiki page on switching from MySQL, which contains a variety of links to articles on the topic. There's also a less detailed article on more or less the same thing on StackOverflow here.


• miles wrote up his notes on converting a MySQL typo blog instance to PostgreSQL. The main hitch is the tinyint to boolean conversion, but he has the complete details of how he handled that.


• Adam Wiggins (of Heroku fame) describes using taps to transfer data from a MySQL database to a PostgreSQL database. This tool results in about the same transfer speeds as AR-DBCopy, so it's not suitable for hefty databases... but, still. The taps git repo has had a bit of activity lately, so looks like that project is still going.


• Harold Giménez wrote up a general overview of PostgreSQL's goodness, noting a variety of features like table partitioning, partial indexes, MVCC, tsearch2, PostGIS, and so on. He notes that replication is getting better soon... and since PostgreSQL 9.0 is out, soon is now here.

In summary, thanks to Heroku for providing a nudge for folks to switch to PostgreSQL!

When you write a Rails app that uses PostgreSQL you'll probably see your development log filled with log entries like this:

 SQL (0.5ms)   SELECT a.attname, format_type(a.atttypid, a.atttypmod), d.adsrc, a.attnotnull
 FROM pg_attribute a LEFT JOIN pg_attrdef d
 ON a.attrelid = d.adrelid AND a.attnum = d.adnum
 WHERE a.attrelid = '"reading_lists"'::regclass
 AND a.attnum > 0 AND NOT a.attisdropped
 ORDER BY a.attnum

This is all boilerplate stuff and definitely not part of your app. So, how to get rid of it? Just use Evgeniy Dolzhenko's silent-postgres gem. Put this in your Rails 3 Gemfile (or add a config.gem entry in your environment.rb file for Rails 2):

group :development, :test do 
  gem 'silent-postgres'
end

And that's it. Cleaner logs, huzzah!

The gem itself is only about 60 lines of code. It creates an initializer that includes itself into ActiveRecord::ConnectionAdapters::PostgreSQLAdapter and uses alias_method_chain to silence output from a couple of methods - tables, pk_and_sequence_for, and a few others. Good stuff!