How do I install the hstore module on PostgreSQL 9.0 (MacPorts install)?

Question

I have a lovely PostgreSQL 9.0 server installed on my laptop via MacPorts. I would like to enable the hstore module, but I can't find any instructions for installing these optional modules (nor can I find any hstore-related code in /opt/local/share/postgresql90/contrib/).

I have found some hstore-related SQL here, but I'm not sure where it comes from or if it's compatible w/ PostgreSQL 9.0.

So, how do I enable the hstore module on my MacPorts-installed Postgres 9.0 server?

Answer 1

You can tell MacPorts to build hstore. Here's how.

If you already have postgresql installed, you will need to uninstall it first (this won't touch your data or users) because the install action will not re-install an already installed port. The uninstall is forced (-f) because postgresql91-server is dependent and will prevent uninstall.

sudo port -f uninstall postgresql91

Edit the Portfile and add hstore to the list on the line which begins with set contribs:

sudo port edit postgresql91

(Re)install from source explicitly (-s) to build the hstore extension:

sudo port -s install postgresql91

Then load hstore, once for each of your databases in which you want to use it:

In >= 9.1: CREATE EXTENSION hstore;

In 9.0: psql -U postgres -f /opt/local/share/postgresql90/contrib/hstore.sql

Note this process works for postgresql92 by just substituting "92" for "91".

Answer 2

It seems that the port for PostgreSQL 9.1 now includes hstore, but it still needs to be enabled. Install and start the database normally.

sudo port install postgresql91 postgresql91-server
sudo mkdir -p /opt/local/var/db/postgresql91/defaultdb
sudo chown postgres:postgres /opt/local/var/db/postgresql91/defaultdb
sudo su postgres -c '/opt/local/lib/postgresql91/bin/initdb \
  -D /opt/local/var/db/postgresql91/defaultdb'
sudo port load postgresql91-server

EDIT: Installing in another computer didn't work as well. The hstore was not installed with the base (I may have made it available trying other solutions). So do this BEFORE the load command above:

sudo port unload postgresql91-server #  if you did load above
sudo port build postgresql91
port work postgresql91 # Gives you base dir for following command
cd /opt/local/var/macports/build/_opt_local_var_macports_sources_rsync.macports.org_release_ports_databases_postgresql91/postgresql91/work/postgresql-9.1.*/contrib/hstore
sudo make all
sudo make install clean
sudo port load postgresql91-server

To enable the hstore extension, use the new "create extension" SQL command in the database(s) you will use hstore. If you install it into the template1 database, all databases created afterwards will have the hstore extension.

psql template1 postgres
template1=# create extension hstore;

If you only need the extension in a particular database:

psql dbname dbuser
dbname=# create extension hstore;
create table a (id serial, data hstore);
NOTICE:  CREATE TABLE will create implicit sequence "a_id_seq" for serial column "a.id"
CREATE TABLE
dbname=# insert into a(data) values('a=>1, b=>2');
INSERT 0 1
dbname=# SELECT * from a;
 id |        data        
----+--------------------
  1 | "a"=>"1", "b"=>"2"
(1 row)

Answer 3

I can't say for MacOS (or whatever MacPorts is), but on Windows there is a file "hstore.sql" in share/contrib and it references a library "hstore.dll" which is part of the regular distribution.

This was included in the one click installer from EnterpriseDB. I would assume that the one click installer for MacOS includes that module as well:

http://www.enterprisedb.com/products-services-training/pgdownload#osx