Working with extensions

PostgreSQL provides a way to extend the functionality of a database by bundling SQL objects into a package and using them as a unit. This functionality is implemented in PostgreSQL extensions.

Extensions package user-visible functions or use hooks in PostgreSQL to modify how the database does certain processes.

Viewing available extensions

You can view available extensions using psql:

  • Use \dx to list available extensions

  • Use \dx+ to list available extensions and their associated objects

Contrib modules

Many contrib modules are implemented as extensions.

A contrib module is an additional feature provided as part of the source distribution but not enabled by default. These are provided either as extension modules or as additional client or server applications. They are part of the core distribution but not enabled by default, as they either provide niche functionality or are considered experimental, or in some cases used to provide backwards compatibility for features since integrated into the PostgreSQL core.

You can install contrib and postgis modules either after you’ve installed ADPG cluster via install contrib and install postgis actions, or at the moment you install ADPG cluster via setting its configuration options.

For more information on the additional supplied modules, refer to the PostgreSQL documentation.

Commands

Basically, you can CREATE and DROP an extension, and also ALTER it.

CREATE EXTENSION

CREATE EXTENSION creates an extension by reading the matching extension control file and creating the database objects from the extension SQL file or files.

If no matching extension is found (because the extension was not installed or does not exist), CREATE EXTENSION will report that the extension’s control file does not exist.

If an extension cannot be found, and PostgreSQL was installed from system packages, it may be necessary to install the appropriate contrib package.

Create the pg_stat_statements extension
postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

For more information refer to the CREATE EXTENSION page of the PostgreSQL documentation.

DROP EXTENSION

DROP EXTENSION removes extensions from the database. Dropping an extension causes its component objects to be dropped as well.

Drop an extension and any dependent extensions
postgres=# DROP EXTENSION plperlu CASCADE ;
NOTICE:  drop cascades to extension bool_plperlu
DROP EXTENSION

For more information refer to the DROP EXTENSION page of the PostgreSQL documentation.

ALTER EXTENSION

ALTER EXTENSION modifies an extension, including performing version upgrades.

Update the hstore extension to version 2.0
ALTER EXTENSION hstore UPDATE TO '2.0';

For more information refer to the ALTER EXTENSION page of the PostgreSQL documentation.

Found a mistake? Seleсt text and press Ctrl+Enter to report it