Error with Rails test database using postgres using hstore

Question

When using hstore in Postgresql 9.2 in a Rails 3.2 app, I got an error complaining as follows when raking my test database:

PG::Error: ERROR: type "hstore" does not exist

Since it built from schema, the test database didn't go through the hstore CREATE EXTENSION migration that the development database. This caused the error on the rake db:test:prepare.

How to fix this? I've actually discovered a fix, happy to hear more.

Answer 1

I simply enabled my postgresql database to support hstore by default (by having the template database support hstore). Run the following command to do so:

psql -d template0 -c 'create extension hstore;'

Then any Rails test db will automatically support the extension.

Answer 2

When I tried to run psql -d template0 -c 'create extension hstore;' (in @Connor's answer) I got the error:

psql: FATAL: database "template0" is not currently accepting connections

Instead I followed the procedure in this blog post which involves updating template1 instead.

1) Create file “hstore.sql” containing:

CREATE EXTENSION hstore;

2) Run it:

psql -f /usr/local/Cellar/postgresql/9.2.1/share/postgresql/extension/hstore.sql -d template1

I suspect that this would also have worked (but I didn't try it):

psql -d template1 -c 'create extension hstore;'

(To see the different write permissions between template0 and template1 I followed this article)

Answer 3

To solve the FATAL error and allow template0 to accept connections, execute the following:

UPDATE pg_database SET datallowconn = TRUE WHERE datname = 'template0';