How to import modules or install extensions in PostgreSQL 9.1+?

Question

Firstly, if you are not using 9.1+, refer to this question .

How to install extension for PostgreSQL 9.1?

Answer 1

Postgrseql 9.1 provides a new CREATE EXTENSION command. You must use it to install the modules.

The modules presented in 9.1 can be found here. . Enable

 adminpack , auth_delay , auto_explain , btree_gin , btree_gist , chkpass , citext , cube , dblink , dict_int , dict_xsyn , dummy_seclabel , earthdistance , file_fdw , fuzzystrmatch , hstore , intagg , intarray , isn , lo , ltree , oid2name , pageinspect , passwordcheck , pg_archivecleanup , pgbench , pg_buffercache , pgcrypto , pg_freespacemap , pgrowlocks , pg_standby , pg_stat_statements , pgstattuple , pg_test_fsync , pg_trgm , pg_upgrade , seg , sepgsql , spi , sslinfo , tablefunc , test_parser , tsearch2 , unaccent , uuid-ossp , vacuumlo , xml2 

If you wanted to install earthdistance just use this command:

 CREATE EXTENSION earthdistance; 

If you want to install the extension with a hyphen in its name, for example uuid-ossp , you need to enclose the extension name in double quotes:

 CREATE EXTENSION "uuid-ossp"; 

• Details on the tab and modules available in 9.1 .
• Read about the new extension infrastructure and the SQL commands for managing it here. Now you can easily remove the module, see DROP EXTENSION , you can also get a list of extensions, and there is basic support for version numbers.

Answer 2

While Evan Carrol's answer is correct, note that you need to install the postgresql contrib package for the CREATE EXTENSION command to work.

On Ubuntu 12.04, it will look like this:

 sudo apt-get install postgresql-contrib 

Reboot the postgresql server:

 sudo /etc/init.d/postgresql restart 

All available extensions:

 /usr/share/postgresql/9.1/extension/ 

Now you can run the CREATE EXTENSION command.

Answer 3

In addition to the extensions that are supported and provided by the core PostgreSQL development team, there are extensions available from third parties. It is noteworthy that there is a site dedicated to this goal: http://www.pgxn.org/

Answer 4

For postgrersql10

I solved it with

 yum install postgresql10-contrib 

Remember to activate extensions in postgresql.conf

 shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all 

then of course restart

 systemctl restart postgresql-10.service 

all the necessary extensions you can find here

 /usr/pgsql-10/share/extension/ 

Answer 5

In psql put terminal:

 \i <path to contrib files> 

in ubuntu usually /usr/share/postgreslq/<your pg version>/contrib/<contrib file>.sql

Answer 6

How to download and install if you have SUSE. As an example, I load the tablefunc module to use a crosstab. I have PostgreSQL 9.6.1.

right click desktop, terminal, type:

 sudo zypper in postgreql-contrib 

Enter your credentials, continue by typing:

 y 

Run the request (I ran my from pgAdminIII):

 CREATE EXTENSION tablefunc; 

You should now have a crosstab function.

I did not have to restart.

Answer 7

The extensions available for each version of Postgresql vary. An easy way to check which extensions are available, as mentioned:

 SELECT * FROM pg_available_extensions; 

If the desired extension is available, you can install it using:

 CREATE EXTENSION 'extensionName'; 

or if you want to remove it, use:

 DROP EXTENSION 'extensionName'; 

Using psql you can additionally check if the extension was successfully installed using \dx , and find more detailed information about the extension using \dx+ extensioName . It returns additional information about the extension, for example, which packages are used with it.

If the extension is not available in your version of Postgres, you need to download the necessary binaries and libraries and find them in /usr/share/conrib