Xblog

I know, the benchmarks are undoubtedly flawed in several ways, and this is just one arbitrary kind of benchmark, with MySQL performing better in other contexts. I don’t care. I’m partisan on this one, and anything that shows that PostgreSQL is better has to be divine truth, and points like the above will be reserved for cases where MySQL benchmarks better than PostgreSQL. ;-)

It’s a trivial bit of code:

require 'postgres'

sequences = [
  'blacklist_patterns', 'blogs', 'categories',
  'contents', 'page_caches', 'pings',
  'redirects', 'resources', 'sessions',
  'sidebars', 'tags', 'text_filters',
  'triggers', 'users'
]

def fixSequence(db,tableName)
  results = db.query("select max(id) from #{tableName}")
  max_id = results.first.first
  if max_id then 
    db.exec("select setval('#{tableName}_id_seq'::text,#{max_id});").clear
  end
end

db = PGconn.connect("localhost", 5432)
sequences.each {|sequence| fixSequence(db,sequence)}
db.close()

So far, my first impression is that Ruby tries to be like Smalltalk, but is Perlish enough to fall short IMHO. Of course, I hardly know the language yet, so there may be a more elegant way to do things that I have yet to uncover. In particular, I’m wondering why the True and False classes don’t have “ifTrue:ifFalse” type methods that take blocks as arugments. Seems like an obvious “nice to have”. IIRC it’s possible to add methods to existing classes, so maybe I can do this to keep the Smalltalk cravings to a minimum.

So, the first thing I had to do was figure out how to migrate the database over. There is probably a poorly documented way to do this through Typo or Rails, but since I wasn’t familiar with either, I just did it the ol’ database developer way. Fortunately, sqlite has a “.dump” command that seems to dump out the entire database as a series of SQL commands. I figured that the commands for creating a schema were not exactly what one should do for postgres, so I stripped them from the dump.

Next, I created a database and user in postgres for the blog, and updated the Typo database.yml to point to postgres. I then ran the schema script for postgres. All was looking good… then I ran the dump script to pull the SQLite data in to postgres. It turns out that the schema script sets up more than the table schemas: it also injects some data. This meant I had some duplicate records getting injected which violated various primary key constraints in the DB. Fortunately, everything got rolled back and I was easily able to remove the offending records from the database. With that the migration of the data was complete.

That’s when the real fun started. I restarted lighttpd, and everything came up until Rails had a cache miss and had to render a page. I got errors like this in my log: “MissingSourceFile (no such file to load – postgres):”. It became abundantly clear that the typo gem that had insisted that SQLite be installed had totally ignored Postgresql. :-(

My first instinct was to install the Postgresql driver using emerge, so I did that. This appears to have been a bad idea. This installed the driver as “ruby-postgres”, and as you can see from the log message, Typo was looking for it in “postgres”. So I switched to installing through gems. That seemed to put things in the right place, but then I did something stupid: I unmerged the gentoo install. Little did I know it, but Gentoo had successfully installed the C shared library that the Ruby driver uses in the right place. So, when I unmerged, this file was removed and now the postgres gem was without its shared library.

This took me more time than I care to admit to understand, but it was a simple matter to rebuild the library, and then things seemed to be working. Except…

Somehow, the “sessions” table got an error as soon as I tried to login. Tracing it back the logs and postgres, the problem seemed to be that the import process had been setting ids explicitly, rather than letting them be set implicitly by the sequences. This is probably a good thing as any foreign keys would be messed up, but it meant that I had to fix the sequences so that they weren’t returning id’s for records that had already been assigned. A few invocations of setval() later, and I was good.

Now the blog is running 100% on Postgres. I’m still trying to make up my mind as to whether it’s worth trying to migrate over the old content from 360, but at this point I’m ready to start using this thing for real.