How do I import modules or install extensions in Postgres?

I'm trying to import several modules that come bundled with postgres, and all the commands to do so (such as contrib.import etc) do not work or cannot be found.


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