Extensions
Module:
Categories:
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 336 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_$v* wal2json_$v* passwordcheck_cracklib_$v* # important extensions
pg_extensions: # install postgis, timescaledb, pgvector by default
- postgis34_$v* timescaledb-2-postgresql-$v* pgvector_$v*
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-$v-repack postgresql-$v-wal2json
pg_extensions: # these extensions are installed by default:
- postgresql-$v-postgis* timescaledb-2-postgresql-$v postgresql-$v-pgvector postgresql-$v-citus-12.1
Here, $v
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 extensionpgvector
: Vector datatype and ivfflat/hnsw indexcitus
: Distributed/columnar storage extension, (citus is conflict withhydra
, 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_$v* timescaledb-2-postgresql-$v* pgvector_$v* hydra_$v* # default extensions to be installed
- pg_cron_$v* # <---- new extension: pg_cron
- apache-age_$v* # <---- new extension: apache-age
- zhparser_$v* # <---- 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: ['*$v*']
, 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
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.