MXDB：MatrixDB Demo

Pigsty matrixdb 4-node exmaple file

``` 1. --- 2. ###################################################################### 3. # File : pigsty.yml 4. # Desc : Pigsty matrixdb 4-node exmaple file 5. # Link : https://pigsty.cc/#/v-config 6. # Ctime : 2020-05-22 7. # Mtime : 2022-05-22 8. # Copyright (C) 2018-2022 Ruohang Feng (rh@vonng.com) 9. ######################################################################

2. 1. mx-mdw-1 : 10.10.10.10 (2 Core | 4GB) mx-mdw-1(m)

3. 2. mx-sdw-1 : 10.10.10.11 (1 Core | 1GB) mx-seg1-1(p) mx-seg2-2(r)

4. 3. mx-sdw-2 : 10.10.10.12 (1 Core | 1GB) mx-seg2-1(p) mx-seg3-2(r)

5. 4. mx-sdw-3 : 10.10.10.13 (1 Core | 1GB) mx-seg3-1(p) mx-seg1-2(r)

7. all:

9. CLUSTERS

11. meta nodes, nodes, pgsql, redis, pgsql clusters are defined as

12. k:v pair inside all.children. Where the key is cluster name

13. and value is cluster definition consist of two parts:

14. hosts: cluster members ip and instance level variables

15. vars : cluster level variables

17. children: # groups definition

18. ================================================================

19. Meta Nodes: Admin Controller

20. ================================================================

21. meta: # meta nodes are defined in this special group "meta"
22. vars:
23. meta_node: true # mark this group as meta nodes

24. ansible_group_priority: 99 # overwrite with the highest priority

25. nginx_enabled: true # setup repo & underlying nginx

26. nameserver_enabled: false # setup dnsmasq
27. prometheus_enabled: true # setup prometheus
28. grafana_enabled: true # setup grafana
29. loki_enabled: true # setup loki
30. docker_enabled: true # setup docker

31. dcs_safeguard: false # protect dcs server from fat fingers

32. hosts: # add nodes to group 'meta'

33. 10.10.10.10: { }

34. 10.10.10.11: { nginx_enabled: false }

35. 10.10.10.12: { nginx_enabled: false }

36. ================================================================

37. ================================================================

38. GPSQL Clusters

39. ================================================================

40. ----------------------------------

41. cluster: mx-mdw (gp master)

42. ----------------------------------

43. mx-mdw:
44. hosts:
45. 10.10.10.10: { pg_seq: 1, pg_role: primary , nodename: mx-mdw-1 }
46. vars:
47. gp_role: master # this cluster is used as greenplum master
48. pg_shard: mx # pgsql sharding name & gpsql deployment name
49. pg_cluster: mx-mdw # this master cluster name is mx-mdw
50. pg_databases:
51. • { name: matrixmgr , extensions: [ { name: matrixdbts } ] }
52. • { name: meta }
53. pg_users:
54. • { name: meta , password: DBUser.Meta , pgbouncer: true }
55. • { name: dbuser_monitor , password: DBUser.Monitor , roles: [ dbrole_readonly ], superuser: true }

56. pgbouncer_enabled: true # enable pgbouncer for greenplum master

57. pgbouncer_exporter_enabled: false # enable pgbouncer_exporter for greenplum master

58. pg_exporter_params: 'host=127.0.0.1&sslmode=disable' # use 127.0.0.1 as local monitor host

59. ----------------------------------

60. cluster: mx-sdw (gp master)

61. ----------------------------------

62. mx-sdw:
63. hosts:
64. 10.10.10.11:
65. nodename: mx-sdw-1 # greenplum segment node
66. pg_instances: # greenplum segment instances
67. 6000: { pg_cluster: mx-seg1, pg_seq: 1, pg_role: primary , pg_exporter_port: 9633 }
68. 6001: { pg_cluster: mx-seg2, pg_seq: 2, pg_role: replica , pg_exporter_port: 9634 }
69. 10.10.10.12:
70. nodename: mx-sdw-2
71. pg_instances:
72. 6000: { pg_cluster: mx-seg2, pg_seq: 1, pg_role: primary , pg_exporter_port: 9633 }
73. 6001: { pg_cluster: mx-seg3, pg_seq: 2, pg_role: replica , pg_exporter_port: 9634 }
74. 10.10.10.13:
75. nodename: mx-sdw-3
76. pg_instances:
77. 6000: { pg_cluster: mx-seg3, pg_seq: 1, pg_role: primary , pg_exporter_port: 9633 }
78. 6001: { pg_cluster: mx-seg1, pg_seq: 2, pg_role: replica , pg_exporter_port: 9634 }
79. vars:
80. gp_role: segment # these are nodes for gp segments
81. pg_shard: mx # pgsql sharding name & gpsql deployment name
82. pg_cluster: mx-sdw # these segment clusters name is mx-sdw
83. pg_preflight_skip: true # skip preflight check (since pg_seq & pg_role & pg_cluster not exists)
84. pg_exporter_config: pg_exporter_basic.yml # use basic config to avoid segment server crash

85. pg_exporter_params: 'options=-c%20gp_role%3Dutility&sslmode=disable' # use gp_role = utility to connect to segments

87. VARS

89. vars: # global variables

90. version: v1.5.1 # pigsty version string

91. ================================================================

92. VARS: INFRA

93. ================================================================

94. -----------------------------------------------------------------

95. CONNECT

96. -----------------------------------------------------------------

97. INSTANCE level ansible connect parameters

98. ansible_user: vagrant # which user to be used (when connecting to remote nodes)

99. ansible_port: 22 # which port to be used (when connecting to remote nodes)

100. ansible_host: meta # ssh alias host name (when connecting to remote nodes)

101. ansible_ssh_private_key_file: ~/.ssh/id_rsa # ssh private key file

102. ansible_ssh_common_args: '-o StrictHostKeyChecking=no' # ssh common args

103. proxy_env: # global proxy env when downloading packages

104. no_proxy: "localhost,127.0.0.1,10.0.0.0/8,192.168.0.0/16,.pigsty,.aliyun.com,mirrors.,.myqcloud.com,*.tsinghua.edu.cn"

105. http_proxy: # set your proxy here: e.g http://user:pass@proxy.xxx.com

106. https_proxy: # set your proxy here: e.g http://user:pass@proxy.xxx.com

107. all_proxy: # set your proxy here: e.g http://user:pass@proxy.xxx.com

108. -----------------------------------------------------------------

109. CA

110. -----------------------------------------------------------------

111. ca_method: create # create|copy|recreate
112. ca_subject: "/CN=root-ca" # self-signed CA subject
113. ca_homedir: /ca # ca cert directory
114. ca_cert: ca.crt # ca public key/cert

115. ca_key: ca.key # ca private key

116. -----------------------------------------------------------------

117. NGINX

118. -----------------------------------------------------------------

119. nginx_enabled: true # build local repo on this node
120. nginx_port: 80 # repo listen address, must same as repo_address

121. nginx_home: /www # default repo home dir

122. nginx_upstream: # domain names and upstream servers

123. • { name: home , domain: pigsty , endpoint: "10.10.10.10:80" }
124. • { name: grafana , domain: g.pigsty , endpoint: "10.10.10.10:3000" }
125. • { name: loki , domain: l.pigsty , endpoint: "10.10.10.10:3100" }
126. • { name: prometheus , domain: p.pigsty , endpoint: "10.10.10.10:9090" }
127. • { name: alertmanager , domain: a.pigsty , endpoint: "10.10.10.10:9093" }
128. • { name: consul , domain: c.pigsty , endpoint: "127.0.0.1:8500" }
129. • { name: matrixdb , domain: mx.pigsty , endpoint: "127.0.0.1:8240" }

130. nginx_indexes: # application nav links on home page

131. • { name: MatrixDB , url : 'http://mx.pigsty' , comment: 'MatrixDB Installer' }
132. • { name: Explain , url : '/pev2' , comment: 'postgres explain visualizer' }
133. • { name: Package , url : '/pigsty' , comment: 'local yum repo packages' }
134. • { name: PG Logs , url : '/logs' , comment: 'postgres raw csv logs' }
135. • { name: Schemas , url : '/schema' , comment: 'schemaspy summary report' }
136. • { name: Reports , url : '/report' , comment: 'pgbadger summary report' }

137. -----------------------------------------------------------------

138. REPO

139. -----------------------------------------------------------------

140. repo_name: pigsty # repo name, pigsty by default
141. repo_address: pigsty # external address to this repo (ip:port or url)
142. repo_rebuild: false # force re-download packages
143. repo_remove: true # remove existing upstream repo
144. repo_upstreams: # where to download packages?
145. • name: base
146. description: CentOS-$releasever - Base
147. gpgcheck: no
148. baseurl:
149. • https://mirrors.tuna.tsinghua.edu.cn/centos/$releasever/os/$basearch/
150. • http://mirrors.aliyun.com/centos/$releasever/os/$basearch/
151. • http://mirror.centos.org/centos/$releasever/os/$basearch/
152. • name: updates
153. description: CentOS-$releasever - Updates
154. gpgcheck: no
155. baseurl:
156. • https://mirrors.tuna.tsinghua.edu.cn/centos/$releasever/updates/$basearch/
157. • http://mirrors.aliyun.com/centos/$releasever/updates/$basearch/
158. • http://mirror.centos.org/centos/$releasever/updates/$basearch/
159. • name: extras
160. description: CentOS-$releasever - Extras
161. baseurl:
162. • https://mirrors.tuna.tsinghua.edu.cn/centos/$releasever/extras/$basearch/
163. • http://mirrors.aliyun.com/centos/$releasever/extras/$basearch/
164. • http://mirror.centos.org/centos/$releasever/extras/$basearch/
165. gpgcheck: no
166. • name: epel
167. description: CentOS $releasever - epel
168. gpgcheck: no
169. baseurl:
170. • https://mirrors.tuna.tsinghua.edu.cn/epel/$releasever/$basearch
171. • http://mirrors.aliyun.com/epel/$releasever/$basearch
172. • http://download.fedoraproject.org/pub/epel/$releasever/$basearch
173. • name: grafana
174. description: Grafana Official Yum Repo
175. enabled: yes
176. gpgcheck: no
177. baseurl:
178. • https://mirrors.tuna.tsinghua.edu.cn/grafana/yum/rpm
179. • https://packages.grafana.com/oss/rpm
180. • name: prometheus
181. description: Prometheus and exporters
182. gpgcheck: no
183. baseurl: https://packagecloud.io/prometheus-rpm/release/el/$releasever/$basearch
184. • name: pgdg-common
185. description: PostgreSQL common RPMs for RHEL/CentOS $releasever - $basearch
186. gpgcheck: no
187. baseurl:
188. • http://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/common/redhat/rhel-$releasever-$basearch
189. • https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-$releasever-$basearch
190. • name: pgdg14
191. description: PostgreSQL 14 for RHEL/CentOS $releasever - $basearch
192. gpgcheck: no
193. baseurl:
194. • https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/14/redhat/rhel-$releasever-$basearch
195. • https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-$releasever-$basearch
196. • name: timescaledb
197. description: TimescaleDB for RHEL/CentOS $releasever - $basearch
198. gpgcheck: no
199. baseurl:
200. • https://packagecloud.io/timescale/timescaledb/el/7/$basearch
201. • name: centos-sclo
202. description: CentOS-$releasever - SCLo
203. gpgcheck: no

204. mirrorlist: http://mirrorlist.centos.org?arch=$basearch&release=$releasever&repo=sclo-sclo

205. baseurl: http://mirrors.aliyun.com/centos/$releasever/sclo/$basearch/sclo/
206. • name: centos-sclo-rh
207. description: CentOS-$releasever - SCLo rh
208. gpgcheck: no

209. mirrorlist: http://mirrorlist.centos.org?arch=$basearch&release=7&repo=sclo-rh

210. baseurl: http://mirrors.aliyun.com/centos/$releasever/sclo/$basearch/rh/
211. • name: nginx
212. description: Nginx Official Yum Repo
213. skip_if_unavailable: true
214. gpgcheck: no
215. baseurl: http://nginx.org/packages/centos/$releasever/$basearch/
216. • name: harbottle # for latest consul & kubernetes
217. description: Copr repo for main owned by harbottle
218. skip_if_unavailable: true
219. gpgcheck: no
220. baseurl: https://download.copr.fedorainfracloud.org/results/harbottle/main/epel-$releasever-$basearch/
221. • name: pg_probackup # for pg_probackup
222. description: PG_PROBACKUP Centos packages for PostgresPro Standard and Enterprise - $basearch
223. skip_if_unavailable: true
224. gpgcheck: no
225. baseurl: https://repo.postgrespro.ru/pg_probackup-forks/rpm/latest/centos-$releasever-$basearch
226. • name: docker-ce # for latest docker
227. description: Docker CE Stable - $basearch
228. skip_if_unavailable: true
229. gpgcheck: no
230. baseurl:
231. • https://mirrors.aliyun.com/docker-ce/linux/centos/$releasever/$basearch/stable
232. • https://download.docker.com/linux/centos/$releasever/$basearch/stable

233. repo_packages: # which packages to be included # what to download #

234. • epel-release nginx wget yum-utils yum createrepo sshpass zip unzip # ---- boot ---- #
235. • ntp chrony uuid lz4 bzip2 nc pv jq vim-enhanced make patch bash lsof wget git tuned perf ftp lrzsz rsync # ---- node ---- #
236. • numactl grubby sysstat dstat iotop bind-utils net-tools tcpdump socat ipvsadm telnet ca-certificates keepalived # ----- utils ----- #
237. • readline zlib openssl openssh-clients libyaml libxml2 libxslt libevent perl perl-devel perl-ExtUtils* # --- deps:pg --- #
238. • readline-devel zlib-devel uuid-devel libuuid-devel libxml2-devel libxslt-devel openssl-devel libicu-devel # --- deps:devel -- #
239. • grafana prometheus2 pushgateway alertmanager mtail consul consul_exporter consul-template etcd dnsmasq # ----- meta ----- #
240. • node_exporter nginx_exporter blackbox_exporter redis_exporter # ---- exporter --- #
241. • ansible python python-pip python-psycopg2 # - ansible & py3 - #
242. • python3 python3-psycopg2 python36-requests python3-etcd python3-consul python36-urllib3 python36-idna python36-pyOpenSSL python36-cryptography
243. • patroni patroni-consul patroni-etcd pgbouncer pg_cli pgbadger pg_activity tail_n_mail # -- pgsql common - #
244. • pgcenter boxinfo check_postgres emaj pgbconsole pg_bloat_check pgquarrel barman barman-cli pgloader pgFormatter pitrery pspg pgxnclient PyGreSQL
245. • postgresql14 postgis32_14 citus_14 pglogical_14 timescaledb-2-postgresql-14 pg_repack_14 wal2json_14 # -- pg14 packages -#
246. • pg_qualstats_14 pg_stat_kcache_14 pg_stat_monitor_14 pg_top_14 pg_track_settings_14 pg_wait_sampling_14 pg_probackup-std-14
247. • pg_statement_rollback_14 system_stats_14 plproxy_14 plsh_14 pldebugger_14 plpgsql_check_14 pgmemcache_14 # plr_14
248. • mysql_fdw_14 ogr_fdw_14 tds_fdw_14 sqlite_fdw_14 firebird_fdw_14 hdfs_fdw_14 mongo_fdw_14 osm_fdw_14 pgbouncer_fdw_14
249. • hypopg_14 geoip_14 rum_14 hll_14 ip4r_14 prefix_14 pguri_14 tdigest_14 topn_14 periods_14
250. • bgw_replstatus_14 count_distinct_14 credcheck_14 ddlx_14 extra_window_functions_14 logerrors_14 mysqlcompat_14 orafce_14
251. • repmgr_14 pg_auth_mon_14 pg_auto_failover_14 pg_background_14 pg_bulkload_14 pg_catcheck_14 pg_comparator_14
252. • pg_cron_14 pg_fkpart_14 pg_jobmon_14 pg_partman_14 pg_permissions_14 pg_prioritize_14 pgagent_14
253. • pgaudit16_14 pgauditlogtofile_14 pgcryptokey_14 pgexportdoc_14 pgfincore_14 pgimportdoc_14 powa_14 pgmp_14 pgq_14
254. • pgquarrel-0.7.0-1 pgsql_tweaks_14 pgtap_14 pgtt_14 postgresql-unit_14 postgresql_anonymizer_14 postgresql_faker_14
255. • safeupdate_14 semver_14 set_user_14 sslutils_14 table_version_14 # pgrouting_14 osm2pgrouting_14
256. • clang coreutils diffutils rpm-build rpm-devel rpmlint rpmdevtools bison flex # gcc gcc-c++ # - build utils - #
257. • docker-ce docker-compose kubelet kubectl kubeadm kubernetes-cni helm # - cloud native- #
258. • ed mlocate parted krb5-devel apr apr-util audit parquet-libs-3.0.0 arrow-libs-3.0.0 # --- deps:gpsql -- #

259. repo_url_packages: # extra packages from url

260. • https://github.com/Vonng/loki-rpm/releases/download/v2.5.0/loki-2.5.0.x86_64.rpm
261. • https://github.com/Vonng/loki-rpm/releases/download/v2.5.0/promtail-2.5.0.x86_64.rpm
262. • https://github.com/Vonng/pg_exporter/releases/download/v0.5.0/pg_exporter-0.5.0.x86_64.rpm
263. • https://github.com/cybertec-postgresql/vip-manager/releases/download/v1.0.2/vip-manager-1.0.2-1.x86_64.rpm
264. • https://github.com/Vonng/haproxy-rpm/releases/download/v2.5.7/haproxy-2.5.7-1.el7.x86_64.rpm
265. • https://github.com/Vonng/pigsty-pkg/releases/download/misc/redis-6.2.7-1.el7.remi.x86_64.rpm
266. • https://github.com/dalibo/pev2/releases/download/v0.24.0/pev2.tar.gz
267. • https://github.com/Vonng/pigsty-pkg/releases/download/misc/polysh-0.4-1.noarch.rpm
268. • https://github.com/greenplum-db/gpdb/releases/download/6.20.3/open-source-greenplum-db-6.20.3-rhel7-x86_64.rpm # -- gpsql --#
269. • https://github.com/Vonng/pigsty-pkg/releases/download/misc/matrixdb-4.4.0.community-1.el7.x86_64.rpm # - matrix - #

270. -----------------------------------------------------------------

271. NAMESERVER

272. -----------------------------------------------------------------

273. nameserver_enabled: false # setup dnsmasq
274. dns_records: # dynamic dns record resolved by dnsmasq
275. • 10.10.10.2 pg-meta # sandbox vip for pg-meta
276. • 10.10.10.3 pg-test # sandbox vip for pg-test
277. • 10.10.10.10 pg-meta-1 # sandbox instance pg-meta-1
278. • 10.10.10.11 pg-test-1 # sandbox instance node-1
279. • 10.10.10.12 pg-test-2 # sandbox instance node-2
280. • 10.10.10.13 pg-test-3 # sandbox instance node-3

281. -----------------------------------------------------------------

282. PROMETHEUS

283. -----------------------------------------------------------------

284. prometheus_enabled: true # setup prometheus
285. prometheus_data_dir: /data/prometheus/data
286. prometheus_options: '--storage.tsdb.retention=15d'
287. prometheus_reload: false # reload prometheus instead of recreate it?
288. prometheus_sd_method: static # service discovery method: static|consul
289. prometheus_sd_interval: 5s # service discovery refresh interval
290. prometheus_scrape_interval: 10s # global scrape & evaluation interval

291. prometheus_scrape_timeout: 8s # scrape timeout

292. -----------------------------------------------------------------

293. EXPORTER

294. -----------------------------------------------------------------

295. exporter_install: none # none|yum|binary, none by default
296. exporter_repo_url: '' # if set, repo will be added to /etc/yum.repos.d/

297. exporter_metrics_path: /metrics # default metric path for pg related exporter

298. -----------------------------------------------------------------

299. GRAFANA

300. -----------------------------------------------------------------

301. grafana_enabled: true # enable grafana? only works on meta nodes
302. grafana_endpoint: http://10.10.10.10:3000 # grafana endpoint url
303. grafana_admin_username: admin # default grafana admin username
304. grafana_admin_password: pigsty # default grafana admin password
305. grafana_database: sqlite3 # default grafana database type: sqlite3|postgres

306. grafana_pgurl: postgres://dbuser_grafana:DBUser.Grafana@meta:5436/grafana

307. grafana_plugin_method: install # none|install|always, none will skip plugin install

308. grafana_plugin_cache: /www/pigsty/plugins.tgz # path to grafana plugins cache tarball
309. grafana_plugin_list: # plugins that will be downloaded via grafana-cli
310. • marcusolsson-csv-datasource
311. • marcusolsson-json-datasource
312. • marcusolsson-treemap-panel
313. grafana_plugin_git: # plugins that will be downloaded via git
314. • https://github.com/Vonng/vonng-echarts-panel

315. -----------------------------------------------------------------

316. LOKI

317. -----------------------------------------------------------------

318. loki_enabled: true # enable loki? only works on meta nodes
319. loki_clean: false # whether remove existing loki data
320. loki_endpoint: http://10.10.10.10:3100/loki/api/v1/push # where to push data
321. loki_options: '-config.file=/etc/loki.yml -config.expand-env=true'
322. loki_data_dir: /data/loki # default loki data dir

323. loki_retention: 15d # log retention period

324. -----------------------------------------------------------------

325. DCS

326. -----------------------------------------------------------------

327. dcs_name: pigsty # consul dc name
328. dcs_servers: # dcs server dict in name:ip format
329. meta-1: 10.10.10.10 # using existing external dcs cluster is recommended for HA

330. meta-2: 10.10.10.11 # node with ip in dcs_servers will be initialized as dcs servers

331. meta-3: 10.10.10.12 # it's recommend to reuse meta nodes as dcs servers if no ad hoc cluster available

332. dcs_registry: consul # where to register services: none | consul | etcd | both
333. dcs_safeguard: false # if true, running dcs will NOT be removed

334. dcs_clean: true # if true, running dcs will be purged during node init, DANGEROUS

335. -----------------------------------------------------------------

336. CONSUL

337. -----------------------------------------------------------------

338. consul_enabled: true # enable consul server/agent by default?

339. consul_data_dir: /data/consul # consul data dir (/data/consul by default)

340. -----------------------------------------------------------------

341. ETCD

342. -----------------------------------------------------------------

343. etcd_enabled: true # enable etcd server by default?

344. etcd_data_dir: /data/etcd # etcd data dir (/data/etcd by default)

345. ================================================================

346. VARS: NODES

347. ================================================================

348. global variables for nodes (including meta)

349. -----------------------------------------------------------------

350. NODE_IDENTITY

351. -----------------------------------------------------------------

352. meta_node: false # node with meta_node flag will be marked as admin node

353. nodename: # [OPTIONAL] # node instance identity, used as ins, hostname by default

354. node_cluster: nodes # [OPTIONAL] # node cluster identity, used as cls, 'nodes' by default
355. nodename_overwrite: true # overwrite node's hostname with nodename?

356. nodename_exchange: true # exchange nodename among play hosts?

357. -----------------------------------------------------------------

358. NODE_DNS

359. -----------------------------------------------------------------

360. node_etc_hosts_default: # static dns records in /etc/hosts
361. • 10.10.10.10 meta pigsty p.pigsty g.pigsty a.pigsty c.pigsty l.pigsty
362. • 10.10.10.10 api.pigsty adm.pigsty cli.pigsty ddl.pigsty lab.pigsty git.pigsty sss.pigsty

363. node_etc_hosts: [] # extra static dns records in /etc/hosts

364. node_dns_method: add # add (default) | none (skip) | overwrite (remove old settings)

365. node_dns_servers: # dynamic nameserver in /etc/resolv.conf
366. • 10.10.10.10
367. node_dns_options: # dns resolv options
368. • options single-request-reopen timeout:1 rotate
369. • domain service.consul

370. -----------------------------------------------------------------

371. NODE_REPO

372. -----------------------------------------------------------------

373. node_repo_method: local # none|local: ad local repo|public: add upstream directly
374. node_repo_remove: true # remove existing repo on nodes?
375. node_repo_local_urls: # list local repo url, if node_repo_method = local
376. • http://pigsty/pigsty.repo
377. • http://pigsty/matrix.repo

378. node_packages: [ ] # extra packages for all nodes

379. node_packages_default: # common packages for all nodes
380. • wget,sshpass,ntp,chrony,tuned,uuid,lz4,make,patch,bash,lsof,wget,unzip,git,ftp,vim-minimal,ca-certificates
381. • numactl,grubby,sysstat,dstat,iotop,bind-utils,net-tools,tcpdump,socat,ipvsadm,telnet,tuned,nc,pv,jq,perf
382. • readline,zlib,openssl,openssl-libs,openssh-clients,python3,python36-requests,node_exporter,consul,etcd,promtail
383. node_packages_meta: # extra packages for meta nodes
384. • grafana,prometheus2,alertmanager,loki,nginx_exporter,blackbox_exporter,pushgateway,redis,postgresql14
385. • nginx,ansible,pgbadger,python-psycopg2,dnsmasq,coreutils,diffutils,polysh,docker-ce,docker-compose

386. node_packages_meta_pip: jupyterlab

387. -----------------------------------------------------------------

388. NODE_TUNE

389. -----------------------------------------------------------------

390. node_disable_firewall: true # disable firewall
391. node_disable_selinux: true # disable selinux
392. node_disable_numa: false # disable numa (node reboot required)
393. node_disable_swap: false # disable swap, use with caution
394. node_static_network: true # keep dns resolver settings after reboot
395. node_disk_prefetch: false # setup disk prefetch on HDD to increase performance
396. node_kernel_modules: [ softdog, br_netfilter, ip_vs, ip_vs_rr, ip_vs_rr, ip_vs_wrr, ip_vs_sh ]
397. node_tune: tiny # install and activate tuned profile: none|oltp|olap|crit|tiny

398. node_sysctl_params: { } # set additional sysctl parameters, k:v format

399. -----------------------------------------------------------------

400. NODE_ADMIN

401. -----------------------------------------------------------------

402. node_data_dir: /data # main data directory
403. node_admin_enabled: true # create a default admin user defined by node_admin_* ?
404. node_admin_uid: 88 # uid and gid for this admin user
405. node_admin_username: dba # name of this admin user, dba by default
406. node_admin_ssh_exchange: true # exchange admin ssh key among each pgsql cluster ?
407. node_admin_pk_current: true # add current user's ~/.ssh/id_rsa.pub to admin authorized_keys ?
408. node_admin_pk_list: # ssh public keys to be added to admin user (REPLACE WITH YOURS!)
409. • 'ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAAAgQC7IMAMNavYtWwzAJajKqwdn3ar5BhvcwCnBTxxEkXhGlCO2vfgosSAQMEflfgvkiI5nM1HIFQ8KINlx1XLO7SdL5KdInG5LIJjAFh0pujS4kNCT9a5IGvSq1BrzGqhbEcwWYdju1ZPYBcJm/MG+JD0dYCh8vfrYB/cYMD0SOmNkQ== vagrant@pigsty.com'

410. -----------------------------------------------------------------

411. NODE_TIME

412. -----------------------------------------------------------------

413. node_timezone: Asia/Hong_Kong # default node timezone, empty will not change
414. node_ntp_enabled: true # enable ntp service? false will leave ntp service untouched
415. node_ntp_service: ntp # ntp service provider: ntp|chrony
416. node_ntp_servers: # default NTP servers
417. • pool cn.pool.ntp.org iburst
418. • pool pool.ntp.org iburst
419. • pool time.pool.aliyun.com iburst
420. • server 10.10.10.10 iburst
421. • server ntp.tuna.tsinghua.edu.cn iburst
422. node_crontab_overwrite: true # true will overwrite /etc/crontab, false will append crontab

423. node_crontab: [ ] # crontab entries in /etc/crontab

424. -----------------------------------------------------------------

425. DOCKER

426. -----------------------------------------------------------------

427. docker_enabled: false # enable docker on all nodes? (you can enable them on meta nodes only)
428. docker_cgroups_driver: systemd # docker cgroup fs driver
429. docker_registry_mirrors: [] # docker registry mirror

430. docker_image_cache: /tmp/docker.tgz # docker images tarball to be loaded if exists

431. -----------------------------------------------------------------

432. NODE_EXPORTER

433. -----------------------------------------------------------------

434. node_exporter_enabled: true # setup node_exporter on instance
435. node_exporter_port: 9100 # default port for node exporter

436. node_exporter_options: '--no-collector.softnet --no-collector.nvme --collector.ntp --collector.tcpstat --collector.processes'

437. -----------------------------------------------------------------

438. PROMTAIL

439. -----------------------------------------------------------------

440. promtail_enabled: true # enable promtail logging collector?
441. promtail_clean: false # remove promtail status file? false by default
442. promtail_port: 9080 # default listen address for promtail
443. promtail_options: '-config.file=/etc/promtail.yml -config.expand-env=true'

444. promtail_positions: /var/log/positions.yaml # position status for promtail

445. ================================================================

446. VARS: PGSQL

447. ================================================================

448. -----------------------------------------------------------------

449. PG_IDENTITY

450. -----------------------------------------------------------------

451. pg_cluster: # <CLUSTER> <REQUIRED> : pgsql cluster name

452. pg_role: replica # <INSTANCE> <REQUIRED> : pg role : primary, replica, offline

453. pg_seq: 0 # <INSTANCE> <REQUIRED> : instance seq number

454. pg_instances: {} # <INSTANCE> : define multiple pg instances on node, used by monly & gpsql

455. pg_upstream: # <INSTANCE> : replication upstream ip addr

456. pg_shard: # <CLUSTER> : pgsql shard name

457. pg_sindex: 0 # <CLUSTER> : pgsql shard index

458. gp_role: master # <CLUSTER> : gpsql role, master or segment

459. pg_offline_query: false # <INSTANCE> [FLAG] set to true to enable offline query on this instance (instance level)

460. pg_backup: false # <INSTANCE> [FLAG] store base backup on this node (instance level, reserved)
461. pg_weight: 100 # <INSTANCE> [FLAG] default load balance weight (instance level)
462. pg_hostname: true # [FLAG] reuse postgres identity name as node identity?

463. pg_preflight_skip: false # [FLAG] skip preflight identity check

464. -----------------------------------------------------------------

465. PG_BUSINESS

466. -----------------------------------------------------------------

467. overwrite these variables on <CLUSTER> level

468. pg_users: [] # business users
469. pg_databases: [] # business databases
470. pg_services_extra: [] # extra services
471. pg_hba_rules_extra: [] # extra hba rules

472. pgbouncer_hba_rules_extra: [] # extra pgbouncer hba rules

473. WARNING: change these in production environment!

474. pg_admin_username: mxadmin
475. pg_admin_password: mxadmin
476. pg_monitor_username: mxadmin
477. pg_monitor_password: mxadmin
478. pg_replication_username: mxadmin

479. pg_replication_password: mxadmin

480. -----------------------------------------------------------------

481. PG_INSTALL

482. -----------------------------------------------------------------

483. pg_dbsu: postgres # os user for database, postgres by default (unwise to change it)
484. pg_dbsu_uid: 26 # os dbsu uid and gid, 26 for default postgres users and groups
485. pg_dbsu_sudo: limit # dbsu sudo privilege: none|limit|all|nopass, limit by default
486. pg_dbsu_home: /var/lib/pgsql # postgresql home directory
487. pg_dbsu_ssh_exchange: true # exchange postgres dbsu ssh key among same cluster ?
488. pg_version: 14 # default postgresql version to be installed
489. pgdg_repo: false # add pgdg official repo before install (in case of no local repo available)
490. pg_add_repo: false # add postgres relate repo before install ?
491. pg_bin_dir: /usr/pgsql/bin # postgres binary dir, default is /usr/pgsql/bin, which use /usr/pgsql -> /usr/pgsql-{ver}
492. pg_packages: # postgresql related packages. ${pg_version} will be replaced bypg_version`
493. • postgresql${pg_version}* # postgresql kernel packages
494. • postgis32_${pg_version}* # postgis
495. • citus_${pg_version}* # citus
496. • timescaledb-2-postgresql-${pg_version} # timescaledb
497. • pgbouncer pg_exporter pgbadger pg_activity node_exporter consul haproxy vip-manager
498. • patroni patroni-consul patroni-etcd python3 python3-psycopg2 python36-requests python3-etcd
499. • python3-consul python36-urllib3 python36-idna python36-pyOpenSSL python36-cryptography
500. pg_extensions: # postgresql extensions, ${pg_version} will be replaced by actualpg_version`
501. • pg_repack_${pg_version} pg_qualstats_${pg_version} pg_stat_kcache_${pg_version} pg_stat_monitor_${pg_version} wal2json_${pg_version}

502. - ogr_fdw${pg_version} mysql_fdw_${pg_version} redis_fdw_${pg_version} mongo_fdw${pg_version} hdfs_fdw_${pg_version}

503. - count_distinct${version} ddlx_${version} geoip${version} orafce${version}

504. - hypopg_${version} ip4r${version} jsquery_${version} logerrors_${version} periods_${version} pg_auto_failover_${version} pg_catcheck${version}

505. - pg_fkpart${version} pg_jobmon${version} pg_partman${version} pg_prioritize_${version} pg_track_settings${version} pgaudit15_${version}

506. - pgcryptokey${version} pgexportdoc${version} pgimportdoc${version} pgmemcache-${version} pgmp${version} pgq-${version} pgquarrel pgrouting_${version}

507. - pguint${version} pguri${version} prefix${version} safeupdate_${version} semver${version} table_version${version} tdigest${version}

508. -----------------------------------------------------------------

509. PG_BOOTSTRAP

510. -----------------------------------------------------------------

511. pg_safeguard: false # true will disable pg_clean at all, even for pgsql-remove.yml
512. pg_clean: true # true will clean running postgres during pgsql init (DANGEROUS)
513. pg_data: /pg/data # postgres data directory (soft link)
514. pg_fs_main: /data # primary data disk mount point /pg -> {{ pg_fs_main }}/postgres/{{ pg_instance }}
515. pg_fs_bkup: /data/backups # backup disk mount point /pg/ -> {{ pg_fs_bkup }}/postgres/{{ pg_instance }}/
516. pg_dummy_filesize: 64MiB # /pg/dummy hold some disk space for emergency use
517. pg_listen: '0.0.0.0' # postgres listen address, '0.0.0.0' (all ipv4 addr) by default
518. pg_port: 5432 # postgres port, 5432 by default
519. pg_localhost: /var/run/postgresql # localhost unix socket dir for connection
520. patroni_enabled: true # if not enabled, no postgres cluster will be created
521. patroni_mode: default # pause|default|remove
522. pg_dcs_type: consul # which dcs to use: consul or etcd or raft
523. pg_namespace: /pg # top level key namespace in dcs
524. patroni_port: 8008 # default patroni port
525. patroni_watchdog_mode: automatic # watchdog mode: off|automatic|required
526. pg_conf: tiny.yml # pgsql template: {oltp|olap|crit|tiny}.yml
527. pg_libs: 'timescaledb, pg_stat_statements, auto_explain' # extensions to be loaded
528. pg_delay: 0 # apply delay for standby cluster leader
529. pg_checksum: false # enable data checksum by default
530. pg_encoding: UTF8 # database cluster encoding, UTF8 by default
531. pg_locale: C # database cluster local, C by default
532. pg_lc_collate: C # database cluster collate, C by default
533. pg_lc_ctype: en_US.UTF8 # database character type, en_US.UTF8 by default (for i18n full-text search)
534. pgbouncer_enabled: false # if not enabled, pgbouncer will not be created
535. pgbouncer_port: 6432 # pgbouncer port, 6432 by default
536. pgbouncer_poolmode: session # pooling mode: session|transaction|statement, transaction pooling by default

537. pgbouncer_max_db_conn: 100 # max connection to single database, DO NOT set this larger than postgres max conn or db connlimit

538. -----------------------------------------------------------------

539. PG_PROVISION

540. -----------------------------------------------------------------

541. pg_provision: false # whether provisioning postgres cluster

542. pg_init: pg-init # init script for cluster template

543. pg_default_roles:

544. • { name: dbrole_readonly , login: false , comment: role for global read-only access } # production read-only role
545. • { name: dbrole_readwrite , login: false , roles: [dbrole_readonly], comment: role for global read-write access } # production read-write role
546. • { name: dbrole_offline , login: false , comment: role for restricted read-only access (offline instance) } # restricted-read-only role
547. • { name: dbrole_admin , login: false , roles: [pg_monitor, dbrole_readwrite] , comment: role for object creation } # production DDL change role
548. • { name: postgres , superuser: true , comment: system superuser } # system dbsu, name is designated by pg_dbsu
549. • { name: dbuser_dba , superuser: true , roles: [dbrole_admin] , comment: system admin user } # admin dbsu, name is designated by pg_admin_username
550. • { name: replicator , replication: true , bypassrls: true , roles: [pg_monitor, dbrole_readonly] , comment: system replicator } # replicator
551. • { name: dbuser_monitor , roles: [pg_monitor, dbrole_readonly] , comment: system monitor user , parameters: {log_min_duration_statement: 1000 } } # monitor user
552. • { name: dbuser_stats , password: DBUser.Stats , roles: [dbrole_offline] , comment: business offline user for offline queries and ETL } # ETL user
553. pg_default_privileges: # - privileges - #
554. • GRANT USAGE ON SCHEMAS TO dbrole_readonly
555. • GRANT SELECT ON TABLES TO dbrole_readonly
556. • GRANT SELECT ON SEQUENCES TO dbrole_readonly
557. • GRANT EXECUTE ON FUNCTIONS TO dbrole_readonly
558. • GRANT USAGE ON SCHEMAS TO dbrole_offline
559. • GRANT SELECT ON TABLES TO dbrole_offline
560. • GRANT SELECT ON SEQUENCES TO dbrole_offline
561. • GRANT EXECUTE ON FUNCTIONS TO dbrole_offline
562. • GRANT INSERT, UPDATE, DELETE ON TABLES TO dbrole_readwrite
563. • GRANT USAGE, UPDATE ON SEQUENCES TO dbrole_readwrite
564. • GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES TO dbrole_admin
565. • GRANT CREATE ON SCHEMAS TO dbrole_admin
566. pg_default_schemas: [ monitor ] # default schemas to be created
567. pg_default_extensions: # default extensions to be created
568. • { name: 'pg_stat_statements', schema: 'monitor' }
569. • { name: 'pgstattuple', schema: 'monitor' }
570. • { name: 'pg_qualstats', schema: 'monitor' }
571. • { name: 'pg_buffercache', schema: 'monitor' }
572. • { name: 'pageinspect', schema: 'monitor' }
573. • { name: 'pg_prewarm', schema: 'monitor' }
574. • { name: 'pg_visibility', schema: 'monitor' }
575. • { name: 'pg_freespacemap', schema: 'monitor' }
576. • { name: 'pg_repack', schema: 'monitor' }
577. • name: postgres_fdw
578. • name: file_fdw
579. • name: btree_gist
580. • name: btree_gin
581. • name: pg_trgm
582. • name: intagg
583. • name: intarray

584. pg_reload: true # reload postgres after hba changes

585. pg_hba_rules: # postgres host-based authentication rules
586. • title: allow meta node password access
587. role: common
588. rules:
589. • host all all 10.10.10.10/32 md5
590. • title: allow intranet admin password access
591. role: common
592. rules:
593. • host all +dbrole_admin 10.0.0.0/8 md5
594. • host all +dbrole_admin 172.16.0.0/12 md5
595. • host all +dbrole_admin 192.168.0.0/16 md5
596. • title: allow intranet password access
597. role: common
598. rules:
599. • host all all 10.0.0.0/8 md5
600. • host all all 172.16.0.0/12 md5
601. • host all all 192.168.0.0/16 md5
602. • title: allow local read/write (local production user via pgbouncer)
603. role: common
604. rules:
605. • local all +dbrole_readonly md5
606. • host all +dbrole_readonly 127.0.0.1/32 md5
607. • title: allow offline query (ETL,SAGA,Interactive) on offline instance
608. role: offline
609. rules:
610. • host all +dbrole_offline 10.0.0.0/8 md5
611. • host all +dbrole_offline 172.16.0.0/12 md5
612. • host all +dbrole_offline 192.168.0.0/16 md5

613. pgbouncer_hba_rules: # pgbouncer host-based authentication rules

614. • title: local password access
615. role: common
616. rules:
617. • local all all md5
618. • host all all 127.0.0.1/32 md5
619. • title: intranet password access
620. role: common
621. rules:
622. • host all all 10.0.0.0/8 md5
623. • host all all 172.16.0.0/12 md5
624. • host all all 192.168.0.0/16 md5

625. -----------------------------------------------------------------

626. PG_EXPORTER

627. -----------------------------------------------------------------

628. pg_exporter_enabled: true # setup pg_exporter on instance
629. pg_exporter_config: pg_exporter.yml # use fast cache exporter for demo
630. pg_exporter_port: 9630 # pg_exporter listen port
631. pg_exporter_params: 'sslmode=disable' # url query parameters for pg_exporter
632. pg_exporter_url: '' # optional, overwrite auto-generate postgres connstr
633. pg_exporter_auto_discovery: true # optional, discovery available database on target instance ?
634. pg_exporter_exclude_database: 'template0,template1,postgres,matrixmgr' # optional, comma separated list of database that WILL NOT be monitored when auto-discovery enabled
635. pg_exporter_include_database: '' # optional, comma separated list of database that WILL BE monitored when auto-discovery enabled, empty string will disable include mode
636. pg_exporter_options: '--log.level=info --log.format="logger

（本文超出站点单文档体积上限，已截断）