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.

Krenn

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.

Krenn

For me, psql -d template1 -c 'create extension hstore;' did the trick (Postgres 9.1). According to a post on PostgreSQL.org, "Template0 is the 'blank' pgsql database template that contains just those things that come out-of-the-box." – Whitson

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)

Senary

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';

Enervated

This fixed my immediate problem, but caused pg_upgrade to fail later - be careful when the time comes to upgrade. – Domineca

Answer 4

For Heroku

heroku pg:psql --app YOUR_APP_NAME

and then run

CREATE EXTENSION hstore;

Kerby