Introduction:
Postgres extensions are modules that can be added to the Postgres database management system to extend its functionality. These extensions can provide additional features, data types, operators, and functions that are not available in the core Postgres system. Examples of popular Postgres extensions include PostGIS for geographic data, pgAdmin for database administration, and PL/Python for writing database functions in Python. Postgres extensions can be installed and managed using the Postgres package manager, and are typically open-source and community-driven. The use of extensions can help developers and organizations customize Postgres to better suit their needs, and can facilitate the development of complex database applications.
The functionality of PostgreSQL can be enhanced through the use of extensions
Install & Compile an Extension:
To compile the extension, you can use the pg_config tool to get the necessary compiler flags and include directories:
postgres@pg15node:~ $ pg_config --libdir/usr/pgsql-15/lib
postgres@pg15node:~ $ pg_config --includedir/usr/pgsql-15/include
postgres@pg15node:~ $ pg_config --pkglibdir/usr/pgsql-15/lib
postgres@pg15node:~ $ pg_config --pgxs/usr/pgsql-15/lib/pgxs/src/makefiles/pgxs.mk
postgres@pg15node:~ $ pg_config --sharedir/usr/pgsql-15/share
Extensions in PostgreSQL can be written in a variety of programming languages, including C, SQL, and PL/Python. When an extension is created in PostgreSQL, it consists of a set of SQL files and C source files that are used to build the extension. The C source files are compiled into a shared object file with the .so extension, which is loaded by the PostgreSQL server at runtime.
Why are .so files important in PostgreSQL extensions?
The .so (shared object) file is a crucial component of PostgreSQL extensions that enables the extension code to be loaded and executed by the server at runtime. It provides a level of abstraction between the extension code and the PostgreSQL server, allowing for efficient memory usage and dynamic linking with other system resources. Additionally, it allows for easier maintenance and updates of the extension code and facilitates debugging of the code.
Here are examples of the error messages you might encounter when a PostgreSQL extension is missing its .so file:
osdb_ext_test=# CREATE EXTENSION pg_trgm;
ERROR: could not load library "/usr/pgsql-15/lib/pg_trgm.so": /usr/pgsql-15/lib/pg_trgm.so: undefined symbol: my_func
ERROR: extension "pg_trgm" is not available
DETAIL: Could not open extension control file "/usr/pgsql-15/share/extension/pg_trgm.control": No such file or directory.
HINT: The extension must first be installed on the system where PostgreSQL is running.
In this example, we are trying to create the “bar” extension, but the server is unable to load the “bar.so” file because it contains an undefined symbol called “my_func”.
To resolve this issue, you will need to ensure that the .so file is present in the correct directory and that all required dependencies are installed and available to the server. You may also need to re-compile the extension with the correct dependencies or architecture.
pg_available_extensions is a view in PostgreSQL that provides information about the extensions that are available to be installed in the current database. This view contains information such as the name of the extension, its default version, whether it is installed by default, and whether it is available in the current version of PostgreSQL.
osdb_ext_test=# select * from pg_available_extensions;
name | default_version | installed_version | comment
------------------+-----------------+-------------------+----------------------------------------
plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
pglogical | 2.4.2 | | PostgreSQL Logical Replication
pg_cron | 1.5 | | Job scheduler for PostgreSQL
pglogical_origin | 1.0.0 | | Dummy extension for compatibilitypostgres=# SELECT
name,
default_version,
installed_version
FROM
pg_available_extensions
WHERE
installed_version IS NULL;
name | default_version | installed_version
------------------+-----------------+-------------------
pglogical | 2.4.2
pg_cron | 1.5 |
pglogical_origin | 1.0.0 |postgres=# SELECT
name,
version,
installed,
superuser,
trusted,
schema
FROM
pg_available_extension_versions
WHERE
name = 'pg_cron';
name | version | installed | superuser | trusted | schema
---------+---------+-----------+-----------+---------+------------
pg_cron | 1.0 | f | t | f | pg_catalog
pg_cron | 1.1 | f | t | f | pg_catalog
pg_cron | 1.2 | f | t | f | pg_catalog
pg_cron | 1.3 | f | t | f | pg_catalog
pg_cron | 1.4 | f | t | f | pg_catalog
pg_cron | 1.4-1 | f | t | f | pg_catalog
pg_cron | 1.5 | f | t | f | pg_catalogThis will return a list of all the available extensions that are not installed by default, along with their default version and other information.
In addition to pg_available_extensions, there are other views and functions in PostgreSQL that can provide information about extensions, such as pg_extension, pg_extension_config_dump, and pg_extension_update_paths.
View Installed Extensions:
To view the extensions that are currently installed on your PostgreSQL Managed Database, run the \dx command at the prompt.
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
-----------+---------+------------+--------------------------------
pg_cron | 1.5 | pg_catalog | Job scheduler for PostgreSQL
pglogical | 2.4.2 | pglogical | PostgreSQL Logical Replication
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural languageThe \dx+ command is an extension of the \dx command, and provides additional details about the installed extensions, including their object file location, the configuration options used to build them, and their dependencies on other extensions.
postgres=# \dx+ pg_cron
Objects in extension “pg_cron“
Object description
----------------------------------------------------------------------
function cron.alter_job(bigint,text,text,text,text,boolean)
function cron.job_cache_invalidate()
function cron.schedule_in_database(text,text,text,text,text,boolean)
function cron.schedule(text,text)
function cron.schedule(text,text,text)
function cron.unschedule(bigint)
function cron.unschedule(text)
schema cron
sequence cron.jobid_seq
sequence cron.runid_seq
table cron.job
table cron.job_run_detailsAs you can see, the \dx+ command provides additional information about the installed extensions, including the path to their object files ($libdir/plpgsql and $libdir/postgis), which can be useful for debugging and troubleshooting.
I have categorized some of my favorite PostgreSQL extensions:
Some of these extensions may require to be included in the shared_preload_libraries server parameter as a comma-separated list to be preloaded at the server start. Although most of the extensions are included in the contrib module of source code.
| Category | Extension | Description |
| Data Analytics | PostGIS | Adds support for geographic objects to PostgreSQL |
| PL/R | Allows the use of the R programming language within PostgreSQL | |
| pg_stat_statements | Provides statistics on SQL statements executed by a server | |
| pgcrypto | Provides cryptographic functions for PostgreSQL | |
| Connection Pooling | pgpool-II | Provides connection pooling and load balancing for PostgreSQL |
| pgbouncer | A lightweight connection pooler for PostgreSQL | |
| Full-Text Search | pg_trgm | Provides fast full-text search capabilities for PostgreSQL |
| tsearch2 | An advanced full-text search module for PostgreSQL | |
| Monitoring | pgAdmin | A popular GUI administration tool for PostgreSQL |
| pgBadger | A PostgreSQL log analyzer | |
| pgDash | Provides real-time monitoring and alerting for PostgreSQL | |
| Replication | Slony-I | Provides asynchronous replication for PostgreSQL |
| pglogical | Provides logical replication for PostgreSQL | |
| pgEdge | Provides multi-master replication for PostgreSQL | |
| Security | pgAudit | Provides auditing capabilities for PostgreSQL |
| pg_cron | Allows scheduled execution of SQL queries and other commands | |
| sepgsql | Provides mandatory access control for PostgreSQL | |
| sslinfo | Provides information on SSL/TLS connections to PostgreSQL | |
| Text Processing | unaccent | Provides functions for removing accents from text |
| fuzzystrmatch | Provides fuzzy string matching functions for PostgreSQL | |
| citext | Provides case-insensitive text types and functions for PostgreSQL |
Stay tuned for this blog series, we will discuss these extensions and their use-cases.