A Word From Our Sponsor
Okay, I’ve now finally updated to Typo 5.. I can’t say the experience was entirely painful. It turns out that “typo install” doesn’t migrate terribly well in a few nasty corner cases that I hit smack dab in the middle. The solution is to do a fresh typo install and then invoke the database migration manually with
RAILS_ENV=production db:migrate.
The other problem I had was that the last migration script (071fixtags_naming.rb if you are curious) somehow failed (apparently some kind of issue with Ruby/Rails/ActiveRecord and Postgreql). Being comfortable with SQL but not comfortable with Rails, I just commented it out and then did the fix manually. Here is the magic SQL invocation I used for posterity:
update tags set name = replace(name, '.', '_');
Other than the migration update, the new Typo does seem to be better and faster than the old one, although I really need to fool around with it at a time when I’m not ready to crash hard.
Migrating to Postgresql 1
Yesterday, the blog got an error. Surprise, surprise, my concerns about SQLite seemed to be realized, as tracing through the error it seemed the database had gotten trapped in this locked state. Now, I’m sure if I had half a clue about SQLite or Rails I might have been able to figure this out and prevent it from happening again, but I know zip about the former and am very much in the early stages of learning about the latter. So, on the “least effort” principle, I decided to carry out that migration to Postgresql I had been wanting to do anyway.
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.