Postgres Beyond the Basics: Exploring Extensibility

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 compatibility

postgres=# 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_catalog

This 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 language

The \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_details

As 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.

CategoryExtensionDescription
Data AnalyticsPostGISAdds support for geographic objects to PostgreSQL
PL/RAllows the use of the R programming language within PostgreSQL
pg_stat_statementsProvides statistics on SQL statements executed by a server
pgcryptoProvides cryptographic functions for PostgreSQL
Connection Poolingpgpool-IIProvides connection pooling and load balancing for PostgreSQL
pgbouncerA lightweight connection pooler for PostgreSQL
Full-Text Searchpg_trgmProvides fast full-text search capabilities for PostgreSQL
tsearch2An advanced full-text search module for PostgreSQL
MonitoringpgAdminA popular GUI administration tool for PostgreSQL
pgBadgerA PostgreSQL log analyzer
pgDashProvides real-time monitoring and alerting for PostgreSQL
ReplicationSlony-IProvides asynchronous replication for PostgreSQL
pglogicalProvides logical replication for PostgreSQL
pgEdgeProvides multi-master replication for PostgreSQL
SecuritypgAuditProvides auditing capabilities for PostgreSQL
pg_cronAllows scheduled execution of SQL queries and other commands
sepgsqlProvides mandatory access control for PostgreSQL
sslinfoProvides information on SSL/TLS connections to PostgreSQL
Text ProcessingunaccentProvides functions for removing accents from text
fuzzystrmatchProvides fuzzy string matching functions for PostgreSQL
citextProvides case-insensitive text types and functions for PostgreSQL

Stay tuned for this blog series, we will discuss these extensions and their use-cases.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>