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?
As of Rails 4, there is a enable_extension method:
class AddFuzzyStringMatching < ActiveRecord::Migration
def change
enable_extension "fuzzystrmatch"
end
end
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.
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
