Install Extension

Pigsty uses the standard OS package managers (yum/apt) to install PostgreSQL extension plugins.

Parameters

You can specify which extensions to install using the following variables, both of which have similar effects:

• pg_packages
• pg_extensions

Generally, pg_packages is used to globally specify the software packages that need to be installed across all PostgreSQL clusters in the environment. This includes essential components like the PostgreSQL core, high-availability setup with Patroni, connection pooling with pgBouncer, monitoring with pgExporter, etc. By default, Pigsty also includes two essential extensions here: pg_repack for bloat management and wal2json for CDC (Change Data Capture).

On the other hand, pg_extensions is typically used to specify extension plugins that need to be installed for a specific cluster. Pigsty defaults to installing three key PostgreSQL ecosystem extensions: postgis, timescaledb, and pgvector. You can also specify any additional extensions you need in this list.

pg_packages:                      # pg packages to be installed, alias can be used
  - postgresql
  - patroni pgbouncer pgbackrest pg_exporter pgbadger vip-manager wal2json pg_repack
pg_extensions:                    # pg extensions to be installed, alias can be used
  - postgis timescaledb pgvector

Another important distinction is that packages installed via pg_packages are merely ensured to be present, whereas those installed via pg_extensions are automatically upgraded to the latest available version. This is not an issue when using a local software repository, but when using upstream online repositories, consider this carefully and move extensions you do not want to be upgraded to pg_packages.

During PGSQL cluster initialization, Pigsty will automatically install the extensions specified in both pg_packages and pg_extensions.

Install on Existing Cluster

For a PostgreSQL cluster that has already been provisioned and initialized, you can first add the desired extensions to either pg_packages or pg_extensions, and then install the extensions using the following command:

./pgsql.yml -t pg_extension  # install extensions specified in pg_extensions 

Alias Translation

When specifying extensions in Pigsty, you can use the following formats in pg_packages and pg_extensions:

• The original OS package name
• A normalized extension name (alias)

postgis                    # Installs the PostGIS package for the current major PG version
postgis34_$v*              # Installs the PostGIS RPM package for the current major PG version
postgis34_15*              # Installs the PostGIS RPM package for PG 15
postgresql-$v-postgis-3*   # Installs the PostGIS DEB package for the current major PG version
postgresql-14-postgis-3*   # Installs the PostGIS DEB package for PG 14

We recommend using the standardized extension names (aliases) provided by Pigsty. Pigsty will translate these aliases into the appropriate RPM/DEB package names corresponding to the PG major version for different OS distributions. This way, users don’t need to worry about the differences in extension package names across various OS distributions:

• RPM Extension Mapping
• DEB Extension Mapping

Pigsty strives to align the PostgreSQL extensions available for EL and Debian-based systems. However, a few extensions may be difficult to migrate or have not yet been ported due to various reasons. For more information, please refer to the RPM Extension List and DEB Extension List.