Load Extension

Some extensions that use hook mechanism must be preloaded and restarted to take effect.
Module:
Tags:

After installing PostgreSQL extensions, you can view them in the pg_available_extensions view in PostgreSQL.

Aside from extensions written purely in SQL, most extensions provide a .so file, which is a dynamic shared library.

Most extensions do not require explicit loading and can be enabled simply with CREATE EXTENSION. However, a small subset of extensions use PostgreSQL’s hook mechanism. These extensions must be preloaded using the shared_preload_libraries parameter and require a PostgreSQL restart to take effect. Attempting to execute CREATE EXTENSION without preloading and restarting will result in an error.

In Pigsty, you can predefine the extensions that need to be loaded in the cluster by specifying them in the cluster’s pg_libs parameter, or modify the cluster configuration after initializing the cluster.

Extensions that Need Loading

In the Extension List, extensions marked with LOAD are the ones that need to be dynamically loaded and require a restart. These include:

Extension Alias Description
timescaledb timescaledb Enables scalable inserts and complex queries for time-series data (Apache 2 Edition)
pgml pgml PostgresML: Run AL/ML workloads with SQL interface
citus citus Distributed PostgreSQL as an extension
pg_squeeze pg_squeeze A tool to remove unused space from a relation.
pgautofailover pgautofailover auto failover for PostgreSQL
pg_prewarm pg_prewarm prewarm relation data
pg_stat_kcache pg_stat_kcache Kernel statistics gathering
bgw_replstatus bgw_replstatus Small PostgreSQL background worker to report whether a node is a replication master or standby
auto_explain auto_explain Provides a means for logging execution plans of slow statements automatically
pg_stat_statements pg_stat_statements track planning and execution statistics of all SQL statements executed
passwordcheck_cracklib passwordcheck Strengthen PostgreSQL user password checks with cracklib
supautils supautils Extension that secures a cluster on a cloud environment
pg_tde pg_tde pg_tde access method
pgaudit pgaudit provides auditing functionality
pg_snakeoil pg_snakeoil The PostgreSQL Antivirus
pgextwlist pgextwlist PostgreSQL Extension Whitelisting
sepgsql sepgsql label-based mandatory access control (MAC) based on SELinux security policy.
auth_delay auth_delay pause briefly before reporting authentication failure
passwordcheck passwordcheck Server side rollback at statement level for PostgreSQL like Oracle or DB2
pg_statement_rollback pg_statement_rollback SQL Server Transact SQL compatibility
babelfishpg_tsql babelfishpg_tsql

Loading Order

In shared_preload_libraries, if multiple extensions need to be loaded, they can be separated by commas, for example:

  • 'timescaledb, pg_stat_statements, auto_explain'

Note that both Citus and TimescaleDB explicitly require preloading in shared_preload_libraries, meaning they should be listed first.

While it is rare to use both Citus and TimescaleDB simultaneously, in such cases, it is recommended to list citus before timescaledb.

Pigsty, by default, will load two extensions: pg_stat_statements and auto_explain. These extensions are very useful for optimizing database performance and are strongly recommended.


Last modified 2024-08-15: udpate event (99fdeb8)