Overview

What is Pigsty?

Pigsty

“PostgreSQL In Great STYle.”

—— A battery-included, local-first, open-source RDS PG alternative.

Release v2.0.2 | Repo | Demo | Docs | Blog | Telegram | Discord

Get Started with curl -fsSL http://download.pigsty.cc/get | bash

Features

Pigsty is a Me-Better Open-Source RDS Alternative with:

Free RDS for PostgreSQL! check Feature for details.

pigsty-distro

Get Started

Prepare a new node with Linux x86_64 EL compatible OS, then run as a sudo-able user:

  1. bash -c "$(curl -fsSL http://download.pigsty.cc/get)" && cd ~/pigsty   
  2. ./bootstrap  && ./configure && ./install.yml # install latest pigsty

Then you will have a pigsty singleton node ready, with Web Services on port 80 and Postgres on port 5432.

Check Installation for details.

Download Directly

You can also download pigsty source and packages with git or curl directly:

  1. # get from GitHub
  2. bash -c "$(curl -fsSL https://raw.githubusercontent.com/Vonng/pigsty/master/bin/get)"
  4. # or download tarball directly with curl
  5. curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.2/pigsty-v2.0.2.tgz -o ~/pigsty.tgz                 # SRC
  6. curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.2/pigsty-pkg-v2.0.2.el9.x86_64.tgz -o /tmp/pkg.tgz  # EL9
  7. curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.2/pigsty-pkg-v2.0.2.el8.x86_64.tgz -o /tmp/pkg.tgz  # EL8
  8. curl -L https://github.com/Vonng/pigsty/releases/download/v2.0.2/pigsty-pkg-v2.0.2.el7.x86_64.tgz -o /tmp/pkg.tgz  # EL7
  10. # or using git if curl not available
  11. git clone https://github.com/Vonng/pigsty; cd pigsty; git checkout v2.0.2

Architecture

Pigsty uses a modular design. There are six default modules available:

  • INFRA: Local yum repo, Nginx, DNS, and entire Prometheus & Grafana observability stack.
  • NODE: Init node name, repo, pkg, NTP, ssh, admin, tune, expose services, collect logs & metrics.
  • ETCD: Init etcd cluster for HA Postgres DCS or Kubernetes, used as distributed config store.
  • PGSQL: Autonomous self-healing PostgreSQL cluster powered by Patroni, Pgbouncer, PgBackrest & HAProxy
  • REDIS: Deploy Redis servers in standalone master-replica, sentinel, and native cluster mode, optional.
  • MINIO: S3-compatible object storage service used as an optional central backup server for PGSQL.

You can compose them freely in a declarative manner. If you want host monitoring, INFRA & NODE will suffice. ETCD and PGSQL are used for HA PG clusters, install them on multiple nodes will automatically form a HA cluster. You can also reuse pigsty infra and develop your own modules, KAFKA, MYSQL, GPSQL, and more will come.

The default install.yml playbook in Get Started will install INFRA, NODE, ETCD & PGSQL on the current node. which gives you a battery-included PostgreSQL singleton instance (admin_ip:5432) with everything ready. This node can be used as an admin center & infra provider to manage, deploy & monitor more nodes & clusters.

Check Architecture for details.

More Clusters

To deploy a 3-node HA Postgres Cluster with streaming replication, define a new cluster on all.children.pg-test of pigsty.yml:

  1. pg-test:
  2.   hosts:
  3.     10.10.10.11: { pg_seq: 1, pg_role: primary }
  4.     10.10.10.12: { pg_seq: 2, pg_role: replica }
  5.     10.10.10.13: { pg_seq: 3, pg_role: offline }
  6.   vars:  { pg_cluster: pg-test }

Then create it with built-in playbooks:

  1. bin/pgsql-add pg-test   # init pg-test cluster

You can deploy different kinds of instance roles, such as primary, replica, offline, delayed, sync standby, and different kinds of clusters, such as standby clusters, Citus clusters, and even Redis/MinIO/Etcd clusters.

Example: Complex Postgres Customize

  1. pg-meta:
  2.   hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary , pg_offline_query: true } }
  3.   vars:
  4.     pg_cluster: pg-meta
  5.     pg_databases:                       # define business databases on this cluster, array of database definition
  6.       - name: meta                      # REQUIRED, `name` is the only mandatory field of a database definition
  7.         baseline: cmdb.sql              # optional, database sql baseline path, (relative path among ansible search path, e.g files/)
  8.         pgbouncer: true                 # optional, add this database to pgbouncer database list? true by default
  9.         schemas: [pigsty]               # optional, additional schemas to be created, array of schema names
  10.         extensions:                     # optional, additional extensions to be installed: array of `{name[,schema]}`
  11.           - { name: postgis , schema: public }
  12.           - { name: timescaledb }
  13.         comment: pigsty meta database   # optional, comment string for this database
  14.         owner: postgres                # optional, database owner, postgres by default
  15.         template: template1            # optional, which template to use, template1 by default
  16.         encoding: UTF8                 # optional, database encoding, UTF8 by default. (MUST same as template database)
  17.         locale: C                      # optional, database locale, C by default.  (MUST same as template database)
  18.         lc_collate: C                  # optional, database collate, C by default. (MUST same as template database)
  19.         lc_ctype: C                    # optional, database ctype, C by default.   (MUST same as template database)
  20.         tablespace: pg_default         # optional, default tablespace, 'pg_default' by default.
  21.         allowconn: true                # optional, allow connection, true by default. false will disable connect at all
  22.         revokeconn: false              # optional, revoke public connection privilege. false by default. (leave connect with grant option to owner)
  23.         register_datasource: true      # optional, register this database to grafana datasources? true by default
  24.         connlimit: -1                  # optional, database connection limit, default -1 disable limit
  25.         pool_auth_user: dbuser_meta    # optional, all connection to this pgbouncer database will be authenticated by this user
  26.         pool_mode: transaction         # optional, pgbouncer pool mode at database level, default transaction
  27.         pool_size: 64                  # optional, pgbouncer pool size at database level, default 64
  28.         pool_size_reserve: 32          # optional, pgbouncer pool size reserve at database level, default 32
  29.         pool_size_min: 0               # optional, pgbouncer pool size min at database level, default 0
  30.         pool_max_db_conn: 100          # optional, max database connections at database level, default 100
  31.       - { name: grafana  ,owner: dbuser_grafana  ,revokeconn: true ,comment: grafana primary database }
  32.       - { name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database }
  33.       - { name: kong     ,owner: dbuser_kong     ,revokeconn: true ,comment: kong the api gateway database }
  34.       - { name: gitea    ,owner: dbuser_gitea    ,revokeconn: true ,comment: gitea meta database }
  35.       - { name: wiki     ,owner: dbuser_wiki     ,revokeconn: true ,comment: wiki meta database }
  36.     pg_users:                           # define business users/roles on this cluster, array of user definition
  37.       - name: dbuser_meta               # REQUIRED, `name` is the only mandatory field of a user definition
  38.         password: DBUser.Meta           # optional, password, can be a scram-sha-256 hash string or plain text
  39.         login: true                     # optional, can log in, true by default  (new biz ROLE should be false)
  40.         superuser: false                # optional, is superuser? false by default
  41.         createdb: false                 # optional, can create database? false by default
  42.         createrole: false               # optional, can create role? false by default
  43.         inherit: true                   # optional, can this role use inherited privileges? true by default
  44.         replication: false              # optional, can this role do replication? false by default
  45.         bypassrls: false                # optional, can this role bypass row level security? false by default
  46.         pgbouncer: true                 # optional, add this user to pgbouncer user-list? false by default (production user should be true explicitly)
  47.         connlimit: -1                   # optional, user connection limit, default -1 disable limit
  48.         expire_in: 3650                 # optional, now + n days when this role is expired (OVERWRITE expire_at)
  49.         expire_at: '2030-12-31'         # optional, YYYY-MM-DD 'timestamp' when this role is expired  (OVERWRITTEN by expire_in)
  50.         comment: pigsty admin user      # optional, comment string for this user/role
  51.         roles: [dbrole_admin]           # optional, belonged roles. default roles are: dbrole_{admin,readonly,readwrite,offline}
  52.         parameters: {}                  # optional, role level parameters with `ALTER ROLE SET`
  53.         pool_mode: transaction          # optional, pgbouncer pool mode at user level, transaction by default
  54.         pool_connlimit: -1              # optional, max database connections at user level, default -1 disable limit
  55.       - {name: dbuser_view     ,password: DBUser.Viewer   ,pgbouncer: true ,roles: [dbrole_readonly], comment: read-only viewer for meta database}
  56.       - {name: dbuser_grafana  ,password: DBUser.Grafana  ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for grafana database   }
  57.       - {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for bytebase database  }
  58.       - {name: dbuser_kong     ,password: DBUser.Kong     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for kong api gateway   }
  59.       - {name: dbuser_gitea    ,password: DBUser.Gitea    ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for gitea service      }
  60.       - {name: dbuser_wiki     ,password: DBUser.Wiki     ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: admin user for wiki.js service    }
  61.     pg_services:                        # extra services in addition to pg_default_services, array of service definition
  62.       # standby service will route {ip|name}:5435 to sync replica's pgbouncer (5435->6432 standby)
  63.       - name: standby                   # required, service name, the actual svc name will be prefixed with `pg_cluster`, e.g: pg-meta-standby
  64.         port: 5435                      # required, service exposed port (work as kubernetes service node port mode)
  65.         ip: "*"                         # optional, service bind ip address, `*` for all ip by default
  66.         selector: "[]"                  # required, service member selector, use JMESPath to filter inventory
  67.         dest: default                   # optional, destination port, default|postgres|pgbouncer|<port_number>, 'default' by default
  68.         check: /sync                    # optional, health check url path, / by default
  69.         backup: "[? pg_role == `primary`]"  # backup server selector
  70.         maxconn: 3000                   # optional, max allowed front-end connection
  71.         balance: roundrobin             # optional, haproxy load balance algorithm (roundrobin by default, other: leastconn)
  72.         options: 'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'
  73.     pg_hba_rules:
  74.       - {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'}
  75.     pg_vip_enabled: true
  76.     pg_vip_address: 10.10.10.2/24
  77.     pg_vip_interface: eth1
  78.     node_crontab:  # make a full backup 1 am everyday
  79.       - '00 01 * * * postgres /pg/bin/pg-backup full'
  80. ``` Example: Security Enhanced PG Cluster with Delayed Replica

pg-meta: # 3 instance postgres cluster pg-meta hosts: 10.10.10.10: { pg_seq: 1, pg_role: primary } 10.10.10.11: { pg_seq: 2, pg_role: replica } 10.10.10.12: { pg_seq: 3, pg_role: replica , pg_offline_query: true } vars: pg_cluster: pg-meta pg_conf: crit.yml pg_users:

  1.   - { name: dbuser_meta , password: DBUser.Meta   , pgbouncer: true , roles: [ dbrole_admin ] , comment: pigsty admin user }
  2.   - { name: dbuser_view , password: DBUser.Viewer , pgbouncer: true , roles: [ dbrole_readonly ] , comment: read-only viewer for meta database }
  3. pg_databases:
  4.   - {name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty] ,extensions: [{name: postgis, schema: public}, {name: timescaledb}]}
  5. pg_services:
  6.   - { name: standby ,src_ip: "*" ,port: 5435 , dest: default ,selector: "[]" , backup: "[? pg_role == `primary`]" }
  7. pg_vip_enabled: true
  8. pg_vip_address: 10.10.10.2/24
  9. pg_vip_interface: eth1

OPTIONAL delayed cluster for pg-meta

pg-meta-delay: # delayed instance for pg-meta (1 hour ago) hosts: { 10.10.10.13: { pg_seq: 1, pg_role: primary, pg_upstream: 10.10.10.10, pg_delay: 1h } } vars: { pg_cluster: pg-meta-delay } ``` Example: Citus Distributed Cluster: 5 Nodes

  1. all:
  2.   children:
  3.     pg-citus0: # citus coordinator, pg_group = 0
  4.       hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  5.       vars: { pg_cluster: pg-citus0 , pg_group: 0 }
  6.     pg-citus1: # citus data node 1
  7.       hosts: { 10.10.10.11: { pg_seq: 1, pg_role: primary } }
  8.       vars: { pg_cluster: pg-citus1 , pg_group: 1 }
  9.     pg-citus2: # citus data node 2
  10.       hosts: { 10.10.10.12: { pg_seq: 1, pg_role: primary } }
  11.       vars: { pg_cluster: pg-citus2 , pg_group: 2 }
  12.     pg-citus3: # citus data node 3, with an extra replica
  13.       hosts:
  14.         10.10.10.13: { pg_seq: 1, pg_role: primary }
  15.         10.10.10.14: { pg_seq: 2, pg_role: replica }
  16.       vars: { pg_cluster: pg-citus3 , pg_group: 3 }
  17.   vars:                               # global parameters for all citus clusters
  18.     pg_mode: citus                    # pgsql cluster mode: citus
  19.     pg_shard: pg-citus                # citus shard name: pg-citus
  20.     patroni_citus_db: meta            # citus distributed database name
  21.     pg_dbsu_password: DBUser.Postgres # all dbsu password access for citus cluster
  22.     pg_users: [ { name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [ dbrole_admin ] } ]
  23.     pg_databases: [ { name: meta ,extensions: [ { name: citus }, { name: postgis }, { name: timescaledb } ] } ]
  24.     pg_hba_rules:
  25.       - { user: 'all' ,db: all  ,addr: 127.0.0.1/32 ,auth: ssl ,title: 'all user ssl access from localhost' }
  26.       - { user: 'all' ,db: all  ,addr: intra        ,auth: ssl ,title: 'all user ssl access from intranet'  }
  27. ``` Example: Redis Cluster/Sentinel/Standalone

redis-ms: # redis classic primary & replica hosts: { 10.10.10.10: { redis_node: 1 , redis_instances: { 6501: { }, 6502: { replica_of: ‘10.10.10.10 6501’ } } } } vars: { redis_cluster: redis-ms ,redis_password: ‘redis.ms’ ,redis_max_memory: 64MB }

redis-meta: # redis sentinel x 3 hosts: { 10.10.10.11: { redis_node: 1 , redis_instances: { 6001: { } ,6002: { } , 6003: { } } } } vars: { redis_cluster: redis-meta ,redis_password: ‘redis.meta’ ,redis_mode: sentinel ,redis_max_memory: 16MB }

redis-test: # redis native cluster: 3m x 3s hosts: 10.10.10.12: { redis_node: 1 ,redis_instances: { 6501: { } ,6502: { } ,6503: { } } } 10.10.10.13: { redis_node: 2 ,redis_instances: { 6501: { } ,6502: { } ,6503: { } } } vars: { redis_cluster: redis-test ,redis_password: ‘redis.test’ ,redis_mode: cluster, redis_max_memory: 32MB } ``` Example: ETCD 3 Node Cluster

  1. etcd: # dcs service for postgres/patroni ha consensus
  2.   hosts:  # 1 node for testing, 3 or 5 for production
  3.     10.10.10.10: { etcd_seq: 1 }  # etcd_seq required
  4.     10.10.10.11: { etcd_seq: 2 }  # assign from 1 ~ n
  5.     10.10.10.12: { etcd_seq: 3 }  # odd number please
  6.   vars: # cluster level parameter override roles/etcd
  7.     etcd_cluster: etcd  # mark etcd cluster name etcd
  8.     etcd_safeguard: false # safeguard against purging
  9.     etcd_clean: true # purge etcd during init process
  10. ``` Example: Minio 3 Node Deployment

minio: hosts: 10.10.10.10: { minio_seq: 1 } 10.10.10.11: { minio_seq: 2 } 10.10.10.12: { minio_seq: 3 } vars: minio_cluster: minio minio_data: ‘/data{1…2}’ # use two disk per node minio_node: ‘${minio_cluster}-${minio_seq}.pigsty’ # minio node name pattern haproxy_services:

  1.   - name: minio                     # [REQUIRED] service name, unique
  2.     port: 9002                      # [REQUIRED] service port, unique
  3.     options:
  4.       - option httpchk
  5.       - option http-keep-alive
  6.       - http-check send meth OPTIONS uri /minio/health/live
  7.       - http-check expect status 200
  8.     servers:
  9.       - { name: minio-1 ,ip: 10.10.10.10 , port: 9000 , options: 'check-ssl ca-file /etc/pki/ca.crt check port 9000' }
  10.       - { name: minio-2 ,ip: 10.10.10.11 , port: 9000 , options: 'check-ssl ca-file /etc/pki/ca.crt check port 9000' }
  11.       - { name: minio-3 ,ip: 10.10.10.12 , port: 9000 , options: 'check-ssl ca-file /etc/pki/ca.crt check port 9000' }

```

Check Configuration for details.

About

Pigsty (/ˈpɪɡˌstaɪ/) is the abbreviation of “PostgreSQL In Great STYle.”

Docs: https://vonng.github.io/pigsty/

Wiki: https://github.com/Vonng/pigsty/wiki

Website: https://pigsty.cc/en/ | https://pigsty.cc/zh/

WeChat: Search pigsty-cc to join the WeChat group.

Telegram: https://t.me/joinchat/gV9zfZraNPM3YjFh

Discord: https://discord.gg/wDzt5VyWEzr

Author: Vonng (rh@vonng.com)

License: AGPL-3.0

Copyright 2018-2023 rh@vonng.com

Last modified 2023-04-07: bump en docs to v2.0.2 (5a16652)