Managing PostgreSQL extensions

In PostgreSQL, extensions are modules that supply extra functions, operators, or types. Many extensions are available in IBM Cloud® Databases for PostgreSQL. To use them, set the admin password for your service, and then use psql to connect.

Listing installed extensions

Get a list of all the extensions installed on a database by using the \dx command.

For example, the output for \dx when run on the Databases for PostgreSQL default database shows the only installed extension.

ibmclouddb=> \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

Installing extensions

To install an extension on to a database use CREATE EXTENSION. For example, to install pg_stat_statements on the ibmclouddb database, use the following command:

ibmclouddb=> CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

Extensions are installed into the read-only ibm_extension schema. The schema is part of the search_path so extension objects do not need to be qualified with a schema. The change from public schema to ibm_extension schema is necessary to protect the security and integrity of your data.

If you run the \dx command after installing an extension, it appears in the table.

ibmclouddb=> \dx
                                             List of installed extensions
        Name        | Version |    Schema     |                              Description
--------------------+---------+---------------+------------------------------------------------------------------------
 pg_stat_statements | 1.10    | ibm_extension | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog    | PL/pgSQL procedural language
(2 rows)

Database extensions in PostgreSQL are managed per database. If you have multiple databases that you need to install an extension on, run the CREATE command on each database.

Upgrading extensions

If there is a newer version of an extension available than the one you currently have installed, use the ALTER EXTENSION command to update it:

ALTER EXTENSION <extension_name> UPDATE;

To upgrade the pg_repack extension, run DROP EXTENSION pg_repack; and then CREATE EXTENSION pg_repack; in each database that uses it.

Extension-specific notes

`pg_repack`

The pg_repack documentation
When you run the pg_repack command, pass the -k flag in to bypass the check for superuser. See the following example:
```
pg_repack -k [OPTION]... [DBNAME]
```
Any user can run pg_repack, but the command is only able to repack a table that they have permissions on.
pg_repack needs to take an exclusive lock on objects it is reorganizing at the end of the reorganization. If it can't get this lock after a certain period, it cancels all conflicting queries. If it can't do so, the reorg fails. By default, only the admin user is able to cancel conflicting queries. To let other database users cancel queries, grant them the pg_signal_backend role from the admin user.

`pgaudit`

The service loads the pgaudit library at startup. You cannot create the pgaudit extension, so DDL entries in the audit logs do not include the object type and name. To enable pgaudit logs, see Logging with pgaudit.

`pgvector`

To add the pgvector extension to your deployment, use the create extension vector command.
Important: pgvector requires PostgreSQL version 15 or higher.

Available extensions

See the following list of all available extensions. For a list of available extensions on your deployment, use SELECT name FROM pg_available_extensions; in psql.

ibmclouddb=> SELECT name FROM pg_available_extensions order by 1;

             name
------------------------------
 address_standardizer
 address_standardizer_data_us
 amcheck
 autoinc
 bloom
 btree_gin
 btree_gist
 citext
 cube
 dblink
 dict_int
 dict_xsyn
 earthdistance
 file_fdw
 fuzzystrmatch
 hstore
 insert_username
 intagg
 intarray
 isn
 lo
 ltree
 moddatetime
 old_snapshot
 pageinspect
 pg_buffercache
 pg_freespacemap
 pg_prewarm
 pg_repack
 pg_stat_statements
 pg_surgery
 pg_trgm
 pg_visibility
 pg_walinspect
 pgaudit
 pgcrypto
 pgrouting
 pgrowlocks
 pgstattuple
 plpgsql
 postgis
 postgis_raster
 postgis_tiger_geocoder
 postgis_topology
 postgres_fdw
 refint
 seg
 sslinfo
 tablefunc
 tcn
 tsm_system_rows
 tsm_system_time
 unaccent
 uuid-ossp
 xml2
(55 rows)

ibmclouddb=> select version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 16.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-2), 64-bit
(1 row)