Archive for the ‘Databases’ category

postgresql rails and rake db:test:prepare

August 23rd, 2012

I am very new to postgresql.  Lots of folks use it and swear by it.  I have no complaints with it as a database and query engine.  I struggle with the administrative aspects of it.

A rails project I’m working on uses postgresql and uses databases with UTF8 encoding.  Well, if you’ve ever dived into what rake db:test:prepare does, you’ll know that along the chain it drops and recreates your databases.  That, of cource, necessitates that your user have sufficient privileges to create databases.  Straightforward stuff.

Well, today when trying to run my tests I was getting the generic error:

Couldn’t create database for {“adapter”=>”postgresql”, “database”=>”ha_ya_right”, “password”=>”nopes”, “pool”=>5, “timeout”=>5000, “username”=>”uh_uh”}

That was at the end of the reams of backtrace.  The magic was at the top of those reams:

PG::Error: ERROR:  new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)

HINT:  Use the same encoding as in the template database, or use template0 as template.

Yeah, templates.  I missed that error and ended up using ruby-debug (best. gem. ever.) and breaking right in the postgresql connection adapter.  I’ll have to remember to remove that line.

Anyway, my system is running Ubuntu 12.04, PostgreSQL 9.1.4, Ruby 1.9.2, and Rails 3.2.3.  I had to get the database connection to use template0.  It’s quite easy:

  adapter: postgresql
  database: ha_ya_right
  password: nopes
  pool: 5
  timeout: 5000
  username: uh_uh
  template: template0

That last line is the money line. I mentioned all my environment specs, because some folks in the blogs I came across said that solution didn’t work for them. Hopefully this can save you some time.

When having trouble installing the mysql gem on OSX

April 21st, 2010

And you get an error like:

*** extconf.rb failed ***

Go read:

Renaming a database in Rails

December 14th, 2009

Hooray for a non-rant post!

The heart of this method works outside of Rails, but there’s one particular convenience method that Rails provides.  Find some way to mimic that behavior, and the rest would apply.  Also, this is a MySQL-specific solution.  I haven’t tried it with any other DB systems.

We “rename” a database by moving all of its tables into a new database.  We do so via MySQL’s RENAME TABLE command.  Basically, create an empty database with the new name.  We then use our ActiveRecord connection’s “tables” method to loop over all the tables in our database, executing a RENAME TABLE from the old to the new with each iteration. “Tables” is the convenience method I mentioned above. Encapsulating this in a method we get:

def rename_database(old_db_name, new_db_name)
    ActiveRecord::Base.connection.execute "CREATE DATABASE #{new_db_name}"
    ActiveRecord::Base.connection.tables.each do |table|
            "RENAME TABLE #{old_db_name}.#{table} TO #{new_db_name}.#{table}"
    ActiveRecord::Base.connection.execute "DROP DATABASE #{old_db_name}"

Error checking is left as an (important!) exercise to the reader.  Also, you may prefer to monkey patch this into ActiveRecord itself as opposed to some odd method floating around in global space.

This presupposes that the new database destination will be on the same server.

Another way to rename a database is to mysqldump the old one to a file and then reimport it to the renamed database.  The method presented in this article is advantageous if your database has a large amount of data.  Renaming a table is almost instant.  Dumping millions of rows and reimporting them is not so instant.