Config

Some extensions require preloading dynamic libraries or configuring parameters before use. This section describes how to configure extensions.

Preload Extensions

Most extensions can be enabled directly with CREATE EXTENSION after installation, but some extensions using PostgreSQL’s Hook mechanism require preloading.

Preloading is specified via the shared_preload_libraries parameter and requires a database restart to take effect.

Extensions Requiring Preload

Common extensions that require preloading:

For the complete list, see the Extension Catalog (marked with LOAD).

Preload Order

The loading order of extensions in shared_preload_libraries is important:

• timescaledb and citus must be placed first
• If using both, citus should come before timescaledb
• Statistics extensions should come after pg_stat_statements to use the same query_id

pg_libs: 'citus, timescaledb, pg_stat_statements, auto_explain'

Configure During Cluster Initialization

When creating a new cluster, use the pg_libs parameter to specify preloaded extensions:

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'
    pg_extensions: [ timescaledb, postgis, pgvector ]

The value of pg_libs will be written to shared_preload_libraries during cluster initialization.

Default Value

The default value of pg_libs is pg_stat_statements, auto_explain. These two Contrib extensions provide basic observability:

• pg_stat_statements: Track execution statistics of all SQL statements
• auto_explain: Automatically log execution plans for slow queries

Modify Configuration on Existing Cluster

For initialized clusters, use patronictl to modify shared_preload_libraries:

# Add timescaledb to preload libraries
pg edit-config pg-meta --force -p shared_preload_libraries='timescaledb, pg_stat_statements, auto_explain'

# Restart cluster to apply configuration
pg restart pg-meta

You can also directly modify postgresql.conf or use ALTER SYSTEM:

ALTER SYSTEM SET shared_preload_libraries = 'timescaledb, pg_stat_statements, auto_explain';

A PostgreSQL service restart is required after modification.

Extension Parameter Configuration

Many extensions have configurable parameters that can be set in the following locations:

During Cluster Initialization

Use the pg_parameters parameter to specify:

pg-meta:
  vars:
    pg_cluster: pg-meta
    pg_libs: 'pg_cron, pg_stat_statements, auto_explain'
    pg_parameters:
      cron.database_name: postgres           # Database used by pg_cron
      pg_stat_statements.track: all          # Track all statements
      auto_explain.log_min_duration: 1000    # Log queries exceeding 1 second

Runtime Modification

Use ALTER SYSTEM or patronictl:

-- Modify parameter
ALTER SYSTEM SET pg_stat_statements.track = 'all';

-- Reload configuration
SELECT pg_reload_conf();

# Modify using patronictl
pg edit-config pg-meta --force -p 'pg_stat_statements.track=all'

Important Notes

1. Preload errors prevent startup: If an extension in shared_preload_libraries doesn’t exist or fails to load, PostgreSQL will not start. Ensure extensions are properly installed before adding to preload.

2. Modification requires restart: Changes to shared_preload_libraries require restarting the PostgreSQL service to take effect.

3. Partial functionality available: Some extensions can be partially used without preloading, but full functionality requires preloading.

4. View current configuration: Use the following command to view current preload libraries:

SHOW shared_preload_libraries;