Extensions

• 1: Quick Start
• 2: Introduction
• 3: Packages
• 4: Download
• 5: Install
• 6: Config
• 7: Create
• 8: Update
• 9: Remove
• 10: Default Extensions
• 11: Repository

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_databases:
      - name: meta
        extensions: [ postgis, timescaledb, vector ]   # Create: Create extensions in database
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain' # Config: Preload extension libraries
    pg_extensions: [ postgis, timescaledb, pgvector ]  # Install: Install extension packages

1 - Quick Start

Using extensions in Pigsty requires four steps: Download, Install, Config, and Create.

1. Download: Download extension packages to the local repository (Pigsty has already downloaded mainstream extensions by default)
2. Install: Install extension packages on cluster nodes
3. Config: Some extensions need to be preloaded or configured with parameters
4. Create: Execute CREATE EXTENSION in the database to create the extension

Declarative Configuration

Declare extensions in the Pigsty configuration manifest, and they will be automatically installed and created during cluster initialization:

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_databases:
      - name: meta
        extensions: [ postgis, timescaledb, vector ]   # Create extensions in database
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain' # Preload extension libraries
    pg_extensions: [ postgis, timescaledb, pgvector ]  # Install extension packages

After executing ./pgsql.yml to initialize the cluster, the three extensions postgis, timescaledb, and vector will be available in the meta database.

Imperative Operations

For existing clusters, you can add extensions using command-line methods:

# 1. Install extension packages
./pgsql.yml -l pg-meta -t pg_extension -e '{"pg_extensions":["pgvector"]}'

# 2. Preload extension (if needed, requires restart after modification)
pg edit-config pg-meta --force -p shared_preload_libraries='timescaledb, pg_stat_statements, auto_explain'

# 3. Create extension in database
psql -d meta -c 'CREATE EXTENSION vector;'

You can also use the pig package manager to install directly:

pig install pgvector        # Install extension package
pig extension create vector  # Create extension in database

Process Quick Reference

For detailed instructions, please refer to each subsection: Download, Install, Config, Create

2 - Introduction

Extensions are the soul of PostgreSQL. Pigsty includes 440+ pre-compiled, out-of-the-box extension plugins, fully unleashing PostgreSQL’s potential.

What are Extensions

PostgreSQL extensions are a modular mechanism that allows enhancing database functionality without modifying the core code. An extension typically consists of three parts:

• Control file (.control): Required, contains extension metadata
• SQL scripts (.sql): Optional, defines functions, types, operators, and other database objects
• Dynamic library (.so): Optional, provides high-performance functionality implemented in C

Extensions can add to PostgreSQL: new data types, index methods, functions and operators, foreign data access, procedural languages, performance monitoring, security auditing, and more.

Core Extensions

Among the extensions included in Pigsty, the following are most representative:

Most extensions can coexist and even be combined, creating synergistic effects far greater than the sum of their parts.

Extension Categories

Pigsty organizes extensions into 16 categories:

You can batch install an entire category of extensions using category aliases, for example: pg_extensions: [ pgsql-gis, pgsql-rag ].

Predefined Extension Stacks

Pigsty provides several predefined extension stacks for convenient scenario-based selection:

Simply use these names in pg_extensions to install the entire stack.

Extension Resources

• Extension Catalog: Browse detailed information about all available extensions
• Extension Repository: Pigsty extension software repository
• pig Package Manager: Command-line extension management tool
• GitHub Pigsty: Pigsty source code repository

3 - Packages

Pigsty uses a package alias mechanism to simplify extension installation and management.

Package Alias Mechanism

Managing extensions involves multiple layers of name mapping:

Pigsty provides a package alias abstraction layer, so users don’t need to worry about specific RPM/DEB package names:

pg_extensions: [ pgvector, postgis, timescaledb ]  # Use package aliases

Pigsty automatically translates to the correct package names based on the operating system and PostgreSQL version.

Note: When using CREATE EXTENSION, you use the extension name (e.g., vector), not the package alias (pgvector).

Category Aliases

All extensions are organized into 16 categories, which can be batch installed using category aliases:

# Use generic category aliases (auto-adapt to current PG version)
pg_extensions: [ pgsql-gis, pgsql-rag, pgsql-fts ]

# Or use version-specific category aliases
pg_extensions: [ pg18-gis, pg18-rag, pg18-fts ]

Except for the olap category, all category extensions can be installed simultaneously. Within the olap category, there are conflicts: pg_duckdb and pg_mooncake are mutually exclusive.

Category List

Browse Extension Catalog

You can browse detailed information about all available extensions on the Pigsty Extension Catalog website, including:

• Extension name, description, version
• Supported PostgreSQL versions
• Supported OS distributions
• Installation methods, preloading requirements
• License, source repository

4 - Download

Before installing extensions, ensure that extension packages are downloaded to the local repository or available from upstream.

Default Behavior

Pigsty automatically downloads mainstream extensions available for the default PostgreSQL version to the local software repository during installation.

Benefits of using a local repository:

• Accelerated installation, avoiding repeated downloads
• Reduced network traffic consumption
• Improved delivery reliability
• Ensured version consistency

Download New Extensions

To download additional extensions, add them to repo_extra_packages and rebuild the repository:

all:
  vars:
    repo_extra_packages: [ pgvector, postgis, timescaledb, pg_duckdb ]

# Re-download packages to local repository
./infra.yml -t repo_build

# Refresh package source cache on all nodes
./node.yml -t node_repo

Using Upstream Repositories

You can also install directly from internet upstream repositories without pre-downloading:

# Add upstream software sources on nodes
./node.yml -t node_repo -e node_repo_modules=node,pgsql

This approach is suitable for:

• Quick testing of latest versions
• Installing rare extensions
• Environments with good network conditions

But may face:

• Network instability affecting installation
• Version inconsistency risks

Extension Sources

Extension packages come from two main sources:

The Pigsty repository only includes extensions not present in the PGDG repository. Once an extension enters the PGDG repository, the Pigsty repository will remove it or keep it consistent.

Repository URLs:

• PGDG YUM: https://download.postgresql.org/pub/repos/yum/
• PGDG APT: https://apt.postgresql.org/pub/repos/apt/
• Pigsty YUM: https://repo.pigsty.io/yum/
• Pigsty APT: https://repo.pigsty.io/apt/

For detailed repository configuration, see Extension Repository.

5 - Install

Pigsty uses the operating system’s package manager (yum/apt) to install extension packages.

Related Parameters

Two parameters are used to specify extensions to install:

pg_packages is typically used to specify base components needed by all clusters (PostgreSQL kernel, Patroni, pgBouncer, etc.) and essential extensions.

pg_extensions is used to specify extensions needed by specific clusters.

pg_packages:                           # Global base packages
  - pgsql-main pgsql-common
pg_extensions:                         # Cluster extensions
  - postgis timescaledb pgvector

Install During Cluster Initialization

Declare extensions in cluster configuration, and they will be automatically installed during initialization:

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

When executing ./pgsql.yml to initialize the cluster, extensions will be automatically installed.

Install Extensions on Existing Cluster

For initialized clusters, there are multiple ways to install extensions:

Using Pigsty Playbook

# Install using playbook after modifying configuration
./pgsql.yml -l pg-meta -t pg_extension

# Or specify extensions directly on command line
./pgsql.yml -l pg-meta -t pg_extension -e '{"pg_extensions":["pg_duckdb"]}'

Using pig Package Manager

# Install extension using pig
pig install pg_duckdb

# Batch install
ansible pg-meta -b -a 'pig install pg_duckdb pgvector'

Using Package Manager Directly

# EL systems
sudo yum install -y pg_duckdb_18*

# Debian/Ubuntu systems
sudo apt install -y postgresql-18-pg-duckdb

Using Package Aliases

Pigsty supports using standardized package aliases, automatically translating to package names for the corresponding PG version:

pg_extensions:
  - pgvector           # Auto-translates to pgvector_18* (EL) or postgresql-18-pgvector (Debian)
  - postgis            # Auto-translates to postgis36_18* (EL) or postgresql-18-postgis-3* (Debian)
  - pgsql-gis          # Category alias, installs entire GIS category of extensions

You can also use raw package names directly:

pg_extensions:
  - pgvector_18*                    # EL system raw package name
  - postgresql-18-pgvector          # Debian system raw package name

For package alias definitions, see:

• EL8 Extension List
• EL9 Extension List
• D12 Extension List
• U22 Extension List
• U24 Extension List

Verify Installation

After installation, verify in the database:

-- Check installed extensions
SELECT * FROM pg_available_extensions WHERE name = 'vector';

-- Check if extension files exist
\dx

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

7 - Create

After installing extension packages, you need to execute CREATE EXTENSION in the database to use extension features.

View Available Extensions

After installing extension packages, you can view available extensions:

-- View all available extensions
SELECT * FROM pg_available_extensions;

-- View specific extension
SELECT * FROM pg_available_extensions WHERE name = 'vector';

-- View enabled extensions
SELECT * FROM pg_extension;

Create Extensions

Use CREATE EXTENSION to enable extensions in the database:

-- Create extension
CREATE EXTENSION vector;

-- Create extension in specific schema
CREATE EXTENSION postgis SCHEMA public;

-- Automatically install dependent extensions
CREATE EXTENSION postgis_topology CASCADE;

-- Create if not exists
CREATE EXTENSION IF NOT EXISTS vector;

Note: CREATE EXTENSION uses the extension name (e.g., vector), not the package alias (pgvector).

Create During Cluster Initialization

Declare extensions in pg_databases, and they will be automatically created during cluster initialization:

pg-meta:
  vars:
    pg_cluster: pg-meta
    pg_databases:
      - name: meta
        extensions:
          - { name: vector }                         # Use default schema
          - { name: postgis, schema: public }        # Specify schema
          - { name: pg_stat_statements, schema: monitor }

Pigsty will automatically execute CREATE EXTENSION after database creation.

Extensions Requiring Preload

Some extensions must be added to shared_preload_libraries and restarted before creation:

pg-meta:
  vars:
    pg_cluster: pg-meta
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'
    pg_databases:
      - name: meta
        extensions:
          - { name: timescaledb }  # Requires preload

If you try to create without preloading, you will receive an error message.

Common extensions requiring preload: timescaledb, citus, pg_cron, pg_net, pgaudit, etc. See Configure Extensions.

Extension Dependencies

Some extensions depend on other extensions and need to be created in order:

-- postgis_topology depends on postgis
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

-- Or use CASCADE to automatically install dependencies
CREATE EXTENSION postgis_topology CASCADE;

Extensions Not Requiring Creation

A few extensions don’t provide SQL interfaces and don’t need CREATE EXTENSION:

These extensions can be used immediately after installation, for example:

-- Create logical replication slot using wal2json
SELECT * FROM pg_create_logical_replication_slot('test_slot', 'wal2json');

View Extension Information

-- View extension details
\dx+ vector

-- View objects contained in extension
SELECT * FROM pg_extension_config_dump('vector');

-- View extension version
SELECT extversion FROM pg_extension WHERE extname = 'vector';

8 - Update

Extension updates involve two levels: package updates (operating system level) and extension object updates (database level).

Update Packages

Use package managers to update extension packages:

# EL systems
sudo yum update pgvector_18*

# Debian/Ubuntu systems
sudo apt update && sudo apt upgrade postgresql-18-pgvector

Batch update using Pigsty:

# Update extension packages for specified cluster
./pgsql.yml -l pg-meta -t pg_extension -e '{"pg_extensions":["pgvector"]}'

# Using pig package manager
pig update pgvector

Update Extension Objects

After package updates, extension objects in the database may need to be synchronized.

View Updatable Extensions

-- View installed extensions and their versions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

-- View upgradable extensions
SELECT name, installed_version, default_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL
  AND installed_version <> default_version;

Execute Extension Update

-- Update to latest version
ALTER EXTENSION pgvector UPDATE;

-- Update to specific version
ALTER EXTENSION pgvector UPDATE TO '0.8.0';

View Update Paths

-- View available upgrade paths for extension
SELECT * FROM pg_extension_update_paths('pgvector');

Important Notes

1. Backup first: Backup the database before updating extensions, especially for extensions involving data type changes.

2. Check compatibility: Some extension major version upgrades may be incompatible. Consult the extension’s upgrade documentation.

3. Preloaded extensions: If updating a preloaded extension (like timescaledb), a database restart may be required after the update.

4. Dependencies: If other extensions depend on the updated extension, update them in dependency order.

5. Replication environments: In master-slave replication environments, test updates on slaves first, then update the master after confirmation.

Common Issues

Update Failure

If ALTER EXTENSION UPDATE fails, it may be because:

• No available upgrade path
• Extension is in use
• Insufficient permissions

-- View extension dependencies
SELECT * FROM pg_depend WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'pgvector');

Rollback Update

PostgreSQL extensions typically don’t support direct rollback. To rollback:

1. Restore from backup
2. Or: Uninstall new version extension, install old version package, recreate extension

9 - Remove

Removing extensions involves two levels: dropping extension objects (database level) and uninstalling packages (operating system level).

Drop Extension Objects

Use DROP EXTENSION to remove extensions from the database:

-- Drop extension
DROP EXTENSION pgvector;

-- If there are dependent objects, cascade delete is required
DROP EXTENSION pgvector CASCADE;

Warning: CASCADE will drop all objects that depend on this extension (tables, functions, views, etc.). Use with caution.

Check Extension Dependencies

It’s recommended to check dependencies before dropping:

-- View objects that depend on an extension
SELECT
    classid::regclass,
    objid,
    deptype
FROM pg_depend
WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'pgvector');

-- View tables using extension types
SELECT
    c.relname AS table_name,
    a.attname AS column_name,
    t.typname AS type_name
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_type t ON a.atttypid = t.oid
WHERE t.typname = 'vector';

Remove Preload

If the extension is in shared_preload_libraries, it must be removed from the preload list after dropping:

# Modify shared_preload_libraries, remove extension
pg edit-config pg-meta --force -p shared_preload_libraries='pg_stat_statements, auto_explain'

# Restart to apply configuration
pg restart pg-meta

Uninstall Packages

After dropping the extension from the database, you can optionally uninstall the package:

# EL systems
sudo yum remove pgvector_18*

# Debian/Ubuntu systems
sudo apt remove postgresql-18-pgvector

# Using pig package manager
pig remove pgvector

Typically keeping the package doesn’t cause issues. Only uninstall when you need to free disk space or resolve conflicts.

Important Notes

1. Data loss risk: Using CASCADE will drop dependent objects, potentially causing data loss.

2. Application compatibility: Ensure applications no longer use the extension’s functionality before dropping.

3. Preload order: If dropping a preloaded extension, be sure to also remove it from shared_preload_libraries, otherwise the database may fail to start.

4. Master-slave environments: In replication environments, DROP EXTENSION automatically replicates to slaves.

Operation Sequence

Complete extension removal workflow:

# 1. Check dependencies
psql -d mydb -c "SELECT * FROM pg_depend WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'pgvector');"

# 2. Drop extension from database
psql -d mydb -c "DROP EXTENSION pgvector;"

# 3. If it's a preloaded extension, remove from shared_preload_libraries
pg edit-config pg-meta --force -p shared_preload_libraries='pg_stat_statements, auto_explain'

# 4. Restart database (if preload configuration was modified)
pg restart pg-meta

# 5. Optional: Uninstall package
sudo yum remove pgvector_18*

10 - Default Extensions

Pigsty installs and enables some core extensions by default when initializing PostgreSQL clusters.

Default Installed Extensions

Extensions installed by default via pg_packages:

Extensions optionally installed via pg_extensions (commented by default):

Default Enabled Extensions

Extensions enabled by default in all databases via pg_default_extensions:

These extensions provide basic monitoring, operations, and feature enhancement capabilities.

Default Preloaded Extensions

Extensions preloaded by default into shared_preload_libraries via pg_libs:

These two extensions provide basic observability and are strongly recommended to keep.

Customize Default Extensions

You can customize default installed and enabled extensions by modifying configuration parameters:

all:
  vars:
    # Modify default extension packages
    pg_packages:
      - pgsql-main pgsql-common
      - pg_repack_$v* wal2json_$v*

    # Modify default installed extensions
    pg_extensions: [ postgis, timescaledb, pgvector ]

    # Modify default preloaded extensions
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'

    # Modify default enabled extensions
    pg_default_extensions:
      - { name: pg_stat_statements, schema: monitor }
      - { name: pg_repack }
      # ... add more

For detailed extension usage, please refer to:

• Quick Start: Overview of the extension usage process
• Extension Introduction: Core concepts of extensions
• Install Extensions: How to install extensions
• Configure Extensions: Preloading and parameter configuration
• Create Extensions: Creating extensions in databases

11 - Repository

Pigsty provides supplementary extension repositories, offering additional extension packages on top of the PGDG official repository.

YUM Repository

Applicable to EL 7/8/9/10 and compatible systems (RHEL, Rocky, AlmaLinux, CentOS, etc.).

Add Repository

# Add GPG public key
curl -fsSL https://repo.pigsty.io/key | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-pigsty >/dev/null

# Add repository configuration
curl -fsSL https://repo.pigsty.io/yum/repo | sudo tee /etc/yum.repos.d/pigsty.repo >/dev/null

# Refresh cache
sudo yum makecache

China Mainland Mirror

curl -fsSL https://repo.pigsty.cc/key | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-pigsty >/dev/null
curl -fsSL https://repo.pigsty.cc/yum/repo | sudo tee /etc/yum.repos.d/pigsty.repo >/dev/null

Repository URLs

• International: https://repo.pigsty.io/yum/
• China: https://repo.pigsty.cc/yum/

APT Repository

Applicable to Debian 11/12/13 and Ubuntu 22.04/24.04 and compatible systems.

Add Repository

# Add GPG public key
curl -fsSL https://repo.pigsty.io/key | sudo gpg --dearmor -o /etc/apt/keyrings/pigsty.gpg

# Get distribution codename and add repository
distro_codename=$(lsb_release -cs)
sudo tee /etc/apt/sources.list.d/pigsty.list > /dev/null <<EOF
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.io/apt/infra generic main
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.io/apt/pgsql ${distro_codename} main
EOF

# Refresh cache
sudo apt update

China Mainland Mirror

curl -fsSL https://repo.pigsty.cc/key | sudo gpg --dearmor -o /etc/apt/keyrings/pigsty.gpg

distro_codename=$(lsb_release -cs)
sudo tee /etc/apt/sources.list.d/pigsty.list > /dev/null <<EOF
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.cc/apt/infra generic main
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.cc/apt/pgsql/${distro_codename} ${distro_codename} main
EOF

Repository URLs

• International: https://repo.pigsty.io/apt/
• China: https://repo.pigsty.cc/apt/

GPG Signature

All packages are signed with GPG:

• Fingerprint: 9592A7BC7A682E7333376E09E7935D8DB9BD8B20
• Short ID: B9BD8B20

Repository Policy

The Pigsty repository follows these principles:

1. Supplementary: Only includes extensions not present in the PGDG repository
2. Consistency: Once an extension enters the PGDG repository, the Pigsty repository will remove it or keep it consistent
3. Compatibility: Supports multiple major versions of PostgreSQL 13-18
4. Multi-platform: Supports x86_64 and aarch64 architectures

Related Resources

• Pigsty Extension Catalog: Browse all available extensions
• PGDG YUM Repository
• PGDG APT Repository