How to install Postgres extensions at database creation?

Question

It would be lovely if the rake command db:create could be followed by a postgresql installation script. This, for example. (It must be run as postgres user):

CREATE EXTENSION "fuzzystrmatch";

This because, in this moment, i'm doing it manually every time I create a database.

Any hints?

Arturoartus

Answer 1

As of Rails 4, there is a enable_extension method:

class AddFuzzyStringMatching < ActiveRecord::Migration
  def change
    enable_extension "fuzzystrmatch"
  end
end

Execrable

Answer 2

I just do this in a migration

class AddCryptoToDb < ActiveRecord::Migration
  def up
    execute <<-SQL
      CREATE extension IF NOT EXISTS pgcrypto;
    SQL
  end
end

You can execute any sql want there. I also do it for functions

class BuildFnSetWebUsersUid < ActiveRecord::Migration
  def up
    say "building fn_set_web_users_uid function"
    execute <<-SQL
      CREATE OR REPLACE FUNCTION fn_set_web_users_uid()
        RETURNS trigger AS
      $BODY$
        BEGIN
          IF NEW.uid IS NULL THEN
            SELECT UID into NEW.uid
            FROM generate_series(10000, (SELECT last_value FROM web_users_uid_seq)) AS s(uid)
            EXCEPT
            SELECT uid FROM web_users
            ORDER BY uid;
            IF NEW.uid is NULL THEN
              SELECT nextval('web_users_uid_seq') INTO NEW.uid;
             END IF;
          END IF;
          RETURN NEW;
        END;
      $BODY$
        LANGUAGE 'plpgsql';
  SQL
  end

  def down
    execute "DROP FUNCTION IF EXISTS fn_set_web_users_uid;"
  end
end

Also it doesn't need to be postgres user, depending upon the extension it needs to be superuser or database owner. So on my dev boxes I give the user Super User Rights for ease of use.

Matronize

Answer 3

You could create a rake task (it's fairly straightforward), or mess around with the template1 database (not recommended, but possible):

http://www.postgresql.org/docs/9.2/static/manage-ag-templatedbs.html

Gavotte