# Extensions

> Define, Create, Install, Enable Extensions in Pigsty
---

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**](#extension-list) for details.

Pigsty includes **255** 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](https://github.com/Vonng/pigsty/tree/v2.7.0/app/supabase): Open-Source Firebase alternative based on PostgreSQL
- [FerretDB](https://github.com/Vonng/pigsty/tree/v2.7.0/app/ferretdb): Open-Source MongoDB alternative based on PostgreSQL
- [PostgresML](https://github.com/Vonng/pigsty/tree/v2.7.0/app/pgml): Use machine learning algorithms and pretrained models with SQL
- [ParadeDB](https://www.paradedb.com/): 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](#install-extension). Pigsty also introduces a complete compilation environment and infrastructure, allowing you to [compile extensions](https://github.com/Vonng/pigsty-rpm) not included in Pigsty & PGDG.

[![pigsty-extension.jpg](/img/pigsty/extension.png)](/img/pigsty/extension.png)

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

- [Supabase](https://github.com/Vonng/pigsty/tree/v2.7.0/app/supabase): Open-Source Firebase Alternative (based on PostgreSQL)
- [FerretDB](https://github.com/Vonng/pigsty/tree/v2.7.0/app/ferretdb): Open-Source MongoDB Alternative (based on PostgreSQL)
- [NocoDB](https://github.com/Vonng/pigsty/tree/v2.7.0/app/nocodb): Open-Source Airtable Alternative (based on PostgreSQL)
- [DuckDB](https://github.com/Vonng/pigsty/tree/v2.7.0/app/duckdb): Open-Source Analytical SQLite Alternative (PostgreSQL Compatible)


----------------

## Install Extension

When you init a PostgreSQL cluster, the extensions listed in [`pg_packages`](/docs/reference/param/#pg_packages) & [`pg_extensions`](/docs/reference/param/#pg_extension) will be installed.

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

```yaml
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.

```yaml
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`](/docs/reference/param/#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:


```yaml
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`](/docs/pgsql/playbook#pgsqlyml) to add extensions to clusters that have already been created.

```bash
./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:

```bash
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:

```sql
CREATE EXTENSION age;          -- install the graph database extension
```