我的书签
添加书签
移除书签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.
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)
