Install Extension

This section of the guide describes how to install extensions.

info

See the list of 300+ extensions available for installation, but you’re not limited to it—you can install any extension through various methods, including building from source.

Console (UI)

Extension installation is currently supported only through the command line.

tip

If you’re interested in having this functionality available through the UI, please consider becoming a sponsor.

Command line

Method 1: Auto-Setup

This feature simplifies the installation of popular PostgreSQL extensions by automating setup and configuration.
It adds repositories, installs packages, and sets parameters like shared_preload_libraries as needed for seamless integration.

List of extensions supported in “Auto-Setup” mode:

Extension	Description	Variable
timescaledb	Enables scalable inserts and complex queries for time-series data. Provided by Timescale	enable_timescaledb
citus	Distributed PostgreSQL as an extension	enable_citus
pg_search	Full text search for PostgreSQL using BM25. Provided by ParadeDB	enable_pg_search or enable_paradedb
pg_analytics	Postgres for analytics, powered by DuckDB. Provided by ParadeDB	enable_pg_analytics or enable_paradedb
vector	pgvector: vector data type and ivfflat and hnsw access methods	enable_pgvector
vectorscale	pgvectorscale: Advanced indexing for vector data	enable_pgvectorscale
postgis	PostGIS geometry and geography spatial types and functions	enable_postgis
pgrouting	Routing extension for PostGIS	enable_pgrouting
pg_cron	Job scheduler for PostgreSQL	enable_pg_cron
pgaudit	PostgreSQL Audit Extension	enable_pgaudit
pg_partman	Partition management extension for PostgreSQL	enable_pg_partman
pg_repack	Reorganize tables in PostgreSQL databases with minimal locks	enable_pg_repack
pg_stat_kcache	Gather statistics about physical disk access and CPU consumption done by backends	enable_pg_stat_kcache
pg_wait_sampling	Sampling based statistics of wait events	enable_pg_wait_sampling

Enable the following extensions by setting the respective variables to true, example:

ansible-playbook deploy_pgcluster.yml -e "enable_timescaledb=true"

Method 2: Packages

You can define any repositories and extension packages for installation.

1. Define the necessary repositories in apt_repository (or yum_repository) variable.

Example:

apt_repository:
  - repo: "deb https://apt.postgresql.org/pub/repos/apt/ {{ ansible_distribution_release }}-pgdg main"  # postgresql apt repository
    key: "https://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc"  # postgresql apt repository key
  - repo: "deb https://repo.pigsty.io/apt/pgsql/{{ ansible_distribution_release }} {{ ansible_distribution_release }} main"  # Pigsty apt repository
    key: "https://repo.pigsty.io/key"  # Pigsty apt repository key

2. Define the necessary packages in postgresql_packages variable.

Example:

postgresql_packages:
  - postgresql-{{ postgresql_version }}
  - postgresql-client-{{ postgresql_version }}
  - postgresql-contrib-{{ postgresql_version }}
  - postgresql-server-dev-{{ postgresql_version }}
  - postgresql-{{ postgresql_version }}-dbgsym
  - postgresql-{{ postgresql_version }}-cron
  - postgresql-{{ postgresql_version }}-repack
  - postgresql-{{ postgresql_version }}-pg-stat-kcache
  - postgresql-{{ postgresql_version }}-pg-wait-sampling
#  - postgresql-{{ postgresql_version }}-postgis-3
#  - postgresql-{{ postgresql_version }}-pgrouting
#  - postgresql-{{ postgresql_version }}-pgvector
#  - postgresql-{{ postgresql_version }}-pgaudit
#  - postgresql-{{ postgresql_version }}-partman

3. Define the necessary parameters in postgresql_parameters variable.

Example:

postgresql_parameters:
  - { option: "shared_preload_libraries", value:  "pg_stat_statements,auto_explain,pg_stat_kcache,pg_wait_sampling,pg_cron" }
  - { option: "pg_stat_statements.max", value: "10000" }
  - { option: "pg_stat_statements.track", value: "all" }
  - { option: "pg_stat_statements.track_utility", value: "false" }
  - { option: "pg_stat_statements.save", value: "true" }
  - { option: "auto_explain.log_min_duration", value: "10s" }  # enable auto_explain for 10-second logging threshold
  - { option: "auto_explain.log_analyze", value: "true" }
  - { option: "auto_explain.log_buffers", value: "true" }
  - { option: "auto_explain.log_timing", value: "false" }
  - { option: "auto_explain.log_triggers", value: "true" }
  - { option: "auto_explain.log_verbose", value: "true" }
  - { option: "auto_explain.log_nested_statements", value: "true" }
  - { option: "auto_explain.sample_rate", value: "0.01" }  # enable auto_explain for 1% of queries logging threshold
  - { option: "cron.database_name", value: "postgres" }

4. Define the necessary extensions in postgresql_extensions variable.

Example:

postgresql_extensions:
  - { ext: "pg_cron", db: "postgres" }
  - { ext: "pg_stat_statements", db: "mydatabase" }
  - { ext: "pg_stat_kcache", db: "mydatabase" }
  - { ext: "pg_wait_sampling", db: "mydatabase" }
  - { ext: "dblink", db: "mydatabase" }

Method 3: Source code

If needed, use pre_deploy_command or post_deploy_command to run a command, bash script, or script path on the host to build an extension from source.

Available variables:

# Execute custom commands or scripts
pre_deploy_command: "" # Command or script to be executed before the Postgres cluster deployment
pre_deploy_command_timeout: 3600 # Timeout in seconds
pre_deploy_command_hosts: "postgres_cluster" # host groups where the pre_deploy_command should be executed
pre_deploy_command_print: true # Print the command in the ansible log
pre_deploy_command_print_result: true # Print the result of the command execution to the ansible log

post_deploy_command: "" # Command or script to be executed after the Postgres cluster deployment
post_deploy_command_timeout: 3600 # Timeout in seconds
post_deploy_command_hosts: "postgres_cluster" # host groups where the post_deploy_command should be executed
post_deploy_command_print: true # Print the command in the ansible log
post_deploy_command_print_result: true # Print the result of the command execution to the ansible log