PIGSTY

PostgreSQL

The vanilla PostgreSQL kernel with 420+ extensions

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

Pigsty supports PostgreSQL 13 ~ 17 (and 18beta2), and provides 420+ extensions alone with it.

Get Started

install Pigsty’s with the pgsql config template.

curl -fsSL https://repo.pigsty.io/get | bash; cd ~/pigsty;
./configure -c pgsql     # use percona 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: [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]

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

./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 18beta2

To use PostgreSQL 18beta2, 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 18 argument:

./configure -c pgsql -v 18     # use the postgresql 18beta2 kernel
./install.yml                  # setup everything with pigsty

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

So to install PG18 on EL-system (now), you'll have to replace the pgsql-main to pgsql-core to avoid installing the not-exists pgvector extensions.

Video Demo: Install PostgreSQL 18beta2 with Pigsty v3.6.0 on el9.aarch64

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:

On this page

Get StartedConfigurePostgreSQL 18beta2Multi-Node