Skip to content

PostgreSQL

Vanilla PostgreSQL kernel with 575 extensions
Vanilla PostgreSQL kernel with 575 extensions

PostgreSQL is the world’s most advanced and popular open-source database.

Pigsty installs PostgreSQL 18 by default, supports PostgreSQL 14 ~ 18, and provides 575 PG extensions.

Quick Start

Install Pigsty using the pgsql configuration template.

./configure -c pgsql     # Use postgres kernel
./deploy.yml             # Deploy the Pigsty core chain with native PostgreSQL

Most configuration templates use PostgreSQL kernel by default, for example:

  • meta : Default, postgres with core extensions (vector, postgis, timescale)
  • rich : postgres with all extensions installed
  • slim : postgres only, no monitoring infrastructure
  • ha/full : 4-node sandbox for HA demonstration
  • pgsql : minimal postgres kernel configuration example

Configuration

Vanilla PostgreSQL kernel requires no special adjustments:

pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - { name: dbuser_meta ,password: DBUser.Meta   ,pgbouncer: true ,roles: [dbrole_admin   ] ,comment: pigsty admin user }
      - { name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer  }
    pg_databases:
      - { name: meta, baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [ vector ]}
    pg_hba_rules:
      - { user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes' }
    pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ] # Full backup at 1 AM daily
    pg_packages: [ pgsql-main, pgsql-common ]   # pg kernel and common utilities
    #pg_extensions: [ pg18-time ,pg18-gis ,pg18-rag ,pg18-fts ,pg18-olap ,pg18-feat ,pg18-lang ,pg18-type ,pg18-util ,pg18-func ,pg18-admin ,pg18-stat ,pg18-sec ,pg18-fdw ,pg18-sim ,pg18-etl]

Version Selection

To use a different PostgreSQL major version, you can configure it using the -v parameter:

./configure -c pgsql            # Default is postgresql 18, no need to specify explicitly
./configure -c pgsql -v 18      # Explicitly use postgresql 18
./configure -c pgsql -v 17      # Use postgresql 17
./configure -c pgsql -v 16      # Use postgresql 16
./configure -c pgsql -v 15      # Use postgresql 15
./configure -c pgsql -v 14      # Use postgresql 14

If a PostgreSQL cluster is already installed, you need to uninstall it before installing a new version:

./pgsql-rm.yml -l pg-meta # Uninstall cluster pg-meta

Extension Ecosystem

Pigsty provides a rich extension ecosystem for PostgreSQL. See the Extension Catalog for details.