Work 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 as PostgreSQL extensions.
Extensions are modules that provide additional functionality: functions, operators, types, etc.
View available extensions
You can use psql to view the installed extensions:
-
The
\dx
meta-command lists installed extensions. -
The
\dx+
meta-command displays installed extensions and their associated objects.
The pg_available_extensions view contains the extensions available for installation. Use the following code to list them:
SELECT * FROM pg_available_extensions;
You can also find the list of available extensions in the Supported extensions article.
The pg_extension catalog stores information about the installed extensions. In ADPG, the plpgsql
and pg_stat_statements
extensions are preinstalled in the postgres
database. When creating a new database, the extensions that are present in the template1
database are used (by default, only plpgsql
). See Template Databases.
Utilize the following code to check installed extensions:
SELECT * FROM pg_extension;
Result:
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition -------+--------------------+----------+--------------+----------------+------------+-----------+-------------- 14472 | plpgsql | 10 | 11 | f | 1.0 | | 16388 | pg_stat_statements | 10 | 2200 | t | 1.9 | |
Manage extensions
You can create, alter, and drop extensions using corresponding commands.
CAUTION
If you use PL/Perl, PL/PerlU, PL/Python3U, PL/Tcl, or PL/TclU procedural languages that require additional packages, make sure that all packages for corresponding extensions are installed on all ADPG nodes — the leader and all replicas. Otherwise, cluster can be damaged during minor/major upgrade.
|
Create an extension
CREATE EXTENSION
loads a new extension into the current database. An extension with the same name cannot be loaded.
PostgreSQL runs the extension script file. The script creates new SQL objects, for example, functions, data types, operators, and index support methods. CREATE EXTENSION
also records the identifiers of all the created objects, so that they can be dropped if DROP EXTENSION
is run.
The user who runs CREATE EXTENSION
becomes the extension owner and the owner of any objects created by the extension script.
If a matching extension is not found, CREATE EXTENSION
reports that the extension script file does not exist.
If an extension cannot be found, install an appropriate package that contains this extension.
The following code creates the pg_stat_statements
module, which is included in the contrib extension:
CREATE EXTENSION pg_stat_statements;
For more information on this command, refer to CREATE EXTENSION.
Drop an extension
DROP EXTENSION
removes extensions from the database. Dropping an extension causes its component objects to be also dropped.
The following code uses the CASCADE
option to drop an extension, all dependent objects, and objects that depend on the dependent objects:
DROP EXTENSION plperlu CASCADE;
For additional information, see DROP EXTENSION.
Alter an extension
ALTER EXTENSION
modifies an extension.
The following code changes the schema of the hstore
extension to schema1
:
ALTER EXTENSION hstore SET SCHEMA schema1;
For more information, refer to ALTER EXTENSION.