Config

Some extensions require you to preload dynamic libraries or configure parameters before use. This section gives the configuration procedures.

Preload Extensions

After installation, you can enable most extensions with CREATE EXTENSION. Some extensions use PostgreSQL’s Hook mechanism and require preloading.

Set shared_preload_libraries to specify the libraries to preload. A database restart is required to apply the change.

Extensions Requiring Preload

Common extensions that require preloading:

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

Preload Order

Use the required loading order in shared_preload_libraries:

• Put timescaledb and citus first.
• If you use 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

For a new cluster, use pg_libs to specify the extensions to preload:

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 ]

During cluster initialization, Pigsty writes the value of pg_libs to shared_preload_libraries.

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';

After the change, restart the PostgreSQL service.

Extension Parameter Configuration

Many extensions have parameters that you can configure in the following locations:

During Cluster Initialization

Use pg_parameters to set extension parameters:

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 does not exist or fails to load, PostgreSQL will not start. Install each extension correctly before you add it to the preload list.

2. Modification requires restart: After you change shared_preload_libraries, restart the PostgreSQL service to apply the change.

3. Partial functionality available: Some extensions provide limited functionality without preloading. Full functionality requires preloading.

4. View current configuration: Run this command to show the current preload libraries:

SHOW shared_preload_libraries;