61

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.

Braiam
  • 1
  • 11
  • 47
  • 78

2 Answers2

91

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
Braiam
  • 1
  • 11
  • 47
  • 78
  • 17
    In postgresql 9.1 on ubuntu the modules are in `/usr/share/postgresql/9.1/extension` and you have to use [CREATE EXTENSION](http://www.postgresql.org/docs/9.1/static/sql-createextension.html) to install them. – Christopher Manning Feb 21 '12 at 15:16
6
  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

David
  • 633
  • 3
  • 7
  • 13
  • 2
    The script syntax is incorrect, would have to be `psql -U postgres book -c "create extension cube"`. And `CREATE EXTENSION` was introduced with Postgres 9.1. This question is about 8.4. – Erwin Brandstetter Aug 11 '12 at 02:25
  • 2
    Those looking for help for PostgreSQL 9.1+ for 7 Databases in 7 weeks should find this useful. – bobby Dec 20 '12 at 17:19
  • Can confirm this method works for loading tablefunc extension for postgres 10.13 and helped me to avoid giving SUPERUSER privileges to a regular user. – Alex Volkov Nov 30 '20 at 19:56