Skip to content

Version v3.7.0 of the documentation is no longer actively maintained. The site that you are currently viewing is an archived snapshot. For up-to-date documentation, see the latest version.

PostgreSQL

The vanilla PostgreSQL kernel with 437 extensions

PostgreSQL is the most advanced & popular open source database in the world.

Pigsty supports PostgreSQL 13 ~ 18, and provides 437 extensions alone with it.

Get Started

install Pigsty’s with the pgsql config template.

curl -fsSL https://repo.pigsty.io/get | bash -s v3.7.0; cd ~/pigsty;
./configure -c pgsql     # use vanilla postgres kernel
./install.yml            # setup everything with pigsty

Most of the config template is use the PostgreSQL kernel by default, such as:

  • meta : DEFAULT, postgres with core extensions (vector, postgis, timescale)
  • rich : postgres with all extensions installed
  • slim : postgres only without monitor infra
  • full : the 4-node sandbox for HA demonstration
  • pgsql : the minimal postgres kernel config example (THIS CONFIG)

Configure

Nothing special needs to be tuned for vanilla PostgreSQL kernel:

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' }
    node_crontab: [ '00 01 * * * postgres /pg/bin/pg-backup full' ] # make a full backup every 1am
    pg_packages: [ pgsql-main, pgsql-common ]   # pg kernel and common utils
    #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]

To use a different PostgreSQL major version, you can configure with -v parameter:

./configure -c pgsql            # the default is 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
./configure -c pgsql -v 13      # use postgresql 13

If PostgreSQL cluster is already installed, you’ll need to uninstall it before installing the new version

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

PostgreSQL beta

To use PostgreSQL beta version (19 not released), you’ll also need to add the beta repo to your node_repo_modules (or repo_modules if you are building a local repo)

The configure will do that for you if you are use the -v 19 argument:

./configure -c pgsql -v 19     # use the postgresql 19 kernel (beta not released yet)
./install.yml                  # setup everything with pigsty

Beware Pigsty is not build extensions for PostgreSQL 19 yet, so only those existing extensions in the PGDG repo are currently available. We will start building them after the PostgreSQL 19 is released.

Multi-Node

To setup a multi-node PostgreSQL cluster, you can check the PGSQL: Configure for details:

This is the example 3-node pg-test cluster in the full config template: