Extension Usage

• 1: Get Started
• 2: Get Extension
• 3: Install Extension
• 4: Load Extension
• 5: Create Extensions
• 6: Update Extension
• 7: Remove Extension

1 - Get Started

Pigsty allows you to download, install, load, and create PostgreSQL extensions declaratively with the PGSQL module.

Out of the Box

Pigsty abstracts away the complexity of extension management, allowing you to declare your extensions in the config inventory:

For example, the following configuration snippet declares a PostgreSQL cluster that downloads, installs, dynamically loads, and enables 3 extensions:

all:
  children:
    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 ]} # Enable 3 extensions (using extension names)
        pg_libs: 'timescaledb, pg_stat_statements, auto_explain'                 # Load 3 extensions (last 2 are built-in)
        pg_extensions: [ pgsql-main, postgis pgvector timescaledb ]              # Install 3 additional extensions
        repo_extra_packages: [ postgis, timescaledb, vector ]                    # Download 3 extension packages

When you run the ./install.yml playbook, these 3 extension packages will be downloaded to the local repo, and the newly created PostgreSQL cluster pg-meta will automatically install, load, and enable these specified extensions.

Concept

Using extensions in a PostgreSQL cluster involves 4 essential operations: download, install, load, and enable:

• How to download extensions: repo_extra_packages

  By default, Pigsty’s online installation only downloads three extensions for the current PostgreSQL major version (pg_repack, wal2json, pgvector).

  To download extra extensions, add them to repo_extra_packages. use the extension package alias and category alias for batch download.

• Which extensions to install: pg_extensions

  Pigsty’s configuration templates include a comprehensive list of available extensions. Simply add the extensions you want to install to pg_extensions.

  To install additional extensions after cluster creation, configure the extensions and run ./pgsql.yml -t pg_extension.

• Which extensions to load: pg_libs

  Some extensions using PostgreSQL hook functions must be dynamically loaded and require a database restart to take effect. Add these extensions to pg_libs.

  For existing clusters, modify the cluster config by changing the shared_preload_libraries parameter and restart the database.

• Which extensions to create: pg_databases.extensions

  Most extensions require executing the CREATE EXTENSION DDL statement after installation to be enabled in a specific database.

  You can execute this DDL manually or specify extensions to enable in pg_databases.extensions, which will be automatically enabled during cluster initialization.

Package Alias

When loading and enabling extensions, we use “extension names” (ext), while for downloading and installing, we use “extension package names” (pkg or alias).

For example, the vector database extension’s name ext is vector, while its pkg is pgvector.

flowchart LR
    ext[( EXTNAME )] -- "n:1" --> pkg[( PKGNAME )]
    pkg[( PKGNAME )] -- "1:n" --> packages[( RPM/DEB )]

The extension alias is an abstraction layer added by Pigsty to address package name differences across OS distros. In most cases, extension names (ext) and package names (pkg) are identical. However, one extension pkg may contain multiple ext extension objects.

For example, the postgis package includes postgis and 6+ extensions. Additionally, some extension names ext conflict with OS packages (lkie acl, vector), so you must use the extension package name (pkg) for downloading and installation.

In Pigsty, you can use extension package names (pkg) in parameters like repo_extra_packages, pg_packages, and pg_extensions. To install the postgis extension, you can use:

• pkg alias provided by Pigsty, which will translate to actual RPM/DEB package names based on active PG version and OS.
• Real package names with $v placeholders, automatically replaced with the value of pg_version.
• Original OS RPM/DEB package names with * wildcards or specified each directly

postgis                   # Extension alias, auto-translated to appropriate package name
postgis35_$v*             # RPM package name pattern with PG version placeholder
postgis35_15*             # Direct RPM package name
postgresql-$v-postgis-3*  # DEB package name pattern with PG version placeholder
postgresql-14-postgis-3*  # Direct DEB package name

Extension-to-package mappings are available on the Extension List page. Each extension package corresponds to different RPM/DEB packages depending on OS and PostgreSQL version combinations.

We recommend using Pigsty’s standardized extension alias (pkg) for installation. Pigsty translates these names into appropriate package names for your specific OS/PG combination, eliminating the need to manage these differences manually.

There are slight variations in available extensions across operating systems and architectures. For authoritative references, check these configuration files:

• EL8 : x86_64 , aarch64
• EL9 : x86_64 , aarch64
• D12 : x86_64 , aarch64
• U22 : x86_64 , aarch64
• U24 : x86_64 , aarch64

Pigsty strives to align PostgreSQL extensions between EL and Debian operating systems, but some extensions remain difficult to port or haven’t yet been ported. See the RPM Extensions List and DEB Extensions List for more information.

Complex Example

Here’s a concrete example: the app/supa configuration template used to build Supabase:

Supabase is an “open source Firebase alternative” built on PostgreSQL that heavily uses extensions. The following snippet defines the extensions needed by Supabase:

all:
  children:
    # pg-meta, the underlying postgres database for supabase
    pg-meta:
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars:
        pg_cluster: pg-meta
        pg_users:
          # supabase roles: anon, authenticated, dashboard_user
          - { name: anon           ,login: false }
          - { name: authenticated  ,login: false }
          - { name: dashboard_user ,login: false ,replication: true ,createdb: true ,createrole: true }
          - { name: service_role   ,login: false ,bypassrls: true }
          # supabase users: please use the same password
          - { name: supabase_admin             ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: true   ,roles: [ dbrole_admin ] ,superuser: true ,replication: true ,createdb: true ,createrole: true ,bypassrls: true }
          - { name: authenticator              ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false  ,roles: [ dbrole_admin, authenticated ,anon ,service_role ] }
          - { name: supabase_auth_admin        ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false  ,roles: [ dbrole_admin ] ,createrole: true }
          - { name: supabase_storage_admin     ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false  ,roles: [ dbrole_admin, authenticated ,anon ,service_role ] ,createrole: true }
          - { name: supabase_functions_admin   ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false  ,roles: [ dbrole_admin ] ,createrole: true }
          - { name: supabase_replication_admin ,password: 'DBUser.Supa' ,replication: true ,roles: [ dbrole_admin ]}
          - { name: supabase_read_only_user    ,password: 'DBUser.Supa' ,bypassrls: true ,roles: [ dbrole_readonly, pg_read_all_data ] }
        pg_databases:
          - name: postgres
            baseline: supabase.sql
            owner: supabase_admin
            comment: supabase postgres database
            schemas: [ extensions ,auth ,realtime ,storage ,graphql_public ,supabase_functions ,_analytics ,_realtime ]
            extensions:                                 # Extensions to enable in the postgres database
              - { name: pgcrypto  ,schema: extensions } # Encryption functions
              - { name: pg_net    ,schema: extensions } # Asynchronous HTTP
              - { name: pgjwt     ,schema: extensions } # JSON Web Token API for PostgreSQL
              - { name: uuid-ossp ,schema: extensions } # Generate universally unique identifiers (UUIDs)
              - { name: pgsodium        }               # Modern cryptography for PostgreSQL
              - { name: supabase_vault  }               # Supabase Vault extension
              - { name: pg_graphql      }               # GraphQL support
              - { name: pg_jsonschema   }               # JSON schema validation
              - { name: wrappers        }               # Collection of foreign data wrappers
              - { name: http            }               # Web page retrieval within the database
              - { name: pg_cron         }               # Job scheduler for PostgreSQL
              - { name: timescaledb     }               # Time-series data support
              - { name: pg_tle          }               # Trusted Language Extensions for PostgreSQL
              - { name: vector          }               # Vector similarity search
              - { name: pgmq            }               # Lightweight message queue
        # supabase required extensions for loading
        pg_libs: 'timescaledb, plpgsql, plpgsql_check, pg_cron, pg_net, pg_stat_statements, auto_explain, pg_tle, plan_filter'
        pg_parameters:
          cron.database_name: postgres
          pgsodium.enable_event_trigger: off
        pg_hba_rules: # supabase hba rules, require access from docker network
          - { user: all ,db: postgres  ,addr: intra         ,auth: pwd ,title: 'allow supabase access from intranet'    }
          - { user: all ,db: postgres  ,addr: 172.17.0.0/16 ,auth: pwd ,title: 'allow access from local docker network' }
        node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am

  vars:  # Global parameter configuration
    pg_version: 17
    repo_modules: node,pgsql,infra,docker
    repo_packages: [node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility, docker ]
    repo_extra_packages: [pg17-core ,pg17-time ,pg17-gis ,pg17-rag ,pg17-fts ,pg17-olap ,pg17-feat ,pg17-lang ,pg17-type ,pg17-util ,pg17-func ,pg17-admin ,pg17-stat ,pg17-sec ,pg17-fdw ,pg17-sim ,pg17-etl ]
    pg_extensions:                  [pg17-time ,pg17-gis ,pg17-rag ,pg17-fts ,pg17-feat ,pg17-lang ,pg17-type ,pg17-util ,pg17-func ,pg17-admin ,pg17-stat ,pg17-sec ,pg17-fdw ,pg17-sim ,pg17-etl ] #,pg17-olap]

In this example, we declare a PostgreSQL cluster named pg-meta with the following extension configuration:

• repo_extra_packages: Batch download all available extensions by 16 major categories
• pg_extensions: Install all extension packages except for pg17-olap
• pg_libs: Dynamically load the extensions required by Supabase
• pg_parameters: Set configuration parameters needed by extensions (e.g., pgsodium and pg_cron)
• pg_databases.extensions: Specify which extensions to enable and in which schema

The baseline: supabase.sql includes additional SQL migration logic for custom extension configuration.

By simply executing ./install.yml, users get a fully configured PostgreSQL cluster with all the extensions Supabase requires, ready to use out of the box!

2 - Get Extension

In Pigsty, downloading and installing extensions are separate steps. During INFRA module installation, Pigsty downloads all required software to the local machine and creates a local YUM/APT repo for the entire deployment.

This approach accelerates installation, eliminates redundant downloads, removes the need for database nodes to access the internet, reduces network traffic, improves delivery reliability, and ensures consistent versions across your environment - all best practices for production deployments.

For development environments, installing extensions directly from internet repo is also acceptable

Quick Start

Packages defined in repo_packages and repo_extra_packages are automatically downloaded to your local repo during Pigsty installation.

For PostgreSQL-related packages (core and extensions), typically put them in repo_extra_packages while leaving repo_packages with its os-specific global defaults.

The default value for repo_extra_packages is [pgsql-main], an alias representing core PostgreSQL and critical extensions for the current active major version.

repo_extra_packages: [ pgsql-main ]  # main packages (kernel + 3 extension) for current pg major 17

To add specific extensions, simply add Pigsty extension package name (pkg) to this parameter. Pigsty automatically downloads the appropriate packages for your active PG version and current OS distro.

repo_extra_packages: [ pgsql-main, documentdb, citus, postgis, pgvector, pg_cron, rum ]

To download all available extensions for the current PG version, add all 16 extension category aliases (as in the rich config template):

repo_extra_packages: [ pgsql-main ,pgsql-time ,pgsql-gis ,pgsql-rag ,pgsql-fts ,pgsql-olap ,pgsql-feat ,pgsql-lang ,pgsql-type ,pgsql-util ,pgsql-func ,pgsql-admin ,pgsql-stat ,pgsql-sec ,pgsql-fdw ,pgsql-sim ,pgsql-etl]

Alternatively, use version-specific aliases to download extensions for multiple PostgreSQL versions:

repo_extra_packages: [
    pg17-core,pg17-time,pg17-gis,pg17-rag,pg17-fts,pg17-olap,pg17-feat,pg17-lang,pg17-type,pg17-util,pg17-func,pg17-admin,pg17-stat,pg17-sec,pg17-fdw,pg17-sim,pg17-etl,
    pg16-core,pg16-time,pg16-gis,pg16-rag,pg16-fts,pg16-olap,pg16-feat,pg16-lang,pg16-type,pg16-util,pg16-func,pg16-admin,pg16-stat,pg16-sec,pg16-fdw,pg16-sim,pg16-etl,
    pg15-core,pg15-time,pg15-gis,pg15-rag,pg15-fts,pg15-olap,pg15-feat,pg15-lang,pg15-type,pg15-util,pg15-func,pg15-admin,pg15-stat,pg15-sec,pg15-fdw,pg15-sim,pg15-etl,
    pg14-core,pg14-time,pg14-gis,pg14-rag,pg14-fts,pg14-olap,pg14-feat,pg14-lang,pg14-type,pg14-util,pg14-func,pg14-admin,pg14-stat,pg14-sec,pg14-fdw,pg14-sim,pg14-etl,
    pg13-core,pg13-time,pg13-gis,pg13-rag,pg13-fts,pg13-olap,pg13-feat,pg13-lang,pg13-type,pg13-util,pg13-func,pg13-admin,pg13-stat,pg13-sec,pg13-fdw,pg13-sim,pg13-etl,
]

To add new extensions to your local repo, modify the parameters above and run:

./infra.yml -t repo_build   # Re-download and rebuild local repo

To refresh the repo metadata on all other nodes in your environment, run:

./node.yml  -t node_repo    # [Optional] apt update / yum makecache

Alias Mapping

PostgreSQL has a rich open-source ecosystem with numerous packages across different systems and architectures.

Pigsty provides an abstraction layer that categorizes PostgreSQL packages into “aliases,” hiding differences between systems, architectures, and PG versions.

In the Quick Start section, we used aliases like pgsql-main and pgsql-core. These aliases are translated into specific package names based on your system and architecture. For EL systems, pgsql-main expands to postgresql$v* kernel packages with pgvector_$v*, pg_repack_$v*, and wal2json_$v* extension packages.

pgsql-main:   "postgresql$v* pg_repack_$v* wal2json_$v* pgvector_$v*"

The $v placeholder is replaced by the pg_version value (default: 17) to target the correct version. The * wildcard expands to include all package variants (e.g., server, libs, contrib, devel). Pigsty handles these details automatically.

The complete list of available packages and aliases is in roles/node_id/vars/<os.arch>.yml. Here are commonly used aliases available across all supported systems:

• Package Aliases
• EL 9
• Ubuntu 22.04

postgresql:   "postgresql$v*"
pgsql-main:   "postgresql$v* pg_repack_$v* wal2json_$v* pgvector_$v*"
pgsql-core:   "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl postgresql$v-test postgresql$v-devel postgresql$v-llvmjit"
pgsql-simple: "postgresql$v postgresql$v-server postgresql$v-libs postgresql$v-contrib postgresql$v-plperl postgresql$v-plpython3 postgresql$v-pltcl"
pgsql-client: "postgresql$v"
pgsql-server: "postgresql$v-server postgresql$v-libs postgresql$v-contrib"
pgsql-devel:  "postgresql$v-devel"
pgsql-basic:  "pg_repack_$v* wal2json_$v* pgvector_$v*"

pgsql-time:   "timescaledb-tsl_$v* timescaledb-toolkit_$v pg_timeseries_$v periods_$v* temporal_tables_$v* e-maj_$v table_version_$v pg_cron_$v* pg_task_$v* pg_later_$v pg_background_$v*"
pgsql-gis:    "postgis35_$v* pgrouting_$v* pointcloud_$v* h3-pg_$v* q3c_$v* ogr_fdw_$v* geoip_$v pg_polyline_$v pg_geohash_$v*"
pgsql-rag:    "pgvector_$v* vchord_$v pgvectorscale_$v pg_vectorize_$v pg_similarity_$v* smlar_$v* pg_summarize_$v pg_tiktoken_$v pg4ml_$v"
pgsql-fts:    "pg_search_$v pgroonga_$v* pg_bigm_$v* zhparser_$v* pg_bestmatch_$v vchord_bm25_$v hunspell_cs_cz_$v hunspell_de_de_$v hunspell_en_us_$v hunspell_fr_$v hunspell_ne_np_$v hunspell_nl_nl_$v hunspell_nn_no_$v hunspell_ru_ru_$v hunspell_ru_ru_aot_$v"
pgsql-olap:   "citus_$v* pg_analytics_$v pg_duckdb_$v* pg_mooncake_$v* duckdb_fdw_$v* pg_parquet_$v pg_fkpart_$v pg_partman_$v* plproxy_$v*" #hydra_$v* #pg_strom_$v*
pgsql-feat:   "hll_$v* rum_$v pg_graphql_$v pg_jsonschema_$v jsquery_$v* pg_hint_plan_$v* hypopg_$v* index_advisor_$v pg_plan_filter_$v* imgsmlr_$v* pg_ivm_$v* pg_incremental_$v* pgmq_$v pgq_$v* pg_cardano_$v omnigres_$v" #apache-age_$v*
pgsql-lang:   "pg_tle_$v* plv8_$v* pllua_$v* pldebugger_$v* plpgsql_check_$v* plprofiler_$v* plsh_$v* pljava_$v*" #plprql_$v #plr_$v* #pgtap_$v* #postgresql_faker_$v* #dbt2-pgsql-extensions*
pgsql-type:   "prefix_$v* semver_$v* postgresql-unit_$v* pgpdf_$v* pglite_fusion_$v md5hash_$v* asn1oid_$v* pg_roaringbitmap_$v* pgfaceting_$v pgsphere_$v* pg_country_$v* pg_xenophile_$v pg_currency_$v* pgcollection_$v* pgmp_$v* numeral_$v* pg_rational_$v* pguint_$v* pg_uint128_$v* hashtypes_$v* ip4r_$v* pg_duration_$v* pg_uri_$v* pg_emailaddr_$v* acl_$v* timestamp9_$v* chkpass_$v*"
pgsql-util:   "pgsql_gzip_$v* pg_bzip_$v* pg_zstd_$v* pgsql_http_$v* pg_net_$v* pg_curl_$v* pgjq_$v* pgjwt_$v pg_smtp_client_$v pg_html5_email_address_$v url_encode_$v* pgsql_tweaks_$v pg_extra_time_$v pgpcre_$v icu_ext_$v* pgqr_$v* pg_protobuf_$v pg_envvar_$v* floatfile_$v* pg_readme_$v ddl_historization_$v data_historization_$v pg_schedoc_$v pg_hashlib_$v pg_xxhash_$v* postgres_shacrypt_$v* cryptint_$v* pg_ecdsa_$v* pgsparql_$v"
pgsql-func:   "pg_idkit_$v pg_uuidv7_$v* permuteseq_$v* pg_hashids_$v* sequential_uuids_$v topn_$v* quantile_$v* lower_quantile_$v* count_distinct_$v* omnisketch_$v* ddsketch_$v* vasco_$v* pgxicor_$v* tdigest_$v* first_last_agg_$v extra_window_functions_$v* floatvec_$v* aggs_for_vecs_$v* aggs_for_arrays_$v* pg_arraymath_$v* pg_math_$v* pg_random_$v* pg_base36_$v* pg_base62_$v* pg_base58_$v pg_financial_$v*"
pgsql-admin:  "pg_repack_$v* pg_squeeze_$v* pg_dirtyread_$v* pgfincore_$v* pg_cooldown_$v* ddlx_$v pg_prioritize_$v* pg_readonly_$v* pg_upless_$v pg_permissions_$v pg_catcheck_$v* preprepare_$v* pgcozy_$v pg_orphaned_$v* pg_crash_$v* pg_cheat_funcs_$v* pg_fio_$v pg_savior_$v* safeupdate_$v* pg_drop_events_$v table_log_$v" #pg_checksums_$v* #pg_auto_failover_$v* #pgagent_$v* #pgpool-II-pgsql-extensions
pgsql-stat:   "pg_profile_$v* pg_tracing_$v* pg_show_plans_$v* pg_stat_kcache_$v* pg_stat_monitor_$v* pg_qualstats_$v* pg_store_plans_$v* pg_track_settings_$v pg_wait_sampling_$v* system_stats_$v* pg_meta_$v pgnodemx_$v pg_sqlog_$v bgw_replstatus_$v* pgmeminfo_$v* toastinfo_$v* pg_explain_ui_$v pg_relusage_$v pagevis_$v powa_$v*"
pgsql-sec:    "passwordcheck_cracklib_$v* supautils_$v* pgsodium_$v* vault_$v* pg_session_jwt_$v pg_anon_$v pgsmcrypto_$v pgaudit_$v* pgauditlogtofile_$v* pg_auth_mon_$v* credcheck_$v* pgcryptokey_$v pg_jobmon_$v logerrors_$v* login_hook_$v* set_user_$v* pg_snakeoil_$v* pgextwlist_$v* pg_auditor_$v sslutils_$v* noset_$v*" #pg_tde_$v*
pgsql-fdw:    "wrappers_$v multicorn2_$v* odbc_fdw_$v* mysql_fdw_$v* tds_fdw_$v* sqlite_fdw_$v* pgbouncer_fdw_$v redis_fdw_$v* pg_redis_pubsub_$v* hdfs_fdw_$v* firebird_fdw_$v aws_s3_$v log_fdw_$v*" #jdbc_fdw_$v* #oracle_fdw_$v* #db2_fdw_$v* #mongo_fdw_$v* #kafka_fdw_$v
pgsql-sim:    "documentdb_$v* orafce_$v pgtt_$v* session_variable_$v* pg_statement_rollback_$v* pg_dbms_metadata_$v pg_dbms_lock_$v pgmemcache_$v*" #pg_dbms_job_$v #wiltondb
pgsql-etl:    "pglogical_$v* pglogical_ticker_$v* pgl_ddl_deploy_$v* pg_failover_slots_$v* db_migrator_$v wal2json_$v* postgres-decoderbufs_$v* decoder_raw_$v* mimeo_$v pg_fact_loader_$v* pg_bulkload_$v*" #wal2mongo_$v* #repmgr_$v*

postgresql:   "postgresql-$v postgresql-client-$v postgresql-plpython3-$v postgresql-plperl-$v postgresql-pltcl-$v postgresql-server-dev-$v"
pgsql-main:   "postgresql-$v postgresql-client-$v postgresql-plpython3-$v postgresql-plperl-$v postgresql-pltcl-$v postgresql-server-dev-$v postgresql-$v-repack postgresql-$v-wal2json postgresql-$v-pgvector"
pgsql-core:   "postgresql-$v postgresql-client-$v postgresql-plpython3-$v postgresql-plperl-$v postgresql-pltcl-$v postgresql-server-dev-$v"
pgsql-simple: "postgresql-$v postgresql-client-$v postgresql-plperl-$v postgresql-plpython3-$v postgresql-pltcl-$v"
pgsql-client: "postgresql-client-$v"
pgsql-server: "postgresql-$v"
pgsql-devel:  "postgresql-server-dev-$v"
pgsql-basic:  "postgresql-$v-repack postgresql-$v-wal2json postgresql-$v-pgvector"
pgsql-time:    "postgresql-$v-timescaledb-tsl postgresql-$v-timescaledb-toolkit postgresql-$v-pg-timeseries postgresql-$v-periods postgresql-$v-temporal-tables postgresql-$v-emaj postgresql-$v-table-version postgresql-$v-cron postgresql-$v-pg-task postgresql-$v-pg-later postgresql-$v-pg-background"

pgsql-gis:     "postgresql-$v-postgis-3 postgresql-$v-postgis-3-scripts postgresql-$v-pgrouting postgresql-$v-pgrouting-scripts postgresql-$v-pointcloud postgresql-$v-h3 postgresql-$v-q3c postgresql-$v-ogr-fdw postgresql-$v-geoip postgresql-$v-pg-polyline postgresql-$v-pg-geohash postgresql-$v-mobilitydb"
pgsql-rag:     "postgresql-$v-pgvector postgresql-$v-vchord postgresql-$v-pgvectorscale postgresql-$v-pg-vectorize postgresql-$v-similarity postgresql-$v-smlar postgresql-$v-pg-summarize postgresql-$v-pg-tiktoken postgresql-$v-pg4ml postgresql-$v-pgml"
pgsql-fts:     "postgresql-$v-pg-search postgresql-$v-pgroonga postgresql-$v-pg-bigm postgresql-$v-zhparser postgresql-$v-pg-bestmatch postgresql-$v-vchord-bm25 postgresql-$v-hunspell-cs-cz postgresql-$v-hunspell-de-de postgresql-$v-hunspell-en-us postgresql-$v-hunspell-fr postgresql-$v-hunspell-ne-np postgresql-$v-hunspell-nl-nl postgresql-$v-hunspell-nn-no postgresql-$v-hunspell-ru-ru postgresql-$v-hunspell-ru-ru-aot"
pgsql-olap:    "postgresql-$v-citus postgresql-$v-pg-analytics postgresql-$v-pg-duckdb postgresql-$v-pg-mooncake postgresql-$v-duckdb-fdw postgresql-$v-pg-parquet postgresql-$v-pg-fkpart postgresql-$v-partman postgresql-$v-plproxy" #postgresql-$v-hydra
pgsql-feat:    "postgresql-$v-age postgresql-$v-hll postgresql-$v-rum postgresql-$v-pg-graphql postgresql-$v-pg-jsonschema postgresql-$v-jsquery postgresql-$v-pg-hint-plan postgresql-$v-hypopg postgresql-$v-index-advisor postgresql-$v-pg-plan-filter postgresql-$v-imgsmlr postgresql-$v-pg-ivm postgresql-$v-pg-incremental postgresql-$v-pgmq postgresql-$v-pgq3 postgresql-$v-pg-cardano postgresql-$v-omnigres" #postgresql-$v-rdkit
pgsql-lang:    "postgresql-$v-pg-tle postgresql-$v-plv8 postgresql-$v-pllua postgresql-$v-pldebugger postgresql-$v-plpgsql-check postgresql-$v-plprofiler postgresql-$v-plsh postgresql-$v-pljava" #postgresql-$v-plprql #postgresql-$v-plr #postgresql-$v-pgtap
pgsql-type:    "postgresql-$v-prefix postgresql-$v-semver postgresql-$v-unit postgresql-$v-pgpdf postgresql-$v-pglite-fusion postgresql-$v-md5hash postgresql-$v-asn1oid postgresql-$v-roaringbitmap postgresql-$v-pgfaceting postgresql-$v-pgsphere postgresql-$v-pg-country postgresql-$v-pg-xenophile postgresql-$v-pg-currency postgresql-$v-collection postgresql-$v-pgmp postgresql-$v-numeral postgresql-$v-rational postgresql-$v-pguint postgresql-$v-pg-uint128 postgresql-$v-hashtypes postgresql-$v-ip4r postgresql-$v-pg-duration postgresql-$v-pg-uri postgresql-$v-pg-emailaddr postgresql-$v-acl postgresql-$v-debversion postgresql-$v-pg-rrule postgresql-$v-timestamp9 postgresql-$v-chkpass"
pgsql-util:    "postgresql-$v-gzip postgresql-$v-bzip postgresql-$v-zstd postgresql-$v-http postgresql-$v-pg-net postgresql-$v-pg-curl postgresql-$v-pgjq postgresql-$v-pgjwt postgresql-$v-pg-smtp-client postgresql-$v-pg-html5-email-address postgresql-$v-url-encode postgresql-$v-pgsql-tweaks postgresql-$v-pg-extra-time postgresql-$v-pgpcre postgresql-$v-icu-ext postgresql-$v-pgqr postgresql-$v-pg-protobuf postgresql-$v-pg-envvar postgresql-$v-floatfile postgresql-$v-pg-readme postgresql-$v-ddl-historization postgresql-$v-data-historization postgresql-$v-pg-schedoc postgresql-$v-pg-hashlib postgresql-$v-pg-xxhash postgresql-$v-shacrypt postgresql-$v-cryptint postgresql-$v-pg-ecdsa postgresql-$v-pgsparql"
pgsql-func:    "postgresql-$v-pg-idkit postgresql-$v-pg-uuidv7 postgresql-$v-permuteseq postgresql-$v-pg-hashids postgresql-$v-sequential-uuids postgresql-$v-topn postgresql-$v-quantile postgresql-$v-lower-quantile postgresql-$v-count-distinct postgresql-$v-omnisketch postgresql-$v-ddsketch postgresql-$v-vasco postgresql-$v-pgxicor postgresql-$v-tdigest postgresql-$v-first-last-agg postgresql-$v-extra-window-functions postgresql-$v-floatvec postgresql-$v-aggs-for-vecs postgresql-$v-aggs-for-arrays postgresql-$v-pg-arraymath postgresql-$v-pg-math postgresql-$v-random postgresql-$v-base36 postgresql-$v-base62 postgresql-$v-pg-base58 postgresql-$v-pg-financial"
pgsql-admin:   "postgresql-$v-repack postgresql-$v-squeeze postgresql-$v-dirtyread postgresql-$v-pgfincore postgresql-$v-pg-cooldown postgresql-$v-ddlx postgresql-$v-prioritize postgresql-$v-pg-checksums postgresql-$v-pg-readonly postgresql-$v-pg-upless postgresql-$v-pg-permissions postgresql-$v-auto-failover postgresql-$v-pg-catcheck postgresql-$v-preprepare postgresql-$v-pgcozy postgresql-$v-pg-orphaned postgresql-$v-pg-crash postgresql-$v-pg-cheat-funcs postgresql-$v-pg-fio postgresql-$v-pg-savior postgresql-$v-pg-safeupdate postgresql-$v-pg-drop-events postgresql-$v-tablelog" #pgagent #postgresql-$v-pgpool2
pgsql-stat:    "postgresql-$v-pg-profile postgresql-$v-pg-tracing postgresql-$v-show-plans postgresql-$v-pg-stat-kcache postgresql-$v-pg-stat-monitor postgresql-$v-pg-qualstats postgresql-$v-pg-store-plan postgresql-$v-pg-track-settings postgresql-$v-pg-wait-sampling postgresql-$v-system-stats postgresql-$v-pg-meta postgresql-$v-pgnodemx postgresql-$v-pg-sqlog postgresql-$v-bgw-replstatus postgresql-$v-pgmeminfo postgresql-$v-toastinfo postgresql-$v-pg-explain-ui postgresql-$v-pg-relusage postgresql-$v-pagevis postgresql-$v-powa"
pgsql-sec:     "postgresql-$v-passwordcheck-cracklib postgresql-$v-supautils postgresql-$v-pgsodium postgresql-$v-vault postgresql-$v-pg-session-jwt postgresql-$v-pg-anon postgresql-$v-pgsmcrypto postgresql-$v-pgaudit postgresql-$v-pgauditlogtofile postgresql-$v-pg-auth-mon postgresql-$v-credcheck postgresql-$v-pgcryptokey postgresql-$v-pg-jobmon postgresql-$v-logerrors postgresql-$v-login-hook postgresql-$v-set-user postgresql-$v-snakeoil postgresql-$v-pgextwlist postgresql-$v-pg-auditor postgresql-$v-sslutils postgresql-$v-noset" #postgresql-$v-pg-tde
pgsql-fdw:     "postgresql-$v-wrappers postgresql-$v-mysql-fdw postgresql-$v-tds-fdw postgresql-$v-redis-fdw postgresql-$v-pg-redis-pubsub postgresql-$v-firebird-fdw postgresql-$v-aws-s3 postgresql-$v-log-fdw" # #postgresql-$v-oracle-fdw #postgresql-$v-sqlite-fdw #postgresql-$v-kafka-fdw
pgsql-sim:     "postgresql-$v-documentdb postgresql-$v-orafce postgresql-$v-pgtt postgresql-$v-session-variable postgresql-$v-pg-statement-rollback postgresql-$v-pgmemcache" # #wiltondb
pgsql-etl:     "postgresql-$v-pglogical postgresql-$v-pglogical-ticker postgresql-$v-pgl-ddl-deploy postgresql-$v-pg-failover-slots postgresql-$v-db-migrator postgresql-$v-wal2json postgresql-$v-decoderbufs postgresql-$v-decoder-raw postgresql-$v-mimeo postgresql-$v-pg-fact-loader postgresql-$v-pg-bulkload" #postgresql-$v-wal2mongo #postgresql-$v-repmgr

When using these aliases, the $v placeholder is replaced with the PostgreSQL major version number from pg_version (default: 17).

To download packages for different PostgreSQL versions, either:

• Change the pg_version parameter, or
• Use version-specific aliases by replacing the pgsql- prefix with pg17-, pg16-, pg15-, etc.

Not all extensions are available on all systems. Some extensions are commented out in the aliases because they:

• Are unavailable on specific systems
• Have extensive dependencies (like pl/R)
• Depend on commercial software (like oracle_fdw)
• Are unavailable in the latest PG 17 but available in earlier versions

You can still manually add these extensions if needed.

3 - Install Extension

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

Quick Start

When installing extensions, Pigsty uses the same alias mapping in the download section.

Install all extensions explicitly specified in the pg_extensions parameter, for the cluster pg-meta:

all:
  children:
    pg-meta:
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars:
        pg_cluster: pg-meta
        pg_extensions: # extensions to be installed on this cluster
          - timescaledb timescaledb_toolkit pg_timeseries periods temporal_tables emaj table_version pg_cron pg_task pg_later pg_background
          - postgis pgrouting pointcloud pg_h3 q3c ogr_fdw geoip pg_polyline pg_geohash #mobilitydb
          - pgvector vchord pgvectorscale pg_vectorize pg_similarity smlar pg_summarize pg_tiktoken pg4ml #pgml
          - pg_search pgroonga pg_bigm zhparser pg_bestmatch vchord_bm25 hunspell
          - citus hydra pg_analytics pg_duckdb pg_mooncake duckdb_fdw pg_parquet pg_fkpart pg_partman plproxy #pg_strom
          - age hll rum pg_graphql pg_jsonschema jsquery pg_hint_plan hypopg index_advisor pg_plan_filter imgsmlr pg_ivm pg_incremental pgmq pgq pg_cardano omnigres #rdkit
          - pg_tle plv8 pllua plprql pldebugger plpgsql_check plprofiler plsh pljava #plr #pgtap #faker #dbt2
          - pg_prefix pg_semver pgunit pgpdf pglite_fusion md5hash asn1oid roaringbitmap pgfaceting pgsphere pg_country pg_xenophile pg_currency pg_collection pgmp numeral pg_rational pguint pg_uint128 hashtypes ip4r pg_uri pgemailaddr pg_acl timestamp9 chkpass #pg_duration #debversion #pg_rrule
          - pg_gzip pg_bzip pg_zstd pg_http pg_net pg_curl pgjq pgjwt pg_smtp_client pg_html5_email_address url_encode pgsql_tweaks pg_extra_time pgpcre icu_ext pgqr pg_protobuf envvar floatfile pg_readme ddl_historization data_historization pg_schedoc pg_hashlib pg_xxhash shacrypt cryptint pg_ecdsa pgsparql
          - pg_idkit pg_uuidv7 permuteseq pg_hashids sequential_uuids topn quantile lower_quantile count_distinct omnisketch ddsketch vasco pgxicor tdigest first_last_agg extra_window_functions floatvec aggs_for_vecs aggs_for_arrays pg_arraymath pg_math pg_random pg_base36 pg_base62 pg_base58 pg_financial
          - pg_repack pg_squeeze pg_dirtyread pgfincore pg_cooldown pg_ddlx pg_prioritize pg_checksums pg_readonly pg_upless pg_permissions pgautofailover pg_catcheck preprepare pgcozy pg_orphaned pg_crash pg_cheat_funcs pg_fio pg_savior safeupdate pg_drop_events table_log #pgagent #pgpool
          - pg_profile pg_tracing pg_show_plans pg_stat_kcache pg_stat_monitor pg_qualstats pg_store_plans pg_track_settings pg_wait_sampling system_stats pg_meta pgnodemx pg_sqlog bgw_replstatus pgmeminfo toastinfo pg_explain_ui pg_relusage pagevis powa
          - passwordcheck supautils pgsodium pg_vault pg_session_jwt pg_anon pg_tde pgsmcrypto pgaudit pgauditlogtofile pg_auth_mon credcheck pgcryptokey pg_jobmon logerrors login_hook set_user pg_snakeoil pgextwlist pg_auditor sslutils pg_noset
          - wrappers multicorn odbc_fdw jdbc_fdw mysql_fdw tds_fdw sqlite_fdw pgbouncer_fdw mongo_fdw redis_fdw pg_redis_pubsub kafka_fdw hdfs_fdw firebird_fdw aws_s3 log_fdw #oracle_fdw #db2_fdw
          - documentdb orafce pgtt session_variable pg_statement_rollback pg_dbms_metadata pg_dbms_lock pgmemcache #pg_dbms_job #wiltondb
          - pglogical pglogical_ticker pgl_ddl_deploy pg_failover_slots db_migrator wal2json wal2mongo decoderbufs decoder_raw mimeo pg_fact_loader pg_bulkload #repmgr

Or install all extensions by category aliases globally:

all:
  vars:
    pg_version: 17   # default postgres version 17, so the pgsql-main is equivalent to pg17-main
    pg_extensions: [ pgsql-main ,pgsql-time ,pgsql-gis ,pgsql-rag ,pgsql-fts ,pgsql-olap ,pgsql-feat ,pgsql-lang ,pgsql-type ,pgsql-util ,pgsql-func ,pgsql-admin ,pgsql-stat ,pgsql-sec ,pgsql-fdw ,pgsql-sim ,pgsql-etl]

You can also specify the PG major version explicitly in these alias:

all:
  vars:
    
    pg_extensions: [pg17-time ,pg17-gis ,pg17-rag ,pg17-fts ,pg17-feat ,pg17-lang ,pg17-type ,pg17-util ,pg17-func ,pg17-admin ,pg17-stat ,pg17-sec ,pg17-fdw ,pg17-sim ,pg17-etl ] #,pg17-olap]

Install all extensions simultaneously is applicable (except two conflicts in the olap category) but not recommended. Just install the extensions you need by explicitly specifying them in the pg_extensions parameter.

Configure

During PGSQL cluster init, Pigsty will automatically install packages (& alias) specified in pg_packages and pg_extensions.

• pg_packages
• pg_extensions

Both parameters can be used to install PostgreSQL-related packages. Typically, pg_packages is used to globally specify packages that should be installed across all PostgreSQL clusters in your environment: such as the PostgreSQL kernel, high-availability agent like Patroni, connection pooling with pgBouncer, monitoring with pgExporter, etc.

By default, Pigsty also specifies 3 important extensions here: pgvector, pg_repack, and wal2json for vector search, bloat management, and CDC change extraction.

Meanwhile, pg_extensions is usually used to specify extension for a specific cluster. The default is an empty list, indicating no other extensions will be installed by default.

pg_packages:                      # pg packages to be installed, alias can be used, state=present
  - postgresql
  - wal2json pg_repack pgvector
  - patroni pgbouncer pgbackrest pg_exporter pgbadger vip-manager
pg_extensions: []                 # pg extensions to be installed, alias can be used, state=latest

An important distinction: 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.

When using a local software repo, this distinction isn’t an issue. However, when using upstream internet repo, consider this carefully and move extensions you don’t want automatically upgraded to pg_packages.

Install

Extensions pre-defined in the pg_extensions (and pg_packages) will be installed during cluster provisioning.

To install new extensions on a provisioned PostgreSQL cluster:

First, add extensions to pg_extensions, then execute the playbook subtask:

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

Note that extension plugins specified in the pg_extension task will be upgraded to the latest available version in your current environment by default.

Repo

To install extension, you need to ensure one of the following conditions is met:

• Local Repo: You have configured using Pigsty’s local repo, and the extensions have already been downloaded to the local repo.
• Online Repo: You have directly configured upstream internet repo on the target node, and internet access is available on these nodes.

For production environments, we recommend using Pigsty’s local software repo to manage and install extensions uniformly: First download extensions to the local repo, then install them from there. This ensures consistent extension versions across your environment and prevents database nodes from directly accessing the internet. You have to do nothing when install from local repo, just make sure they are downloaded to the local repo.

For development environments, you may choose to directly use upstream internet repo for convenience. Use the following commands to add Internet repo and install extensions on the target cluster directly:

./node.yml  -l <cls> -t node_repo -e node_repo_modules=local,node,pgsql    # Enable internet repo on target node
./pgsql.yml -l <cls> -t pg_extension                                        # Install extensions using local+internet upstream repos

Package Alias

When installing extensions, users can use extension aliases to specify extension.

The aliases will be translated to the current active PG major version and OS environment,

and translated to the corresponding RPM/DEB package names by alias translation mechanism.

Caveats

• Check bad case for known issues.
• There are two known confliction:
  • citus and hydra are mutually exclusive, since hydra is a fork of citus columnar without renaming
  • Only install one from pg_duckdb, pg_mooncake, duckdb_fdw, they all using their own libduckdb
• pgaudit got a different naming pattern on el for pg 15-: pg16+ = pgaudit, pg15=pgaudit17, pg14=pgaudit16 pg13=pgaudit15 pg12=pgaudit14
• postgis got its own version in el package name: postgis35 by default, and postgis33 for legacy el7

4 - Load Extension

While most PostgreSQL extensions written in SQL can be directly enabled with CREATE EXTENSION, extensions that provide dynamic libraries (.so, .dylib, .dll) have different loading requirements.

Most library-based extensions don’t need explicit loading. However, extensions using PostgreSQL’s Hook mechanism require an additional step — modifying the shared_preload_libraries parameter and restarting the database server.

Attempting to execute CREATE EXTENSION without proper preloading will result in an error. And wrongly configured loading may lead to a failure on database restart/start.

Quick Start

For example, in the conf/app/supa config template, we load multiple extensions by setting the pg_libs parameter:

all:
  children:
    pg-meta:
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars:
        pg_cluster: pg-meta
        pg_databases:
          - name: postgres
            pg_libs: 'timescaledb, plpgsql, plpgsql_check, pg_cron, pg_net, pg_stat_statements, auto_explain, pg_tle, plan_filter'

Not all extensions require dynamic loading via pg_libs (e.g., pgcrypto, pgjwt, vector). For extensions requiring dynamic loading, please refer to the Extensions that Need Loading list below.

Configure

There are several ways to modify PostgreSQL cluster configuration in Pigsty:

For new clusters, configure the pg_libs parameter to specify the initial value of shared_preload_libraries.

Note that pg_libs only takes effect during cluster creation. After creation, it becomes the initial value for the PostgreSQL parameter shared_preload_libraries.

To modify loaded extensions in an existing cluster, use the Patroni command line to config cluster, change shared_preload_libraries, and restart to apply changes.

Alternatively, you can modify shared_preload_libraries by editing postgresql.conf, using the ALTER SYSTEM command, or overriding it through pg_parameters. Ensure configuration remains consistent across the cluster.

Default

Pigsty preloads these two Contrib extensions by default:

• auto_explain: Provides automatic logging of slow query execution plans
• pg_stat_statements: Tracks planning and execution statistics for grouped SQL statements

These extensions are critical for query performance monitoring, which is why the default value of pg_libs is pg_stat_statements, auto_explain.

We strongly recommend retaining these extensions when configuring additional loaded modules.

Caveats

In shared_preload_libraries, separate multiple extensions with commas:

shared_preload_libraries = 'timescaledb, pg_stat_statements, auto_explain'

Loading sequence can be significant. For example, citus and timescaledb must be placed at the beginning of shared_preload_libraries. If using both extensions simultaneously (uncommon), place citus before timescaledb.

The full-text search plugin pg_search requires explicit loading in current versions, but in PostgreSQL 17, this requirement is removed.

For the MongoDB emulation plugin documentdb, note that the dynamic library names differ from the extension names — use pg_documentdb and pg_documentdb_core instead.

Extensions that Need Loading

In the Extension List, extensions marked with LOAD require dynamic loading and a server restart. These include:

5 - Create Extensions

Quick Start

After installing PostgreSQL extensions, you can enable (create) them using the CREATE EXTENSION statement:

CREATE EXTENSION vector;      -- Enable vector database extension (no explicit loading required)
CREATE EXTENSION timescaledb; -- Enable time-series database extension (explicit loading required)

Some extensions have dependencies on other extensions. In such cases, you can either install the dependencies first or use the CREATE EXTENSION CASCADE command to install all dependencies at once.

CREATE EXTENSION documentdb CASCADE; -- create documentdb extension and all its dependencies

You can also specify the schema and specific version in the command.

Configure

Extensions (database logical objects) are logically part of PostgreSQL databases. In Pigsty, you can specify which extensions to be created in a database using pg_databases.

pg_databases:
  - name: postgres
    baseline: supabase.sql
    schemas: [ extensions ,auth ,realtime ,storage ,graphql_public ,supabase_functions ,_analytics ,_realtime ]
    extensions:                                 # Extensions to be enabled in the postgres database
      - { name: pgcrypto  ,schema: extensions } # cryptographic functions
      - { name: pg_net    ,schema: extensions } # async HTTP
      - { name: pgjwt     ,schema: extensions } # json web token API for postgres
      - { name: uuid-ossp ,schema: extensions } # generate universally unique identifiers (UUIDs)
      - { name: pgsodium        }               # pgsodium is a modern cryptography library for Postgres.
      - { name: supabase_vault  }               # Supabase Vault Extension
      - { name: pg_graphql      }               # pg_graphql: GraphQL support
      - { name: pg_jsonschema   }               # pg_jsonschema: Validate json schema
      - { name: wrappers        }               # wrappers: FDW collections
      - { name: http            }               # http: allows web page retrieval inside the database.
      - { name: pg_cron         }               # pg_cron: Job scheduler for PostgreSQL
      - { name: timescaledb     }               # timescaledb: Enables scalable inserts and complex queries for time-series data
      - { name: pg_tle          }               # pg_tle: Trusted Language Extensions for PostgreSQL
      - { name: vector          }               # pgvector: the vector similarity search
      - { name: pgmq            }               # pgmq: A lightweight message queue like AWS SQS and RSMQ

Here, the extensions in the database object is a list where each element can be:

• A simple string representing the extension name, such as vector
• A dictionary that may contain the following fields:
  • name: The only required field, specifying the extension name, which may differ from the extension package name.
  • schema: Specifies the schema for installing the extension, defaults to the first schema in the current dbsu search path, usually the default public.
  • version: Specifies the extension version, defaults to the latest version, rarely used.

If the database doesn’t exist yet, the extensions defined here will be automatically created when creating a cluster or creating a database through Pigsty.

If the database is already created, it’s recommended to manage extension through standard schema migration procedure. You can bookkeeping corresponding changes in the Pigsty inventory to help future migration tasks.

Default

Pigsty create several extensions by default for managed PostgreSQL databases. These extensions are created in the default template1 database and the postgres database. Any newly created database will inherit the extension from template1, so you don’t need extra configure.

You can modify default extensions list by overwritting the pg_default_extensions.

All default extensions are built-in Contrib extensions that come with PostgreSQL, with the sole exception of the pg_repack, which is a third-party extension from PGDG. and pg_repack is crucial for PostgreSQL bloat maintainance, so Pigsty installs it by default and enables it in all databases.

pg_default_extensions:
  - { name: pg_stat_statements ,schema: monitor }
  - { name: pgstattuple        ,schema: monitor }
  - { name: pg_buffercache     ,schema: monitor }
  - { name: pageinspect        ,schema: monitor }
  - { name: pg_prewarm         ,schema: monitor }
  - { name: pg_visibility      ,schema: monitor }
  - { name: pg_freespacemap    ,schema: monitor }
  - { name: postgres_fdw       ,schema: public  }
  - { name: file_fdw           ,schema: public  }
  - { name: btree_gist         ,schema: public  }
  - { name: btree_gin          ,schema: public  }
  - { name: pg_trgm            ,schema: public  }
  - { name: intagg             ,schema: public  }
  - { name: intarray           ,schema: public  }
  - { name: pg_repack } # <-- The only 3rd-party extension created by default

In Pigsty’s design, monitoring-related extensions are created in the monitor schema, while other functional extensions are created in the public schema.

Additionally, the vector database extension pgvector has a special status. It is installed by default in Pigsty (in the pgsql-main alias) and enabled in the placeholder meta database.

Finally, the key extension for implementing CDC (Change Data Capture), the wal2json, is also installed by default, but since it’s an “Extension without DDL”, so it doesn’t appear in pg_default_extensions.

Extensions without DDL

Not all extensions require the CREATE EXTENSION command to be enabled.

In principle, PostgreSQL extensions typically consist of three parts:

• Control file: Contains key metadata, required
• SQL file: Contains SQL statements, optional
• Library file: Contains binary shared libraries (.so, .dylib, .dll), optional

The SQL file is optional, so extensions without an SQL file typically don’t require executing the CREATE EXTENSION command to enable.

For example, the wal2json extension providing CDC extraction capabilities, the pg_stat_statements and auto_explain extensions providing slow query statistics. They only have shared library files and extension .control files, without SQL files, so they don’t need/cannot be enabled through the CREATE EXTENSION command.

Note that not having a CREATE EXTENSION command doesn’t affect whether an extension needs to be LOAD. Some extensions may not have SQL/DDL but still require explicit loading, such as some security, stat, audit-related extensions.

List of Extensions Without DDL

Below is a list of all extensions that don’t require CREATE EXTENSION DDL:

6 - Update Extension

To update an existing extension, you need to first update the RPM/DEB package in your OS’s package manager, then update the extension to the new version in PostgreSQL using the ALTER EXTENSION ... UPDATE command.

Upgrade Packages

All extensions listed in pg_extensions can be upgraded using Pigsty’s pgsql.yml playbook:

./pgsql.yml -t pg_extension

This will automatically install the latest available version of extension RPM/DEB packages in your current environment.

You can also upgrade extensions with the pig ext update command, or using yum/apt upgrade directly.

yum upgrade extname...
apt upgrade extname...

Pigsty’s pig cli can also help you with that, without the burden of specifying full package names:

pig ext update extname...

Alter Extension

Execute the ALTER EXTENSION ... UPDATE command in the database to update the extension to the new version:

ALTER EXTENSION name UPDATE [ TO new_version ]

If you omit the TO new_version clause, the extension will be updated to the latest version available in the database.

7 - Remove Extension

Remove Extension

To uninstall an extension, you typically need to run the DROP EXTENSION statement:

DROP EXTENSION "<extname>";

Note that if other extensions or database objects depend on this extension, you’ll need to remove those dependencies first before uninstalling the extension.

Alternatively, you can forcefully uninstall the extension and all its dependencies in one operation:

DROP EXTENSION "<extname>" CASCADE;

Warning: The CASCADE option will delete all objects that depend on this extension, including database objects, functions, views, etc. Use with caution!

Not all extensions are installed via the CREATE EXTENSION statement. These extensions don’t require explicit execution of the DROP EXTENSION statement. Please refer to the Extensions Without DDL section.

Remove Loading

If you’re using an extension that requires dynamic loading (which modifies the shared_preload_libraries parameter), you need to first re-confnigure the shared_preload_libraries parameter.

Remove the extension name from shared_preload_libraries and restart the database cluster for the changes to take effect.

For extensions that need dynamic loading, refer to the Extensions that Need Loading list.

Uninstall Package

After removing the extension (logical object) from all databases in the cluster, you can safely uninstall the extension’s software package. Ansible commands can help you do this conveniently:

ansible <cls> -m package -a "name=<extname> state=absent"

You can also use pig, or apt/yum commands directly to uninstall.

If you don’t know the extension package name, you can refer to the Extension List or check the extension package name mapping defined in roles/node_id/vars.