How do I import modules or install extensions in Postgres?

Question

To install PostgreSQL contrib modules on Ubuntu or Kubuntu (or similar Linux distributions):

1. Install the contrib package: sudo apt-get install postgresql-contrib
2. Change to the database owner account (e.g., postgres).
3. CREATE EXTENSION "uuid-ossp";

If you are trying to install non-"trusted" modules, you need to be a superuser to install them. Otherwise, you only need to have CREATE privilege on the database you are trying to use the module on.

For versions before 9.1, do step #1 above, and then:

1. Restart the database: sudo /etc/init.d/postgresql-8.4 restart

2. Change to the database owner account (e.g., postgres).

3. Change to the contrib modules' directory: /usr/share/postgresql/8.4/contrib/

4. Use ls to see a list of the following modules:

    adminpack               autoinc
    btree_gin               btree_gist
    chkpass                 citext
    cube                    dblink
    dict_int                dict_xsyn
    earthdistance           fuzzystrmatch
    hstore                  insert_username
    int_aggregate           isn
    lo                      ltree
    moddatetime             pageinspect
    pg_buffercache          pgcrypto
    pg_freespacemap         pgrowlocks
    pg_stat_statements      pgstattuple
    pg_trgm                 pgxml
    refint                  seg
    sslinfo                 tablefunc
    test_parser             timetravel
    tsearch2                uuid-ossp
   

5. Load the SQL files using: psql -U user_name -d database_name -f module_name.sql

For example, if your administrative user was named postgres and your database was named storage and the module you wanted was cube, you would type:

psql -U postgres -d storage -f cube.sql

1. login as postgres user
2. use create extension to load it

I have a database named 'book' for example,

psql -U postgres book create extension cube

Repeat for each extension required, then \q to logouy