Re: Single server multiple databases - extension

[Bartosz Dmytrak <bdmytrak@xxxxxxxxx>]

Hi,

there shouldn't be any problem in installing extensions to multiple databases in the same server. Extensions are per database: http://www.postgresql.org/docs/9.1/static/sql-createextension.html

You can use pgAdmin, or try this syntax:

 CREATE EXTENSION hstore

  SCHEMA public

  VERSION "1.0";

if hstore is installed in public schema, sometimes You have to use public.hstore syntax (fully qualified name) - this depends on your search_path setting.

in your example it looks like hstore is installed, but question is: where is it?

You can find this info using SQL like this one:

SELECT * FROM 

pg_extension e INNER JOIN pg_namespace n ON (e.extnamespace = n.oid)

http://www.postgresql.org/docs/9.1/static/catalog-pg-extension.html

this could be useful too:

SELECT * FROM  pg_available_extension_versions

WHERE name = 'hstore'

http://www.postgresql.org/docs/9.1/static/view-pg-available-extension-versions.html

Regards,
Bartek

2012/3/6 Brian Trudal <dbrb2002-sql@xxxxxxxxx>

Any one know how to install extensions to multiple databases in the same server ?

Thanks in advance
Brian

---

From: Brian Trudal <dbrb2002-sql@xxxxxxxxx>
To: "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, March 5, 2012 4:52 PM
Subject: Single server multiple databases - extension

Hi

I have 2 databases running in a single server; and I installed extension 'hstore' to one database and it works fine. When I tried to use the same extension in another database, it gives an error saying 'extension does not exist'; nor it allow to install as it complains about its existence.

Any help ?

db1=# CREATE EXTENSION hstore;
ERROR:  type "hstore" already exists
db1=# DROP EXTENSION hstore;
ERROR:  extension "hstore" does not exist
db1=# create table foo(id hstore);
ERROR:  type "hstore" is only a shell
LINE 1: create table foo(id hstore);
                            ^