Single server multiple databases - extension

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

Thanks in advance
Brian

________________________________
From: Brian Trudal <dbrb2002-sql@yahoo.com>
To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
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);
                            ^

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@yahoo.com>

Thanks for getting back to me. Still no luck; and I tried all possibilities..

For example, when I tried on new DB:

db1=# CREATE EXTENSION hstore         
  SCHEMA public
  VERSION "1.0";
ERROR:  type "hstore" already exists

db1=# create table foo(id hstore);
ERROR:  type "hstore" is only a shell
LINE 1: create table foo(id hstore);
                            ^
db1=# create table foo(id public.hstore);
ERROR:  type "public.hstore" is only a shell
LINE 1: create table foo(id public.hstore);

db1=# SELECT * FROM  pg_available_extension_versions
engine_db-# WHERE name = 'hstore';
-[ RECORD 1 ]-------------------------------------------------
name        | hstore
version     | 1.0
installed   | f
superuser   | t
relocatable | t
schema      |
requires    |
comment     | data type for storing sets of (key, value) pairs

db1=# SELECT * FROM
pg_extension e INNER JOIN pg_namespace n ON (e.extnamespace = n.oid);
-[ RECORD 1 ]--+-----------------------------------
extname        | plpgsql
extowner       | 10
extnamespace   | 11
extrelocatable | f
extversion     | 1.0
extconfig      |
extcondition   |
nspname        | pg_catalog
nspowner       | 10
nspacl         | {postgres=UC/postgres,=U/postgres}

But if I use it in other DB, where it was installed; it works fine..

db2=# SELECT * FROM
pg_extension e INNER JOIN pg_namespace n ON (e.extnamespace = n.oid);
-[ RECORD 1 ]--+-----------------------------------
extname        | plpgsql
extowner       | 10
extnamespace   | 11
extrelocatable | f
extversion     | 1.0
extconfig      |
extcondition   |
nspname        | pg_catalog
nspowner       | 10
nspacl         | {postgres=UC/postgres,=U/postgres}
-[ RECORD 2 ]--+-----------------------------------
extname        | hstore
extowner       | 10
extnamespace   | 2200
extrelocatable | t
extversion     | 1.0
extconfig      |
extcondition   |
nspname        | public
nspowner       | 10
nspacl         | {postgres=UC/postgres,=U/postgres}

db2 =# SELECT * FROM  pg_available_extension_versions
WHERE name = 'hstore';
-[ RECORD 1 ]-------------------------------------------------
name        | hstore
version     | 1.0
installed   | t
superuser   | t
relocatable | t
schema      |
requires    |
comment     | data type for storing sets of (key, value) pairs

any other hints ?

________________________________
From: Bartosz Dmytrak <bdmytrak@eranet.pl>
To: Brian Trudal <dbrb2002-sql@yahoo.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Tuesday, March 6, 2012 1:02 PM
Subject: Re: [GENERAL] Single server multiple databases - extension

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@yahoo.com>

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

Brian Trudal <dbrb2002-sql@yahoo.com> writes:

Thanks for getting back to me. Still no luck; and I tried all possibilities..
For example, when I tried on new DB:

db1=# CREATE EXTENSION hstore
� SCHEMA public
� VERSION "1.0";
ERROR:� type "hstore" already exists

db1=# create table foo(id hstore);
ERROR:� type "hstore" is only a shell

Apparently you've got a shell type named "hstore" cluttering that
database. Try "DROP TYPE hstore" and then see if you can create
the extension.

regards, tom lane

That solved the issue. Apart from hstore, I needed to drop ghstore as well.

Thanks again

________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Brian Trudal <dbrb2002-sql@yahoo.com>
Cc: Bartosz Dmytrak <bdmytrak@eranet.pl>; "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Sent: Tuesday, March 6, 2012 4:09 PM
Subject: Re: [GENERAL] Single server multiple databases - extension

Brian Trudal <dbrb2002-sql@yahoo.com> writes:

Thanks for getting back to me. Still no luck; and I tried all possibilities..
For example, when I tried on new DB:

db1=# CREATE EXTENSION hstore
  SCHEMA public
  VERSION "1.0";
ERROR:  type "hstore" already exists

db1=# create table foo(id hstore);
ERROR:  type "hstore" is only a shell

Apparently you've got a shell type named "hstore" cluttering that
database.  Try "DROP TYPE hstore" and then see if you can create
the extension.

            regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general