Skip to content

pig ext

Manage PostgreSQL extensions with pig ext

pig ext searches the extension catalog and installs, updates, or removes PostgreSQL extension packages on the host. It also supports PostgreSQL server packages. After installing a package, apply the SQL and configuration required by the extension.

CommandDescriptionNotes
ext listSearch extensions
ext infoShow extension details
ext availShow extension availability matrix
ext statusShow host-visible extension files/packagesDoes not query every database
ext scanScan installed control/library filesDoes not query every database
ext addInstall extensionsRequires sudo or root
ext rmRemove extensionsRequires sudo or root
ext updateUpdate extensionsRequires sudo or root
ext importDownload extensions for offline useRequires sudo or root
ext linkLink a PG version into PATHRequires sudo or root
ext reloadRefresh extension catalog

Quick Start

This example uses a running PostgreSQL 18 instance. To install and start one, follow Getting Started.

Add the PostgreSQL repositories, inspect the extension, then install its package:

pig repo add pgsql -u
pig ext list vector
pig ext info vector
pig ext add vector -v 18

Enable the extension in the target database. For example:

CREATE EXTENSION vector;

See the extension list for other extensions. If an extension requires preloading or other configuration, complete the steps from pig ext info NAME before enabling it.

Select a PostgreSQL installation

  • -v, --version 18: select a PostgreSQL major version.
  • -p, --path /path/to/pg_config: select an installation by its pg_config path.
  • Use one option at a time. When neither is supplied, PIG attempts local PostgreSQL detection. Set -v explicitly in automation.

The default PGDG paths are /usr/pgsql-18/bin/pg_config on EL and /usr/lib/postgresql/18/bin/pg_config on Debian/Ubuntu. The package manager resolves RPM/DEB dependencies. -y confirms package operations automatically.

Package and database operations

TaskPIG operationDatabase operation
Installext add NAMEConfigure preloading if required, then run CREATE EXTENSION
Upgradeext update NAMEFollow the extension’s upgrade instructions and run ALTER EXTENSION ... UPDATE
Removeext rm NAMERemove or migrate dependencies in affected databases before uninstalling the package
Inspectext status / ext scan inspect host filesQuery pg_extension for enabled database extensions
Refresh catalogext reload updates extension metadataNone

pg_available_extensions lists extensions available to the server. pg_extension lists extensions enabled in the current database.

ext list

List or search extensions in the extension catalog.

pig ext list                     # list all extensions
pig ext list duck                # search extensions containing "duck"
pig ext list -v 18               # evaluate availability for PG 18
pig ext ls olap                  # list OLAP category extensions
pig ext ls gis -v 16             # list GIS entries with PG 16 availability
pig ext ls rag                   # list RAG category extensions

Category filtering is done by passing the category name as the query. Supported categories include: time, gis, rag, fts, olap, feat, lang, type, func, util, admin, stat, sec, fdw, sim, and etl.

OptionDescription
-v, --versionSelect the PG version used to evaluate availability; unavailable entries remain in the list
--pkgShow package names instead of extension names, listing only leading extensions

Status column values:

  • installed: extension is installed
  • available: extension is available but not installed
  • not avail: extension is not available on the current system

Availability and text/JSON/YAML summaries use the same platform, architecture, and PostgreSQL version. For platforms covered by a package matrix, that matrix takes precedence; a missing cell is unavailable. Catalog entries without a matrix retain RPM/DEB metadata and platform-exception fallbacks. amd64/x86_64 and arm64/aarch64 are equivalent. On macOS, this status describes declared PostgreSQL version support, not the availability of an RPM/DEB package.

Catalog count

The website and CLI count every catalog record, including packaged SQL utilities such as pg_relation_sql and retained records for removed extensions. The bundled catalog currently contains 625 entries. This total does not mean that every entry supports CREATE EXTENSION or is available on every platform.

The unfiltered list includes every record. Its text row count and JSON/YAML data.count describe the rows returned; a search counts its matches, and --pkg counts leading package records. The global ext avail text summary uses the full catalog total. ext reload reports the number of records in the downloaded catalog through data.extension_count.

The counting decision explains this shared definition. The default extension catalog is defined in cli/ext/assets/extension.csv.

Use pig ext reload to refresh to the latest catalog. The downloaded catalog is stored at ~/.pig/extension.csv; the latest online catalog is also published at repo.pigsty.io/ext/data/extension.csv.

ext info

Show detailed information for selected extensions.

pig ext info postgis        # show PostGIS details
pig ext info timescaledb    # show TimescaleDB details
pig ext info vector postgis # show multiple extensions

ext avail

Show the extension availability matrix across operating systems, architectures, and PostgreSQL versions.

pig ext avail                     # show package availability on current system
pig ext avail timescaledb         # show timescaledb availability matrix
pig ext avail postgis pg_duckdb   # show multiple extensions
pig ext av pgvector               # show pgvector availability
pig ext matrix citus              # alias for avail

The matrix shows availability across operating systems (EL8/9/10, Debian 12/13, Ubuntu 22/24/26), architectures (x86_64/aarch64), and PostgreSQL versions (14-18).

ext status

Show extension files/packages visible to the selected PostgreSQL installation on this host.

pig ext status              # show installed extensions
pig ext status -c           # include contrib extensions
pig ext status -v 16        # show installed extensions for PG 16
OptionDescription
-c, --contribInclude contrib extensions in the result

ext scan

Scan the selected PostgreSQL installation’s extension directories.

pig ext scan [-v version]

The command reads control files and related installation paths for the selected PostgreSQL installation. It also matches shared-library names against catalog metadata, so a catalog entry without a control file can still be reported as installed. This does not imply CREATE EXTENSION support.

ext add

Install one or more PostgreSQL extensions. Aliases for pig ext add include pig ext install, pig ext ins, and pig ext a. The top-level pig install command also accepts extension aliases and general OS packages.

pig ext add pg_duckdb            # install pg_duckdb
pig ext add pg_duckdb -v 18      # install for PG 18
pig ext add pg_duckdb -y         # auto-confirm installation
pig ext add vector postgis       # install multiple extensions
pig ext add postgis --plan       # preview install plan without executing

# Top-level package installer
pig install pg_duckdb
pig install pg_duckdb -v 18 -y

# Install PostgreSQL packages
pig ext add pg18                 # install PostgreSQL 18
pig ext add pg18-client          # install client tools only

Use these aliases to select a PostgreSQL package group:

AliasPackages
pg18Server, client, contrib modules, and procedural languages
pg18-serverServer and contrib modules
pg18-contribSeparate contrib package on EL; no mapping on Debian/Ubuntu
pg18-clientClient tools
pg18-develDevelopment files
pg18-basicpg_repack, wal2json, and pgvector
pgsql-commonpatroni, pgbouncer, pgbackrest, pg-exporter, pgbackrest-exporter, vip-manager, plus patroni-etcd (EL) or python3-etcd (Debian/Ubuntu)

Replace 18 with the required major version. You can also use pgsql with -v 18. To select a specific package version, use NAME=VERSION with a version available in the configured repository.

Category aliases

Category aliases select packages from the current platform and PostgreSQL-major matrix. The numbered form takes its target major from the alias; pgsql-<category> -v N uses the same target major as pgN-<category>.

pig ext add pg14-gis --plan
pig ext add pgsql-gis -v 14 --plan
pig install pg14-gis --plan

When pgsql-<category> has no explicit version, the existing command version probing and default selection apply; the resolver uses the resulting target (latest stable when no target is supplied). Stable targets use their exact matrix row for the selected OS and architecture. Missing cells on a known OS remain unavailable, and visible leading non-contrib entries that resolve to the same package are deduplicated. Beta PostgreSQL targets retain the compatibility behavior of borrowing the latest stable matrix and keeping PGDG-origin entries only.

The package name comes from the catalog for the requested major. For example, EL PG14/15 pgaudit resolves through pgaudit_14/pgaudit_15, while the PostGIS 3.7 family resolves through postgis37_N; its address-standardizer and PG16+ Tiger Geocoder packages have their own catalog entries. The configured repository must provide the selected package.

OptionDescription
-v, --versionSpecify PG major version
-y, --yesAuto-confirm installation
--planPreview the install plan without running package-manager commands

Successful installation only makes the extension files available on the host. Follow the extension’s ext info hints for preload, restart, and CREATE EXTENSION steps.

ext rm

Select OS packages to remove by extension name or explicit package-group alias.

pig ext rm pg_duckdb             # remove pg_duckdb
pig ext rm pg_duckdb -v 18       # remove PG 18 package
pig ext rm pgvector -y           # auto-confirm removal
pig ext rm pgvector --plan       # preview remove plan without executing
pig ext rm plpython3u -v 18      # remove a standalone language package
pig ext rm pg18-contrib --plan   # preview removing the entire contrib package (EL only)
pig ext rm pg18-server --plan    # preview explicit server package-group removal
pig ext rm orioledb-server -v 18 --plan  # preview OrioleDB kernel removal
OptionDescription
-v, --versionSpecify PG major version
-y, --yesAuto-confirm removal
--planPreview the remove plan without running package-manager commands

This removes an OS package, not database objects. Drop or migrate dependent extensions in every affected database before removing the shared files.

Server, shared contrib, and supported alternative kernel packages are protected from removal through extension names. Names such as pg_trgm, earthdistance, tablefunc, and plpgsql cannot remove their supplying server or contrib package. To remove an entire package group, explicitly select its alias:

SelectionRemoval Rule
pg18-server, pgsql-server -v 18Explicit server group allowed; includes libraries and contrib on EL
pg18, pg18-mini, pg18-core, pg18-full, pg18-main, postgresql -v 18Selected PostgreSQL package group allowed
pg18-contrib, pgsql-contrib -v 18Allowed on EL; rejected on Debian/Ubuntu, which have no separate contrib mapping
plpython3u, pltcl, pltclu, plperl, plperluStandalone language packages allowed, including trusted/untrusted variants sharing a package
spock, snowflake, lolor, orioledb, pg_tdeExtension-name selection of a kernel package is rejected
pgedge -v 18, orioledb-server -v 18, ivorysql -v 18, babelfish -v 18, openhalo -v 18Explicit kernel package groups allowed

Numbered aliases use their own major version: pg18-server -v 17 still selects PostgreSQL 18. Existing fixed-version aliases pgtde, percona-core, and percona-main select PG 18. orioledb retains its extension meaning; use orioledb-server to remove the kernel explicitly. Category aliases and extension package shorthands never authorize kernel removal.

Each input is checked before package deduplication. Any violation rejects the entire request before sudo or the package manager is invoked. Adding pg18-server to a request containing pg_trgm does not authorize the latter. Argument order and --yes do not affect the decision. Execution returns error code 100102 and exit status 2. A blocked --plan also exits 2, includes failed preconditions, and contains no removal action; structured stdout remains a single plan object.

Allowed plans list OS packages and the catalog extensions they supply, including other extensions in the same package, but do not simulate additional dependency removals by APT/DNF. See the design record.

ext update

Update the named packages to the versions available from the configured repositories. With no arguments, the command makes no changes.

pig ext update                   # no-op: explicit targets are required
pig ext update pg_duckdb         # update one extension
pig ext update postgis timescaledb  # update multiple extensions
pig ext update pg_duckdb -y      # auto-confirm update
pig ext update pg_duckdb -m      # refresh the catalog from the China mirror first
OptionDescription
-v, --versionSpecify PG major version
-y, --yesAuto-confirm update
-m, --mirrorRefresh the extension catalog from the pigsty.cc mirror before updating packages; package downloads still use the configured OS repositories

The command does not run SQL migrations. After the package upgrade, follow the extension’s release notes and run ALTER EXTENSION ... UPDATE in each database where appropriate.

ext import

Download extension packages into a local repository for offline installation.

pig ext import postgis                # import PostGIS packages
pig ext import timescaledb pg_cron    # import multiple extension packages
pig ext import pg16                   # import PostgreSQL 16 packages
pig ext import pgsql-common           # import common utility packages
pig ext import -d /www/pigsty postgis # import to a selected path
OptionDescription
-d, --repoLocal repository directory (default: /www/pigsty)

If some names cannot be resolved, successfully resolved packages are still downloaded, but the command exits with failure. Structured output preserves requested, resolved packages, confirmed downloaded packages, and unresolved failed names. A native download failure can also leave files in the destination; it does not imply rollback or a complete download.

On Debian/Ubuntu, virtual dependency markers such as <virtual-package> are excluded from the package download list; real package dependencies remain eligible.

JSON/YAML output and PIG_NON_INTERACTIVE=1 use non-interactive sudo. If privilege escalation requires a password, the command fails instead of waiting for input; configure suitable sudo access first.

Link a selected PG version into the system PATH.

pig ext link 18                  # link PG 18 to PATH
pig ext link pg17                # strip the pg prefix and link PG 17
pig ext link 16                  # link PG 16 to /usr/pgsql
pig ext link /usr/pgsql-16       # link from a selected path to /usr/pgsql
pig ext link polar               # link PolarDB / PolarPG installation
pig ext link /usr/polar-17       # link from a selected PolarDB path
pig ext link null                # remove current PostgreSQL link
pig ext link none                # null / none / nil / nop / no all remove the link

Relative installation paths resolve against the caller’s working directory. Empty paths, system roots such as /usr/, and paths containing shell expansion characters or newlines are rejected before links or profiles are changed.

This command creates the /usr/pgsql symlink and writes /etc/profile.d/pgsql.sh. Load the profile in your current shell:

. /etc/profile.d/pgsql.sh

ext reload

Refresh extension metadata.

pig ext reload                   # refresh extension catalog

The updated catalog is stored at ~/.pig/extension.csv.

Was this page helpful?

Table reader