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.
You can view available extensions using psql:
\dxto list available extensions
\dx+to list available extensions and their associated objects
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.
Basically, you can CREATE and DROP an extension, and also ALTER it.
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.
postgres=# CREATE EXTENSION pg_stat_statements; CREATE EXTENSION
For more information refer to the CREATE EXTENSION page of the PostgreSQL documentation.
DROP EXTENSION removes extensions from the database. Dropping an extension causes its component objects to be dropped as well.
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 modifies an extension, including performing version upgrades.
hstoreextension to version 2.0
ALTER EXTENSION hstore UPDATE TO '2.0';
For more information refer to the ALTER EXTENSION page of the PostgreSQL documentation.