Infra as Code Toolbox

HashiCorp for Database! Infra as Code, Database as Data

A database is a software that manages the data, and a control system is software that manages the database.

Pigsty adopts the design philosophy of Infra as Data, using a declarative configuration similar to Kubernetes, with a large number of optional configuration options to describe the database and the operating environment, and an idempotent preconfigured script to automatically create the required database clusters, providing a private cloud experience.

Pigsty creates the required database clusters from bare metal nodes in minutes based on a list of user config files.

For example, creating a one-master-two-slave database cluster pg-test on three machines requires only a few lines of config and a single command pgsql.yml -l pg-test to create a highly available database cluster as described in the following section.

Infra as Code Toolbox - 图1

Example: Customize PGSQL Clusters

  1. #----------------------------------#
  2. # cluster: pg-meta (on meta node)  #
  3. #----------------------------------#
  4. # pg-meta is the default SINGLE-NODE pgsql cluster deployed on meta node (10.10.10.10)
  5. # if you have multiple n meta nodes, consider deploying pg-meta as n-node cluster too
  7. pg-meta:                                # required, ansible group name , pgsql cluster name. should be unique among environment
  8.   hosts:                                # `<cluster>.hosts` holds instances definition of this cluster
  9.     10.10.10.10:                        # INSTANCE-LEVEL CONFIG: ip address is the key. values are instance level config entries (dict)
  10.       pg_seq: 1                         # required, unique identity parameter (+integer) among pg_cluster
  11.       pg_role: primary                  # required, pg_role is mandatory identity parameter, primary|replica|offline|delayed
  12.       pg_offline_query: true            # instance with `pg_offline_query: true` will take offline traffic (saga, etl,...)
  13.       # some variables can be overwritten on instance level. e.g: pg_upstream, pg_weight, etc...
  14.     #---------------
  15.     # mandatory                         # all configuration above (`ip`, `pg_seq`, `pg_role`) and `pg_cluster` are mandatory
  16.     #---------------
  17.   vars:                                 # `<cluster>.vars` holds CLUSTER LEVEL CONFIG of this pgsql cluster
  18.     pg_cluster: pg-meta                 # required, pgsql cluster name, unique among cluster, used as namespace of cluster resources
  20.     #---------------
  21.     # optional                          # all configuration below are OPTIONAL for a pgsql cluster (Overwrite global default)
  22.     #---------------
  23.     pg_version: 14                      # pgsql version to be installed (use global version if missing)
  24.     node_tune: tiny                     # node optimization profile: {oltp|olap|crit|tiny}, use tiny for vm sandbox
  25.     pg_conf: tiny.yml                   # pgsql template:  {oltp|olap|crit|tiny}, use tiny for sandbox
  26.     patroni_mode: default               # entering patroni pause mode after bootstrap  {default|pause|remove}
  27.     patroni_watchdog_mode: off          # disable patroni watchdog on meta node        {off|require|automatic}
  28.     pg_lc_ctype: en_US.UTF8             # use en_US.UTF8 locale for i18n char support  (required by `pg_trgm`)
  30.     #---------------
  31.     # biz databases                     # Defining Business Databases (Optional)
  32.     #---------------
  33.     pg_databases:                       # define business databases on this cluster, array of database definition
  34.       # define the default `meta` database
  35.       - name: meta                      # required, `name` is the only mandatory field of a database definition
  36.         baseline: cmdb.sql              # optional, database sql baseline path, (relative path among ansible search path, e.g files/)
  37.         # owner: postgres               # optional, database owner, postgres by default
  38.         # template: template1           # optional, which template to use, template1 by default
  39.         # encoding: UTF8                # optional, database encoding, UTF8 by default. (MUST same as template database)
  40.         # locale: C                     # optional, database locale, C by default.  (MUST same as template database)
  41.         # lc_collate: C                 # optional, database collate, C by default. (MUST same as template database)
  42.         # lc_ctype: C                   # optional, database ctype, C by default.   (MUST same as template database)
  43.         # tablespace: pg_default        # optional, default tablespace, 'pg_default' by default.
  44.         # allowconn: true               # optional, allow connection, true by default. false will disable connect at all
  45.         # revokeconn: false             # optional, revoke public connection privilege. false by default. (leave connect with grant option to owner)
  46.         # pgbouncer: true               # optional, add this database to pgbouncer database list? true by default
  47.         comment: pigsty meta database   # optional, comment string for this database
  48.         connlimit: -1                   # optional, database connection limit, default -1 disable limit
  49.         schemas: [pigsty]               # optional, additional schemas to be created, array of schema names
  50.         extensions:                     # optional, additional extensions to be installed: array of schema definition `{name,schema}`
  51.           - { name: adminpack, schema: pg_catalog }    # install adminpack to pg_catalog
  52.           - { name: postgis, schema: public }          # if schema is omitted, extension will be installed according to search_path.
  53.           - { name: timescaledb }                      # some extensions are not relocatable, you can just omit the schema part
  55.       # define an additional database named grafana & prometheus (optional)
  56.       # - { name: grafana,    owner: dbuser_grafana    , revokeconn: true , comment: grafana    primary database }
  57.       # - { name: prometheus, owner: dbuser_prometheus , revokeconn: true , comment: prometheus primary database , extensions: [{ name: timescaledb }]}
  59.     #---------------
  60.     # biz users                         # Defining Business Users (Optional)
  61.     #---------------
  62.     pg_users:                           # define business users/roles on this cluster, array of user definition
  63.       # define admin user for meta database (This user are used for pigsty app deployment by default)
  64.       - name: dbuser_meta               # required, `name` is the only mandatory field of a user definition
  65.         password: md5d3d10d8cad606308bdb180148bf663e1  # md5 salted password of 'DBUser.Meta'
  66.         # optional, plain text and md5 password are both acceptable (prefixed with `md5`)
  67.         login: true                     # optional, can login, true by default  (new biz ROLE should be false)
  68.         superuser: false                # optional, is superuser? false by default
  69.         createdb: false                 # optional, can create database? false by default
  70.         createrole: false               # optional, can create role? false by default
  71.         inherit: true                   # optional, can this role use inherited privileges? true by default
  72.         replication: false              # optional, can this role do replication? false by default
  73.         bypassrls: false                # optional, can this role bypass row level security? false by default
  74.         pgbouncer: true                 # optional, add this user to pgbouncer user-list? false by default (production user should be true explicitly)
  75.         connlimit: -1                   # optional, user connection limit, default -1 disable limit
  76.         expire_in: 3650                 # optional, now + n days when this role is expired (OVERWRITE expire_at)
  77.         expire_at: '2030-12-31'         # optional, YYYY-MM-DD 'timestamp' when this role is expired  (OVERWRITTEN by expire_in)
  78.         comment: pigsty admin user      # optional, comment string for this user/role
  79.         roles: [dbrole_admin]           # optional, belonged roles. default roles are: dbrole_{admin,readonly,readwrite,offline}
  80.         parameters: {}                  # optional, role level parameters with `ALTER ROLE SET`
  81.         # search_path: public         # key value config parameters according to postgresql documentation (e.g: use pigsty as default search_path)
  82.       - {name: dbuser_view , password: DBUser.Viewer  ,pgbouncer: true ,roles: [dbrole_readonly], comment: read-only viewer for meta database}
  84.       # define additional business users for prometheus & grafana (optional)
  85.       - {name: dbuser_grafana    , password: DBUser.Grafana    ,pgbouncer: true ,roles: [dbrole_admin], comment: admin user for grafana database }
  86.       - {name: dbuser_prometheus , password: DBUser.Prometheus ,pgbouncer: true ,roles: [dbrole_admin], comment: admin user for prometheus database , createrole: true }
  88.     #---------------
  89.     # hba rules                                         # Defining extra HBA rules on this cluster (Optional)
  90.     #---------------
  91.     pg_hba_rules_extra:                                 # Extra HBA rules to be installed on this cluster
  92.       - title: reject grafana non-local access          # required, rule title (used as hba description & comment string)
  93.         role: common                                    # required, which roles will be applied? ('common' applies to all roles)
  94.         rules:                                          # required, rule content: array of hba string
  95.           - local   grafana         dbuser_grafana                          md5
  96.           - host    grafana         dbuser_grafana      127.0.0.1/32        md5
  97.           - host    grafana         dbuser_grafana      10.10.10.10/32      md5
  99.     vip_mode: l2                        # setup a level-2 vip for cluster pg-meta
  100.     vip_address: 10.10.10.2             # virtual ip address that binds to primary instance of cluster pg-meta
  101.     vip_cidrmask: 8                     # cidr network mask length
  102.     vip_interface: eth1                 # interface to add virtual ip

In addition, in addition to PostgreSQL, support for Redis deployment and monitoring has been provided since Pigsty v1.3

Example: Redis Cache Cluster

  1. #----------------------------------#
  2. # redis sentinel example           #
  3. #----------------------------------#
  4. redis-meta:
  5.   hosts:
  6.     10.10.10.10:
  7.       redis_node: 1
  8.       redis_instances:  { 6001 : {} ,6002 : {} , 6003 : {} }
  9.   vars:
  10.     redis_cluster: redis-meta
  11.     redis_mode: sentinel
  12.     redis_max_memory: 128MB
  14. #----------------------------------#
  15. # redis cluster example            #
  16. #----------------------------------#
  17. redis-test:
  18.   hosts:
  19.     10.10.10.11:
  20.       redis_node: 1
  21.       redis_instances: { 6501 : {} ,6502 : {} ,6503 : {} ,6504 : {} ,6505 : {} ,6506 : {} }
  22.     10.10.10.12:
  23.       redis_node: 2
  24.       redis_instances: { 6501 : {} ,6502 : {} ,6503 : {} ,6504 : {} ,6505 : {} ,6506 : {} }
  25.   vars:
  26.     redis_cluster: redis-test           # name of this redis 'cluster'
  27.     redis_mode: cluster                 # standalone,cluster,sentinel
  28.     redis_max_memory: 64MB              # max memory used by each redis instance
  29.     redis_mem_policy: allkeys-lru       # memory eviction policy
  31. #----------------------------------#
  32. # redis standalone example         #
  33. #----------------------------------#
  34. redis-common:
  35.   hosts:
  36.     10.10.10.13:
  37.       redis_node: 1
  38.       redis_instances:
  39.         6501: {}
  40.         6502: { replica_of: '10.10.10.13 6501' }
  41.         6503: { replica_of: '10.10.10.13 6501' }
  42.   vars:
  43.     redis_cluster: redis-common         # name of this redis 'cluster'
  44.     redis_mode: standalone              # standalone,cluster,sentinel
  45.     redis_max_memory: 64MB              # max memory used by each redis instance

Starting with Pigsty v1.4, initial support for MatrixDB (Greenplum7) is provided

Example: MatrixDB Data WareHouse

  1. #----------------------------------#
  2. # cluster: mx-mdw (gp master)
  3. #----------------------------------#
  4. mx-mdw:
  5.   hosts:
  6.     10.10.10.10: { pg_seq: 1, pg_role: primary , nodename: mx-mdw-1 }
  7.   vars:
  8.     gp_role: master          # this cluster is used as greenplum master
  9.     pg_shard: mx             # pgsql sharding name & gpsql deployment name
  10.     pg_cluster: mx-mdw       # this master cluster name is mx-mdw
  11.     pg_databases:
  12.       - { name: matrixmgr , extensions: [ { name: matrixdbts } ] }
  13.       - { name: meta }
  14.     pg_users:
  15.       - { name: meta , password: DBUser.Meta , pgbouncer: true }
  16.       - { name: dbuser_monitor , password: DBUser.Monitor , roles: [ dbrole_readonly ], superuser: true }
  18.     pgbouncer_enabled: true                # enable pgbouncer for greenplum master
  19.     pgbouncer_exporter_enabled: false      # enable pgbouncer_exporter for greenplum master
  20.     pg_exporter_params: 'host=127.0.0.1&sslmode=disable'  # use 127.0.0.1 as local monitor host
  22. #----------------------------------#
  23. # cluster: mx-sdw (gp master)
  24. #----------------------------------#
  25. mx-sdw:
  26.   hosts:
  27.     10.10.10.11:
  28.       nodename: mx-sdw-1        # greenplum segment node
  29.       pg_instances:             # greenplum segment instances
  30.         6000: { pg_cluster: mx-seg1, pg_seq: 1, pg_role: primary , pg_exporter_port: 9633 }
  31.         6001: { pg_cluster: mx-seg2, pg_seq: 2, pg_role: replica , pg_exporter_port: 9634 }
  32.     10.10.10.12:
  33.       nodename: mx-sdw-2
  34.       pg_instances:
  35.         6000: { pg_cluster: mx-seg2, pg_seq: 1, pg_role: primary , pg_exporter_port: 9633  }
  36.         6001: { pg_cluster: mx-seg3, pg_seq: 2, pg_role: replica , pg_exporter_port: 9634  }
  37.     10.10.10.13:
  38.       nodename: mx-sdw-3
  39.       pg_instances:
  40.         6000: { pg_cluster: mx-seg3, pg_seq: 1, pg_role: primary , pg_exporter_port: 9633 }
  41.         6001: { pg_cluster: mx-seg1, pg_seq: 2, pg_role: replica , pg_exporter_port: 9634 }
  42.   vars:
  43.     gp_role: segment               # these are nodes for gp segments
  44.     pg_shard: mx                   # pgsql sharding name & gpsql deployment name
  45.     pg_cluster: mx-sdw             # these segment clusters name is mx-sdw
  46.     pg_preflight_skip: true        # skip preflight check (since pg_seq & pg_role & pg_cluster not exists)
  47.     pg_exporter_config: pg_exporter_basic.yml                             # use basic config to avoid segment server crash
  48.     pg_exporter_params: 'options=-c%20gp_role%3Dutility&sslmode=disable'  # use gp_role = utility to connect to segments

Last modified 2022-06-04: fill en docs (5a858d3)