Demo：4节点沙箱模板

4节点本地沙箱环境默认使用的模板

``` 1. --- 2. ###################################################################### 3. # File : pigsty.yml 4. # Desc : Pigsty 4-node local sandbox demo config 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. Sandbox (4-node)

3. ====================================================================

4. admin user : vagrant (nopass ssh & sudo already set)

5. 1. meta : 10.10.10.10 (2 Core | 4GB) pg-meta

6. 2. node-1 : 10.10.10.11 (1 Core | 1GB) pg-test-1

7. 3. node-2 : 10.10.10.12 (1 Core | 1GB) pg-test-2

8. 4. node-3 : 10.10.10.13 (1 Core | 1GB) pg-test-3

9. (replace these ip if your 4-node env have different ip addr)

10. VIP 2: (l2 vip is available inside same LAN )

11. pg-meta ---> 10.10.10.2 ---> 10.10.10.10

12. pg-test ---> 10.10.10.3 ---> 10.10.10.1{1,2,3}

14. all:

16. CLUSTERS

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

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

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

21. hosts: cluster members ip and instance level variables

22. vars : cluster level variables

24. children: # groups definition

25. ================================================================

26. Meta Nodes: Admin Controller

27. ================================================================

28. meta: # meta nodes are defined in this special group "meta"
29. vars:
30. meta_node: true # mark this group as meta nodes

31. ansible_group_priority: 99 # overwrite with the highest priority

32. nginx_enabled: true # setup repo & underlying nginx

33. nameserver_enabled: false # setup dnsmasq
34. prometheus_enabled: true # setup prometheus
35. grafana_enabled: true # setup grafana
36. loki_enabled: true # setup loki
37. docker_enabled: true # setup docker

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

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

40. 10.10.10.10: { }

41. 10.10.10.11: { nginx_enabled: false }

42. 10.10.10.12: { nginx_enabled: false }

43. ================================================================

44. ================================================================

45. PGSQL Clusters

46. ================================================================

47. ----------------------------------

48. pgsql cluster: pg-meta (CMDB)

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

50. pg-meta:
51. hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary , pg_offline_query: true } }
52. vars:
53. pg_cluster: pg-meta
54. patroni_watchdog_mode: off
55. pg_databases:
56. • { name: meta, baseline: cmdb.sql , schemas: [ pigsty ] , extensions: [ { name: adminpack, schema: pg_catalog } , { name: postgis, schema: public } , { name: timescaledb } ] ,comment: pigsty meta database }
57. • { name: grafana, owner: dbuser_grafana , revokeconn: true , comment: grafana primary database }
58. • { name: bytebase, owner: dbuser_bytebase , revokeconn: true , comment: bytebase primary database }
59. • { name: kong, owner: dbuser_kong , revokeconn: true , comment: kong the api gateway database }
60. • { name: gitea, owner: dbuser_gitea , revokeconn: true , comment: gitea meta database }

61. pg_users:

62. • {name: dbuser_meta ,password: DBUser.Meta ,pgbouncer: true ,roles: [dbrole_admin] ,comment: pigsty cmdb admin user }
63. • {name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
64. • {name: dbuser_grafana ,password: DBUser.Grafana ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for grafana database }
65. • {name: dbuser_bytebase ,password: DBUser.Bytebase ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for bytebase database }
66. • {name: dbuser_kong ,password: DBUser.Kong ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for kong api gateway }
67. • {name: dbuser_gitea ,password: DBUser.Gitea ,pgbouncer: true ,roles: [dbrole_admin] ,comment: admin user for gitea service }

68. pg_hba_rules_extra:

69. • title: allow grafana bytebase kong intranet access
70. role: common
71. rules:
72. • host kong dbuser_kong 10.0.0.0/8 md5
73. • host bytebase dbuser_bytebase 10.0.0.0/8 md5
74. • host grafana dbuser_grafana 10.0.0.0/8 md5

75. vip_mode: l2

76. vip_address: 10.10.10.2
77. vip_cidrmask: 8
78. vip_interface: eth1
79. node_crontab:
80. • '00 01 * * * postgres /pg/bin/pg-backup 2>>/pg/log/backup.log'
81. • '* * * * * postgres /usr/pgsql/bin/pgbench meta -nv -T10 -c2 2>>/pg/log/bench.log' # random load

82. ----------------------------------

83. pgsql cluster: pg-test (3 nodes)

84. ----------------------------------

85. pg-test ---> 10.10.10.3 ---> 10.10.10.1{1,2,3}

86. pg-test: # define the new 3-node cluster pg-test
87. hosts:
88. 10.10.10.11: { pg_seq: 1, pg_role: primary } # primary instance, leader of cluster
89. 10.10.10.12: { pg_seq: 2, pg_role: replica } # replica instance, follower of leader
90. 10.10.10.13: { pg_seq: 3, pg_role: replica, pg_offline_query: true } # replica with offline access
91. vars:
92. pg_cluster: pg-test # define pgsql cluster name
93. pg_version: 14 # test postgresql 13 with pg-test cluster
94. pg_users:
95. • name: test
96. password: test
97. pgbouncer: true
98. roles: [ dbrole_admin ]
99. pg_databases: # create a database and user named 'test'
100. • name: test

101. vip_mode: l2 # enable/disable vip (require members in same LAN)

102. vip_address: 10.10.10.3 # virtual ip address for this cluster
103. vip_cidrmask: 8 # cidr network mask length

104. vip_interface: eth1 # interface to add virtual ip

105. pg_services_extra: # extra services in addition to pg_services_default, array of service definition

106. standby service will route {ip|name}:5435 to sync replica's pgbouncer (5435->6432 standby)

107. • name: standby # required, service name, the actual svc name will be prefixed with pg_cluster, e.g: pg-meta-standby
108. src_ip: "*" # required, service bind ip address, * for all ip, vip for cluster vip_address
109. src_port: 5435 # required, service exposed port (work as kubernetes service node port mode)
110. dst_port: pgbouncer # optional, destination port, postgres|pgbouncer|<port_number> , pgbouncer(6432) by default
111. check_method: http # optional, health check method: http is the only available method for now
112. check_port: patroni # optional, health check port: patroni|pg_exporter|<port_number> , patroni(8008) by default
113. check_url: /sync # optional, health check url path, /read-only?lag=0 by default
114. check_code: 200 # optional, health check expected http code, 200 by default
115. selector: "[]" # required, JMESPath to filter inventory ()
116. selector_backup: "[? pg_role == primary]" # primary used as backup server for standby service (will not work because /sync for )
117. haproxy: # optional, adhoc parameters for haproxy service provider (vip_l4 is another service provider)
118. maxconn: 3000 # optional, max allowed front-end connection
119. balance: roundrobin # optional, haproxy load balance algorithm (roundrobin by default, other: leastconn)

120. default_server_options: 'inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100'

121. ================================================================

122. REDIS Clusters

123. ================================================================

124. ----------------------------------

125. redis sentinel example

126. ----------------------------------

127. redis-meta:
128. hosts:
129. 10.10.10.10:
130. redis_node: 1
131. redis_instances: { 6001 : {} ,6002 : {} , 6003 : {} }
132. vars:
133. redis_cluster: redis-meta
134. redis_mode: sentinel

135. redis_max_memory: 64MB

136. ----------------------------------

137. redis cluster example

138. ----------------------------------

139. redis-test:
140. hosts:
141. 10.10.10.11:
142. redis_node: 1
143. redis_instances: { 6501 : {} ,6502 : {} ,6503 : {} ,6504 : {} ,6505 : {} ,6506 : {} }
144. 10.10.10.12:
145. redis_node: 2
146. redis_instances: { 6501 : {} ,6502 : {} ,6503 : {} ,6504 : {} ,6505 : {} ,6506 : {} }
147. vars:
148. redis_cluster: redis-test # name of this redis 'cluster'
149. redis_mode: cluster # standalone,cluster,sentinel
150. redis_max_memory: 32MB # max memory used by each redis instance

151. redis_mem_policy: allkeys-lru # memory eviction policy

152. ----------------------------------

153. redis standalone example

154. ----------------------------------

155. redis-common:
156. hosts:
157. 10.10.10.13:
158. redis_node: 1
159. redis_instances:
160. 6501: {}
161. 6502: { replica_of: '10.10.10.13 6501' }
162. 6503: { replica_of: '10.10.10.13 6501' }
163. vars:
164. redis_cluster: redis-common # name of this redis 'cluster'
165. redis_mode: standalone # standalone,cluster,sentinel

166. redis_max_memory: 64MB # max memory used by each redis instance

168. VARS

170. vars: # global variables

171. version: v1.5.1 # pigsty version string

172. ================================================================

173. VARS: INFRA

174. ================================================================

175. -----------------------------------------------------------------

176. CONNECT

177. -----------------------------------------------------------------

178. INSTANCE level ansible connect parameters

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

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

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

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

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

184. proxy_env: # global proxy env when downloading packages

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

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

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

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

189. -----------------------------------------------------------------

190. CA

191. -----------------------------------------------------------------

192. ca_method: create # create|copy|recreate
193. ca_subject: "/CN=root-ca" # self-signed CA subject
194. ca_homedir: /ca # ca cert directory
195. ca_cert: ca.crt # ca public key/cert

196. ca_key: ca.key # ca private key

197. -----------------------------------------------------------------

198. NGINX

199. -----------------------------------------------------------------

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

202. nginx_home: /www # default repo home dir

203. nginx_upstream: # domain names and upstream servers

204. • { name: home , domain: pigsty , endpoint: "10.10.10.10:80" }
205. • { name: grafana , domain: g.pigsty , endpoint: "10.10.10.10:3000" }
206. • { name: loki , domain: l.pigsty , endpoint: "10.10.10.10:3100" }
207. • { name: prometheus , domain: p.pigsty , endpoint: "10.10.10.10:9090" }
208. • { name: alertmanager , domain: a.pigsty , endpoint: "10.10.10.10:9093" }
209. • { name: consul , domain: c.pigsty , endpoint: "127.0.0.1:8500" } #== ^ required ==#
210. • { name: postgrest , domain: api.pigsty.cc , endpoint: "127.0.0.1:8884" } #== v optional ==#
211. • { name: pgadmin , domain: adm.pigsty.cc , endpoint: "127.0.0.1:8885" }
212. • { name: pgweb , domain: cli.pigsty.cc , endpoint: "127.0.0.1:8886" }
213. • { name: bytebase , domain: ddl.pigsty.cc , endpoint: "127.0.0.1:8887" }
214. • { name: jupyter , domain: lab.pigsty.cc , endpoint: "127.0.0.1:8888" }
215. • { name: gitea , domain: git.pigsty.cc , endpoint: "127.0.0.1:8889" }
216. • { name: minio , domain: sss.pigsty.cc , endpoint: "127.0.0.1:9000" }

217. nginx_indexes: # application nav links on home page

218. • { name: PgAdmin4 , url : 'http://adm.pigsty' , comment: 'PgAdmin4 for PostgreSQL' }
219. • { name: PGWeb , url : 'http://cli.pigsty' , comment: 'PGWEB Browser Client' }
220. • { name: ByteBase , url : 'http://ddl.pigsty' , comment: 'ByteBase Schema Migrator' }
221. • { name: PostgREST , url : 'http://api.pigsty' , comment: 'Kong API Gateway' }
222. • { name: Gitea , url : 'http://git.pigsty' , comment: 'Minio Object Storage' }
223. • { name: Minio , url : 'http://sss.pigsty' , comment: 'Minio Object Storage' }
224. • { name: Explain , url : '/pev2' , comment: 'postgres explain visualizer' }
225. • { name: Package , url : '/pigsty' , comment: 'local yum repo packages' }
226. • { name: Matrix Pkg , url : '/matrix' , comment: 'matrixdb repo packages' }
227. • { name: PG Logs , url : '/logs' , comment: 'postgres raw csv logs' }
228. • { name: Schemas , url : '/schema' , comment: 'schemaspy summary report' }
229. • { name: Reports , url : '/report' , comment: 'pgbadger summary report' }
230. • { name: ISD , url : '${grafana}/d/isd-overview' , comment: 'noaa isd data visualization' }
231. • { name: Covid , url : '${grafana}/d/covid-overview' , comment: 'covid data visualization' }
232. • { name: Worktime , url : '${grafana}/d/worktime-overview' , comment: 'worktime query' }
233. • { name: DBTrend , url : '${grafana}/d/dbeng-trending' , comment: 'DB Engine Trending Graph' }

234. -----------------------------------------------------------------

235. REPO

236. -----------------------------------------------------------------

237. repo_name: pigsty # repo name, pigsty by default
238. repo_address: pigsty # external address to this repo (ip:port or url)
239. repo_rebuild: false # force re-download packages
240. repo_remove: true # remove existing upstream repo
241. repo_upstreams: # where to download packages?
242. • name: base
243. description: CentOS-$releasever - Base
244. gpgcheck: no
245. baseurl:
246. • https://mirrors.tuna.tsinghua.edu.cn/centos/$releasever/os/$basearch/
247. • http://mirrors.aliyun.com/centos/$releasever/os/$basearch/
248. • http://mirror.centos.org/centos/$releasever/os/$basearch/
249. • name: updates
250. description: CentOS-$releasever - Updates
251. gpgcheck: no
252. baseurl:
253. • https://mirrors.tuna.tsinghua.edu.cn/centos/$releasever/updates/$basearch/
254. • http://mirrors.aliyun.com/centos/$releasever/updates/$basearch/
255. • http://mirror.centos.org/centos/$releasever/updates/$basearch/
256. • name: extras
257. description: CentOS-$releasever - Extras
258. baseurl:
259. • https://mirrors.tuna.tsinghua.edu.cn/centos/$releasever/extras/$basearch/
260. • http://mirrors.aliyun.com/centos/$releasever/extras/$basearch/
261. • http://mirror.centos.org/centos/$releasever/extras/$basearch/
262. gpgcheck: no
263. • name: epel
264. description: CentOS $releasever - epel
265. gpgcheck: no
266. baseurl:
267. • https://mirrors.tuna.tsinghua.edu.cn/epel/$releasever/$basearch
268. • http://mirrors.aliyun.com/epel/$releasever/$basearch
269. • http://download.fedoraproject.org/pub/epel/$releasever/$basearch
270. • name: grafana
271. description: Grafana
272. enabled: yes
273. gpgcheck: no
274. baseurl:
275. • https://mirrors.tuna.tsinghua.edu.cn/grafana/yum/rpm
276. • https://packages.grafana.com/oss/rpm
277. • name: prometheus
278. description: Prometheus and exporters
279. gpgcheck: no
280. baseurl: https://packagecloud.io/prometheus-rpm/release/el/$releasever/$basearch
281. • name: pgdg-common
282. description: PostgreSQL common RPMs for RHEL/CentOS $releasever - $basearch
283. gpgcheck: no
284. baseurl:
285. • http://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/common/redhat/rhel-$releasever-$basearch
286. • https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-$releasever-$basearch
287. • name: pgdg14
288. description: PostgreSQL 14 for RHEL/CentOS $releasever - $basearch
289. gpgcheck: no
290. baseurl:
291. • https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/14/redhat/rhel-$releasever-$basearch
292. • https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-$releasever-$basearch
293. • name: timescaledb
294. description: TimescaleDB for RHEL/CentOS $releasever - $basearch
295. gpgcheck: no
296. baseurl:
297. • https://packagecloud.io/timescale/timescaledb/el/7/$basearch
298. • name: centos-sclo
299. description: CentOS-$releasever - SCLo
300. gpgcheck: no

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

302. baseurl: http://mirrors.aliyun.com/centos/$releasever/sclo/$basearch/sclo/
303. • name: centos-sclo-rh
304. description: CentOS-$releasever - SCLo rh
305. gpgcheck: no

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

307. baseurl: http://mirrors.aliyun.com/centos/$releasever/sclo/$basearch/rh/
308. • name: nginx
309. description: Nginx Official Yum Repo
310. skip_if_unavailable: true
311. gpgcheck: no
312. baseurl: http://nginx.org/packages/centos/$releasever/$basearch/
313. • name: harbottle # for latest consul & kubernetes
314. description: Copr repo for main owned by harbottle
315. skip_if_unavailable: true
316. gpgcheck: no
317. baseurl: https://download.copr.fedorainfracloud.org/results/harbottle/main/epel-$releasever-$basearch/
318. • name: pg_probackup # for pg_probackup
319. description: PG_PROBACKUP Centos packages for PostgresPro Standard and Enterprise - $basearch
320. skip_if_unavailable: true
321. gpgcheck: no
322. baseurl: https://repo.postgrespro.ru/pg_probackup-forks/rpm/latest/centos-$releasever-$basearch
323. • name: docker-ce # for latest docker
324. description: Docker CE Stable - $basearch
325. skip_if_unavailable: true
326. gpgcheck: no
327. baseurl:
328. • https://mirrors.aliyun.com/docker-ce/linux/centos/$releasever/$basearch/stable
329. • https://download.docker.com/linux/centos/$releasever/$basearch/stable

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

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

355. repo_url_packages: # extra packages from url

356. • https://github.com/Vonng/loki-rpm/releases/download/v2.5.0/loki-2.5.0.x86_64.rpm
357. • https://github.com/Vonng/loki-rpm/releases/download/v2.5.0/promtail-2.5.0.x86_64.rpm
358. • https://github.com/Vonng/pg_exporter/releases/download/v0.5.0/pg_exporter-0.5.0.x86_64.rpm
359. • https://github.com/cybertec-postgresql/vip-manager/releases/download/v1.0.2/vip-manager-1.0.2-1.x86_64.rpm
360. • https://github.com/Vonng/haproxy-rpm/releases/download/v2.5.7/haproxy-2.5.7-1.el7.x86_64.rpm
361. • https://github.com/Vonng/pigsty-pkg/releases/download/misc/redis-6.2.7-1.el7.remi.x86_64.rpm
362. • https://github.com/dalibo/pev2/releases/download/v0.24.0/pev2.tar.gz
363. • https://github.com/Vonng/pigsty-pkg/releases/download/misc/polysh-0.4-1.noarch.rpm

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

365. NAMESERVER

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

367. nameserver_enabled: false # setup dnsmasq
368. dns_records: # dynamic dns record resolved by dnsmasq
369. • 10.10.10.2 pg-meta # sandbox vip for pg-meta
370. • 10.10.10.3 pg-test # sandbox vip for pg-test
371. • 10.10.10.10 pg-meta-1 # sandbox instance pg-meta-1
372. • 10.10.10.11 pg-test-1 # sandbox instance node-1
373. • 10.10.10.12 pg-test-2 # sandbox instance node-2
374. • 10.10.10.13 pg-test-3 # sandbox instance node-3

375. -----------------------------------------------------------------

376. PROMETHEUS

377. -----------------------------------------------------------------

378. prometheus_enabled: true # setup prometheus
379. prometheus_data_dir: /data/prometheus/data
380. prometheus_options: '--storage.tsdb.retention=15d'
381. prometheus_reload: false # reload prometheus instead of recreate it?
382. prometheus_sd_method: static # service discovery method: static|consul
383. prometheus_sd_interval: 5s # service discovery refresh interval
384. prometheus_scrape_interval: 10s # global scrape & evaluation interval

385. prometheus_scrape_timeout: 8s # scrape timeout

386. -----------------------------------------------------------------

387. EXPORTER

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

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

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

392. -----------------------------------------------------------------

393. GRAFANA

394. -----------------------------------------------------------------

395. grafana_enabled: true # enable grafana? only works on meta nodes
396. grafana_endpoint: http://10.10.10.10:3000 # grafana endpoint url
397. grafana_admin_username: admin # default grafana admin username
398. grafana_admin_password: pigsty # default grafana admin password
399. grafana_database: sqlite3 # default grafana database type: sqlite3|postgres

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

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

402. grafana_plugin_cache: /www/pigsty/plugins.tgz # path to grafana plugins cache tarball
403. grafana_plugin_list: # plugins that will be downloaded via grafana-cli
404. • marcusolsson-csv-datasource
405. • marcusolsson-json-datasource
406. • marcusolsson-treemap-panel
407. grafana_plugin_git: # plugins that will be downloaded via git
408. • https://github.com/Vonng/vonng-echarts-panel

409. -----------------------------------------------------------------

410. LOKI

411. -----------------------------------------------------------------

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

417. loki_retention: 15d # log retention period

418. -----------------------------------------------------------------

419. DCS

420. -----------------------------------------------------------------

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

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

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

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

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

429. -----------------------------------------------------------------

430. CONSUL

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

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

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

434. -----------------------------------------------------------------

435. ETCD

436. -----------------------------------------------------------------

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

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

439. ================================================================

440. VARS: NODES

441. ================================================================

442. global variables for nodes (including meta)

443. -----------------------------------------------------------------

444. NODE_IDENTITY

445. -----------------------------------------------------------------

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

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

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

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

451. -----------------------------------------------------------------

452. NODE_DNS

453. -----------------------------------------------------------------

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

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

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

459. node_dns_servers: # dynamic nameserver in /etc/resolv.conf
460. • 10.10.10.10
461. node_dns_options: # dns resolv options
462. • options single-request-reopen timeout:1 rotate
463. • domain service.consul

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

465. NODE_REPO

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

467. node_repo_method: local # none|local: ad local repo|public: add upstream directly
468. node_repo_remove: true # remove existing repo on nodes?
469. node_repo_local_urls: # list local repo url, if node_repo_method = local
470. • http://pigsty/pigsty.repo

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

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

479. node_packages_meta_pip: jupyterlab

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

481. NODE_TUNE

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

483. node_disable_firewall: true # disable firewall
484. node_disable_selinux: true # disable selinux
485. node_disable_numa: false # disable numa (node reboot required)
486. node_disable_swap: false # disable swap, use with caution
487. node_static_network: true # keep dns resolver settings after reboot
488. node_disk_prefetch: false # setup disk prefetch on HDD to increase performance
489. node_kernel_modules: [ softdog, br_netfilter, ip_vs, ip_vs_rr, ip_vs_rr, ip_vs_wrr, ip_vs_sh ]
490. node_tune: tiny # install and activate tuned profile: none|oltp|olap|crit|tiny

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

492. -----------------------------------------------------------------

493. NODE_ADMIN

494. -----------------------------------------------------------------

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

503. -----------------------------------------------------------------

504. NODE_TIME

505. -----------------------------------------------------------------

506. node_timezone: Asia/Hong_Kong # default node timezone, empty will not change
507. node_ntp_enabled: true # enable ntp service? false will leave ntp service untouched
508. node_ntp_service: ntp # ntp service provider: ntp|chrony
509. node_ntp_servers: # default NTP servers
510. • pool cn.pool.ntp.org iburst
511. • pool pool.ntp.org iburst
512. • pool time.pool.aliyun.com iburst
513. • server 10.10.10.10 iburst
514. • server ntp.tuna.tsinghua.edu.cn iburst
515. node_crontab_overwrite: true # true will overwrite /etc/crontab, false will append crontab

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

517. -----------------------------------------------------------------

518. DOCKER

519. -----------------------------------------------------------------

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

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

524. -----------------------------------------------------------------

525. NODE_EXPORTER

526. -----------------------------------------------------------------

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

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

530. -----------------------------------------------------------------

531. PROMTAIL

532. -----------------------------------------------------------------

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

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

538. ================================================================

539. VARS: PGSQL

540. ================================================================

541. -----------------------------------------------------------------

542. PG_IDENTITY

543. -----------------------------------------------------------------

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

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

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

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

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

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

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

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

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

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

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

557. -----------------------------------------------------------------

558. PG_BUSINESS

559. -----------------------------------------------------------------

560. overwrite these variables on <CLUSTER> level

561. pg_users: [] # business users
562. pg_databases: [] # business databases
563. pg_services_extra: [] # extra services
564. pg_hba_rules_extra: [] # extra hba rules

565. pgbouncer_hba_rules_extra: [] # extra pgbouncer hba rules

566. WARNING: change these in production environment!

567. pg_admin_username: dbuser_dba
568. pg_admin_password: DBUser.DBA
569. pg_monitor_username: dbuser_monitor
570. pg_monitor_password: DBUser.Monitor
571. pg_replication_username: replicator

572. pg_replication_password: DBUser.Replicator

573. -----------------------------------------------------------------

574. PG_INSTALL

575. -----------------------------------------------------------------

576. pg_dbsu: postgres # os user for database, postgres by default (unwise to change it)
577. pg_dbsu_uid: 26 # os dbsu uid and gid, 26 for default postgres users and groups
578. pg_dbsu_sudo: limit # dbsu sudo privilege: none|limit|all|nopass, limit by default
579. pg_dbsu_home: /var/lib/pgsql # postgresql home directory
580. pg_dbsu_ssh_exchange: true # exchange postgres dbsu ssh key among same cluster ?
581. pg_version: 14 # default postgresql version to be installed
582. pgdg_repo: false # add pgdg official repo before install (in case of no local repo available)
583. pg_add_repo: false # add postgres relate repo before install ?
584. pg_bin_dir: /usr/pgsql/bin # postgres binary dir, default is /usr/pgsql/bin, which use /usr/pgsql -> /usr/pgsql-{ver}
585. pg_packages: # postgresql related packages. ${pg_version} will be replaced bypg_version`
586. • postgresql${pg_version}* # postgresql kernel packages
587. • postgis32_${pg_version}* # postgis
588. • citus_${pg_version}* # citus
589. • timescaledb-2-postgresql-${pg_version} # timescaledb
590. • pgbouncer pg_exporter pgbadger pg_activity node_exporter consul haproxy vip-manager
591. • patroni patroni-consul patroni-etcd python3 python3-psycopg2 python36-requests python3-etcd
592. • python3-consul python36-urllib3 python36-idna python36-pyOpenSSL python36-cryptography
593. pg_extensions: # postgresql extensions, ${pg_version} will be replaced by actualpg_version`
594. • pg_repack_${pg_version} pg_qualstats_${pg_version} pg_stat_kcache_${pg_version} pg_stat_monitor_${pg_version} wal2json_${pg_version}

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

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

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

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

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

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

601. -----------------------------------------------------------------

602. PG_BOOTSTRAP

603. -----------------------------------------------------------------

604. pg_safeguard: false # true will disable pg_clean at all, even for pgsql-remove.yml
605. pg_clean: true # true will clean running postgres during pgsql init (DANGEROUS)
606. pg_data: /pg/data # postgres data directory (soft link)
607. pg_fs_main: /data # primary data disk mount point /pg -> {{ pg_fs_main }}/postgres/{{ pg_instance }}
608. pg_fs_bkup: /data/backups # backup disk mount point /pg/ -> {{ pg_fs_bkup }}/postgres/{{ pg_instance }}/
609. pg_dummy_filesize: 64MiB # /pg/dummy hold some disk space for emergency use
610. pg_listen: '0.0.0.0' # postgres listen address, '0.0.0.0' (all ipv4 addr) by default
611. pg_port: 5432 # postgres port, 5432 by default
612. pg_localhost: /var/run/postgresql # localhost unix socket dir for connection
613. patroni_enabled: true # if not enabled, no postgres cluster will be created
614. patroni_mode: default # pause|default|remove
615. pg_dcs_type: consul # which dcs to use: consul or etcd or raft
616. pg_namespace: /pg # top level key namespace in dcs
617. patroni_port: 8008 # default patroni port
618. patroni_watchdog_mode: automatic # watchdog mode: off|automatic|required
619. pg_conf: tiny.yml # pgsql template: {oltp|olap|crit|tiny}.yml
620. pg_libs: 'timescaledb, pg_stat_statements, auto_explain' # extensions to be loaded
621. pg_delay: 0 # apply delay for standby cluster leader
622. pg_checksum: false # enable data checksum by default
623. pg_encoding: UTF8 # database cluster encoding, UTF8 by default
624. pg_locale: C # database cluster local, C by default
625. pg_lc_collate: C # database cluster collate, C by default
626. pg_lc_ctype: en_US.UTF8 # database character type, en_US.UTF8 by default (for i18n full-text search)
627. pgbouncer_enabled: true # if not enabled, pgbouncer will not be created
628. pgbouncer_port: 6432 # pgbouncer port, 6432 by default
629. pgbouncer_poolmode: transaction # pooling mode: session|transaction|statement, transaction pooling by default

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

631. -----

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