Get Started

How to use PostgreSQL extensions out-of-the-box in Pigsty
Module:
Tags:

Pigsty allows you to download, install, load, and create PostgreSQL extensions declaratively with the PGSQL module.

Out of the Box

Pigsty abstracts away the complexity of extension management, allowing you to declare your extensions in the config inventory:

For example, the following configuration snippet declares a PostgreSQL cluster that downloads, installs, dynamically loads, and enables 3 extensions:

all:
  children:
    pg-meta:
      hosts: {10.10.10.10: { pg_seq: 1, pg_role: primary }}
      vars:           
        pg_cluster: pg-meta
        pg_databases: {name: meta, extensions: [ postgis, timescaledb, vector ]} # Enable 3 extensions (using extension names)
        pg_libs: 'timescaledb, pg_stat_statements, auto_explain'                 # Load 3 extensions (last 2 are built-in)
        pg_extensions: [ pgsql-main, postgis pgvector timescaledb ]              # Install 3 additional extensions
        repo_extra_packages: [ postgis, timescaledb, vector ]                    # Download 3 extension packages

When you run the ./install.yml playbook, these 3 extension packages will be downloaded to the local repo, and the newly created PostgreSQL cluster pg-meta will automatically install, load, and enable these specified extensions.

Concept

Using extensions in a PostgreSQL cluster involves 4 essential operations: download, install, load, and enable:

  • How to download extensions: repo_extra_packages

    By default, Pigsty’s online installation only downloads three extensions for the current PostgreSQL major version (pg_repack, wal2json, pgvector).

    To download extra extensions, add them to repo_extra_packages. use the extension package alias and category alias for batch download.

  • Which extensions to install: pg_extensions

    Pigsty’s configuration templates include a comprehensive list of available extensions. Simply add the extensions you want to install to pg_extensions.

    To install additional extensions after cluster creation, configure the extensions and run ./pgsql.yml -t pg_extension.

  • Which extensions to load: pg_libs

    Some extensions using PostgreSQL hook functions must be dynamically loaded and require a database restart to take effect. Add these extensions to pg_libs.

    For existing clusters, modify the cluster config by changing the shared_preload_libraries parameter and restart the database.

  • Which extensions to create: pg_databases.extensions

    Most extensions require executing the CREATE EXTENSION DDL statement after installation to be enabled in a specific database.

    You can execute this DDL manually or specify extensions to enable in pg_databases.extensions, which will be automatically enabled during cluster initialization.

Package Alias

When loading and enabling extensions, we use “extension names” (ext), while for downloading and installing, we use “extension package names” (pkg or alias).

For example, the vector database extension’s name ext is vector, while its pkg is pgvector.

flowchart LR
    ext[( EXTNAME )] -- "n:1" --> pkg[( PKGNAME )]
    pkg[( PKGNAME )] -- "1:n" --> packages[( RPM/DEB )]

The extension alias is an abstraction layer added by Pigsty to address package name differences across OS distros. In most cases, extension names (ext) and package names (pkg) are identical. However, one extension pkg may contain multiple ext extension objects.

For example, the postgis package includes postgis and 6+ extensions. Additionally, some extension names ext conflict with OS packages (lkie acl, vector), so you must use the extension package name (pkg) for downloading and installation.

In Pigsty, you can use extension package names (pkg) in parameters like repo_extra_packages, pg_packages, and pg_extensions. To install the postgis extension, you can use:

  • pkg alias provided by Pigsty, which will translate to actual RPM/DEB package names based on active PG version and OS.
  • Real package names with $v placeholders, automatically replaced with the value of pg_version.
  • Original OS RPM/DEB package names with * wildcards or specified each directly
postgis                   # Extension alias, auto-translated to appropriate package name
postgis35_$v*             # RPM package name pattern with PG version placeholder
postgis35_15*             # Direct RPM package name
postgresql-$v-postgis-3*  # DEB package name pattern with PG version placeholder
postgresql-14-postgis-3*  # Direct DEB package name

Extension-to-package mappings are available on the Extension List page. Each extension package corresponds to different RPM/DEB packages depending on OS and PostgreSQL version combinations.

We recommend using Pigsty’s standardized extension alias (pkg) for installation. Pigsty translates these names into appropriate package names for your specific OS/PG combination, eliminating the need to manage these differences manually.

There are slight variations in available extensions across operating systems and architectures. For authoritative references, check these configuration files:

Pigsty strives to align PostgreSQL extensions between EL and Debian operating systems, but some extensions remain difficult to port or haven’t yet been ported. See the RPM Extensions List and DEB Extensions List for more information.

Complex Example

Here’s a concrete example: the app/supa configuration template used to build Supabase:

Supabase is an “open source Firebase alternative” built on PostgreSQL that heavily uses extensions. The following snippet defines the extensions needed by Supabase:

all:
  children:
    # pg-meta, the underlying postgres database for supabase
    pg-meta:
      hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
      vars:
        pg_cluster: pg-meta
        pg_users:
          # supabase roles: anon, authenticated, dashboard_user
          - { name: anon           ,login: false }
          - { name: authenticated  ,login: false }
          - { name: dashboard_user ,login: false ,replication: true ,createdb: true ,createrole: true }
          - { name: service_role   ,login: false ,bypassrls: true }
          # supabase users: please use the same password
          - { name: supabase_admin             ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: true   ,roles: [ dbrole_admin ] ,superuser: true ,replication: true ,createdb: true ,createrole: true ,bypassrls: true }
          - { name: authenticator              ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false  ,roles: [ dbrole_admin, authenticated ,anon ,service_role ] }
          - { name: supabase_auth_admin        ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false  ,roles: [ dbrole_admin ] ,createrole: true }
          - { name: supabase_storage_admin     ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false  ,roles: [ dbrole_admin, authenticated ,anon ,service_role ] ,createrole: true }
          - { name: supabase_functions_admin   ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: false  ,roles: [ dbrole_admin ] ,createrole: true }
          - { name: supabase_replication_admin ,password: 'DBUser.Supa' ,replication: true ,roles: [ dbrole_admin ]}
          - { name: supabase_read_only_user    ,password: 'DBUser.Supa' ,bypassrls: true ,roles: [ dbrole_readonly, pg_read_all_data ] }
        pg_databases:
          - name: postgres
            baseline: supabase.sql
            owner: supabase_admin
            comment: supabase postgres database
            schemas: [ extensions ,auth ,realtime ,storage ,graphql_public ,supabase_functions ,_analytics ,_realtime ]
            extensions:                                 # Extensions to enable in the postgres database
              - { name: pgcrypto  ,schema: extensions } # Encryption functions
              - { name: pg_net    ,schema: extensions } # Asynchronous HTTP
              - { name: pgjwt     ,schema: extensions } # JSON Web Token API for PostgreSQL
              - { name: uuid-ossp ,schema: extensions } # Generate universally unique identifiers (UUIDs)
              - { name: pgsodium        }               # Modern cryptography for PostgreSQL
              - { name: supabase_vault  }               # Supabase Vault extension
              - { name: pg_graphql      }               # GraphQL support
              - { name: pg_jsonschema   }               # JSON schema validation
              - { name: wrappers        }               # Collection of foreign data wrappers
              - { name: http            }               # Web page retrieval within the database
              - { name: pg_cron         }               # Job scheduler for PostgreSQL
              - { name: timescaledb     }               # Time-series data support
              - { name: pg_tle          }               # Trusted Language Extensions for PostgreSQL
              - { name: vector          }               # Vector similarity search
              - { name: pgmq            }               # Lightweight message queue
        # supabase required extensions for loading
        pg_libs: 'timescaledb, plpgsql, plpgsql_check, pg_cron, pg_net, pg_stat_statements, auto_explain, pg_tle, plan_filter'
        pg_parameters:
          cron.database_name: postgres
          pgsodium.enable_event_trigger: off
        pg_hba_rules: # supabase hba rules, require access from docker network
          - { user: all ,db: postgres  ,addr: intra         ,auth: pwd ,title: 'allow supabase access from intranet'    }
          - { user: all ,db: postgres  ,addr: 172.17.0.0/16 ,auth: pwd ,title: 'allow access from local docker network' }
        node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am

  vars:  # Global parameter configuration
    pg_version: 17
    repo_modules: node,pgsql,infra,docker
    repo_packages: [node-bootstrap, infra-package, infra-addons, node-package1, node-package2, pgsql-utility, docker ]
    repo_extra_packages: [pg17-core ,pg17-time ,pg17-gis ,pg17-rag ,pg17-fts ,pg17-olap ,pg17-feat ,pg17-lang ,pg17-type ,pg17-util ,pg17-func ,pg17-admin ,pg17-stat ,pg17-sec ,pg17-fdw ,pg17-sim ,pg17-etl ]
    pg_extensions:                  [pg17-time ,pg17-gis ,pg17-rag ,pg17-fts ,pg17-feat ,pg17-lang ,pg17-type ,pg17-util ,pg17-func ,pg17-admin ,pg17-stat ,pg17-sec ,pg17-fdw ,pg17-sim ,pg17-etl ] #,pg17-olap]

In this example, we declare a PostgreSQL cluster named pg-meta with the following extension configuration:

  • repo_extra_packages: Batch download all available extensions by 16 major categories
  • pg_extensions: Install all extension packages except for pg17-olap
  • pg_libs: Dynamically load the extensions required by Supabase
  • pg_parameters: Set configuration parameters needed by extensions (e.g., pgsodium and pg_cron)
  • pg_databases.extensions: Specify which extensions to enable and in which schema

The baseline: supabase.sql includes additional SQL migration logic for custom extension configuration.

By simply executing ./install.yml, users get a fully configured PostgreSQL cluster with all the extensions Supabase requires, ready to use out of the box!




Last modified 2025-03-21: replace vonng to pgsty (75336f2)