Adding server extensions
Introduction
A useful extension to PostgreSQL typically includes multiple SQL objects; for example, a new data type will require new functions, new operators, and probably new index operator classes. It is helpful to collect all these objects into a single package to simplify database management. PostgreSQL calls such a package an extension.
The main advantage of using an extension, rather than just running the SQL script to load a bunch of “loose” objects into your database, is that PostgreSQL will then understand that the objects of the extension go together. You can drop all the objects with a single DROP EXTENSION command (no need to maintain a separate “uninstall” script). Even more useful, pg_dump knows that it should not dump the individual member objects of the extension — it will just include a CREATE EXTENSION command in dumps, instead. This vastly simplifies migration to a new version of the extension that might contain more or different objects than the old version. Note however that you must have the extension's control, script, and other files available when loading such a dump into a new database.
Before you can use CREATE EXTENSION to load an extension into a database, the extension's supporting files must be installed.
The extensions currently available for loading can be identified from the pg_available_extensions or pg_available_extension_versions system views. The pg_available_extensions view lists the extensions that are available for installation. The catalog pg_extension stores information about the installed extensions.
postgres=# select * from pg_available_extensions order by name;
name | default_version | installed_version | comment
------------------------------+-----------------+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
address_standardizer | 3.2.1 | | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
address_standardizer_data_us | 3.2.1 | | Address Standardizer US dataset example
adminpack | 2.1 | | administrative functions for PostgreSQL
amcheck | 1.3 | | functions for verifying relation integrity
autoinc | 1.0 | | functions for autoincrementing fields
bloom | 1.0 | | bloom access method - signature file based index
btree_gin | 1.3 | | support for indexing common datatypes in GIN
btree_gist | 1.6 | | support for indexing common datatypes in GiST
citext | 1.6 | | data type for case-insensitive character strings
cube | 1.5 | | data type for multidimensional cubes
dblink | 1.2 | | connect to other PostgreSQL databases from within a database
dict_int | 1.0 | | text search dictionary template for integers
dict_xsyn | 1.0 | | text search dictionary template for extended synonym processing
earthdistance | 1.1 | | calculate great-circle distances on the surface of the Earth
file_fdw | 1.0 | | foreign-data wrapper for flat file access
fuzzystrmatch | 1.1 | | determine similarities and distance between strings
hstore | 1.8 | | data type for storing sets of (key, value) pairs
hstore_plperl | 1.0 | | transform between hstore and plperl
hstore_plperlu | 1.0 | | transform between hstore and plperlu
hstore_plpython2u | 1.0 | | transform between hstore and plpython2u
hstore_plpython3u | 1.0 | | transform between hstore and plpython3u
hstore_plpythonu | 1.0 | | transform between hstore and plpythonu
insert_username | 1.0 | | functions for tracking who changed a table
intagg | 1.1 | | integer aggregator and enumerator (obsolete)
intarray | 1.5 | | functions, operators, and index support for 1-D arrays of integers
isn | 1.2 | | data types for international product numbering standards
lo | 1.1 | | Large Object maintenance
ltree | 1.2 | | data type for hierarchical tree-like structures
ltree_plpython2u | 1.0 | | transform between ltree and plpython2u
ltree_plpython3u | 1.0 | | transform between ltree and plpython3u
ltree_plpythonu | 1.0 | | transform between ltree and plpythonu
moddatetime | 1.0 | | functions for tracking last modification time
old_snapshot | 1.0 | | utilities in support of old_snapshot_threshold
oracle_fdw | 1.2 | | foreign data wrapper for Oracle access
pageinspect | 1.9 | | inspect the contents of database pages at a low level
pg_buffercache | 1.3 | | examine the shared buffer cache
pg_cron | 1.4-1 | | Job scheduler for PostgreSQL
pg_freespacemap | 1.2 | | examine the free space map (FSM)
pg_prewarm | 1.2 | | prewarm relation data
pg_show_plans | 1.0 | | show query plans of all currently running SQL statements
pg_stat_monitor | 1.0 | | The pg_stat_monitor is a PostgreSQL Query Performance Monitoring tool, based on PostgreSQL contrib module pg_stat_statements. pg_stat_monitor provides aggregated statistics, client information, plan details including plan, and histogram information.
pg_stat_statements | 1.9 | | track planning and execution statistics of all SQL statements executed
pg_surgery | 1.0 | | extension to perform surgery on a damaged relation
pg_trgm | 1.6 | | text similarity measurement and index searching based on trigrams
pg_visibility | 1.2 | | examine the visibility map (VM) and page-level visibility info
pgaudit | 1.6.2 | | provides auditing functionality
pgcrypto | 1.3 | | cryptographic functions
pgrowlocks | 1.2 | | show row-level locking information
pgsentinel | 1.0 | | active session history
pgstattuple | 1.5 | | show tuple-level statistics
plperl | 1.0 | | PL/Perl procedural language
plperlu | 1.0 | | PL/PerlU untrusted procedural language
plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
plpython3u | 1.0 | | PL/Python3U untrusted procedural language
postgis | 3.2.1 | | PostGIS geometry and geography spatial types and functions
postgis_raster | 3.2.1 | | PostGIS raster types and functions
postgis_tiger_geocoder | 3.2.1 | | PostGIS tiger geocoder and reverse geocoder
postgis_topology | 3.2.1 | | PostGIS topology spatial types and functions
postgres_fdw | 1.1 | | foreign-data wrapper for remote PostgreSQL servers
refint | 1.0 | | functions for implementing referential integrity (obsolete)
seg | 1.4 | | data type for representing line segments or floating-point intervals
tablefunc | 1.0 | | functions that manipulate whole tables, including crosstab
tcn | 1.0 | | Triggered change notifications
timescaledb | 2.9.3 | | Enables scalable inserts and complex queries for time-series data
tsm_system_rows | 1.0 | | TABLESAMPLE method which accepts number of rows as a limit
tsm_system_time | 1.0 | | TABLESAMPLE method which accepts time in milliseconds as a limit
unaccent | 1.1 | | text search dictionary that removes accents
(67 rows)
Those extensions are part of the DBOD PostgreSQL Server package. If you need an extension not listed there, the DBOD team will first have to evaluate your request and the feasibility of integrating it into a new RPM.
Loading a PostgreSQL extension is database-specific, so it will only work in the database where you have created it. An Extension can be installed w/o superuser privileges depending on some settings. You can try if that's true for the extension you might need to install. If not, please open a SNOW Ticket to the DBOD service and we will do that for you. This is for security reasons, as with some extensions you could execute system commands.
For more information on extensions, you can read: Packaging Related Objects into an Extension
Loading an extension into a database
As explained above, loading an extension is done with the CREATE EXTENSION command. CREATE EXTENSION will load a new extension into the current database, so there must not be an extension of the same name already loaded.
We connect to the postgres installation using your admin role.
$ psql -h dbod-xxxx.cern.ch -p port_number -U admin
postgres=# select datname,datallowconn,datistemplate from pg_database order by 3;
postgres=# \c my_db
postgres=# SELECT * FROM pg_available_extensions;
postgres=# \dx
postgres=# CREATE EXTENSION cube WITH SCHEMA public;
postgres=# CREATE EXTENSION hstore;
postgres=# \dx
In the example above, we:
- connect to the Postgres DB service
- check the list of databases available in the database service
- select the database my_db where we want to install the extension cube
- check the available extensions
- check the extensions already loaded in the selected database
- create cube in the schema public
- create hstore in the current database
- query the extensions loaded to see that cube and hstore appear there now
Particularities
If you need to automate the creation of new databases in your instance having always a particular extension loaded, you can load the requested extension into the user PG template1 database. This template (and template0) is available in all the database servers, and it is the template used by default to create new databases.
By updating template1, all the new databases created based on it will inherit automatically this extension.
$ psql -h dbod-xxxx.cern.ch -p port_number -U admin
postgres=# \c template1
postgres=# SELECT * FROM pg_available_extensions;
postgres=# \dx
postgres=# CREATE EXTENSION pgcrypto WITH SCHEMA public;
postgres=# \dx
In the example above, we:
- connect to the Postgres DB service
- select the database template1 where we want to install the extension (ex pgcrypto)
- check the available extensions
- check the extensions already loaded in the selected database
- create pgcrypto in the schema public
- query the extensions to check if the new one has been loaded correctly