Auto: 1 Node Default

Pigsty singleton meta auto-generated config template

``` 1. --- 2. ###################################################################### 3. # File : pigsty.yml 4. # Desc : Pigsty singleton meta auto-generated config template 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. ######################################################################

1. all:

3. CLUSTERS

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

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

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

8. hosts: cluster members ip and instance level variables

9. vars : cluster level variables

11. children: # groups definition

12. ================================================================

13. Meta Nodes: Admin Controller

14. ================================================================

15. meta: # meta nodes are defined in this special group "meta"
16. vars:
17. meta_node: true # mark this group as meta nodes

18. ansible_group_priority: 99 # overwrite with the highest priority

19. nginx_enabled: true # setup repo & underlying nginx

20. nameserver_enabled: false # setup dnsmasq
21. prometheus_enabled: true # setup prometheus
22. grafana_enabled: true # setup grafana
23. loki_enabled: true # setup loki
24. docker_enabled: true # setup docker

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

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

27. 10.10.10.10: { }

28. 10.10.10.11: { nginx_enabled: false }

29. 10.10.10.12: { nginx_enabled: false }

30. ================================================================

31. ================================================================

32. PGSQL Clusters

33. ================================================================

34. ----------------------------------

35. pgsql cluster: pg-meta (CMDB)

36. ----------------------------------

37. pg-meta:
38. hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary , pg_offline_query: true } }
39. vars:
40. pg_cluster: pg-meta
41. patroni_watchdog_mode: off
42. pg_databases:
43. • { name: meta, baseline: cmdb.sql , schemas: [ pigsty ] , extensions: [ { name: adminpack, schema: pg_catalog } , { name: postgis, schema: public } , { name: timescaledb } ] ,comment: pigsty meta database }
44. pg_users:
45. • {name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [dbrole_admin] ,comment: pigsty cmdb admin user }
46. • {name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
47. node_crontab:
48. • '00 01 * * * postgres /pg/bin/pg-backup 2>>/pg/log/backup.log'

49. ----------------------------------

50. pgsql cluster: pg-test (example)

51. ----------------------------------

52. pg-test:

53. hosts:

54. 10.10.10.11: { pg_seq: 1, pg_role: primary }

55. 10.10.10.12: { pg_seq: 2, pg_role: replica }

56. 10.10.10.13: { pg_seq: 3, pg_role: replica, pg_offline_query: true }

57. vars:

58. pg_cluster: pg-test

59. pg_users: [ { name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] , comment: test user } ]

60. pg_databases: [ { name: test , extensions: [ { name: postgis, schema: public } ] } ]

62. VARS

64. vars: # global variables

65. version: v1.5.1 # pigsty version string

66. ================================================================

67. VARS: INFRA

68. ================================================================

69. -----------------------------------------------------------------

70. CONNECT

71. -----------------------------------------------------------------

72. INSTANCE level ansible connect parameters

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

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

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

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

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

78. proxy_env: # global proxy env when downloading packages

79. 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"

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

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

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

83. -----------------------------------------------------------------

84. CA

85. -----------------------------------------------------------------

86. ca_method: create # create|copy|recreate
87. ca_subject: "/CN=root-ca" # self-signed CA subject
88. ca_homedir: /ca # ca cert directory
89. ca_cert: ca.crt # ca public key/cert

90. ca_key: ca.key # ca private key

91. -----------------------------------------------------------------

92. NGINX

93. -----------------------------------------------------------------

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

96. nginx_home: /www # default repo home dir

97. nginx_upstream: # domain names and upstream servers

98. • { name: home , domain: pigsty , endpoint: "10.10.10.10:80" }
99. • { name: grafana , domain: g.pigsty , endpoint: "10.10.10.10:3000" }
100. • { name: loki , domain: l.pigsty , endpoint: "10.10.10.10:3100" }
101. • { name: prometheus , domain: p.pigsty , endpoint: "10.10.10.10:9090" }
102. • { name: alertmanager , domain: a.pigsty , endpoint: "10.10.10.10:9093" }
103. • { name: consul , domain: c.pigsty , endpoint: "127.0.0.1:8500" }

104. nginx_indexes: # application nav links on home page

105. • { name: Explain , url : '/pev2' , comment: 'postgres explain visualizer' }
106. • { name: Package , url : '/pigsty' , comment: 'local yum repo packages' }
107. • { name: PG Logs , url : '/logs' , comment: 'postgres raw csv logs' }
108. • { name: Schemas , url : '/schema' , comment: 'schemaspy summary report' }
109. • { name: Reports , url : '/report' , comment: 'pgbadger summary report' }

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

111. REPO

112. -----------------------------------------------------------------

113. repo_name: pigsty # repo name, pigsty by default
114. repo_address: pigsty # external address to this repo (ip:port or url)
115. repo_rebuild: false # force re-download packages
116. repo_remove: true # remove existing upstream repo
117. repo_upstreams: # where to download packages?
118. • name: base
119. description: CentOS-$releasever - Base
120. gpgcheck: no
121. baseurl:
122. • https://mirrors.tuna.tsinghua.edu.cn/centos/$releasever/os/$basearch/
123. • http://mirrors.aliyun.com/centos/$releasever/os/$basearch/
124. • http://mirror.centos.org/centos/$releasever/os/$basearch/
125. • name: updates
126. description: CentOS-$releasever - Updates
127. gpgcheck: no
128. baseurl:
129. • https://mirrors.tuna.tsinghua.edu.cn/centos/$releasever/updates/$basearch/
130. • http://mirrors.aliyun.com/centos/$releasever/updates/$basearch/
131. • http://mirror.centos.org/centos/$releasever/updates/$basearch/
132. • name: extras
133. description: CentOS-$releasever - Extras
134. baseurl:
135. • https://mirrors.tuna.tsinghua.edu.cn/centos/$releasever/extras/$basearch/
136. • http://mirrors.aliyun.com/centos/$releasever/extras/$basearch/
137. • http://mirror.centos.org/centos/$releasever/extras/$basearch/
138. gpgcheck: no
139. • name: epel
140. description: CentOS $releasever - epel
141. gpgcheck: no
142. baseurl:
143. • https://mirrors.tuna.tsinghua.edu.cn/epel/$releasever/$basearch
144. • http://mirrors.aliyun.com/epel/$releasever/$basearch
145. • http://download.fedoraproject.org/pub/epel/$releasever/$basearch
146. • name: grafana
147. description: Grafana Official Yum Repo
148. enabled: yes
149. gpgcheck: no
150. baseurl:
151. • https://mirrors.tuna.tsinghua.edu.cn/grafana/yum/rpm
152. • https://packages.grafana.com/oss/rpm
153. • name: prometheus
154. description: Prometheus and exporters
155. gpgcheck: no
156. baseurl: https://packagecloud.io/prometheus-rpm/release/el/$releasever/$basearch
157. • name: pgdg-common
158. description: PostgreSQL common RPMs for RHEL/CentOS $releasever - $basearch
159. gpgcheck: no
160. baseurl:
161. • http://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/common/redhat/rhel-$releasever-$basearch
162. • https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-$releasever-$basearch
163. • name: pgdg14
164. description: PostgreSQL 14 for RHEL/CentOS $releasever - $basearch
165. gpgcheck: no
166. baseurl:
167. • https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/14/redhat/rhel-$releasever-$basearch
168. • https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-$releasever-$basearch
169. • name: timescaledb
170. description: TimescaleDB for RHEL/CentOS $releasever - $basearch
171. gpgcheck: no
172. baseurl:
173. • https://packagecloud.io/timescale/timescaledb/el/7/$basearch
174. • name: centos-sclo
175. description: CentOS-$releasever - SCLo
176. gpgcheck: no

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

178. baseurl: http://mirrors.aliyun.com/centos/$releasever/sclo/$basearch/sclo/
179. • name: centos-sclo-rh
180. description: CentOS-$releasever - SCLo rh
181. gpgcheck: no

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

183. baseurl: http://mirrors.aliyun.com/centos/$releasever/sclo/$basearch/rh/
184. • name: nginx
185. description: Nginx Official Yum Repo
186. skip_if_unavailable: true
187. gpgcheck: no
188. baseurl: http://nginx.org/packages/centos/$releasever/$basearch/
189. • name: harbottle # for latest consul & kubernetes
190. description: Copr repo for main owned by harbottle
191. skip_if_unavailable: true
192. gpgcheck: no
193. baseurl: https://download.copr.fedorainfracloud.org/results/harbottle/main/epel-$releasever-$basearch/
194. • name: pg_probackup # for pg_probackup
195. description: PG_PROBACKUP Centos packages for PostgresPro Standard and Enterprise - $basearch
196. skip_if_unavailable: true
197. gpgcheck: no
198. baseurl: https://repo.postgrespro.ru/pg_probackup-forks/rpm/latest/centos-$releasever-$basearch
199. • name: docker-ce # for latest docker
200. description: Docker CE Stable - $basearch
201. skip_if_unavailable: true
202. gpgcheck: no
203. baseurl:
204. • https://mirrors.aliyun.com/docker-ce/linux/centos/$releasever/$basearch/stable
205. • https://download.docker.com/linux/centos/$releasever/$basearch/stable

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

207. • epel-release nginx wget yum-utils yum createrepo sshpass zip unzip # ---- boot ---- #
208. • ntp chrony uuid lz4 bzip2 nc pv jq vim-enhanced make patch bash lsof wget git tuned perf ftp lrzsz rsync # ---- node ---- #
209. • numactl grubby sysstat dstat iotop bind-utils net-tools tcpdump socat ipvsadm telnet ca-certificates keepalived # ----- utils ----- #
210. • readline zlib openssl openssh-clients libyaml libxml2 libxslt libevent perl perl-devel perl-ExtUtils* # --- deps:pg --- #
211. • readline-devel zlib-devel uuid-devel libuuid-devel libxml2-devel libxslt-devel openssl-devel libicu-devel # --- deps:devel -- #
212. • grafana prometheus2 pushgateway alertmanager mtail consul consul_exporter consul-template etcd dnsmasq # ----- meta ----- #
213. • node_exporter nginx_exporter blackbox_exporter redis_exporter # ---- exporter --- #
214. • ansible python python-pip python-psycopg2 # - ansible & py3 - #
215. • python3 python3-psycopg2 python36-requests python3-etcd python3-consul python36-urllib3 python36-idna python36-pyOpenSSL python36-cryptography
216. • patroni patroni-consul patroni-etcd pgbouncer pg_cli pgbadger pg_activity tail_n_mail # -- pgsql common - #
217. • pgcenter boxinfo check_postgres emaj pgbconsole pg_bloat_check pgquarrel barman barman-cli pgloader pgFormatter pitrery pspg pgxnclient PyGreSQL
218. • postgresql14 postgis32_14 citus_14 pglogical_14 timescaledb-2-postgresql-14 pg_repack_14 wal2json_14 # -- pg14 packages -#
219. • 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
220. • pg_statement_rollback_14 system_stats_14 plproxy_14 plsh_14 pldebugger_14 plpgsql_check_14 pgmemcache_14 # plr_14
221. • 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
222. • hypopg_14 geoip_14 rum_14 hll_14 ip4r_14 prefix_14 pguri_14 tdigest_14 topn_14 periods_14
223. • bgw_replstatus_14 count_distinct_14 credcheck_14 ddlx_14 extra_window_functions_14 logerrors_14 mysqlcompat_14 orafce_14
224. • repmgr_14 pg_auth_mon_14 pg_auto_failover_14 pg_background_14 pg_bulkload_14 pg_catcheck_14 pg_comparator_14
225. • pg_cron_14 pg_fkpart_14 pg_jobmon_14 pg_partman_14 pg_permissions_14 pg_prioritize_14 pgagent_14
226. • pgaudit16_14 pgauditlogtofile_14 pgcryptokey_14 pgexportdoc_14 pgfincore_14 pgimportdoc_14 powa_14 pgmp_14 pgq_14
227. • pgquarrel-0.7.0-1 pgsql_tweaks_14 pgtap_14 pgtt_14 postgresql-unit_14 postgresql_anonymizer_14 postgresql_faker_14
228. • safeupdate_14 semver_14 set_user_14 sslutils_14 table_version_14 # pgrouting_14 osm2pgrouting_14
229. • clang coreutils diffutils rpm-build rpm-devel rpmlint rpmdevtools bison flex # gcc gcc-c++ # - build utils - #
230. • docker-ce docker-compose kubelet kubectl kubeadm kubernetes-cni helm # - cloud native- #

231. repo_url_packages: # extra packages from url

232. • https://github.com/Vonng/loki-rpm/releases/download/v2.5.0/loki-2.5.0.x86_64.rpm
233. • https://github.com/Vonng/loki-rpm/releases/download/v2.5.0/promtail-2.5.0.x86_64.rpm
234. • https://github.com/Vonng/pg_exporter/releases/download/v0.5.0/pg_exporter-0.5.0.x86_64.rpm
235. • https://github.com/cybertec-postgresql/vip-manager/releases/download/v1.0.2/vip-manager-1.0.2-1.x86_64.rpm
236. • https://github.com/Vonng/haproxy-rpm/releases/download/v2.5.7/haproxy-2.5.7-1.el7.x86_64.rpm
237. • https://github.com/Vonng/pigsty-pkg/releases/download/misc/redis-6.2.7-1.el7.remi.x86_64.rpm
238. • https://github.com/dalibo/pev2/releases/download/v0.24.0/pev2.tar.gz
239. • https://github.com/Vonng/pigsty-pkg/releases/download/misc/polysh-0.4-1.noarch.rpm

240. -----------------------------------------------------------------

241. NAMESERVER

242. -----------------------------------------------------------------

243. nameserver_enabled: false # setup dnsmasq
244. dns_records: # dynamic dns record resolved by dnsmasq
245. • 10.10.10.10 pg-meta # sandbox vip for pg-meta
246. • 10.10.10.10 pg-meta-1 # sandbox instance pg-meta-1

247. -----------------------------------------------------------------

248. PROMETHEUS

249. -----------------------------------------------------------------

250. prometheus_enabled: true # setup prometheus
251. prometheus_data_dir: /data/prometheus/data
252. prometheus_options: '--storage.tsdb.retention=15d'
253. prometheus_reload: false # reload prometheus instead of recreate it?
254. prometheus_sd_method: static # service discovery method: static|consul
255. prometheus_sd_interval: 5s # service discovery refresh interval
256. prometheus_scrape_interval: 10s # global scrape & evaluation interval

257. prometheus_scrape_timeout: 8s # scrape timeout

258. -----------------------------------------------------------------

259. EXPORTER

260. -----------------------------------------------------------------

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

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

264. -----------------------------------------------------------------

265. GRAFANA

266. -----------------------------------------------------------------

267. grafana_enabled: true # enable grafana? only works on meta nodes
268. grafana_endpoint: http://10.10.10.10:3000 # grafana endpoint url
269. grafana_admin_username: admin # default grafana admin username
270. grafana_admin_password: pigsty # default grafana admin password
271. grafana_database: sqlite3 # default grafana database type: sqlite3|postgres

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

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

274. grafana_plugin_cache: /www/pigsty/plugins.tgz # path to grafana plugins cache tarball
275. grafana_plugin_list: # plugins that will be downloaded via grafana-cli
276. • marcusolsson-csv-datasource
277. • marcusolsson-json-datasource
278. • marcusolsson-treemap-panel
279. grafana_plugin_git: # plugins that will be downloaded via git
280. • https://github.com/Vonng/vonng-echarts-panel

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

282. LOKI

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

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

289. loki_retention: 15d # log retention period

290. -----------------------------------------------------------------

291. DCS

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

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

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

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

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

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

301. -----------------------------------------------------------------

302. CONSUL

303. -----------------------------------------------------------------

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

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

306. -----------------------------------------------------------------

307. ETCD

308. -----------------------------------------------------------------

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

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

311. ================================================================

312. VARS: NODES

313. ================================================================

314. global variables for nodes (including meta)

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

316. NODE_IDENTITY

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

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

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

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

322. nodename_exchange: false # exchange nodename among play hosts?

323. -----------------------------------------------------------------

324. NODE_DNS

325. -----------------------------------------------------------------

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

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

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

331. node_dns_servers: # dynamic nameserver in /etc/resolv.conf
332. • 10.10.10.10
333. node_dns_options: # dns resolv options
334. • options single-request-reopen timeout:1 rotate
335. • domain service.consul

336. -----------------------------------------------------------------

337. NODE_REPO

338. -----------------------------------------------------------------

339. node_repo_method: local # none|local: ad local repo|public: add upstream directly
340. node_repo_remove: true # remove existing repo on nodes?
341. node_repo_local_urls: # list local repo url, if node_repo_method = local
342. • http://pigsty/pigsty.repo

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

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

351. node_packages_meta_pip: jupyterlab

352. -----------------------------------------------------------------

353. NODE_TUNE

354. -----------------------------------------------------------------

355. node_disable_firewall: true # disable firewall
356. node_disable_selinux: true # disable selinux
357. node_disable_numa: false # disable numa (node reboot required)
358. node_disable_swap: false # disable swap, use with caution
359. node_static_network: true # keep dns resolver settings after reboot
360. node_disk_prefetch: false # setup disk prefetch on HDD to increase performance
361. node_kernel_modules: [ softdog, br_netfilter, ip_vs, ip_vs_rr, ip_vs_rr, ip_vs_wrr, ip_vs_sh ]
362. node_tune: tiny # install and activate tuned profile: none|oltp|olap|crit|tiny

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

364. -----------------------------------------------------------------

365. NODE_ADMIN

366. -----------------------------------------------------------------

367. node_data_dir: /data # main data directory
368. node_admin_enabled: true # create a default admin user defined by node_admin_* ?
369. node_admin_uid: 88 # uid and gid for this admin user
370. node_admin_username: dba # name of this admin user, dba by default
371. node_admin_ssh_exchange: true # exchange admin ssh key among each pgsql cluster ?
372. node_admin_pk_current: true # add current user's ~/.ssh/id_rsa.pub to admin authorized_keys ?

373. node_admin_pk_list: [ ] # ssh public keys to be added to admin user (REPLACE WITH YOURS!)

374. -----------------------------------------------------------------

375. NODE_TIME

376. -----------------------------------------------------------------

377. node_timezone: Asia/Hong_Kong # default node timezone, empty will not change
378. node_ntp_enabled: false # enable ntp service? false will leave ntp service untouched
379. node_ntp_service: ntp # ntp service provider: ntp|chrony
380. node_ntp_servers: # default NTP servers
381. • pool cn.pool.ntp.org iburst
382. • pool pool.ntp.org iburst
383. • pool time.pool.aliyun.com iburst
384. • server 10.10.10.10 iburst
385. • server ntp.tuna.tsinghua.edu.cn iburst
386. node_crontab_overwrite: true # true will overwrite /etc/crontab, false will append crontab

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

388. -----------------------------------------------------------------

389. DOCKER

390. -----------------------------------------------------------------

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

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

395. -----------------------------------------------------------------

396. NODE_EXPORTER

397. -----------------------------------------------------------------

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

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

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

402. PROMTAIL

403. -----------------------------------------------------------------

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

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

409. ================================================================

410. VARS: PGSQL

411. ================================================================

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

413. PG_IDENTITY

414. -----------------------------------------------------------------

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

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

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

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

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

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

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

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

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

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

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

428. -----------------------------------------------------------------

429. PG_BUSINESS

430. -----------------------------------------------------------------

431. overwrite these variables on <CLUSTER> level

432. pg_users: [] # business users
433. pg_databases: [] # business databases
434. pg_services_extra: [] # extra services
435. pg_hba_rules_extra: [] # extra hba rules

436. pgbouncer_hba_rules_extra: [] # extra pgbouncer hba rules

437. WARNING: change these in production environment!

438. pg_admin_username: dbuser_dba
439. pg_admin_password: DBUser.DBA
440. pg_monitor_username: dbuser_monitor
441. pg_monitor_password: DBUser.Monitor
442. pg_replication_username: replicator

443. pg_replication_password: DBUser.Replicator

444. -----------------------------------------------------------------

445. PG_INSTALL

446. -----------------------------------------------------------------

447. pg_dbsu: postgres # os user for database, postgres by default (unwise to change it)
448. pg_dbsu_uid: 26 # os dbsu uid and gid, 26 for default postgres users and groups
449. pg_dbsu_sudo: limit # dbsu sudo privilege: none|limit|all|nopass, limit by default
450. pg_dbsu_home: /var/lib/pgsql # postgresql home directory
451. pg_dbsu_ssh_exchange: false # exchange postgres dbsu ssh key among same cluster ?
452. pg_version: 14 # default postgresql version to be installed
453. pgdg_repo: false # add pgdg official repo before install (in case of no local repo available)
454. pg_add_repo: false # add postgres relate repo before install ?
455. pg_bin_dir: /usr/pgsql/bin # postgres binary dir, default is /usr/pgsql/bin, which use /usr/pgsql -> /usr/pgsql-{ver}
456. pg_packages: # postgresql related packages. ${pg_version} will be replaced bypg_version`
457. • postgresql${pg_version}* # postgresql kernel packages
458. • postgis32_${pg_version}* # postgis
459. • citus_${pg_version}* # citus
460. • timescaledb-2-postgresql-${pg_version} # timescaledb
461. • pgbouncer pg_exporter pgbadger pg_activity node_exporter consul haproxy vip-manager
462. • patroni patroni-consul patroni-etcd python3 python3-psycopg2 python36-requests python3-etcd
463. • python3-consul python36-urllib3 python36-idna python36-pyOpenSSL python36-cryptography
464. pg_extensions: # postgresql extensions, ${pg_version} will be replaced by actualpg_version`
465. • pg_repack_${pg_version} pg_qualstats_${pg_version} pg_stat_kcache_${pg_version} pg_stat_monitor_${pg_version} wal2json_${pg_version}

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

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

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

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

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

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

472. -----------------------------------------------------------------

473. PG_BOOTSTRAP

474. -----------------------------------------------------------------

475. pg_safeguard: false # true will disable pg_clean at all, even for pgsql-remove.yml
476. pg_clean: false # true will clean running postgres during pgsql init (DANGEROUS)
477. pg_data: /pg/data # postgres data directory (soft link)
478. pg_fs_main: /data # primary data disk mount point /pg -> {{ pg_fs_main }}/postgres/{{ pg_instance }}
479. pg_fs_bkup: /data/backups # backup disk mount point /pg/ -> {{ pg_fs_bkup }}/postgres/{{ pg_instance }}/
480. pg_dummy_filesize: 64MiB # /pg/dummy hold some disk space for emergency use
481. pg_listen: '0.0.0.0' # postgres listen address, '0.0.0.0' (all ipv4 addr) by default
482. pg_port: 5432 # postgres port, 5432 by default
483. pg_localhost: /var/run/postgresql # localhost unix socket dir for connection
484. patroni_enabled: true # if not enabled, no postgres cluster will be created
485. patroni_mode: default # pause|default|remove
486. pg_dcs_type: consul # which dcs to use: consul or etcd or raft
487. pg_namespace: /pg # top level key namespace in dcs
488. patroni_port: 8008 # default patroni port
489. patroni_watchdog_mode: automatic # watchdog mode: off|automatic|required
490. pg_conf: tiny.yml # pgsql template: {oltp|olap|crit|tiny}.yml
491. pg_libs: 'timescaledb, pg_stat_statements, auto_explain' # extensions to be loaded
492. pg_delay: 0 # apply delay for standby cluster leader
493. pg_checksum: false # enable data checksum by default
494. pg_encoding: UTF8 # database cluster encoding, UTF8 by default
495. pg_locale: C # database cluster local, C by default
496. pg_lc_collate: C # database cluster collate, C by default
497. pg_lc_ctype: en_US.UTF8 # database character type, en_US.UTF8 by default (for i18n full-text search)
498. pgbouncer_enabled: true # if not enabled, pgbouncer will not be created
499. pgbouncer_port: 6432 # pgbouncer port, 6432 by default
500. pgbouncer_poolmode: transaction # pooling mode: session|transaction|statement, transaction pooling by default

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

502. -----------------------------------------------------------------

503. PG_PROVISION

504. -----------------------------------------------------------------

505. pg_provision: true # whether provisioning postgres cluster

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

507. pg_default_roles:

508. • { name: dbrole_readonly , login: false , comment: role for global read-only access } # production read-only role
509. • { name: dbrole_readwrite , login: false , roles: [dbrole_readonly], comment: role for global read-write access } # production read-write role
510. • { name: dbrole_offline , login: false , comment: role for restricted read-only access (offline instance) } # restricted-read-only role
511. • { name: dbrole_admin , login: false , roles: [pg_monitor, dbrole_readwrite] , comment: role for object creation } # production DDL change role
512. • { name: postgres , superuser: true , comment: system superuser } # system dbsu, name is designated by pg_dbsu
513. • { name: dbuser_dba , superuser: true , roles: [dbrole_admin] , comment: system admin user } # admin dbsu, name is designated by pg_admin_username
514. • { name: replicator , replication: true , bypassrls: true , roles: [pg_monitor, dbrole_readonly] , comment: system replicator } # replicator
515. • { name: dbuser_monitor , roles: [pg_monitor, dbrole_readonly] , comment: system monitor user , parameters: {log_min_duration_statement: 1000 } } # monitor user
516. • { name: dbuser_stats , password: DBUser.Stats , roles: [dbrole_offline] , comment: business offline user for offline queries and ETL } # ETL user
517. pg_default_privileges: # - privileges - #
518. • GRANT USAGE ON SCHEMAS TO dbrole_readonly
519. • GRANT SELECT ON TABLES TO dbrole_readonly
520. • GRANT SELECT ON SEQUENCES TO dbrole_readonly
521. • GRANT EXECUTE ON FUNCTIONS TO dbrole_readonly
522. • GRANT USAGE ON SCHEMAS TO dbrole_offline
523. • GRANT SELECT ON TABLES TO dbrole_offline
524. • GRANT SELECT ON SEQUENCES TO dbrole_offline
525. • GRANT EXECUTE ON FUNCTIONS TO dbrole_offline
526. • GRANT INSERT, UPDATE, DELETE ON TABLES TO dbrole_readwrite
527. • GRANT USAGE, UPDATE ON SEQUENCES TO dbrole_readwrite
528. • GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES TO dbrole_admin
529. • GRANT CREATE ON SCHEMAS TO dbrole_admin
530. pg_default_schemas: [ monitor ] # default schemas to be created
531. pg_default_extensions: # default extensions to be created
532. • { name: 'pg_stat_statements', schema: 'monitor' }
533. • { name: 'pgstattuple', schema: 'monitor' }
534. • { name: 'pg_qualstats', schema: 'monitor' }
535. • { name: 'pg_buffercache', schema: 'monitor' }
536. • { name: 'pageinspect', schema: 'monitor' }
537. • { name: 'pg_prewarm', schema: 'monitor' }
538. • { name: 'pg_visibility', schema: 'monitor' }
539. • { name: 'pg_freespacemap', schema: 'monitor' }
540. • { name: 'pg_repack', schema: 'monitor' }
541. • name: postgres_fdw
542. • name: file_fdw
543. • name: btree_gist
544. • name: btree_gin
545. • name: pg_trgm
546. • name: intagg
547. • name: intarray

548. pg_reload: true # reload postgres after hba changes

549. pg_hba_rules: # postgres host-based authentication rules
550. • title: allow meta node password access
551. role: common
552. rules:
553. • host all all 10.10.10.10/32 md5
554. • title: allow intranet admin password access
555. role: common
556. rules:
557. • host all +dbrole_admin 10.0.0.0/8 md5
558. • host all +dbrole_admin 172.16.0.0/12 md5
559. • host all +dbrole_admin 192.168.0.0/16 md5
560. • title: allow intranet password access
561. role: common
562. rules:
563. • host all all 10.0.0.0/8 md5
564. • host all all 172.16.0.0/12 md5
565. • host all all 192.168.0.0/16 md5
566. • title: allow local read/write (local production user via pgbouncer)
567. role: common
568. rules:
569. • local all +dbrole_readonly md5
570. • host all +dbrole_readonly 127.0.0.1/32 md5
571. • title: allow offline query (ETL,SAGA,Interactive) on offline instance
572. role: offline
573. rules:
574. • host all +dbrole_offline 10.0.0.0/8 md5
575. • host all +dbrole_offline 172.16.0.0/12 md5
576. • host all +dbrole_offline 192.168.0.0/16 md5

577. pgbouncer_hba_rules: # pgbouncer host-based authentication rules

578. • title: local password access
579. role: common
580. rules:
581. • local all all md5
582. • host all all 127.0.0.1/32 md5
583. • title: intranet password access
584. role: common
585. rules:
586. • host all all 10.0.0.0/8 md5
587. • host all all 172.16.0.0/12 md5
588. • host all all 192.168.0.0/16 md5

589. -----------------------------------------------------------------

590. PG_EXPORTER

591. -----------------------------------------------------------------

592. pg_exporter_enabled: true # setup pg_exporter on instance
593. pg_exporter_config: pg_exporter.yml # use fast cache exporter for demo
594. pg_exporter_port: 9630 # pg_exporter listen port
595. pg_exporter_params: 'sslmode=disable' # url query parameters for pg_exporter
596. pg_exporter_url: '' # optional, overwrite auto-generate postgres connstr
597. pg_exporter_auto_discovery: true # optional, discovery available database on target instance ?
598. pg_exporter_exclude_database: 'template0,template1,postgres' # optional, comma separated list of database that WILL NOT be monitored when auto-discovery enabled
599. pg_exporter_include_database: '' # optional, comma separated list of database that WILL BE monitored when auto-discovery enabled, empty string will disable include mode
600. pg_exporter_options: '--log.level=info --log.format="logger:syslog?appname=pg_exporter&local=7"'
601. pgbouncer_exporter_enabled: true # setup pgbouncer_exporter on instance (if you don't have pgbouncer, disable it)
602. pgbouncer_exporter_port: 9631 # pgbouncer_exporter listen port
603. pgbouncer_exporter_url: '' # optional, overwrite auto-generate pgbouncer connstr

604. pgbouncer_exporter_options: '--log.level=info --log.format="logger:syslog?appname=pgbouncer_exporter&local=7"'

605. -----------------------------------------------------------------

606. PG_SERVICE

607. -----------------------------------------------------------------

608. pg_services: # how to expose postgres service in cluster?
609. • name: primary # service name {{ pg_cluster }}-primary
610. src_ip: "*"
611. src_port: 5433
612. dst_port: pgbouncer # 5433 route to pgbouncer
613. check_url: /primary # primary health check, success when instance is primary

614. selector: "[]" # select all instance as primary service candidate

615. • name: replica # service name {{ pg_cluster }}-replica
616. src_ip: "*"
617. src_port: 5434
618. dst_port: pgbouncer
619. check_url: /read-only # read-only health check. (including primary)
620. selector: "[]" # select all instance as replica service candidate

621. selector_backup: "[? pg_role == primary || pg_role == offline ]"

622. • name: default # service's actual name is {{ pg_cluster }}-default
623. src_ip: "*" # service bind ip address, * for all, vip for cluster virtual ip address
624. src_port: 5436 # bind port, mandatory
625. dst_port: postgres # target port: postgres|pgbouncer|port_number , pgbouncer(6432) by default
626. check_method: http # health check method: only http is available for now
627. check_port: patroni # health check port: patroni|pg_exporter|port_number , patroni by default
628. check_url: /primary # health check url path, / as default
629. check_code: 200 # health check http code, 200 as default
630. selector: "[]" # instance selector
631. haproxy: # haproxy specific fields
632. maxconn: 3000 # default front-end connection
633. balance: roundrobin # load balance algorithm (roundrobin by default)
634. default_server_options: 'inter 3s fastinter 1s downinter 5s rise 3 fall

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