This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Use Extension

How to install, load, create, update, remove PostgreSQL extensions in Pigsty?

1 - Get Started

How to use PostgreSQL extension in an out-of-the-box manner with Pigsty!

Pigsty allows you to manage PostgreSQL extension in a declarative way

Overview

To use an extension in Pigsty managed PostgreSQL cluster, you need to deal with four issues: download, install, load, and create:

  • How to Download?: repo_upstream & repo_packages

    When performing the default online installation in Pigsty, all available extensions for the current primary PostgreSQL version (16) are automatically downloaded. If you do not need additional or niche extensions, you don’t need to worry about repo_upstream, repo_packages, or any issues related to extension downloads.

  • How to Install?: pg_extensions & pg_packages

    In the config template, a complete list of available extension alias is already included. To install additional extensions, simply add/uncomment them to pg_packages and pg_extensions.

  • How to Load?: pg_libs & pg_parameters

    A small number of extensions that utilize PostgreSQL HOOKs need to be dynamically loaded and will only take effect after restarting the database server.

  • You should add these extensions to pg_libs, or manually overwrite shared_preload_libraries in pg_parameters or DCS, and ensure they are loaded upon restart.

  • How to Create: pg_databases.extensions

    Most extensions require the execution of the CREATE EXTENSION DDL statement after installation to actually create and enable them in a specific database.

  • You can manually execute this DDL, or explicitly specify the extensions in pg_databases.extensions, and the database will automatically enable these extensions during initialization.

Out-Of-The-Box

Pigsty seals the complexity of extension management for users. You don’t need to know the RPM package names of these extensions, nor how to download, install, load, or enable them. You only need to declare the extensions you require in the configuration file.

For example, the following configuration snippet declares a PostgreSQL cluster that installs all available extension plugins, dynamically loads three extensions, and enables these 3 extensions.

pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-meta
    pg_databases:
      - name: meta
        extensions:
          - { name: postgis     }
          - { name: timescaledb }
          - { name: vector      }
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'
    pg_extensions: # extensions to be installed on this cluster
      - timescaledb periods temporal_tables emaj table_version pg_cron pg_later pg_background pg_timetable
      - postgis pgrouting pointcloud pg_h3 q3c ogr_fdw geoip #pg_geohash #mobilitydb
      - pgvector pgvectorscale pg_vectorize pg_similarity pg_tiktoken pgml #smlar
      - pg_search pg_bigm zhparser hunspell
      - hydra pg_lakehouse pg_duckdb duckdb_fdw pg_fkpart pg_partman plproxy #pg_strom citus
      - pg_hint_plan age hll rum pg_graphql pg_jsonschema jsquery index_advisor hypopg imgsmlr pg_ivm pgmq pgq #rdkit
      - pg_tle plv8 pllua plprql pldebugger plpgsql_check plprofiler plsh #pljava plr pgtap faker dbt2
      - prefix semver pgunit md5hash asn1oid roaringbitmap pgfaceting pgsphere pg_country pg_currency pgmp numeral pg_rational pguint ip4r timestamp9 chkpass #pg_uri #pgemailaddr #acl #debversion #pg_rrule
      - topn pg_gzip pg_http pg_net pg_html5_email_address pgsql_tweaks pg_extra_time pg_timeit count_distinct extra_window_functions first_last_agg tdigest aggs_for_arrays pg_arraymath pg_idkit pg_uuidv7 permuteseq pg_hashids
      - sequential_uuids pg_math pg_random pg_base36 pg_base62 floatvec pg_financial pgjwt pg_hashlib shacrypt cryptint pg_ecdsa pgpcre icu_ext envvar url_encode #pg_zstd #aggs_for_vecs #quantile #lower_quantile #pgqr #pg_protobuf
      - pg_repack pg_squeeze pg_dirtyread pgfincore pgdd ddlx pg_prioritize pg_checksums pg_readonly safeupdate pg_permissions pgautofailover pg_catcheck preprepare pgcozy pg_orphaned pg_crash pg_cheat_funcs pg_savior table_log pg_fio #pgpool pgagent
      - pg_profile 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 pagevis powa pg_top #pg_statviz #pgexporter_ext #pg_mon
      - passwordcheck supautils pgsodium pg_vault anonymizer pg_tde pgsmcrypto pgaudit pgauditlogtofile pg_auth_mon credcheck pgcryptokey pg_jobmon logerrors login_hook set_user pg_snakeoil pgextwlist pg_auditor noset #sslutils
      - wrappers multicorn 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
      - orafce pgtt session_variable pg_statement_rollback pg_dbms_metadata pg_dbms_lock pgmemcache #pg_dbms_job #wiltondb
      - pglogical pgl_ddl_deploy pg_failover_slots wal2json wal2mongo decoderbufs decoder_raw mimeo pgcopydb pgloader pg_fact_loader pg_bulkload pg_comparator pgimportdoc pgexportdoc #repmgr #slony
      - gis-stack rag-stack fdw-stack fts-stack etl-stack feat-stack olap-stack supa-stack stat-stack json-stack

You might have noticed that the extension names here are not the RPM/DEB package names but rather normalized extension aliases that have been simplified and encapsulated by Pigsty.

Pigsty translates these standardized aliases into the corresponding RPM/DEB package names for the specific PostgreSQL major version on different operating system distributions. This way, you don’t have to worry about the differences in extension package names across various OS distributions.

During the Pigsty configure process, the default configuration generated for your specific OS distro will already include the above list. To install these extensions, you only need to uncomment the ones you need in the configuration file.

Please note that you can still directly use OS-specific RPM/DEB package names here if you prefer.

Predefined Stacks

If you are not sure which extensions to install, Pigsty provides you with some predefined extension collections (Stacks). You can choose one of them and any combination according to your needs, and add them to pg_extensions.

  • gis-stack: postgis, pgrouting, pointcloud, h3, q3c, ogr_fdw
  • rag-stack: pgvector, pgvectorscale, pg_vectorize, pg_similarity, pg_tiktoken, pgml
  • fts-stack: pg_search, pg_bigm, zhparser, hunspell
  • fdw-stack: wrappers, mysql_fdw, tds_fdw, sqlite_fdw
  • etl-stack: pglogical, pgl_ddl_deploy, wal2json, wal2mongo, decoderbufs, pg_fact_loader, pg_bulkload, pgloader, pgcopydb
  • feat-stack: age, hll, rum, pg_graphql, pg_jsonschema, jsquery, pg_ivm, pgq3, gzip, http, topn, pgjwt
  • olap-stack: duckdb, duckdb_fdw, pg-lakehouse, hydra, timescaledb, pg-fkpart, pg-partman, plproxy
  • supa-stack: pg-graphql, pg-jsonschema, wrappers, pgvector, cron, supautils, pgsodium, vault, pgjwt, http, pg-net, index_advisor
  • stat-stack: show-plans, pg-stat-kcache, pg-qualstats, pg-track-settings, pg-wait-sampling, pg-sqlog
  • json-stack: plv8, pg-jsonschema, pgjwt, jsquery, pg-graphql, ferretdb

When you specify these extension stack names in pg_extensions or pg_packages, Pigsty will automatically translate, expand, and install all the extension plugins in them.

Install, Load, and Create

Pigsty not only allows you to declare the extensions you need to install in the configuration file, but it also lets you directly specify the extensions that need to be loaded and enabled.

Here’s a concrete example: Supabase. Supabase is an “upstream abstract database” built on top of PostgreSQL, which heavily utilizes PostgreSQL’s extension mechanism. Below is a sample configuration file for creating a PostgreSQL cluster required for Supabase using Pigsty:

# supabase example cluster: pg-meta
# this cluster needs to be migrated with app/supabase/migration.sql :
# psql postgres://supabase_admin:[email protected]:5432/supa -v ON_ERROR_STOP=1 --no-psqlrc -f ~pigsty/app/supabase/migration.sql
pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - { name: supabase_admin ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: true   ,superuser: true ,replication: true ,createdb: true ,createrole: true ,bypassrls: true }
    pg_databases:
      - name: supa
        baseline: supa.sql    # the init-scripts: https://github.com/supabase/postgres/tree/develop/migrations/db/init-scripts
        owner: supabase_admin
        comment: supabase postgres database
        schemas: [ extensions ,auth ,realtime ,storage ,graphql_public ,supabase_functions ,_analytics ,_realtime ]
        extensions:
          - { name: pgcrypto  ,schema: extensions  } # 1.3   : cryptographic functions
          - { name: pg_net    ,schema: extensions  } # 0.9.2 : async HTTP
          - { name: pgjwt     ,schema: extensions  } # 0.2.0 : json web token API for postgres
          - { name: uuid-ossp ,schema: extensions  } # 1.1   : generate universally unique identifiers (UUIDs)
          - { name: pgsodium        }                # 3.1.9 : pgsodium is a modern cryptography library for Postgres.
          - { name: supabase_vault  }                # 0.2.8 : Supabase Vault Extension
          - { name: pg_graphql      }                # 1.5.7 : pg_graphql: GraphQL support
          - { name: pg_jsonschema   }                # 0.3.1 : pg_jsonschema: Validate json schema
          - { name: wrappers        }                # 0.4.1 : wrappers: FDW collections
          - { name: http            }                # 1.6   : http: allows web page retrieval inside the database.
          - { name: pg_cron         }
    # supabase required extensions
    pg_libs: 'pg_net, pg_cron, pg_stat_statements, auto_explain'    # add pg_net to shared_preload_libraries
    pg_extensions:
      - wal2json pg_repack
      - supa-stack # pgvector pg_cron pgsodium pg_graphql pg_jsonschema wrappers pgjwt pgsql_http pg_net supautils
    pg_parameters:
      cron.database_name: supa
      pgsodium.enable_event_trigger: off
    pg_hba_rules: # supabase hba rules, require access from docker network
      - { user: all ,db: supa ,addr: intra       ,auth: pwd ,title: 'allow supa database access from intranet'      }
      - { user: all ,db: supa ,addr: 172.0.0.0/8 ,auth: pwd ,title: 'allow supa database access from docker network'}
      - { user: all ,db: supa ,addr: all         ,auth: pwd ,title: 'allow supa database access from entire world'  }  # not safe!

In this example, we declare a PostgreSQL cluster named pg-meta, which contains a database called supa along with a set of extension plugins.

The supa-stack defined in pg_extensions translates to pgvector pg_cron pgsodium pg_graphql pg_jsonschema wrappers pgjwt pgsql_http pg_net supautils, which are automatically installed. Meanwhile, pg_libs specifies two extensions that need to be dynamically loaded: pg_net and pg_cron. Additionally, the necessary configuration parameters for the pgsodium and pg_cron extensions are pre-configured via pg_parameters.

Following that, these extensions are sequentially created and enabled in the specified or default schemas within pg_databases.extensions.

Finally, this out-of-the-box, highly available PostgreSQL cluster, ready to be used by stateless Supabase containers, can be fully launched with a single ./pgsql.yml command, providing a seamless experience.

2 - Download Extension

How to download new extension packages to Pigsty’s local software repository?

In Pigsty’s default installation mode, the downloading and installation of extension plugins are handled separately. Before installing extensions, you must ensure that the appropriate software repositories are added to the target node. Otherwise, the installation may fail due to missing packages.

During the installation process, Pigsty downloads all available extensions for the current major PG version (16) to the INFRA node and sets up a local software repository. This repository is used by all nodes, including the local machine. This approach accelerates installation, avoids redundant downloads, reduces network traffic, improves delivery reliability, and mitigates the risk of inconsistent version installations.

Alternatively, you can opt to add the upstream PostgreSQL software repository and its dependencies (OS software sources) directly to the target node managed by Pigsty. This method allows you to easily update plugins to the latest versions but requires internet access or an HTTP proxy. It may also be subject to network conditions and carries the potential risk of inconsistent installation versions.

Software Repo

During the initial installation, Pigsty downloads the packages specified by repo_upstream from the upstream software repository. The package names differ between EL systems and Debian/Ubuntu systems. The complete list can be found at the following links:

A few plugins are excluded by default due to various reasons. If you need these extensions, refer to the RPM/DEB package names in the extension list and add them to repo_upstream for download.

  • Heavy dependencies: pljava, plr
  • Niche overlap: repmgr, pgexporterext, pgpool
  • EL9 exclusives: pljava

Download Extension

To download new extension plugins, you can add them to repo_upstream and run the following tasks to update the local software repository and refresh the package cache on all nodes:

./infra.yml -t repo       # Re-download the specified packages to the local software repository
./node.yml  -t node_repo  # Refresh the metadata cache of the local software repository on all nodes

By default, Pigsty uses the local software source located on the INFRA node. If you prefer not to download these extensions to the local repository but instead use an online software repository for installation, you can directly add the upstream software source to the nodes:

./node.yml -t node_repo -e node_repo_modules=node,pgsql  # Add the Postgres plugin repository and OS software sources (dependencies)

After completing these tasks, you can install PostgreSQL extension plugins using the standard OS package manager (yum/apt).

3 - Extension Repo

How to use the pigsty extension repo?

YUM Repo

Pigsty currently offers a supplementary PG extension repository for EL systems, providing 121 additional RPM plugins in addition to the official PGDG YUM repository (135).

The Pigsty YUM repository only includes extensions not present in the PGDG YUM repository. Once an extension is added to the PGDG YUM repository, Pigsty YUM repository will either remove it or align with the PGDG repository.

For EL 7/8/9 and compatible systems, use the following commands to add the GPG public key and the upstream repository file of the Pigsty repository:

curl -fsSL https://repo.pigsty.io/key      | sudo tee /etc/pki/rpm-gpg/RPM-GPG-KEY-pigsty >/dev/null  # add gpg key
curl -fsSL https://repo.pigsty.io/yum/repo | sudo tee /etc/yum.repos.d/pigsty.repo        >/dev/null  # add repo file

All RPMs are signed with the GPG key fingerprint 9592A7BC7A682E7333376E09E7935D8DB9BD8B20 (B9BD8B20).

Write Repo File Manually
sudo tee /etc/yum.repos.d/pigsty-io.repo > /dev/null <<-'EOF'
[pigsty-infra]
name=Pigsty Infra for $basearch
baseurl=https://repo.pigsty.io/yum/infra/$basearch
skip_if_unavailable = 1
enabled = 1
priority = 1
gpgcheck = 1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-pigsty
module_hotfixes=1

[pigsty-pgsql]
name=Pigsty PGSQL For el$releasever.$basearch
baseurl=https://repo.pigsty.io/yum/pgsql/el$releasever.$basearch
skip_if_unavailable = 1
enabled = 1
priority = 1
gpgcheck = 1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-pigsty
module_hotfixes=1
EOF
sudo yum makecache;

APT Repo

Pigsty currently offers a supplementary PG extension repository for Debian/Ubuntu systems, providing 133 additional DEB packages in addition to the official PGDG APT repository (109).

The Pigsty APT repository only includes extensions not present in the PGDG APT repository. Once an extension is added to the PGDG APT repository, Pigsty APT repository will either remove it or align with the PGDG repository.

For Debian/Ubuntu and compatible systems, use the following commands to sequentially add the GPG public key and the upstream repository file of the Pigsty repository:

# add GPG key to keyring
curl -fsSL https://repo.pigsty.io/key | sudo gpg --dearmor -o /etc/apt/keyrings/pigsty.gpg

# get debian codename, distro_codename=jammy, focal, bullseye, bookworm
distro_codename=$(lsb_release -cs)
sudo tee /etc/apt/sources.list.d/pigsty-io.list > /dev/null <<EOF
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.io/apt/infra generic main 
deb [signed-by=/etc/apt/keyrings/pigsty.gpg] https://repo.pigsty.io/apt/pgsql/${distro_codename} ${distro_codename} main
EOF

# refresh APT repo cache
sudo apt update

All DEBs are signed with the GPG key fingerprint 9592A7BC7A682E7333376E09E7935D8DB9BD8B20 (B9BD8B20).

Repo of Repo

The building recipes and specs, metadata are all open-sourced, related GitHub repos:

  • pkg: The repository of RPM/DEB packages for PostgreSQL extensions
  • infra_pkg: Building observability stack & modules from tarball
  • pgsql-rpm: Building PostgreSQL RPM packages from source code
  • pgsql-deb: Building PostgreSQL DEB packages from source code

4 - Install Extension

How to install a extension from local repo, or directly from the Internet upstream repo.

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:

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:

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.

5 - Load Extension

Some extensions that use hook mechanism must be preloaded and restarted to take effect.

After installing PostgreSQL extensions, you can view them in the pg_available_extensions view in PostgreSQL.

Aside from extensions written purely in SQL, most extensions provide a .so file, which is a dynamic shared library.

Most extensions do not require explicit loading and can be enabled simply with CREATE EXTENSION. However, a small subset of extensions use PostgreSQL’s hook mechanism. These extensions must be preloaded using the shared_preload_libraries parameter and require a PostgreSQL restart to take effect. Attempting to execute CREATE EXTENSION without preloading and restarting will result in an error.

In Pigsty, you can predefine the extensions that need to be loaded in the cluster by specifying them in the cluster’s pg_libs parameter, or modify the cluster configuration after initializing the cluster.

Extensions that Need Loading

In the Extension List, extensions marked with LOAD are the ones that need to be dynamically loaded and require a restart. These include:

Extension Alias Description
timescaledb timescaledb Enables scalable inserts and complex queries for time-series data (Apache 2 Edition)
pgml pgml PostgresML: Run AL/ML workloads with SQL interface
citus citus Distributed PostgreSQL as an extension
pg_squeeze pg_squeeze A tool to remove unused space from a relation.
pgautofailover pgautofailover auto failover for PostgreSQL
pg_prewarm pg_prewarm prewarm relation data
pg_stat_kcache pg_stat_kcache Kernel statistics gathering
bgw_replstatus bgw_replstatus Small PostgreSQL background worker to report whether a node is a replication master or standby
auto_explain auto_explain Provides a means for logging execution plans of slow statements automatically
pg_stat_statements pg_stat_statements track planning and execution statistics of all SQL statements executed
passwordcheck_cracklib passwordcheck Strengthen PostgreSQL user password checks with cracklib
supautils supautils Extension that secures a cluster on a cloud environment
pg_tde pg_tde pg_tde access method
pgaudit pgaudit provides auditing functionality
pg_snakeoil pg_snakeoil The PostgreSQL Antivirus
pgextwlist pgextwlist PostgreSQL Extension Whitelisting
sepgsql sepgsql label-based mandatory access control (MAC) based on SELinux security policy.
auth_delay auth_delay pause briefly before reporting authentication failure
passwordcheck passwordcheck Server side rollback at statement level for PostgreSQL like Oracle or DB2
pg_statement_rollback pg_statement_rollback SQL Server Transact SQL compatibility
babelfishpg_tsql babelfishpg_tsql

Loading Order

In shared_preload_libraries, if multiple extensions need to be loaded, they can be separated by commas, for example:

  • 'timescaledb, pg_stat_statements, auto_explain'

Note that both Citus and TimescaleDB explicitly require preloading in shared_preload_libraries, meaning they should be listed first.

While it is rare to use both Citus and TimescaleDB simultaneously, in such cases, it is recommended to list citus before timescaledb.

Pigsty, by default, will load two extensions: pg_stat_statements and auto_explain. These extensions are very useful for optimizing database performance and are strongly recommended.

6 - Create Extension

How to use CREATE EXTENSION to actually enable a PostgreSQL extensions.

After installing PostgreSQL extensions, you can view them in the pg_available_extensions view. However, enabling these extensions typically requires additional steps:

  1. Some extensions must be added to the shared_preload_libraries for dynamic loading, such as timescaledb and citus.
  2. Most extensions need to be activated by running the SQL statement: CREATE EXTENSION <name>;. A few, like wal2json, do not require this step.

Modifying shared_preload_libraries:

  • Before initializing the database cluster: You can manually specify the required libraries using the pg_libsparameter.
  • After the database cluster has been initialized: You can modify the cluster configuration by directly editing the shared_preload_libraries parameter and applying the changes (no restart required).
  • Typical extensions that require dynamic loading: citus, timescaledb, pg_cron, pg_net, pg_tle

Executing CREATE EXTENSION:

  • Before initializing the database cluster: You can specify the required extensions in the extensions list within pg_databases.
  • After the database cluster has been initialized: You can directly connect to the database and execute the SQL command, or manage extensions using other schema management tools.

Conceptually: PostgreSQL extensions usually consist of three parts: a control file (metadata, always present), an SQL file (optional SQL statements), and a .so file (optional binary shared library). Extensions that provide a .so file may need to be added to shared_preload_libraries to function properly, such as citus and timescaledb. However, many extensions do not require this, such as postgis and pgvector. Extensions that do not expose a SQL interface do not need a CREATE EXTENSION command to be executed, such as the wal2json extension, which provides CDC extraction capabilities.

To complete the extension creation, execute the CREATE EXTENSION SQL statement in the database where you wish to enable the extension.

CREATE EXTENSION vector;  -- create & enable vector extension
CREATE EXTENSION hydra;   -- create & enable columnar extension

7 - Update Extension

How to update an existing extension?

To update an existing extension, you can use the OS package manager

yum upgrade pg_lakehouse_16*

And then perform the ALTER EXTENSION ... UPDATE command in the database to update the extension to the new version:

ALTER EXTENSION name UPDATE [ TO new_version ]
ALTER EXTENSION name SET SCHEMA new_schema
ALTER EXTENSION name ADD member_object
ALTER EXTENSION name DROP member_object

where member_object is:

  ACCESS METHOD object_name |
  AGGREGATE aggregate_name ( aggregate_signature ) |
  CAST (source_type AS target_type) |
  COLLATION object_name |
  CONVERSION object_name |
  DOMAIN object_name |
  EVENT TRIGGER object_name |
  FOREIGN DATA WRAPPER object_name |
  FOREIGN TABLE object_name |
  FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  MATERIALIZED VIEW object_name |
  OPERATOR operator_name (left_type, right_type) |
  OPERATOR CLASS object_name USING index_method |
  OPERATOR FAMILY object_name USING index_method |
  [ PROCEDURAL ] LANGUAGE object_name |
  PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  ROUTINE routine_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
  SCHEMA object_name |
  SEQUENCE object_name |
  SERVER object_name |
  TABLE object_name |
  TEXT SEARCH CONFIGURATION object_name |
  TEXT SEARCH DICTIONARY object_name |
  TEXT SEARCH PARSER object_name |
  TEXT SEARCH TEMPLATE object_name |
  TRANSFORM FOR type_name LANGUAGE lang_name |
  TYPE object_name |
  VIEW object_name

and aggregate_signature is:

* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]

8 - Remove Extension

How to safely remove a PostgreSQL extension from a database cluster?

To uninstall an extension, you typically need to perform the following steps:

DROP EXTENSION "<extname>";

Note that if there are other extensions or database objects dependent on this extension, you will need to uninstall/remove those dependencies first before uninstalling the extension.

Alternatively, you can use the following statement to forcefully uninstall the extension and its dependencies in one go:

DROP EXTENSION "<extname>" CASCADE;

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

If you wish to remove the extension’s package, you can use your operating system’s package manager to uninstall it:

9 - Pre-defined Stacks

How to use the predefined extension stacks in Pigsty?