This is the multi-page printable view of this section. .
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
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.

1 - Quick Start
To use extensions in Pigsty, complete four steps: Download, Install, Config, and Create.
- Download: Download extension packages to the local repository. The default local repository only guarantees the base kernel and
pgsql-mainpackage set. - Install: Install extension packages on cluster nodes
- Config: Some extensions need to be preloaded or configured with parameters
- Create: Execute
CREATE EXTENSIONin 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:
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:
You can also install extension packages with the pig package manager.
Then run CREATE EXTENSION in the database:
Process Quick Reference
| Step | Parameter/Command | Description |
|---|---|---|
| Download | repo_extra_packages | Specify extension packages to download to local repository |
| Install | pg_extensions | Specify extension packages to install on cluster |
| Config | pg_libs | Preload extensions to shared_preload_libraries |
| Create | pg_databases.extensions | Automatically execute CREATE EXTENSION in database |
For detailed instructions, see each subsection: Download, Install, Config, Create
2 - Introduction
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:
| Extension | Description |
|---|---|
| PostGIS | Geospatial data types and indexes, de facto GIS standard |
| TimescaleDB | Time-series database with continuous aggregates, columnar storage, auto-compression |
| PGVector | Vector data type with HNSW/IVFFlat indexes, essential for AI applications |
| Citus | Distributed database with horizontal sharding capabilities |
| pg_duckdb | Embedded DuckDB analytical engine for OLAP acceleration |
| pg_search | ParadeDB search extension, providing BM25 and full-text search capabilities |
| Apache AGE | Graph database supporting OpenCypher query language |
| pg_graphql | Native 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:
| Category | Alias | Description | Typical Extensions |
|---|---|---|---|
| Time-series | time | Time-series data processing | timescaledb, pg_cron, periods |
| Geospatial | gis | Geospatial data | postgis, h3, pgrouting |
| Vector | rag | Vector retrieval and AI | pgvector, vchord, pg_vectorize |
| Search | fts | Full-text search | pgroonga, zhparser, pg_bigm |
| Analytics | olap | OLAP and analytics | pg_duckdb, pg_mooncake, citus |
| Feature | feat | Feature enhancements | age, pg_graphql, hll, rum |
| Language | lang | Procedural languages | plpython3u, pljava, plv8 |
| Type | type | Data types | hstore, ltree, ip4r |
| Utility | util | Utility tools | http, pg_net, pgjwt |
| Function | func | Function libraries | pg_uuidv7, topn, tdigest |
| Admin | admin | Operations management | pg_repack, pg_squeeze, pgagent |
| Stat | stat | Monitoring statistics | pg_stat_statements, pg_qualstats, auto_explain |
| Security | sec | Security auditing | pgaudit, pgsodium, pg_tde |
| FDW | fdw | Foreign data access | postgres_fdw, mysql_fdw, oracle_fdw |
| Compatibility | sim | Database compatibility | orafce, babelfish |
| ETL | etl | Data synchronization | pglogical, 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:
| Stack | Included Extensions |
|---|---|
gis-stack | postgis, pgrouting, pointcloud, h3, q3c, ogr_fdw |
rag-stack | pgvector, vchord, pgvectorscale, pg_similarity, pg_tiktoken |
fts-stack | pgroonga, pg_bigm, zhparser, hunspell |
olap-stack | pg_duckdb, pg_mooncake, timescaledb, pg_partman, plproxy |
feat-stack | age, hll, rum, pg_graphql, pg_jsonschema, jsquery |
stat-stack | pg_show_plans, pg_stat_kcache, pg_qualstats, pg_wait_sampling |
supa-stack | pg_graphql, pg_jsonschema, wrappers, pgvector, pgsodium, vault |
To install an entire stack, add its name to pg_extensions.
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 package aliases to simplify extension installation and management.
Package Alias Mechanism
Extensions have different names at different layers:
| Layer | Example pgvector | Example postgis |
|---|---|---|
| Extension Name | vector | postgis, postgis_topology, … |
| Package Alias | pgvector | postgis |
| RPM Package Name | pgvector_18 | postgis36_18* |
| DEB Package Name | postgresql-18-pgvector | postgresql-18-postgis-3* |
Use a package alias to specify an extension without its RPM or DEB package name:
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:
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
| Category | Description | Typical Extensions |
|---|---|---|
time | Time-series | timescaledb, pg_cron, periods |
gis | Geospatial | postgis, h3, pgrouting |
rag | Vector/RAG | pgvector, pgml, vchord |
fts | Full-text Search | pg_trgm, zhparser, pgroonga |
olap | Analytics | citus, pg_duckdb, pg_mooncake |
feat | Feature | age, pg_graphql, rum |
lang | Language | plpython3u, pljava, plv8 |
type | Data Type | hstore, ltree, citext |
util | Utility | http, pg_net, pgjwt |
func | Function | pgcrypto, uuid-ossp, pg_uuidv7 |
admin | Admin | pg_repack, pgagent, pg_squeeze |
stat | Statistics | pg_stat_statements, pg_qualstats, auto_explain |
sec | Security | pgaudit, pgcrypto, pgsodium |
fdw | Foreign Data Wrapper | postgres_fdw, mysql_fdw, oracle_fdw |
sim | Compatibility | orafce, babelfishpg_tds |
etl | Data/ETL | pglogical, 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
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:
Using Upstream Repositories
You can also install packages directly from upstream repositories on the internet. You do not need to download them in advance:
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:
| Repository | Description |
|---|---|
| PGDG | PostgreSQL official repository, providing core extensions |
| Pigsty | Pigsty 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:
- 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
Use these two parameters to specify which extensions to install:
| Parameter | Purpose | Default Behavior |
|---|---|---|
pg_packages | Global common packages | Ensure present (no upgrade) |
pg_extensions | Cluster-specific extensions | Install 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.
Install During Cluster Initialization
Declare extensions in the cluster configuration. Pigsty installs them automatically during initialization:
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
Using pig Package Manager
Using Package Manager Directly
Using Package Aliases
Pigsty supports standard package aliases. It converts each alias to the package name for the applicable PostgreSQL version:
You can also use raw package names directly:
For package alias definitions, see:
- EL8 Extension List
- EL9 Extension List
- D12 Extension List
- U22 Extension List
- U24 Extension List
- U26 Extension List
Verify Installation
After installation, run these checks in the database:
6 - 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:
| Extension | Description |
|---|---|
timescaledb | Time-series database extension, must be placed first |
citus | Distributed database extension, must be placed first |
pg_stat_statements | SQL statement statistics, enabled by default in Pigsty |
auto_explain | Automatically log slow query execution plans, enabled by default in Pigsty |
pg_cron | Scheduled task scheduling |
pg_net | Asynchronous HTTP requests |
pg_tle | Trusted language extensions |
pgaudit | Audit logging |
pg_stat_kcache | Kernel statistics |
pg_squeeze | Online table space reclamation |
pgml | PostgresML 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
timescaledbandcitusfirst. - If you use both,
citusshould come beforetimescaledb. - Statistics extensions should come after
pg_stat_statementsto use the same query_id.
Configure During Cluster Initialization
For a new cluster, use pg_libs to specify the extensions to preload:
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 statementsauto_explain: Automatically log execution plans for slow queries
Modify Configuration on Existing Cluster
For initialized clusters, use patronictl to modify shared_preload_libraries:
You can also directly modify postgresql.conf or use ALTER SYSTEM:
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:
Runtime Modification
Use ALTER SYSTEM or patronictl:
Important Notes
Preload errors prevent startup: If an extension in
shared_preload_librariesdoes not exist or fails to load, PostgreSQL will not start. Install each extension correctly before you add it to the preload list.Modification requires restart: After you change
shared_preload_libraries, restart the PostgreSQL service to apply the change.Partial functionality available: Some extensions provide limited functionality without preloading. Full functionality requires preloading.
View current configuration: Run this command to show the current preload libraries:
7 - Create
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:
Create Extensions
Use CREATE EXTENSION to enable extensions in the database:
Note:
CREATE EXTENSIONuses 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:
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:
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:
Extensions Not Requiring Creation
A few extensions have no SQL interfaces and do not require CREATE EXTENSION:
| Extension | Description |
|---|---|
wal2json | Logical decoding plugin, used directly in replication slots |
decoderbufs | Logical decoding plugin |
decoder_raw | Logical decoding plugin |
You can use these extensions immediately after installation. For example:
View Extension Information
8 - Update
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:
Use Pigsty to update packages on multiple nodes:
Update Extension Objects
After you update packages, you may also need to update the extension objects in the database.
View Updatable Extensions
Execute Extension Update
View Update Paths
Important Notes
Backup first: Back up the database before you update extensions. This is particularly important if the update changes data types.
Check compatibility: Some major version upgrades may be incompatible. Read the extension’s upgrade documentation.
Preloaded extensions: If you update a preloaded extension, such as
timescaledb, you may need to restart the database.Dependencies: If other extensions depend on the updated extension, update them in dependency order.
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
Rollback Update
PostgreSQL extensions usually do not support direct rollback. To roll back, use one of these methods:
- Restore from backup
- Uninstall the new extension version, install the old package version, and recreate the extension.
9 - Remove
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:
Warning:
CASCADEwill 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:
Remove Preload
If the extension is in shared_preload_libraries, remove it from the preload list after you drop it:
Uninstall Packages
After dropping the extension from the database, you can optionally uninstall the package:
You can usually keep the package without problems. Uninstall it only to free disk space or resolve conflicts.
Important Notes
Data loss risk:
CASCADEdrops dependent objects. This can cause data loss.Application compatibility: Before you drop the extension, make sure that applications no longer use its features.
Preload order: If you drop a preloaded extension, also remove it from
shared_preload_libraries. Otherwise, the database may fail to start.Replication environments: In replication environments,
DROP EXTENSIONautomatically replicates to replicas.
Operation Sequence
Use this sequence to remove an extension completely:
10 - Default Extensions
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:
| Extension | Description |
|---|---|
pg_repack | Handle table bloat online, important maintenance tool |
wal2json | Logical decoding outputs JSON format changes, commonly used in CDC scenarios |
pgvector | Vector 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:
| Extension | Description |
|---|---|
postgis | Geospatial database extension |
timescaledb | Time-series database extension |
pgvector | Vector data type and indexes |
Default Enabled Extensions
Pigsty enables these extensions in all databases by default through pg_default_extensions:
| Extension | Schema | Description |
|---|---|---|
pg_stat_statements | monitor | SQL statement execution statistics |
pgstattuple | monitor | Tuple-level statistics |
pg_buffercache | monitor | Buffer cache inspection |
pageinspect | monitor | Page-level inspection |
pg_prewarm | monitor | Relation prewarming |
pg_visibility | monitor | Visibility map inspection |
pg_freespacemap | monitor | Free space map inspection |
postgres_fdw | public | PostgreSQL foreign data wrapper |
file_fdw | public | File foreign data wrapper |
btree_gist | public | B-tree GiST operator classes |
btree_gin | public | B-tree GIN operator classes |
pg_trgm | public | Trigram matching |
intagg | public | Integer aggregator |
intarray | public | Integer array functions |
pg_repack | repack | Online 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:
| Extension | Description |
|---|---|
pg_stat_statements | Track execution statistics of all SQL statements |
auto_explain | Automatically 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:
For extension procedures and concepts, see:
- 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. 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
China Mainland Mirror
Repository URLs
- International: https://repo.pigsty.io/yum/
- China: https://repo.pigsty.cc/yum/
APT Repository
Use this repository with Debian 12/13, Ubuntu 22.04/24.04/26.04, and compatible systems.
Add Repository
China Mainland Mirror
Repository URLs
- International: https://repo.pigsty.io/apt/
- China Mirror: 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:
- Supplementary: The repository only includes extensions that are absent from the PGDG repository.
- Consistency: If PGDG adds an extension, Pigsty will remove its package or keep it consistent with PGDG.
- Compatibility: The repository supports PostgreSQL major versions 14-18.
- Multi-platform: The repository supports x86_64 and aarch64 architectures.
Related Resources
- Pigsty Extension Catalog: Browse all available extensions
- PGDG YUM Repository
- PGDG APT Repository