Skip to content

Managing PostgreSQL Extensions

Extension management - download, install, configure, enable, update, and remove extensions

Quick Start

Pigsty provides 575 extensions. Using extensions involves four steps: Download, Install, Configure, Enable.

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_extensions: [ postgis, timescaledb, pgvector ]           # <--- Install extension packages
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'    # <--- Configure preload extensions
    pg_databases:
      - name: meta
        extensions: [ postgis, timescaledb, vector ]            # <--- Enable in database
Script
bin/pgsql-ext <cls>           # Install extensions defined in config on <cls> cluster
bin/pgsql-ext <cls> [ext...]  # Install extensions specified on command line
Playbook
./pgsql.yml -l pg-meta -t pg_ext    # Use playbook to install extensions
Example
bin/pgsql-ext pg-meta                         # Install defined extensions on pg-meta cluster
bin/pgsql-ext pg-meta pg_duckdb pg_mooncake   # Install specified extensions

For complete extension reference, see Extensions. For available extensions, see Extension Catalog.

Action Command Description
Download Extensions ./infra.yml -t repo_build Download extensions to local repo
Install Extensions bin/pgsql-ext <cls> Install extension packages on cluster
Configure Extensions pg edit-config <cls> -p Add to preload libs (requires restart)
Enable Extensions psql -c 'CREATE EXT ...' Create extension objects in database
Update Extensions ALTER EXTENSION UPDATE Update packages and extension objects
Remove Extensions DROP EXTENSION Drop extension objects, uninstall pkgs
demo/pgsql-ext.cast

Install Extensions

Extensions defined in pg_extensions are auto-installed during PostgreSQL cluster creation in the pg_extension task.

To install extensions on an existing cluster, add extensions to all.children.<cls>.pg_extensions, then execute:

Script
bin/pgsql-ext <cls>   # Install extensions on <cls> cluster
Playbook
./pgsql.yml -l <cls> -t pg_extension   # Use Ansible playbook
Example
bin/pgsql-ext pg-meta    # Install extensions defined in config on pg-meta

Example: Install PostGIS, TimescaleDB and PGVector on cluster

#all.children.pg-meta.vars:
pg_extensions: [ postgis, timescaledb, pgvector ]

Result: Installs extension packages on all cluster nodes. Pigsty auto-translates package aliases to actual package names for OS and PG version.

Ensure repos available before install

Before installing, ensure nodes have correct repos configured - extensions downloaded to local repo, or upstream repos configured.

Manual Install

If you don’t want to use Pigsty config to manage extensions, pass extension list directly on command line:

Script
bin/pgsql-ext pg-meta pg_duckdb pg_mooncake   # Install specified extensions on pg-meta
Playbook
./pgsql.yml -l pg-meta -t pg_ext -e '{"pg_extensions": ["pg_duckdb", "pg_mooncake"]}'

You can also use pig package manager CLI to install extensions on single node, with auto package alias resolution.

pig install postgis timescaledb       # Install multiple extensions
pig install pgvector -v 18            # Install for specific PG major version

ansible pg-test -b -a 'pig install pg_duckdb'   # Batch install on cluster with Ansible

You can also use OS package manager directly (apt/dnf), but you must know the exact RPM/DEB package name for your OS/PG:

# EL systems (RHEL, Rocky, Alma, Oracle Linux)
sudo yum install -y pgvector_18*

# Debian / Ubuntu
sudo apt install -y postgresql-18-pgvector

Download Extensions

To install extensions, ensure node’s extension repos contain the extension:

Pigsty’s default config auto-downloads mainstream extensions during installation. For additional extensions, add to repo_extra_packages and rebuild repo:

repo_extra_packages: [ pgvector, postgis, timescaledb ]
Script
make repo         # Shortcut = repo-build + node-repo
make repo-build   # Rebuild Infra repo (download packages and deps)
make node-repo    # Refresh node repo cache, update Infra repo reference
Playbook
./deploy.yml -t repo_build,node_repo  # Execute both tasks at once
./infra.yml -t repo_build     # Re-download packages to local repo
./node.yml  -t node_repo      # Refresh node repo cache

Configure Repos

You can also let all nodes use upstream repos directly (not recommended for production), skipping download and installing from upstream extension repos:

./node.yml -t node_repo -e node_repo_modules=node,pgsql   # Add PGDG and Pigsty upstream repos

Configure Extensions

Some extensions require preloading to shared_preload_libraries, requiring database restart after modification.

Use pg_libs as its default value to configure preload extensions, but this only takes effect during cluster init - later modifications are ineffective.

pg-meta:
  vars:
    pg_cluster: pg-meta
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'   # Preload extensions
    pg_extensions: [ timescaledb, postgis, pgvector ]          # Install packages

For existing clusters, refer to Modify Config to modify shared_preload_libraries:

pg edit-config pg-meta --force -p shared_preload_libraries='timescaledb, pg_stat_statements, auto_explain'
pg restart pg-meta   # Modify pg-meta params and restart to apply

Ensure extension packages are correctly installed before adding preload config. If extension in shared_preload_libraries doesn’t exist or fails to load, PostgreSQL won’t start. Also, manage cluster config changes through Patroni - avoid using ALTER SYSTEM or pg_parameters to modify instance config separately. If primary and replica configs differ, it may cause startup failure or replication interruption.

Enable Extensions

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

Enable during cluster init

Declare extensions to enable in database definition via extensions array:

pg_databases:
  - name: meta
    extensions:
      - vector                             # Simple form
      - { name: postgis, schema: public }  # Specify schema

Manual enable

SQL
CREATE EXTENSION vector;                      -- Create extension
CREATE EXTENSION postgis SCHEMA public;       -- Specify schema
CREATE EXTENSION IF NOT EXISTS vector;        -- Idempotent creation
CREATE EXTENSION postgis_topology CASCADE;    -- Auto-install dependencies
psql
psql -d meta -c 'CREATE EXTENSION vector;'                  # Create extension in meta database
psql -d meta -c 'CREATE EXTENSION postgis SCHEMA public;'   # Specify schema
Playbook
# After modifying database definition, use playbook to enable extensions
bin/pgsql-db pg-meta meta    # Creating/modifying database auto-enables defined extensions

Result: Creates extension objects (functions, types, operators, index methods, etc.) in database, enabling use of extension features.

Update Extensions

Extension updates involve two layers: package update and extension object update.

Update packages

pig
pig update pgvector                           # Update extension with pig
yum
sudo yum update pgvector_18 # EL
apt
sudo apt upgrade postgresql-18-pgvector  # Debian/Ubuntu

Update extension objects

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

-- Update extension to latest version
ALTER EXTENSION vector UPDATE;

-- Update to specific version
ALTER EXTENSION vector UPDATE TO '0.8.1';
Update Notes

Backup database before updating extensions. Preloaded extensions may require PostgreSQL restart after update. Some extension version upgrades may be incompatible - check extension docs.

Remove Extensions

Removing extensions involves two layers: drop extension objects and uninstall packages.

Drop extension objects

DROP EXTENSION vector;              -- Drop extension
DROP EXTENSION vector CASCADE;      -- Cascade drop (drops dependent objects)

Remove from preload

For preloaded extensions, remove from shared_preload_libraries and restart:

pg edit-config pg-meta --force -p shared_preload_libraries='pg_stat_statements, auto_explain'
pg restart pg-meta   # Restart to apply config

Uninstall packages (optional)

pig
pig remove pgvector                           # Uninstall with pig
yum
sudo yum remove pgvector_18*                  # EL systems
apt
sudo apt remove postgresql-18-pgvector        # Debian/Ubuntu
CASCADE Warning

Using CASCADE to drop extensions also drops all objects depending on that extension (tables, indexes, views, etc.). Check dependencies before executing.

Query Extensions

Common SQL queries for extension info:

View enabled extensions

SELECT extname, extversion, nspname AS schema
FROM pg_extension e JOIN pg_namespace n ON e.extnamespace = n.oid
ORDER BY extname;

View available extensions

SELECT name, default_version, installed_version, comment
FROM pg_available_extensions
WHERE installed_version IS NOT NULL   -- Only show installed
ORDER BY name;

Check if extension is available

SELECT * FROM pg_available_extensions WHERE name = 'vector';

View extension dependencies

SELECT e.extname, d.refobjid::regclass AS depends_on
FROM pg_extension e
JOIN pg_depend d ON d.objid = e.oid
WHERE d.deptype = 'e' AND e.extname = 'postgis_topology';

View extension objects

SELECT classid::regclass, objid, deptype
FROM pg_depend
WHERE refobjid = (SELECT oid FROM pg_extension WHERE extname = 'vector');

psql shortcuts

\dx                    # List enabled extensions
\dx+ vector            # Show extension details

Add Repos

To install directly from upstream, manually add repos.

Using Pigsty playbook

./node.yml -t node_repo -e node_repo_modules=node,pgsql        # Add PGDG and Pigsty repos
./node.yml -t node_repo -e node_repo_modules=node,pgsql,local  # Including local repo

YUM repos (EL systems)

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

# 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

APT repos (Debian/Ubuntu)

curl -fsSL https://repo.pigsty.io/key | sudo gpg --dearmor -o /etc/apt/keyrings/pigsty.gpg
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 $(lsb_release -cs) main
EOF
sudo apt update

# China mainland mirror: replace repo.pigsty.io with repo.pigsty.cc

FAQ

Difference between extension name and package name

Name Description Example
Extension name Name used with CREATE EXTENSION vector
Package alias Standardized name in Pigsty config pgvector
Package name Actual OS package name pgvector_18* or postgresql-18-pgvector

Preloaded extension prevents startup

If extension in shared_preload_libraries doesn’t exist or fails to load, PostgreSQL won’t start. Solutions:

  1. Ensure extension package is correctly installed
  2. Or remove extension from shared_preload_libraries (edit /pg/data/postgresql.conf)

Extension dependencies

Some extensions depend on others, requiring sequential creation or using CASCADE:

CREATE EXTENSION postgis;                    -- Create base extension first
CREATE EXTENSION postgis_topology;           -- Then create dependent extension
-- Or
CREATE EXTENSION postgis_topology CASCADE;   -- Auto-create dependencies

Extension version incompatibility

View extension versions supported by current PostgreSQL:

SELECT * FROM pg_available_extension_versions WHERE name = 'vector';