Skip to content

This is the multi-page printable view of this section. .

Return to the regular view of this page.

Extensions

Use PostgreSQL extensions with Pigsty

Pigsty provides 600 packaged extensions that are ready to use. The extensions cover 16 major categories, including time-series, geospatial, vector, full-text search, analytics, and feature enhancements.

To use extensions in Pigsty, complete four steps: Download, Install, Config/Load, and Create.

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
Pigsty PostgreSQL extension ecosystem

1 - Quick Start

Four-step process overview for using extensions

To use extensions in Pigsty, complete four steps: Download, Install, Config, and Create.

  1. Download: Download extension packages to the local repository. The default local repository only guarantees the base kernel and pgsql-main package set.
  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 inventory. During cluster initialization, Pigsty installs their packages and creates the extensions automatically:

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

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


Imperative Operations

For an existing cluster, you can use commands to add extensions:

# 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 install extension packages with the pig package manager. Then run CREATE EXTENSION in the database:

pig install pgvector                         # Install extension package
psql -d meta -c 'CREATE EXTENSION vector;'   # Create extension in database

Process Quick Reference

StepParameter/CommandDescription
Downloadrepo_extra_packagesSpecify extension packages to download to local repository
Installpg_extensionsSpecify extension packages to install on cluster
Configpg_libsPreload extensions to shared_preload_libraries
Createpg_databases.extensionsAutomatically execute CREATE EXTENSION in database

For detailed instructions, see each subsection: Download, Install, Config, Create

2 - Introduction

Core concepts of PostgreSQL extensions and the Pigsty extension ecosystem

Pigsty includes 600 precompiled PostgreSQL extensions that are ready to use.


What are Extensions

PostgreSQL extensions add database functionality without changes to 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 data types, index methods, functions, and operators to PostgreSQL. They can also add foreign data access, procedural languages, performance monitoring, security auditing, and other features.


Core Extensions

The following table shows representative extensions that Pigsty includes:

ExtensionDescription
PostGISGeospatial data types and indexes, de facto GIS standard
TimescaleDBTime-series database with continuous aggregates, columnar storage, auto-compression
PGVectorVector data type with HNSW/IVFFlat indexes, essential for AI applications
CitusDistributed database with horizontal sharding capabilities
pg_duckdbEmbedded DuckDB analytical engine for OLAP acceleration
pg_searchParadeDB search extension, providing BM25 and full-text search capabilities
Apache AGEGraph database supporting OpenCypher query language
pg_graphqlNative GraphQL query support

Most extensions can coexist. You can combine their features to perform tasks that one extension cannot perform alone.


Extension Categories

Pigsty organizes extensions into 16 categories:

CategoryAliasDescriptionTypical Extensions
Time-seriestimeTime-series data processingtimescaledb, pg_cron, periods
GeospatialgisGeospatial datapostgis, h3, pgrouting
VectorragVector retrieval and AIpgvector, vchord, pg_vectorize
SearchftsFull-text searchpgroonga, zhparser, pg_bigm
AnalyticsolapOLAP and analyticspg_duckdb, pg_mooncake, citus
FeaturefeatFeature enhancementsage, pg_graphql, hll, rum
LanguagelangProcedural languagesplpython3u, pljava, plv8
TypetypeData typeshstore, ltree, ip4r
UtilityutilUtility toolshttp, pg_net, pgjwt
FunctionfuncFunction librariespg_uuidv7, topn, tdigest
AdminadminOperations managementpg_repack, pg_squeeze, pgagent
StatstatMonitoring statisticspg_stat_statements, pg_qualstats, auto_explain
SecuritysecSecurity auditingpgaudit, pgsodium, pg_tde
FDWfdwForeign data accesspostgres_fdw, mysql_fdw, oracle_fdw
CompatibilitysimDatabase compatibilityorafce, babelfish
ETLetlData synchronizationpglogical, wal2json, decoderbufs

Use category aliases to install all extensions in a category together. For example: pg_extensions: [ pgsql-gis, pgsql-rag ].


Predefined Extension Stacks

Pigsty provides predefined extension stacks for different uses:

StackIncluded Extensions
gis-stackpostgis, pgrouting, pointcloud, h3, q3c, ogr_fdw
rag-stackpgvector, vchord, pgvectorscale, pg_similarity, pg_tiktoken
fts-stackpgroonga, pg_bigm, zhparser, hunspell
olap-stackpg_duckdb, pg_mooncake, timescaledb, pg_partman, plproxy
feat-stackage, hll, rum, pg_graphql, pg_jsonschema, jsquery
stat-stackpg_show_plans, pg_stat_kcache, pg_qualstats, pg_wait_sampling
supa-stackpg_graphql, pg_jsonschema, wrappers, pgvector, pgsodium, vault

To install an entire stack, add its name to pg_extensions.


Extension Resources

3 - Packages

Extension package aliases and category naming conventions

Pigsty uses package aliases to simplify extension installation and management.


Package Alias Mechanism

Extensions have different names at different layers:

LayerExample pgvectorExample postgis
Extension Namevectorpostgis, postgis_topology, …
Package Aliaspgvectorpostgis
RPM Package Namepgvector_18postgis36_18*
DEB Package Namepostgresql-18-pgvectorpostgresql-18-postgis-3*

Use a package alias to specify an extension without its RPM or DEB package name:

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

Pigsty converts each alias to the correct package names for 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

Pigsty organizes all extensions into 16 categories. Use category aliases to install extensions from a category together:

# 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 ]

You can install all category extensions together, except for the olap category. In the olap category, pg_duckdb and pg_mooncake are mutually exclusive.


Category List

CategoryDescriptionTypical Extensions
timeTime-seriestimescaledb, pg_cron, periods
gisGeospatialpostgis, h3, pgrouting
ragVector/RAGpgvector, pgml, vchord
ftsFull-text Searchpg_trgm, zhparser, pgroonga
olapAnalyticscitus, pg_duckdb, pg_mooncake
featFeatureage, pg_graphql, rum
langLanguageplpython3u, pljava, plv8
typeData Typehstore, ltree, citext
utilUtilityhttp, pg_net, pgjwt
funcFunctionpgcrypto, uuid-ossp, pg_uuidv7
adminAdminpg_repack, pgagent, pg_squeeze
statStatisticspg_stat_statements, pg_qualstats, auto_explain
secSecuritypgaudit, pgcrypto, pgsodium
fdwForeign Data Wrapperpostgres_fdw, mysql_fdw, oracle_fdw
simCompatibilityorafce, babelfishpg_tds
etlData/ETLpglogical, wal2json, decoderbufs

Browse Extension Catalog

Use the Pigsty Extension Catalog to find these details for all available extensions:

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

4 - Download

Download extension packages to the local software repository

Before you install extensions, make sure that their packages are available from the local repository or an upstream repository.


Default Behavior

By default, Pigsty downloads the base PostgreSQL 18 kernel packages to the local software repository. The default extra download set is repo_extra_packages_default: [ pgsql-main ]. This set includes the PostgreSQL kernel, client, and procedural languages. It also includes basic extension packages such as pg_repack, wal2json, and pgvector.

If you need other extensions from the 600-extension catalog, add them to repo_extra_packages. Pigsty does not download every extension to the local repository by default.

A local repository provides these benefits:

  • Faster installation without repeated downloads
  • Less network traffic
  • More reliable delivery
  • Consistent package versions

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 packages directly from upstream repositories on the internet. You do not need to download them in advance:

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

Use this method for:

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

This method has these risks:

  • An unstable network can interrupt installation.
  • Package versions can differ.

Extension Sources

Extension packages come from two main sources:

RepositoryDescription
PGDGPostgreSQL official repository, providing core extensions
PigstyPigsty supplementary repository, providing additional extensions

The Pigsty repository only includes extensions that are absent from the PGDG repository. If PGDG adds an extension, Pigsty will remove its package or keep it consistent with PGDG.

Repository URLs:

For detailed repository configuration, see Extension Repository.

5 - Install

Install extension packages on cluster nodes

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


Use these two parameters to specify which extensions to install:

ParameterPurposeDefault Behavior
pg_packagesGlobal common packagesEnsure present (no upgrade)
pg_extensionsCluster-specific extensionsInstall latest version

Use pg_packages for essential extensions and base components that all clusters need. Typical components include the PostgreSQL kernel, Patroni, and pgBouncer.

Use pg_extensions for extensions that specific clusters need.

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

Install During Cluster Initialization

Declare extensions in the cluster configuration. Pigsty installs them automatically 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 you run ./pgsql.yml to initialize the cluster, Pigsty installs the extensions automatically.


Install Extensions on Existing Cluster

For an initialized cluster, use one of these methods 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 standard package aliases. It converts each alias to the package name for the applicable PostgreSQL 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:


Verify Installation

After installation, run these checks in the database:

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

-- Check if extension files exist
\dx

6 - Config

Preload extension libraries and configure extension parameters

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:

ExtensionDescription
timescaledbTime-series database extension, must be placed first
citusDistributed database extension, must be placed first
pg_stat_statementsSQL statement statistics, enabled by default in Pigsty
auto_explainAutomatically log slow query execution plans, enabled by default in Pigsty
pg_cronScheduled task scheduling
pg_netAsynchronous HTTP requests
pg_tleTrusted language extensions
pgauditAudit logging
pg_stat_kcacheKernel statistics
pg_squeezeOnline table space reclamation
pgmlPostgresML machine learning

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;

7 - Create

Create and enable extensions in databases

After you install extension packages, run CREATE EXTENSION in the database to use their features.


View Available Extensions

After you install extension packages, run these queries to show the 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. Pigsty creates them 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 }

After it creates the database, Pigsty runs CREATE EXTENSION automatically.


Extensions Requiring Preload

Some extensions require preloading before creation. Add them to shared_preload_libraries, then restart PostgreSQL:

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 create such an extension without preloading it, PostgreSQL returns an error.

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


Extension Dependencies

Some extensions depend on other extensions. Create the dependencies first:

-- 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 have no SQL interfaces and do not require CREATE EXTENSION:

ExtensionDescription
wal2jsonLogical decoding plugin, used directly in replication slots
decoderbufsLogical decoding plugin
decoder_rawLogical decoding plugin

You can use these extensions 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

Upgrade PostgreSQL extension versions

Update extensions at two levels: packages in the operating system, and extension objects in the database.


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

Use Pigsty to update packages on multiple nodes:

# 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 you update packages, you may also need to update the extension objects in the database.

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: Back up the database before you update extensions. This is particularly important if the update changes data types.

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

  3. Preloaded extensions: If you update a preloaded extension, such as timescaledb, you may need to restart the database.

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

  5. Replication environments: Test updates on replicas first. After you verify the results, update the primary.


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 usually do not support direct rollback. To roll back, use one of these methods:

  1. Restore from backup
  2. Uninstall the new extension version, install the old package version, and recreate the extension.

9 - Remove

Uninstall PostgreSQL extensions

Extension removal has two levels: drop extension objects from the database, and uninstall packages from the operating system.


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

We recommend that you check dependencies before you drop an extension:

-- 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, remove it from the preload list after you drop it:

# 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

You can usually keep the package without problems. Uninstall it only to free disk space or resolve conflicts.


Important Notes

  1. Data loss risk: CASCADE drops dependent objects. This can cause data loss.

  2. Application compatibility: Before you drop the extension, make sure that applications no longer use its features.

  3. Preload order: If you drop a preloaded extension, also remove it from shared_preload_libraries. Otherwise, the database may fail to start.

  4. Replication environments: In replication environments, DROP EXTENSION automatically replicates to replicas.


Operation Sequence

Use this sequence to remove an extension completely:

# 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

PostgreSQL extensions installed by default in Pigsty

During PostgreSQL cluster initialization, Pigsty installs and enables some core extensions by default.


Default Installed Extensions

Pigsty installs these extensions by default through pg_packages:

ExtensionDescription
pg_repackHandle table bloat online, important maintenance tool
wal2jsonLogical decoding outputs JSON format changes, commonly used in CDC scenarios
pgvectorVector data type and indexes, installed with pgsql-main by default

The default value of pg_extensions is an empty array []. Declare additional extensions as required. For example:

ExtensionDescription
postgisGeospatial database extension
timescaledbTime-series database extension
pgvectorVector data type and indexes

Default Enabled Extensions

Pigsty enables these extensions in all databases by default through pg_default_extensions:

ExtensionSchemaDescription
pg_stat_statementsmonitorSQL statement execution statistics
pgstattuplemonitorTuple-level statistics
pg_buffercachemonitorBuffer cache inspection
pageinspectmonitorPage-level inspection
pg_prewarmmonitorRelation prewarming
pg_visibilitymonitorVisibility map inspection
pg_freespacemapmonitorFree space map inspection
postgres_fdwpublicPostgreSQL foreign data wrapper
file_fdwpublicFile foreign data wrapper
btree_gistpublicB-tree GiST operator classes
btree_ginpublicB-tree GIN operator classes
pg_trgmpublicTrigram matching
intaggpublicInteger aggregator
intarraypublicInteger array functions
pg_repackrepackOnline table reorganization

These extensions provide basic monitoring and operations tools. They also add database features.


Default Preloaded Extensions

By default, pg_libs adds these extensions to shared_preload_libraries:

ExtensionDescription
pg_stat_statementsTrack execution statistics of all SQL statements
auto_explainAutomatically log execution plans for slow queries

These two extensions provide basic observability. We strongly recommend that you keep them.


Customize Default Extensions

To change which extensions Pigsty installs and enables by default, edit these 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 extension procedures and concepts, see:

11 - Repository

Pigsty extension software repository configuration

Pigsty provides supplementary extension repositories. They supply extension packages in addition to those in the official PGDG repository.


YUM Repository

Use this repository with EL 8/9/10 and compatible systems, such as RHEL, Rocky, AlmaLinux, and CentOS.

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


APT Repository

Use this repository with Debian 12/13, Ubuntu 22.04/24.04/26.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} ${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


GPG Signature

All packages are signed with GPG:

  • Fingerprint: 9592A7BC7A682E7333376E09E7935D8DB9BD8B20
  • Short ID: B9BD8B20

Repository Policy

The Pigsty repository follows these principles:

  1. Supplementary: The repository only includes extensions that are absent from the PGDG repository.
  2. Consistency: If PGDG adds an extension, Pigsty will remove its package or keep it consistent with PGDG.
  3. Compatibility: The repository supports PostgreSQL major versions 14-18.
  4. Multi-platform: The repository supports x86_64 and aarch64 architectures.