Introduction
Compared with other DBMSs such as Oracle and MySQL, PostgreSQL ships with relatively few built-in functions and additional features. To make up for this, PostgreSQL provides extensions.
PostgreSQL extensions extend what PostgreSQL can do. With them, you can easily plug in features that the PostgreSQL database does not provide on its own.
Extension components
To use an extension, the
{PostgreSQL engine path}/share/extension directory must contain a .control file and one or more SQL script files (.sql), as shown below.{extension_name}.control file
- Contains metadata such as the extension's description and version
- The
{extension_name}.controlfile is located in {PostgreSQL engine path}/share/extension
- The extension_name used as this file's name must match the extension_name in the command used to enable the extension (
CREATE EXTENSION {extension_name})
{extension_name}--version.sql file
- The extension's source code file, which actually implements the features and behavior the extension provides
- The
{extension_name}--version.sqlfile is either in the same location as the .control file, or its location is defined in the .control file
- At least one .sql file must exist
Installing extensions
How you install an extension depends on how you installed the PostgreSQL database. Let's look at how the extension installation process differs when PostgreSQL was installed from source code and when it was installed with a package manager.
📢 This post only covers installing
pg_stat_statements, one of the default extensions provided by PostgreSQL. All default extensions are installed the same way as pg_stat_statements.Installing from source code
If you installed the PostgreSQL database from source code, the default extensions that PostgreSQL provides are in the contrib subdirectory of the path where you extracted the source files.
Find the extension you want to install under the contrib subdirectory, move to that directory, compile the source files (
make), and then install them (make install).Depending on the extension, you may need to edit the configuration file (
postgresql.conf) and restart the PostgreSQL database.📢 The PostgreSQL environment used for the tests below is as follows.
- PostgreSQL engine path: /home/postgres/PostgreSQL_Engine
- Default extension path: /home/postgres/PostgreSQL_Engine/contrib
- PostgreSQL data path: /home/postgres/PostgreSQL_Data
Step 1. Move to the extension's directory
Step 2. Compile the source files (make)
Step 3. Install the compiled files (make install)
Step 4. Edit the PostgreSQL configuration file and restart PostgreSQL
Step 5. Connect to the database, create the pg_stat_statements extension, and verify it
Installing with a package manager
If you installed the PostgreSQL database with a package manager, contrib, which contains the default extensions, is not installed, so install contrib with the package manager before use. Depending on the extension, you may need to edit the configuration file (
postgresql.conf) and restart the PostgreSQL database.📢 This assumes the PostgreSQL PGDG yum repository is already installed.
Step 1. Install the contrib package matching your PostgreSQL version
Step 2. Edit the PostgreSQL configuration file and restart PostgreSQL
Step 3. Connect to the database, create the pg_stat_statements extension, and verify it
Built-in extensions
You can see which extensions the PostgreSQL database provides by default in the
pg_available_extensions Catalog. The set of default extensions can differ by PostgreSQL version.Extension Name | Description |
adminpack | Provides administrative functions for pgAdmin and other management tools, such as remote management of server log files |
amcheck | Verifies relation integrity by checking objects for structural and logical corruption |
autoinc | Provides auto-increment for sequence fields |
bloom | Provides an index access method that uses a bloom filter to check whether a value belongs to a set |
btree_gin | Provides GIN operator indexes for testing GIN and developing other GIN operator classes |
btree_gist | Provides GiST operator indexes for testing GiST and developing other GiST operator classes |
citext | Supports a case-insensitive character string data type |
bloom | Supports a data type for multidimensional cubes |
cube | Provides an index access method that uses a bloom filter to check whether a value belongs to a set |
dblink | Provides connections to other databases from within a database |
dict_int | Provides a text search dictionary template for integers (max digits, parameter length, sign) |
dict_xsyn | Provides a text search dictionary template for extended synonym processing |
earthdistance | Provides calculation of great-circle distances on the surface of the Earth |
file_fdw | Provides a foreign data wrapper for accessing data stored in the server's file system |
fuzzystrmatch | Provides functions to determine similarity and distance between strings. Also checks similarity by mapping similar-sounding names to the same code or by building a representative code for an input string |
hstore | Provides a data type for storing sets of (key, value) pairs |
insert_username | Tracks the name of the user who changed a table |
intagg | Supports an aggregate function that builds an integer array containing exactly the integers supplied |
intarray | Supports functions, operators, and indexes for 1-dimensional integer arrays |
isn | Provides data types for ISNs (International Standard Numbers) such as EAN13, UPC, and ISBN |
lo | Provides maintenance of large objects such as LO and BLOB |
ltree | Provides a data type and search for label trees in a hierarchical tree structure |
moddatetime | Tracks the last modification time of a specific row in a table |
old_snapshot | [Since. 14] Provides utilities that support old_snapshot_threshold |
pageinspect | Inspects the contents of database pages at a low level, useful for debugging |
pg_buffercache | Monitors the shared buffer cache in real time |
pg_freespacemap | Inspects the FSM (Free Space Map), which returns free space |
pg_prewarm | Provides a way to load data into the buffer cache |
pg_stat_statements | Tracks planning and execution statistics of all SQL statements executed |
pg_surgery | [Since. 14] Provides functions such as force-kill and force-freeze for dealing with corrupted relations |
pg_trgm | Measures similarity of alphanumeric text based on trigrams and provides index search |
pg_visibility | Provides utilities to inspect visibility information at the VM and page level by returning bit values |
pg_walinspect | [Since. 15] Supports functions that inspect the contents of the PostgreSQL WAL (Write-Ahead Log) at a low level |
pgcrypto | Provides cryptographic functions |
pgrowlocks | Shows row-level locking information |
pgstattuple | Shows tuple-level statistics |
plpgsql | Supports the PL/pgSQL procedural language |
postgres_fdw | Supports a foreign data wrapper for remote PostgreSQL servers |
refint | Provides functions for implementing referential integrity (checking foreign key constraints) |
seg | Provides a data type that represents line segments or floating-point intervals |
sslinfo | Provides information about the SSL certificate the client supplied to PostgreSQL |
tablefunc | Supports various functions that manipulate whole tables, including crosstab |
tcn | Notifies listeners when the contents of a linked table change |
tsm_system_rows | Limits the number of rows returned when sampling a table |
tsm_system_time | Limits table sampling by time, returning the samples that can be produced within a given number of milliseconds |
unaccent | Provides a text search dictionary that filters text to remove accents |
uuid-ossp | Generates UUIDs (Universally Unique Identifiers) |
xml2 | Provides XPath queries and XSLT |
Related articles
