Extensions

Extensions are the soul of PostgreSQL, and Pigsty deeply integrates the core extension plugins of the PostgreSQL ecosystem, providing you with battery-included distributed temporal, geospatial text, graph, and vector database capabilities! Check extension list for details.

Pigsty includes over 150+ PostgreSQL extension plugins and has compiled, packaged, integrated, and maintained many extensions not included in the official PGDG source. It also ensures through thorough testing that all these plugins can work together seamlessly. Including some potent extensions:

• PostGIS: Add geospatial data support to PostgreSQL
• TimescaleDB: Add time-series/continuous-aggregation support to PostgreSQL
• PGVector: AI vector/embedding data type support, and ivfflat / hnsw index access method
• Citus: Turn a standalone primary-replica postgres cluster into a horizontally scalable distributed cluster
• Apache AGE: Add OpenCypher graph query language support to PostgreSQL, works like Neo4J
• PG GraphQL: Add GraphQL language support to PostgreSQL
• zhparser : Add Chinese word segmentation support to PostgreSQL, works like ElasticSearch
• Supabase: Open-Source Firebase alternative based on PostgreSQL
• FerretDB: Open-Source MongoDB alternative based on PostgreSQL
• PostgresML: Use machine learning algorithms and pretrained models with SQL
• ParadeDB: Open-Source ElasticSearch Alternative (based on PostgreSQL)

Plugins are already included and placed in the yum repo of the infra nodes, which can be directly enabled through PGSQL Cluster Config. Pigsty also introduces a complete compilation environment and infrastructure, allowing you to compile extensions not included in Pigsty & PGDG.

Some “database” are not actual PostgreSQL extensions, but also supported by pigsty, such as:

• Supabase: Open-Source Firebase Alternative (based on PostgreSQL)
• FerretDB: Open-Source MongoDB Alternative (based on PostgreSQL)
• NocoDB: Open-Source Airtable Alternative (based on PostgreSQL)
• DuckDB: Open-Source Analytical SQLite Alternative (PostgreSQL Compatible)

Install Extension

When you init a PostgreSQL cluster, the extensions listed in pg_packages & pg_extensions will be installed.

For default EL systems, the default values of pg_packages and pg_extensions are defined as follows:

pg_packages:     # these extensions are always installed by default : pg_repack, wal2json, passwordcheck_cracklib
  - pg_repack_${pg_version}* wal2json_${pg_version}* passwordcheck_cracklib_${pg_version}* # important extensions
pg_extensions:   # install postgis, timescaledb, pgvector by default
  - postgis34_${pg_version}* timescaledb-2-postgresql-${pg_version}* pgvector_${pg_version}*

For ubuntu / debian, package names are different, and passwordcheck_cracklib is not available.

pg_packages:    # these extensions are always installed by default : pg_repack, wal2json
  - postgresql-${pg_version}-repack postgresql-${pg_version}-wal2json
pg_extensions:  # these extensions are installed by default:
  - postgresql-${pg_version}-postgis* timescaledb-2-postgresql-${pg_version} postgresql-${pg_version}-pgvector postgresql-${pg_version}-citus-12.1

Here, ${pg_version} is a placeholder that will be replaced with the actual major version number pg_version of that PostgreSQL cluster Therefore, the default configuration will install these extensions:

• pg_repack: Extension for online table bloat processing.
• wal2json: Extracts changes in JSON format through logical decoding.
• passwordcheck_cracklib: Enforce password policy. (EL only)
• postgis: Geospatial database extension (postgis34, EL7: postgis33)
• timescaledb: Time-series database extension
• pgvector: Vector datatype and ivfflat/hnsw index
• citus: Distributed/columnar storage extension, (citus is conflict with hydra, choose one of them on EL systems)

If you want to enable certain extensions in a target cluster that has not yet been created, you can directly declare them with the parameters:

pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1 ,pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_databases:
      - name: test
        extensions:                 # <----- install these extensions for database `test`
          - { name: postgis, schema: public }
          - { name: timescaledb }
          - { name: pg_cron }
          - { name: vector }
          - { name: age }
    pg_libs: 'timescaledb, pg_cron, pg_stat_statements, auto_explain' # <- some extension require a share library to work
    pg_extensions:
      - postgis34_${pg_version}* timescaledb-2-postgresql-${pg_version}* pgvector_${pg_version}* hydra_${pg_version}*   # default extensions to be installed
      - pg_cron_${pg_version}*        # <---- new extension: pg_cron
      - apache-age_${pg_version}*     # <---- new extension: apache-age
      - zhparser_${pg_version}*       # <---- new extension: zhparser

You can run the pg_extension sub-task in pgsql.yml to add extensions to clusters that have already been created.

./pgsql.yml -l pg-meta -t pg_extension    # install specified extensions for cluster pg-v15

To install all available extensions in one pass, you can just specify pg_extensions: ['*${pg_version}*'], which is really a bold move.

Install Manually

After the PostgreSQL cluster is inited, you can manually install plugins via Ansible or Shell commands. For example, if you want to enable a specific extension on a cluster that has already been initialized:

cd ~/pigsty;    # enter pigsty home dir and install the apache age extension for the pg-test cluster
ansible pg-test -m yum -b -a 'name=apache-age_16*'     # The extension name usually has a suffix like `_<pgmajorversion>`

Most plugins are already included in the yum repository on the infrastructure node and can be installed directly using the yum command. If not included, you can consider downloading from the PGDG upstream source using the repotrack / apt download command or compiling source code into RPMs for distribution.

After the extension installation, you should be able to see them in the pg_available_extensions view of the target database cluster. Next, execute in the database where you want to install the extension:

CREATE EXTENSION age;          -- install the graph database extension