Configuration

• 1: Inventory
• 2: Configure
• 3: Tutorial
• 4: Template
• 5: Security
• 6: CMDB

    The main config file of Pigsty, Describe your entire deployment

    Generate config file according to your input & environment

    Planning your deployment according to business requirements

    Available configuration templates and examples

    Security considerations and best practices for prod deployment

    Use PostgreSQL as CMDB instead of local YAML config file

    PostgreSQL Cluster with HA, PITR, IaC, ACL, Monitoring, Pooling

    Nginx, Repo, DNS, NTP, Prometheus and Grafana stack for Observability

    Enroll nodes into the desired state and monitor it, and VIP, HAProxy

    Reliable distributed consensus storage (DCS), empowering PGSQL HA

    S3 compatible object storage, optional backup storage

    High-performance in-memory cache, optional data structure server

1 - Inventory

Each pigsty deployment has a corresponding config inventory. It could be stored in a local config file in YAML format, or dynamically generated from CMDB or any ansible compatible format. Pigsty uses one monolith YAML config file by default, which is pigsty.yml, located in the pigsty home directory.

• pigsty/
  • pigsty.yml
  • ansible.cfg

The configure script will generate the pigsty.yml file scaffold with good defaults according to your env and input, but it’s OPTIONAL: you can always edit the pigsty.yml file directly as the tutorial shows.

Structure

The inventory consists of two parts: global vars & multiple groups. You can define new clusters in all.children. And describe infra with global vars: all.vars. Which may look like this:

all:                  # Top-level object: all
  vars: {...}         # Global Parameters
  children:           # Group Definitions
    infra:            # Group Definition: 'infra'
      hosts: {...}        # Group Membership: 'infra'
      vars:  {...}        # Group Parameters: 'infra'
    etcd:    {...}    # Group Definition: 'etcd'
    pg-meta: {...}    # Group Definition: 'pg-meta'
    pg-test: {...}    # Group Definition: 'pg-test'
    redis-test: {...} # Group Definition: 'redis-test'
    # ...

There are plenty of examples under conf/, which can also be used as templates during configure.

Cluster

Each ansible group may represent a cluster, which could be a Node cluster, PostgreSQL cluster, Redis cluster, Etcd cluster, or Minio cluster, etc…

Cluster definition consists of two parts: hosts & vars. You can define cluster members in <cls>.hosts and describe cluster with parameters in <cls>.vars. Here’s an example of a 3-node HA PG cluster:

all:
  children:    # All Groups
    pg-test:   # Group Name
      hosts:   # Group Host (Cluster Membership)
        10.10.10.11: { pg_seq: 1, pg_role: primary } # Host1
        10.10.10.12: { pg_seq: 2, pg_role: replica } # Host2
        10.10.10.13: { pg_seq: 3, pg_role: offline } # Host3
      vars:    # Group Vars (Cluster Parameters)
        pg_cluster: pg-test

The vars in cluster level will override the global vars, and vars in host level will override the cluster vars and global vars.

Parameter

Parameters are key-value pairs that define all entities in the deployment. The key is a string name, and the value can be one of five types: boolean, string, number, array, or object.

And parameters can be set at different levels with the following precedence:

Here are some examples about parameter precedence:

• Override the PostgreSQL major version with Playbook CLI Args -e pg_version=16
• Override pg instance role with Instance Level Parameter pg_role on Host Vars
• Override pg cluster name with Cluster Level Parameter pg_cluster on Group Vars.
• Specify global NTP servers with Global Parameter node_ntp_servers on Global Vars
• If no pg_version is set, pigsty will use the default value from role implementation (18 by default)

Every parameter has a proper default value except for mandatory IDENTITY PARAMETERS; they are used as identifiers and must be set explicitly. Such as pg_cluster, pg_role, and pg_seq in above snippet.

Available parameters vary according to the modules:

Reference

Pigsty has 290+ parameters, check module parameters for details.

2 - Configure

The configure script will generate the pigsty.yml config file inventory with good defaults according to your environment and input. It’s OPTIONAL, you can edit the pigsty.yml directly as the tutorial shows.

• pigsty/
  • configure
  • pigsty.yml

Usage

The configure script is an interactive wizard unless -n|--non-interactive is specified.

./configure
    [-c|--conf <confname>   # [meta|dual|trio|full|app/supa|...]
    [-i|--ip <ip>]          # primary IP address (skip with -s)
    [-v|--version <pgver>   # [18|17|16|15|14|13]
    [-r|--region <region>   # [default|china|europe]
    [-o|--output <file>]    # output config file (default: pigsty.yml)
    [-s|--skip]             # skip IP address probing
    [-x|--proxy]            # write proxy env from environment
    [-n|--non-interactive]  # non-interactively mode
    [-p|--port <port>]      # specify SSH port (only used if set)

Example

./configure                # if there's only one IP address, otherwise it will ask you for it
./configure -i 10.11.12.13 # just tell the primary IP address explicitly
./configure -c rich -v 16  # use the conf/rich.yml as template, with PG 16 and all extensions
./configure -c app/supa    # use the app/supa template, run self-hosting supabase
./configure -c mssql       # use the babelfish template, run MSSQL compatible PG kernel fork
./configure -c full -s     # use the 4-node sandbox config template, without IP replace & probe
./configure -r china       # use use china mirror instead of default repo

configure output example:

[vagrant@node-1 pigsty]$ ./configure
configure pigsty v3.7.0 begin
[ OK ] region = china
[ OK ] kernel  = Linux
[ OK ] machine = x86_64
[ OK ] package = rpm,dnf
[ OK ] vendor  = rocky (Rocky Linux)
[ OK ] version = 9 (9.5)
[ OK ] sudo = vagrant ok
[ OK ] ssh = [email protected] ok
[WARN] Multiple IP address candidates found:
    (1) 192.168.121.193	    inet 192.168.121.193/24 brd 192.168.121.255 scope global dynamic noprefixroute eth0
    (2) 10.10.10.11	    inet 10.10.10.11/24 brd 10.10.10.255 scope global noprefixroute eth1
[ IN ] INPUT primary_ip address (of current meta node, e.g 10.10.10.10):
=> 10.10.10.11
[ OK ] primary_ip = 10.10.10.11 (from input)
[ OK ] admin = [email protected] ok
[ OK ] mode = meta (el9)
[ OK ] locale  = C.UTF-8
[ OK ] configure pigsty done
proceed with ./install.yml

Behavior

3 - Tutorial

Instead of using configure to generate config, you can write a pigsty config file from scratch manually.

Here is a tutorial to help you to build a complex config file inventory from scratch.

Minimal

This is a minimal working config example, you have to tell pigsty the IP of admin node and infra node.

all:
  children: {infra: {hosts: {10.10.10.10: { infra_seq: 1 }}}}
  vars: { admin_ip: 10.10.10.10 }

This will install INFRA and NODE module on 10.10.10.10 (change to your IP address).

./install.yml

You’ll have a complete observability stack with node monitoring. But database service is not running yet.

PGSQL & ETCD

To provide PostgreSQL service, you have to define additional groups and install PGSQL & ETCD modules.

all:
  children:
    infra:   { hosts: { 10.10.10.10: { infra_seq: 1 } } }
    etcd:    { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
    pg-meta: { hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }, vars: { pg_cluster: pg-meta } }
  vars:
    admin_ip: 10.10.10.10

We add two new groups here: etcd and pg-meta, which defines an 1-node ETCD cluster and a 1-node PGSQL cluster. Recreate everything with ./install.yml, or using these cmd to do incremental steps:

./etcd.yml  -l etcd      # install etcd module on group etcd
./pgsql.yml -l pg-meta   # install pgsql module on group pg-meta

The PGSQL module depends on ETCD for HA consensus, so make sure to install ETCD module first.

Database & Users

Now we want to customize our postgres database cluster, with users, database, and backups:

all:
  children:
    infra:   { hosts: { 10.10.10.10: { infra_seq: 1 } } }
    etcd:    { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
    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: admin user}
        pg_databases:
          - { name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [vector]}
        node_crontab:
          - '00 01 * * * postgres /pg/bin/pg-backup full'
  vars:
    admin_ip: 10.10.10.10

We define some additional details at the pg-meta cluster level:

• pg_users: define a new user dbuser_meta with password DBUser.Meta
• pg_databases: define a new database meta with pigsty CMDB schema and vector extension
• node_crontab: define crontab that takes a full backup at 1 am every day

Instead of recreating everything with ./install.yml, we make changes incrementally:

bin/pgsql-user pg-meta dbuser_meta      # create user dbuser_meta on pg-meta
bin/pgsql-db   pg-meta meta             # create database meta on pg-meta
./node.yml -l pg-meta -t node_crontab   # add backup task to crontab on pg-meta

PG VER & Extensions

You can install different major versions of PostgreSQL, and 437+ corresponding extensions.

Let’s install PostgreSQL 16 (instead of default 18) with timescaledb, postgis, and pgvector extensions.

all:
  children:
    infra:   { hosts: { 10.10.10.10: { infra_seq: 1 } } }
    etcd:    { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
    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
        pg_databases:
          - name: meta
            baseline: cmdb.sql
            comment: pigsty meta database
            schemas: [pigsty]
            extensions: [ vector, postgis, timescaledb ]           # <--- CREATE EXTENSIONS
        pg_libs: 'timescaledb, pg_stat_statements, auto_explain'   # <--- LOAD EXTENSIONS
        node_crontab:
          - '00 01 * * * postgres /pg/bin/pg-backup full'
  vars:
    admin_ip: 10.10.10.10
    region: default # use local mirror for faster download speed   # <--- default|china|europe
    repo_extra_packages: [ timescaledb, postgis, pgvector, pgsql ] # <--- DOWNLOAD EXTENSIONS
    pg_extensions:       [ timescaledb, postgis, pgvector ]        # <--- INSTALL EXTENSIONS
    pg_version: 16   # PG 17 is the default latest major version   # <--- USE PG 16 VERSION

• repo_extra_packages: download timescaledb and postgis extensions.
• pg_libs: preload timescaledb, pg_stat_statements, auto_explain extensions.

Let’s redownload missing packages (PG 16 kernel & extensions), remove the old cluster, and recreate it:

make repo                   # redownload packages
./pgsql-rm.yml -l pg-meta   # remove the old pg-meta cluster (because it is PG18)
./pgsql.yml    -l pg-meta   # recreate the pg-meta cluster with PG16 & extensions

More NODE

We can add 3 more nodes to this deployment.

bin/node-add pg-test

Or add them one by one:

bin/node-add 10.10.10.11
bin/node-add 10.10.10.12
bin/node-add 10.10.10.13

PGSQL HA

Now we want to add a new database cluster pg-test, with 3-node HA setup:

all:
  children:
    infra:   { hosts: { 10.10.10.10: { infra_seq: 1 } } }
    etcd:    { hosts: { 10.10.10.10: { etcd_seq: 1 } }, vars: { etcd_cluster: etcd } }
    pg-meta: { hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }, vars: { pg_cluster: pg-meta } }
    pg-test:
      hosts:
        10.10.10.11: { pg_seq: 1, pg_role: primary }
        10.10.10.12: { pg_seq: 2, pg_role: replica  }
        10.10.10.13: { pg_seq: 3, pg_role: replica  }
      vars: { pg_cluster: pg-test }
  vars:
    admin_ip: 10.10.10.10

Pigsty’s working assumption is there’s only ONE postgres instance on each node. There’s no support to run multiple postgres instances on a single node.

Redis Up

Pigsty has optional Redis support, which is used as a cache in front of PostgreSQL.

bin/redis-add redis-ms
bin/redis-add redis-meta
bin/redis-add redis-test

Redis HA setup requires cluster mode, or sentinel infra, check Redis Config for details.

MinIO Up

Pigsty has optional MinIO support, which is used as a backup storage for PostgreSQL.

./minio.yml -l minio

Serious production MinIO deployment usually requires at least 4 nodes with 4 disks each (4N/16D)

Docker Up

Install docker on the infra group:

./docker.yml -l infra

Run PgAdmin

Check the App: Pgadmin for details on how to run pgAdmin with Pigsty. Short version:

./docker.yml -l infra
./app.yml    -l infra -e app=pgadmin

Self-Hosting Supabase

Check the App: Supabase for details on how to run Supabase with Pigsty. Short version:

./configure -c app/supa
./install.yml
./docker.yml
./app.yml

4 - Template

This directory conf contains pigsty config templates, which will be used during configure procedure.

Config templates can be designated using ./configure -c <conf>, where the conf is a relative path to conf directory (with or without .yml suffix). e.g. the ~/pigsty/conf/rich.yml can be specified as rich

./configure                     # use the meta.yml config template by default
./configure -c meta             # use the meta.yml 1-node template explicitly
./configure -c rich             # use the 1-node template with all extensions & minio
./configure -c slim             # use the minimal 1-node template
./configure -c supabase         # use the Supabase 1-node template
./configure -c app/dify         # use the dify app template

If no -c|--conf is given, the single-node meta config template will be automatically selected by default.

Basic Templates

Here are one-node templates, which provide different features and configurations.

Exotic Kernel

Using exotic Postgres kernel forks:

Multi Node

Applications

Demo Templates

Build Templates

5 - Security

Pigsty already provides a secure-by-default database authentication and access control model.

It is strong enough for most common scenarios, as long as you follow the security best-practices below.

Confidentiality

Files

Passwords

- { name: dbuser_meta , password: Pleas3-ChangeThisPwd ,expire_in: 7300 ,pgbouncer: true ,roles: [ dbrole_admin ]    ,comment: pigsty admin user }
- { name: dbuser_view , password: Make.3ure-Compl1ance  ,expire_in: 7300 ,pgbouncer: true ,roles: [ dbrole_readonly ] ,comment: read-only viewer for meta database }
- { name: postgres     ,superuser: true  ,expire_in: 7300                        ,comment: system superuser }
- { name: replicator ,replication: true  ,expire_in: 7300 ,roles: [pg_monitor, dbrole_readonly]   ,comment: system replicator }
- { name: dbuser_dba   ,superuser: true  ,expire_in: 7300 ,roles: [dbrole_admin]  ,pgbouncer: true ,pool_mode: session, pool_connlimit: 16 , comment: pgsql admin user }
- { name: dbuser_monitor ,roles: [pg_monitor] ,expire_in: 7300 ,pgbouncer: true ,parameters: {log_min_duration_statement: 1000 } ,pool_mode: session ,pool_connlimit: 8 ,comment: pgsql monitor user }

SET log_statement TO 'none';
ALTER USER "{{ user.name }}" PASSWORD '{{ user.password }}';
SET log_statement TO DEFAULT;

IP Addresses

Network Traffic

Integrity

Consistency

Audit

Fat Finger

Availability

Redundancy

Fault Tolerance

Access

6 - CMDB

Pigsty allows you to use a database (CMDB) as a dynamic configuration source instead of a static configuration file. You can use the built-in PostgreSQL as config inventory for config management.

With Postgres CMDB, configuration is organized in structured relational tables, which can be easily queried and manipulated using SQL. This allows for easier integration with other systems and tools.

How does it work?

Ansible allows you to use a dynamic inventory script to generate the inventory config on-the-fly.

The idea is to replace static pigsty.yml in ansible.cfg with a dynamic shell script inventory.sh

---
inventory = pigsty.yml
+++
inventory = inventory.sh

The content of inventory.sh is very simple, it will query the PostgreSQL CMDB and retrieve config.

psql ${METADB_URL} -AXtwc 'SELECT text FROM pigsty.inventory;'

CMDB Schema

The CMDB baseline schema is shipped with pigsty: files/cmdb.sql And most of the default config templates will use it as example baseline. Which means it can be used by default.

all:
  children:
    pg-meta:
      hosts:
        10.10.10.10: { pg_seq: 1, pg_role: primary }
      vars:
        pg_cluster: pg-meta
        pg_databases:
          - name: meta
            baseline: cmdb.sql  # <--- use this as database schema baseline

Load Config Data

CMDB is empty by default, load config file into the CMDB with the bin/inventory_load script.

Run bin/inventory_load without arguments will load the default pigsty.yml into the default CMDB.

usage: inventory_load [-h] [-p PATH] [-d CMDB_URL]

load config arguments

optional arguments:
  -h, --help            show this help message and exit
  -p PATH, --path PATH  config path, ${PIGSTY_HOME}/pigsty.yml by default
  -d DATA, --data DATA  postgres cmdb pgurl, ${METADB_URL} by default

Use -p to specify the config file path, and -d to specify the CMDB URL.

bin/inventory_load
bin/inventory_load -p conf/demo.yml
bin/inventory_load -p conf/ha/full.yml -d postgresql://dbuser_meta:[email protected]:5432/meta

Switch Inventory

You can switch to dynamic CMDB inventory with:

bin/inventory_cmdb

Which essentially changes the inventory parameter in the ansible.cfg to use the inventory.sh script.