Making Rails’ rake:test not drop your PGSQL database

Let’s say you’re using Rails with PostgreSQL and the TSearch2 built-in full text search engine.

Did you notice that every time you run rake test, that depends on db:test:prepare, which depends on db:test:clone, which depends on db:test:purge, which drops the database and creates it again?

Along with your dropped database goes the TSearch2 functions that wrap the C libraries that do the actual work. So, in effect, you no longer have TSearch2 installed. (“Uh… I kinda needed those…”) Presumably if you have tests that exercise search functionality, they will always fail because the TSearch2 functions are gone by the time the tests run.

Since these functions are just wrappers for C libraries, which are not subject to the PostgreSQL plugin security model, PostgreSQL wisely prevents any old user from getting at them. Only a superuser can create them, which means you can’t just add the tsearch2.sql script to a Rails DB migration and get them back each time that way.

Options include:

  1. Making a setuid script (or a script with the postgres user’s password embedded) that the migration can run, which will log in as the postgres user, run the tsearch2.sql script, and grant permissions to your Rails DB user to use them
  2. Changing the rules of the PostgreSQL instance you’re using to allow any old user to mess with C libraries (a pretty big security hole, but maybe you don’t care about that on your development DB on your laptop), and putting tsearch2.sql in a migration. (I dunno if this is even technically possible, but it seems like such a bad idea that I’m not even bothering to look.)
  3. Using Rake to tell Rails not to drop and re-create your database for each test run, but instead to migrate back to 0 and then re-migrate to the latest version.
  4. I chose #3. Here’s the code, which is in my Rakefile:

    In the rare case where you really wanted to drop and re-create your test database, just use the command line PostgreSQL commands dropdb and createdb, and then (still as the postgres user) run the tsearch2.sql script.

    Then resume normal Rails rake:test use, until such time as you irrevocably hose your database (really?) whereupon you’ll need to use the dropdb/createdb method again.

3 thoughts on “Making Rails’ rake:test not drop your PGSQL database”

  1. great code sample. thanks! I don’t know if it comes from upgrading to rails 2 but I actually had to add : ActiveRecord::Base.establish_connection(ActiveRecord::Base.configurations[‘test’])
    before the ActiveRecord::Migrator… or the reverse migration was applied to the development database instead of the test db. Anyway it helped.

  2. Hello, previous version has some problems with rails 2.0.2. Following code seems to be working correctly.

  3. Hi,
    I really need help man,Actually i was looking for something as u mentioned above.My problem is that i am using a postgresql function for calculating distance between points.I have put the creation of this function in Migration file.But when ever i run Rake test,it simple drop my function. :(-.I don’t know how to solve this.I follow ur above method but it is dropping my all tables and i got a lot of errors.Any help?

Leave a Reply

Your email address will not be published. Required fields are marked *