Get Started

How to use PostgreSQL extension in an out-of-the-box manner with Pigsty!
Module:
Tags:

Pigsty allows you to manage PostgreSQL extension in a declarative way

Overview

To use an extension in Pigsty managed PostgreSQL cluster, you need to deal with four issues: download, install, load, and create:

  • How to Download?: repo_upstream & repo_packages

    When performing the default online installation in Pigsty, all available extensions for the current primary PostgreSQL version (16) are automatically downloaded. If you do not need additional or niche extensions, you don’t need to worry about repo_upstream, repo_packages, or any issues related to extension downloads.

  • How to Install?: pg_extensions & pg_packages

    In the config template, a complete list of available extension alias is already included. To install additional extensions, simply add/uncomment them to pg_packages and pg_extensions.

  • How to Load?: pg_libs & pg_parameters

    A small number of extensions that utilize PostgreSQL HOOKs need to be dynamically loaded and will only take effect after restarting the database server.

  • You should add these extensions to pg_libs, or manually overwrite shared_preload_libraries in pg_parameters or DCS, and ensure they are loaded upon restart.

  • How to Create: pg_databases.extensions

    Most extensions require the execution of the CREATE EXTENSION DDL statement after installation to actually create and enable them in a specific database.

  • You can manually execute this DDL, or explicitly specify the extensions in pg_databases.extensions, and the database will automatically enable these extensions during initialization.

Out-Of-The-Box

Pigsty seals the complexity of extension management for users. You don’t need to know the RPM package names of these extensions, nor how to download, install, load, or enable them. You only need to declare the extensions you require in the configuration file.

For example, the following configuration snippet declares a PostgreSQL cluster that installs all available extension plugins, dynamically loads three extensions, and enables these 3 extensions.

pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-meta
    pg_databases:
      - name: meta
        extensions:
          - { name: postgis     }
          - { name: timescaledb }
          - { name: vector      }
    pg_libs: 'timescaledb, pg_stat_statements, auto_explain'
    pg_extensions: # extensions to be installed on this cluster
      - timescaledb periods temporal_tables emaj table_version pg_cron pg_later pg_background pg_timetable
      - postgis pgrouting pointcloud pg_h3 q3c ogr_fdw geoip #pg_geohash #mobilitydb
      - pgvector pgvectorscale pg_vectorize pg_similarity pg_tiktoken pgml #smlar
      - pg_search pg_bigm zhparser hunspell
      - hydra pg_lakehouse pg_duckdb duckdb_fdw pg_fkpart pg_partman plproxy #pg_strom citus
      - pg_hint_plan age hll rum pg_graphql pg_jsonschema jsquery index_advisor hypopg imgsmlr pg_ivm pgmq pgq #rdkit
      - pg_tle plv8 pllua plprql pldebugger plpgsql_check plprofiler plsh #pljava plr pgtap faker dbt2
      - prefix semver pgunit md5hash asn1oid roaringbitmap pgfaceting pgsphere pg_country pg_currency pgmp numeral pg_rational pguint ip4r timestamp9 chkpass #pg_uri #pgemailaddr #acl #debversion #pg_rrule
      - topn pg_gzip pg_http pg_net pg_html5_email_address pgsql_tweaks pg_extra_time pg_timeit count_distinct extra_window_functions first_last_agg tdigest aggs_for_arrays pg_arraymath pg_idkit pg_uuidv7 permuteseq pg_hashids
      - sequential_uuids pg_math pg_random pg_base36 pg_base62 floatvec pg_financial pgjwt pg_hashlib shacrypt cryptint pg_ecdsa pgpcre icu_ext envvar url_encode #pg_zstd #aggs_for_vecs #quantile #lower_quantile #pgqr #pg_protobuf
      - pg_repack pg_squeeze pg_dirtyread pgfincore pgdd ddlx pg_prioritize pg_checksums pg_readonly safeupdate pg_permissions pgautofailover pg_catcheck preprepare pgcozy pg_orphaned pg_crash pg_cheat_funcs pg_savior table_log pg_fio #pgpool pgagent
      - pg_profile pg_show_plans pg_stat_kcache pg_stat_monitor pg_qualstats pg_store_plans pg_track_settings pg_wait_sampling system_stats pg_meta pgnodemx pg_sqlog bgw_replstatus pgmeminfo toastinfo pagevis powa pg_top #pg_statviz #pgexporter_ext #pg_mon
      - passwordcheck supautils pgsodium pg_vault anonymizer pg_tde pgsmcrypto pgaudit pgauditlogtofile pg_auth_mon credcheck pgcryptokey pg_jobmon logerrors login_hook set_user pg_snakeoil pgextwlist pg_auditor noset #sslutils
      - wrappers multicorn mysql_fdw tds_fdw sqlite_fdw pgbouncer_fdw mongo_fdw redis_fdw pg_redis_pubsub kafka_fdw hdfs_fdw firebird_fdw aws_s3 log_fdw #oracle_fdw #db2_fdw
      - orafce pgtt session_variable pg_statement_rollback pg_dbms_metadata pg_dbms_lock pgmemcache #pg_dbms_job #wiltondb
      - pglogical pgl_ddl_deploy pg_failover_slots wal2json wal2mongo decoderbufs decoder_raw mimeo pgcopydb pgloader pg_fact_loader pg_bulkload pg_comparator pgimportdoc pgexportdoc #repmgr #slony
      - gis-stack rag-stack fdw-stack fts-stack etl-stack feat-stack olap-stack supa-stack stat-stack json-stack

You might have noticed that the extension names here are not the RPM/DEB package names but rather normalized extension aliases that have been simplified and encapsulated by Pigsty.

Pigsty translates these standardized aliases into the corresponding RPM/DEB package names for the specific PostgreSQL major version on different operating system distributions. This way, you don’t have to worry about the differences in extension package names across various OS distributions.

During the Pigsty configure process, the default configuration generated for your specific OS distro will already include the above list. To install these extensions, you only need to uncomment the ones you need in the configuration file.

Please note that you can still directly use OS-specific RPM/DEB package names here if you prefer.

Predefined Stacks

If you are not sure which extensions to install, Pigsty provides you with some predefined extension collections (Stacks). You can choose one of them and any combination according to your needs, and add them to pg_extensions.

  • gis-stack: postgis, pgrouting, pointcloud, h3, q3c, ogr_fdw
  • rag-stack: pgvector, pgvectorscale, pg_vectorize, pg_similarity, pg_tiktoken, pgml
  • fts-stack: pg_search, pg_bigm, zhparser, hunspell
  • fdw-stack: wrappers, mysql_fdw, tds_fdw, sqlite_fdw
  • etl-stack: pglogical, pgl_ddl_deploy, wal2json, wal2mongo, decoderbufs, pg_fact_loader, pg_bulkload, pgloader, pgcopydb
  • feat-stack: age, hll, rum, pg_graphql, pg_jsonschema, jsquery, pg_ivm, pgq3, gzip, http, topn, pgjwt
  • olap-stack: duckdb, duckdb_fdw, pg-lakehouse, hydra, timescaledb, pg-fkpart, pg-partman, plproxy
  • supa-stack: pg-graphql, pg-jsonschema, wrappers, pgvector, cron, supautils, pgsodium, vault, pgjwt, http, pg-net, index_advisor
  • stat-stack: show-plans, pg-stat-kcache, pg-qualstats, pg-track-settings, pg-wait-sampling, pg-sqlog
  • json-stack: plv8, pg-jsonschema, pgjwt, jsquery, pg-graphql, ferretdb

When you specify these extension stack names in pg_extensions or pg_packages, Pigsty will automatically translate, expand, and install all the extension plugins in them.

Install, Load, and Create

Pigsty not only allows you to declare the extensions you need to install in the configuration file, but it also lets you directly specify the extensions that need to be loaded and enabled.

Here’s a concrete example: Supabase. Supabase is an “upstream abstract database” built on top of PostgreSQL, which heavily utilizes PostgreSQL’s extension mechanism. Below is a sample configuration file for creating a PostgreSQL cluster required for Supabase using Pigsty:

# supabase example cluster: pg-meta
# this cluster needs to be migrated with app/supabase/migration.sql :
# psql postgres://supabase_admin:[email protected]:5432/supa -v ON_ERROR_STOP=1 --no-psqlrc -f ~pigsty/app/supabase/migration.sql
pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - { name: supabase_admin ,password: 'DBUser.Supa' ,pgbouncer: true ,inherit: true   ,superuser: true ,replication: true ,createdb: true ,createrole: true ,bypassrls: true }
    pg_databases:
      - name: supa
        baseline: supa.sql    # the init-scripts: https://github.com/supabase/postgres/tree/develop/migrations/db/init-scripts
        owner: supabase_admin
        comment: supabase postgres database
        schemas: [ extensions ,auth ,realtime ,storage ,graphql_public ,supabase_functions ,_analytics ,_realtime ]
        extensions:
          - { name: pgcrypto  ,schema: extensions  } # 1.3   : cryptographic functions
          - { name: pg_net    ,schema: extensions  } # 0.9.2 : async HTTP
          - { name: pgjwt     ,schema: extensions  } # 0.2.0 : json web token API for postgres
          - { name: uuid-ossp ,schema: extensions  } # 1.1   : generate universally unique identifiers (UUIDs)
          - { name: pgsodium        }                # 3.1.9 : pgsodium is a modern cryptography library for Postgres.
          - { name: supabase_vault  }                # 0.2.8 : Supabase Vault Extension
          - { name: pg_graphql      }                # 1.5.7 : pg_graphql: GraphQL support
          - { name: pg_jsonschema   }                # 0.3.1 : pg_jsonschema: Validate json schema
          - { name: wrappers        }                # 0.4.1 : wrappers: FDW collections
          - { name: http            }                # 1.6   : http: allows web page retrieval inside the database.
          - { name: pg_cron         }
    # supabase required extensions
    pg_libs: 'pg_net, pg_cron, pg_stat_statements, auto_explain'    # add pg_net to shared_preload_libraries
    pg_extensions:
      - wal2json pg_repack
      - supa-stack # pgvector pg_cron pgsodium pg_graphql pg_jsonschema wrappers pgjwt pgsql_http pg_net supautils
    pg_parameters:
      cron.database_name: supa
      pgsodium.enable_event_trigger: off
    pg_hba_rules: # supabase hba rules, require access from docker network
      - { user: all ,db: supa ,addr: intra       ,auth: pwd ,title: 'allow supa database access from intranet'      }
      - { user: all ,db: supa ,addr: 172.0.0.0/8 ,auth: pwd ,title: 'allow supa database access from docker network'}
      - { user: all ,db: supa ,addr: all         ,auth: pwd ,title: 'allow supa database access from entire world'  }  # not safe!

In this example, we declare a PostgreSQL cluster named pg-meta, which contains a database called supa along with a set of extension plugins.

The supa-stack defined in pg_extensions translates to pgvector pg_cron pgsodium pg_graphql pg_jsonschema wrappers pgjwt pgsql_http pg_net supautils, which are automatically installed. Meanwhile, pg_libs specifies two extensions that need to be dynamically loaded: pg_net and pg_cron. Additionally, the necessary configuration parameters for the pgsodium and pg_cron extensions are pre-configured via pg_parameters.

Following that, these extensions are sequentially created and enabled in the specified or default schemas within pg_databases.extensions.

Finally, this out-of-the-box, highly available PostgreSQL cluster, ready to be used by stateless Supabase containers, can be fully launched with a single ./pgsql.yml command, providing a seamless experience.


Last modified 2024-11-14: adjust master branch links to main (d93529c6)