postgres hstore exists and doesn't exist at same time [duplicate]

Question

I set up a Rails app on a remote server and created an hstore extension

 sudo -u postgres psql
 CREATE EXTENSION hstore;

I then deployed an iteration of the app that uses hstore in one of the postgres tables, but when it ran the migrations it gave an error message

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

I then tried to do this again

 sudo -u postgres psql
 CREATE EXTENSION hstore;

but it told me hstore already exists

ERROR:  extension "hstore" already exists

and this circle continued on.

Any idea what might be causing this problem? I'm using postgres 9.1 on an Ubuntu 12.04 server

Update Note, wondering if this issue was related to permissions, I tried to check my permissions like this but got the following error

sudo -u postgres psql -U username
psql: FATAL:  Peer authentication failed for user "username"

Update Although hstore is installed, it's not an extension for the database I'm using. How to install it in a specific database?

psql -d db_production -c '\dx'
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

Statism

This is not a duplicate question. It just needs a better title such as, "Type 'hstore' does not exist for ActiveRecord Table" Simply add "execute 'create extension hstore'" just above the create_table command in the ActiveRecord::Migration and redo the migration. No need to open a separate psql session. This is yet another example of why Rails is so awesome. Easy Peasy. – Flory

I agree: it does not appear to be a duplicate. – Twinkling

Answer 1

To create extension in your database, you have to explicitly connect to that database. So, if your database is my_app_development, you have to do :

sudo -u postgres psql my_app_development
create extension hstore;

Also, you do not tell which rails version you're on. If you're not on rails-4, you will have to use the postgres hstore gem.

Sixpence

I have tried your steps but still its saying ERROR: extension "hstore" already exists. – Sanferd

Answer 2

Using this SO answer How to create a new database with the hstore extension already installed?, I found out that I had to create the extension for the template1 database and then any other database I created thereafter would have it installed

Statism

No—see @I3x's comment. That should work fine. – Twinkling