Citus: Distributive Extension

Pigsty 4-node citus config, 1 coordinator & 3 data nodes

  1. ---
  2. ######################################################################
  3. # File : pigsty.yml
  4. # Desc : Pigsty 4-node citus 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. ######################################################################
  10. ######################################################################
  11. # Sandbox (4-node) #
  12. #====================================================================#
  13. # admin user : vagrant (nopass ssh & sudo already set) #
  14. # 1. meta : 10.10.10.10 (2 Core | 4GB) pg-meta-1 (coord)#
  15. # 2. node-1 : 10.10.10.11 (1 Core | 1GB) pg-node1-1 (node) #
  16. # 3. node-2 : 10.10.10.12 (1 Core | 1GB) pg-node1-1 (node) #
  17. # 4. node-3 : 10.10.10.13 (1 Core | 1GB) pg-node1-1 (node) #
  18. ######################################################################
  19. all:
  20. ##################################################################
  21. # CLUSTERS #
  22. ##################################################################
  23. # meta nodes, nodes, pgsql, redis, pgsql clusters are defined as
  24. # k:v pair inside `all.children`. Where the key is cluster name
  25. # and value is cluster definition consist of two parts:
  26. # `hosts`: cluster members ip and instance level variables
  27. # `vars` : cluster level variables
  28. ##################################################################
  29. children: # groups definition
  30. #================================================================#
  31. # Meta Nodes: Admin Controller #
  32. #================================================================#
  33. meta: # meta nodes are defined in this special group "meta"
  34. vars:
  35. meta_node: true # mark this group as meta nodes
  36. ansible_group_priority: 99 # overwrite with the highest priority
  37. nginx_enabled: true # setup repo & underlying nginx
  38. nameserver_enabled: false # setup dnsmasq
  39. prometheus_enabled: true # setup prometheus
  40. grafana_enabled: true # setup grafana
  41. loki_enabled: true # setup loki
  42. docker_enabled: true # setup docker
  43. dcs_safeguard: false # protect dcs server from fat fingers
  44. hosts: # add nodes to group 'meta'
  45. 10.10.10.10: { }
  46. #================================================================#
  47. # CITUS Clusters #
  48. #================================================================#
  49. #----------------------------------#
  50. # cluster: citus coordinator
  51. #----------------------------------#
  52. pg-meta:
  53. hosts:
  54. 10.10.10.10: { pg_seq: 1, pg_role: primary , pg_offline_query: true }
  55. vars:
  56. pg_cluster: pg-meta
  57. vip_address: 10.10.10.2
  58. pg_users: [ { name: citus , password: citus , pgbouncer: true , roles: [ dbrole_admin ] } ]
  59. pg_databases: [ { name: meta , owner: citus , extensions: [ { name: citus } ] } ]
  60. #----------------------------------#
  61. # cluster: citus data nodes
  62. #----------------------------------#
  63. pg-node1:
  64. hosts:
  65. 10.10.10.11: { pg_seq: 1, pg_role: primary }
  66. vars:
  67. pg_cluster: pg-node1
  68. vip_address: 10.10.10.3
  69. pg_users: [ { name: citus , password: citus , pgbouncer: true , roles: [ dbrole_admin ] } ]
  70. pg_databases: [ { name: meta , owner: citus , extensions: [ { name: citus } ] } ]
  71. pg-node2:
  72. hosts:
  73. 10.10.10.12: { pg_seq: 1, pg_role: primary , pg_offline_query: true }
  74. vars:
  75. pg_cluster: pg-node2
  76. vip_address: 10.10.10.4
  77. pg_users: [ { name: citus , password: citus , pgbouncer: true , roles: [ dbrole_admin ] } ]
  78. pg_databases: [ { name: meta , owner: citus , extensions: [ { name: citus } ] } ]
  79. pg-node3:
  80. hosts:
  81. 10.10.10.13: { pg_seq: 1, pg_role: primary , pg_offline_query: true }
  82. vars:
  83. pg_cluster: pg-node3
  84. vip_address: 10.10.10.5
  85. pg_users: [ { name: citus , password: citus , pgbouncer: true , roles: [ dbrole_admin ] } ]
  86. pg_databases: [ { name: meta , owner: citus , extensions: [ { name: citus } ] } ]
  87. ####################################################################
  88. # VARS #
  89. ####################################################################
  90. vars: # global variables
  91. version: v1.5.1 # pigsty version string
  92. #================================================================#
  93. # VARS: INFRA #
  94. #================================================================#
  95. #-----------------------------------------------------------------
  96. # CONNECT
  97. #-----------------------------------------------------------------
  98. # INSTANCE level ansible connect parameters
  99. # ansible_user: vagrant # which user to be used (when connecting to remote nodes)
  100. # ansible_port: 22 # which port to be used (when connecting to remote nodes)
  101. # ansible_host: meta # ssh alias host name (when connecting to remote nodes)
  102. # ansible_ssh_private_key_file: ~/.ssh/id_rsa # ssh private key file
  103. # ansible_ssh_common_args: '-o StrictHostKeyChecking=no' # ssh common args
  104. proxy_env: # global proxy env when downloading packages
  105. 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"
  106. # http_proxy: # set your proxy here: e.g http://user:pass@proxy.xxx.com
  107. # https_proxy: # set your proxy here: e.g http://user:pass@proxy.xxx.com
  108. # all_proxy: # set your proxy here: e.g http://user:pass@proxy.xxx.com
  109. #-----------------------------------------------------------------
  110. # CA
  111. #-----------------------------------------------------------------
  112. ca_method: create # create|copy|recreate
  113. ca_subject: "/CN=root-ca" # self-signed CA subject
  114. ca_homedir: /ca # ca cert directory
  115. ca_cert: ca.crt # ca public key/cert
  116. ca_key: ca.key # ca private key
  117. #-----------------------------------------------------------------
  118. # NGINX
  119. #-----------------------------------------------------------------
  120. nginx_enabled: true # build local repo on this node
  121. nginx_port: 80 # repo listen address, must same as repo_address
  122. nginx_home: /www # default repo home dir
  123. nginx_upstream: # domain names and upstream servers
  124. - { name: home , domain: pigsty , endpoint: "10.10.10.10:80" }
  125. - { name: grafana , domain: g.pigsty , endpoint: "10.10.10.10:3000" }
  126. - { name: loki , domain: l.pigsty , endpoint: "10.10.10.10:3100" }
  127. - { name: prometheus , domain: p.pigsty , endpoint: "10.10.10.10:9090" }
  128. - { name: alertmanager , domain: a.pigsty , endpoint: "10.10.10.10:9093" }
  129. - { name: consul , domain: c.pigsty , endpoint: "127.0.0.1:8500" }
  130. nginx_indexes: # application nav links on home page
  131. - { name: Explain , url : '/pev2' , comment: 'postgres explain visualizer' }
  132. - { name: Package , url : '/pigsty' , comment: 'local yum repo packages' }
  133. - { name: PG Logs , url : '/logs' , comment: 'postgres raw csv logs' }
  134. - { name: Schemas , url : '/schema' , comment: 'schemaspy summary report' }
  135. - { name: Reports , url : '/report' , comment: 'pgbadger summary report' }
  136. #-----------------------------------------------------------------
  137. # REPO
  138. #-----------------------------------------------------------------
  139. repo_name: pigsty # repo name, pigsty by default
  140. repo_address: pigsty # external address to this repo (ip:port or url)
  141. repo_rebuild: false # force re-download packages
  142. repo_remove: true # remove existing upstream repo
  143. repo_upstreams: # where to download packages?
  144. - name: base
  145. description: CentOS-$releasever - Base
  146. gpgcheck: no
  147. baseurl:
  148. - https://mirrors.tuna.tsinghua.edu.cn/centos/$releasever/os/$basearch/
  149. - http://mirrors.aliyun.com/centos/$releasever/os/$basearch/
  150. - http://mirror.centos.org/centos/$releasever/os/$basearch/
  151. - name: updates
  152. description: CentOS-$releasever - Updates
  153. gpgcheck: no
  154. baseurl:
  155. - https://mirrors.tuna.tsinghua.edu.cn/centos/$releasever/updates/$basearch/
  156. - http://mirrors.aliyun.com/centos/$releasever/updates/$basearch/
  157. - http://mirror.centos.org/centos/$releasever/updates/$basearch/
  158. - name: extras
  159. description: CentOS-$releasever - Extras
  160. baseurl:
  161. - https://mirrors.tuna.tsinghua.edu.cn/centos/$releasever/extras/$basearch/
  162. - http://mirrors.aliyun.com/centos/$releasever/extras/$basearch/
  163. - http://mirror.centos.org/centos/$releasever/extras/$basearch/
  164. gpgcheck: no
  165. - name: epel
  166. description: CentOS $releasever - epel
  167. gpgcheck: no
  168. baseurl:
  169. - https://mirrors.tuna.tsinghua.edu.cn/epel/$releasever/$basearch
  170. - http://mirrors.aliyun.com/epel/$releasever/$basearch
  171. - http://download.fedoraproject.org/pub/epel/$releasever/$basearch
  172. - name: grafana
  173. description: Grafana Official Yum Repo
  174. enabled: yes
  175. gpgcheck: no
  176. baseurl:
  177. - https://mirrors.tuna.tsinghua.edu.cn/grafana/yum/rpm
  178. - https://packages.grafana.com/oss/rpm
  179. - name: prometheus
  180. description: Prometheus and exporters
  181. gpgcheck: no
  182. baseurl: https://packagecloud.io/prometheus-rpm/release/el/$releasever/$basearch
  183. - name: pgdg-common
  184. description: PostgreSQL common RPMs for RHEL/CentOS $releasever - $basearch
  185. gpgcheck: no
  186. baseurl:
  187. - http://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/common/redhat/rhel-$releasever-$basearch
  188. - https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-$releasever-$basearch
  189. - name: pgdg14
  190. description: PostgreSQL 14 for RHEL/CentOS $releasever - $basearch
  191. gpgcheck: no
  192. baseurl:
  193. - https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/14/redhat/rhel-$releasever-$basearch
  194. - https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-$releasever-$basearch
  195. - name: timescaledb
  196. description: TimescaleDB for RHEL/CentOS $releasever - $basearch
  197. gpgcheck: no
  198. baseurl:
  199. - https://packagecloud.io/timescale/timescaledb/el/7/$basearch
  200. - name: centos-sclo
  201. description: CentOS-$releasever - SCLo
  202. gpgcheck: no
  203. #mirrorlist: http://mirrorlist.centos.org?arch=$basearch&release=$releasever&repo=sclo-sclo
  204. baseurl: http://mirrors.aliyun.com/centos/$releasever/sclo/$basearch/sclo/
  205. - name: centos-sclo-rh
  206. description: CentOS-$releasever - SCLo rh
  207. gpgcheck: no
  208. #mirrorlist: http://mirrorlist.centos.org?arch=$basearch&release=7&repo=sclo-rh
  209. baseurl: http://mirrors.aliyun.com/centos/$releasever/sclo/$basearch/rh/
  210. - name: nginx
  211. description: Nginx Official Yum Repo
  212. skip_if_unavailable: true
  213. gpgcheck: no
  214. baseurl: http://nginx.org/packages/centos/$releasever/$basearch/
  215. - name: harbottle # for latest consul & kubernetes
  216. description: Copr repo for main owned by harbottle
  217. skip_if_unavailable: true
  218. gpgcheck: no
  219. baseurl: https://download.copr.fedorainfracloud.org/results/harbottle/main/epel-$releasever-$basearch/
  220. - name: pg_probackup # for pg_probackup
  221. description: PG_PROBACKUP Centos packages for PostgresPro Standard and Enterprise - $basearch
  222. skip_if_unavailable: true
  223. gpgcheck: no
  224. baseurl: https://repo.postgrespro.ru/pg_probackup-forks/rpm/latest/centos-$releasever-$basearch
  225. - name: docker-ce # for latest docker
  226. description: Docker CE Stable - $basearch
  227. skip_if_unavailable: true
  228. gpgcheck: no
  229. baseurl:
  230. - https://mirrors.aliyun.com/docker-ce/linux/centos/$releasever/$basearch/stable
  231. - https://download.docker.com/linux/centos/$releasever/$basearch/stable
  232. repo_packages: # which packages to be included # what to download #
  233. - epel-release nginx wget yum-utils yum createrepo sshpass zip unzip # ---- boot ---- #
  234. - ntp chrony uuid lz4 bzip2 nc pv jq vim-enhanced make patch bash lsof wget git tuned perf ftp lrzsz rsync # ---- node ---- #
  235. - numactl grubby sysstat dstat iotop bind-utils net-tools tcpdump socat ipvsadm telnet ca-certificates keepalived # ----- utils ----- #
  236. - readline zlib openssl openssh-clients libyaml libxml2 libxslt libevent perl perl-devel perl-ExtUtils* # --- deps:pg --- #
  237. - readline-devel zlib-devel uuid-devel libuuid-devel libxml2-devel libxslt-devel openssl-devel libicu-devel # --- deps:devel -- #
  238. - grafana prometheus2 pushgateway alertmanager mtail consul consul_exporter consul-template etcd dnsmasq # ----- meta ----- #
  239. - node_exporter nginx_exporter blackbox_exporter redis_exporter # ---- exporter --- #
  240. - ansible python python-pip python-psycopg2 # - ansible & py3 - #
  241. - python3 python3-psycopg2 python36-requests python3-etcd python3-consul python36-urllib3 python36-idna python36-pyOpenSSL python36-cryptography
  242. - patroni patroni-consul patroni-etcd pgbouncer pg_cli pgbadger pg_activity tail_n_mail # -- pgsql common - #
  243. - pgcenter boxinfo check_postgres emaj pgbconsole pg_bloat_check pgquarrel barman barman-cli pgloader pgFormatter pitrery pspg pgxnclient PyGreSQL
  244. - postgresql14* postgis32_14* citus_14* pglogical_14* timescaledb-2-postgresql-14 pg_repack_14 wal2json_14 # -- pg14 packages -#
  245. - 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
  246. - pg_statement_rollback_14 system_stats_14 plproxy_14 plsh_14 pldebugger_14 plpgsql_check_14 pgmemcache_14 # plr_14
  247. - 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
  248. - hypopg_14 geoip_14 rum_14 hll_14 ip4r_14 prefix_14 pguri_14 tdigest_14 topn_14 periods_14
  249. - bgw_replstatus_14 count_distinct_14 credcheck_14 ddlx_14 extra_window_functions_14 logerrors_14 mysqlcompat_14 orafce_14
  250. - repmgr_14 pg_auth_mon_14 pg_auto_failover_14 pg_background_14 pg_bulkload_14 pg_catcheck_14 pg_comparator_14
  251. - pg_cron_14 pg_fkpart_14 pg_jobmon_14 pg_partman_14 pg_permissions_14 pg_prioritize_14 pgagent_14
  252. - pgaudit16_14 pgauditlogtofile_14 pgcryptokey_14 pgexportdoc_14 pgfincore_14 pgimportdoc_14 powa_14 pgmp_14 pgq_14
  253. - pgquarrel-0.7.0-1 pgsql_tweaks_14 pgtap_14 pgtt_14 postgresql-unit_14 postgresql_anonymizer_14 postgresql_faker_14
  254. - safeupdate_14 semver_14 set_user_14 sslutils_14 table_version_14 # pgrouting_14 osm2pgrouting_14
  255. - clang coreutils diffutils rpm-build rpm-devel rpmlint rpmdevtools bison flex # gcc gcc-c++ # - build utils - #
  256. - docker-ce docker-compose kubelet kubectl kubeadm kubernetes-cni helm # - cloud native- #
  257. repo_url_packages: # extra packages from url
  258. - https://github.com/Vonng/loki-rpm/releases/download/v2.5.0/loki-2.5.0.x86_64.rpm
  259. - https://github.com/Vonng/loki-rpm/releases/download/v2.5.0/promtail-2.5.0.x86_64.rpm
  260. - https://github.com/Vonng/pg_exporter/releases/download/v0.5.0/pg_exporter-0.5.0.x86_64.rpm
  261. - https://github.com/cybertec-postgresql/vip-manager/releases/download/v1.0.2/vip-manager-1.0.2-1.x86_64.rpm
  262. - https://github.com/Vonng/haproxy-rpm/releases/download/v2.5.7/haproxy-2.5.7-1.el7.x86_64.rpm
  263. - https://github.com/Vonng/pigsty-pkg/releases/download/misc/redis-6.2.7-1.el7.remi.x86_64.rpm
  264. - https://github.com/dalibo/pev2/releases/download/v0.24.0/pev2.tar.gz
  265. - https://github.com/Vonng/pigsty-pkg/releases/download/misc/polysh-0.4-1.noarch.rpm
  266. #-----------------------------------------------------------------
  267. # NAMESERVER
  268. #-----------------------------------------------------------------
  269. nameserver_enabled: false # setup dnsmasq
  270. dns_records: # dynamic dns record resolved by dnsmasq
  271. - 10.10.10.2 pg-meta # sandbox vip for pg-meta
  272. - 10.10.10.3 pg-test # sandbox vip for pg-test
  273. - 10.10.10.10 meta-1 # sandbox node meta-1
  274. - 10.10.10.11 node-1 # sandbox node node-1
  275. - 10.10.10.12 node-2 # sandbox node node-2
  276. - 10.10.10.13 node-3 # sandbox node node-3
  277. #-----------------------------------------------------------------
  278. # PROMETHEUS
  279. #-----------------------------------------------------------------
  280. prometheus_enabled: true # setup prometheus
  281. prometheus_data_dir: /data/prometheus/data
  282. prometheus_options: '--storage.tsdb.retention=15d'
  283. prometheus_reload: false # reload prometheus instead of recreate it?
  284. prometheus_sd_method: static # service discovery method: static|consul
  285. prometheus_sd_interval: 5s # service discovery refresh interval
  286. prometheus_scrape_interval: 10s # global scrape & evaluation interval
  287. prometheus_scrape_timeout: 8s # scrape timeout
  288. #-----------------------------------------------------------------
  289. # EXPORTER
  290. #-----------------------------------------------------------------
  291. exporter_install: none # none|yum|binary, none by default
  292. exporter_repo_url: '' # if set, repo will be added to /etc/yum.repos.d/
  293. exporter_metrics_path: /metrics # default metric path for pg related exporter
  294. #-----------------------------------------------------------------
  295. # GRAFANA
  296. #-----------------------------------------------------------------
  297. grafana_enabled: true # enable grafana? only works on meta nodes
  298. grafana_endpoint: http://10.10.10.10:3000 # grafana endpoint url
  299. grafana_admin_username: admin # default grafana admin username
  300. grafana_admin_password: pigsty # default grafana admin password
  301. grafana_database: sqlite3 # default grafana database type: sqlite3|postgres
  302. grafana_pgurl: postgres://dbuser_grafana:DBUser.Grafana@meta:5436/grafana
  303. grafana_plugin_method: install # none|install|always, none will skip plugin install
  304. grafana_plugin_cache: /www/pigsty/plugins.tgz # path to grafana plugins cache tarball
  305. grafana_plugin_list: # plugins that will be downloaded via grafana-cli
  306. - marcusolsson-csv-datasource
  307. - marcusolsson-json-datasource
  308. - marcusolsson-treemap-panel
  309. grafana_plugin_git: # plugins that will be downloaded via git
  310. - https://github.com/Vonng/vonng-echarts-panel
  311. #-----------------------------------------------------------------
  312. # LOKI
  313. #-----------------------------------------------------------------
  314. loki_enabled: true # enable loki? only works on meta nodes
  315. loki_clean: false # whether remove existing loki data
  316. loki_endpoint: http://10.10.10.10:3100/loki/api/v1/push # where to push data
  317. loki_options: '-config.file=/etc/loki.yml -config.expand-env=true'
  318. loki_data_dir: /data/loki # default loki data dir
  319. loki_retention: 15d # log retention period
  320. #-----------------------------------------------------------------
  321. # DCS
  322. #-----------------------------------------------------------------
  323. dcs_name: pigsty # consul dc name
  324. dcs_servers: # dcs server dict in name:ip format
  325. meta-1: 10.10.10.10 # using existing external dcs cluster is recommended for HA
  326. # meta-2: 10.10.10.11 # node with ip in dcs_servers will be initialized as dcs servers
  327. # meta-3: 10.10.10.12 # it's recommend to reuse meta nodes as dcs servers if no ad hoc cluster available
  328. dcs_registry: consul # where to register services: none | consul | etcd | both
  329. dcs_safeguard: false # if true, running dcs will NOT be removed
  330. dcs_clean: true # if true, running dcs will be purged during node init, DANGEROUS
  331. #-----------------------------------------------------------------
  332. # CONSUL
  333. #-----------------------------------------------------------------
  334. consul_enabled: true # enable consul server/agent by default?
  335. consul_data_dir: /data/consul # consul data dir (/data/consul by default)
  336. #-----------------------------------------------------------------
  337. # ETCD
  338. #-----------------------------------------------------------------
  339. etcd_enabled: true # enable etcd server by default?
  340. etcd_data_dir: /data/etcd # etcd data dir (/data/etcd by default)
  341. #================================================================#
  342. # VARS: NODES #
  343. #================================================================#
  344. # global variables for nodes (including meta)
  345. #-----------------------------------------------------------------
  346. # NODE_IDENTITY
  347. #-----------------------------------------------------------------
  348. meta_node: false # node with meta_node flag will be marked as admin node
  349. # nodename: # [OPTIONAL] # node instance identity, used as `ins`, hostname by default
  350. node_cluster: nodes # [OPTIONAL] # node cluster identity, used as `cls`, 'nodes' by default
  351. nodename_overwrite: true # overwrite node's hostname with nodename?
  352. nodename_exchange: false # exchange nodename among play hosts?
  353. #-----------------------------------------------------------------
  354. # NODE_DNS
  355. #-----------------------------------------------------------------
  356. node_etc_hosts_default: # static dns records in /etc/hosts
  357. - 10.10.10.10 meta pigsty c.pigsty g.pigsty l.pigsty p.pigsty a.pigsty cli.pigsty lab.pigsty api.pigsty
  358. node_etc_hosts:
  359. - 10.10.10.10 meta
  360. - 10.10.10.11 node-1
  361. - 10.10.10.12 node-2
  362. - 10.10.10.13 node-3
  363. - 10.10.10.2 pg-meta
  364. - 10.10.10.11 pg-node1-1
  365. - 10.10.10.12 pg-node2-1
  366. - 10.10.10.13 pg-node3-1
  367. node_dns_method: add # add (default) | none (skip) | overwrite (remove old settings)
  368. node_dns_servers: # dynamic nameserver in /etc/resolv.conf
  369. - 10.10.10.10
  370. node_dns_options: # dns resolv options
  371. - options single-request-reopen timeout:1 rotate
  372. - domain service.consul
  373. #-----------------------------------------------------------------
  374. # NODE_REPO
  375. #-----------------------------------------------------------------
  376. node_repo_method: local # none|local: ad local repo|public: add upstream directly
  377. node_repo_remove: true # remove existing repo on nodes?
  378. node_repo_local_urls: # list local repo url, if node_repo_method = local
  379. - http://pigsty/pigsty.repo
  380. node_packages: [ ] # extra packages for all nodes
  381. node_packages_default: # common packages for all nodes
  382. - wget,sshpass,ntp,chrony,tuned,uuid,lz4,make,patch,bash,lsof,wget,unzip,git,ftp,vim-minimal,ca-certificates
  383. - numactl,grubby,sysstat,dstat,iotop,bind-utils,net-tools,tcpdump,socat,ipvsadm,telnet,tuned,nc,pv,jq,perf
  384. - readline,zlib,openssl,openssl-libs,openssh-clients,python3,python36-requests,node_exporter,consul,etcd,promtail
  385. node_packages_meta: # extra packages for meta nodes
  386. - grafana,prometheus2,alertmanager,loki,nginx_exporter,blackbox_exporter,pushgateway,redis,postgresql14
  387. - nginx,ansible,pgbadger,python-psycopg2,dnsmasq,coreutils,diffutils,polysh,docker-ce,docker-compose
  388. node_packages_meta_pip: jupyterlab
  389. #-----------------------------------------------------------------
  390. # NODE_TUNE
  391. #-----------------------------------------------------------------
  392. node_disable_firewall: true # disable firewall
  393. node_disable_selinux: true # disable selinux
  394. node_disable_numa: false # disable numa (node reboot required)
  395. node_disable_swap: false # disable swap, use with caution
  396. node_static_network: true # keep dns resolver settings after reboot
  397. node_disk_prefetch: false # setup disk prefetch on HDD to increase performance
  398. node_kernel_modules: [ softdog, br_netfilter, ip_vs, ip_vs_rr, ip_vs_rr, ip_vs_wrr, ip_vs_sh ]
  399. node_tune: tiny # install and activate tuned profile: none|oltp|olap|crit|tiny
  400. node_sysctl_params: { } # set additional sysctl parameters, k:v format
  401. #-----------------------------------------------------------------
  402. # NODE_ADMIN
  403. #-----------------------------------------------------------------
  404. node_data_dir: /data # main data directory
  405. node_admin_enabled: true # create a default admin user defined by `node_admin_*` ?
  406. node_admin_uid: 88 # uid and gid for this admin user
  407. node_admin_username: dba # name of this admin user, dba by default
  408. node_admin_ssh_exchange: true # exchange admin ssh key among each pgsql cluster ?
  409. node_admin_pk_current: true # add current user's ~/.ssh/id_rsa.pub to admin authorized_keys ?
  410. node_admin_pk_list: # ssh public keys to be added to admin user (REPLACE WITH YOURS!)
  411. - 'ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAAAgQC7IMAMNavYtWwzAJajKqwdn3ar5BhvcwCnBTxxEkXhGlCO2vfgosSAQMEflfgvkiI5nM1HIFQ8KINlx1XLO7SdL5KdInG5LIJjAFh0pujS4kNCT9a5IGvSq1BrzGqhbEcwWYdju1ZPYBcJm/MG+JD0dYCh8vfrYB/cYMD0SOmNkQ== vagrant@pigsty.com'
  412. #-----------------------------------------------------------------
  413. # NODE_TIME
  414. #-----------------------------------------------------------------
  415. node_timezone: Asia/Hong_Kong # default node timezone, empty will not change
  416. node_ntp_enabled: true # enable ntp service? false will leave ntp service untouched
  417. node_ntp_service: ntp # ntp service provider: ntp|chrony
  418. node_ntp_servers: # default NTP servers
  419. - pool cn.pool.ntp.org iburst
  420. - pool pool.ntp.org iburst
  421. - pool time.pool.aliyun.com iburst
  422. - server 10.10.10.10 iburst
  423. - server ntp.tuna.tsinghua.edu.cn iburst
  424. node_crontab_overwrite: true # true will overwrite /etc/crontab, false will append crontab
  425. node_crontab: [ ] # crontab entries in /etc/crontab
  426. #-----------------------------------------------------------------
  427. # DOCKER
  428. #-----------------------------------------------------------------
  429. docker_enabled: false # enable docker on all nodes? (you can enable them on meta nodes only)
  430. docker_cgroups_driver: systemd # docker cgroup fs driver
  431. docker_registry_mirrors: [] # docker registry mirror
  432. docker_image_cache: /tmp/docker.tgz # docker images tarball to be loaded if exists
  433. #-----------------------------------------------------------------
  434. # NODE_EXPORTER
  435. #-----------------------------------------------------------------
  436. node_exporter_enabled: true # setup node_exporter on instance
  437. node_exporter_port: 9100 # default port for node exporter
  438. node_exporter_options: '--no-collector.softnet --no-collector.nvme --collector.ntp --collector.tcpstat --collector.processes'
  439. #-----------------------------------------------------------------
  440. # PROMTAIL
  441. #-----------------------------------------------------------------
  442. promtail_enabled: true # enable promtail logging collector?
  443. promtail_clean: false # remove promtail status file? false by default
  444. promtail_port: 9080 # default listen address for promtail
  445. promtail_options: '-config.file=/etc/promtail.yml -config.expand-env=true'
  446. promtail_positions: /var/log/positions.yaml # position status for promtail
  447. #================================================================#
  448. # VARS: PGSQL #
  449. #================================================================#
  450. #-----------------------------------------------------------------
  451. # PG_IDENTITY
  452. #-----------------------------------------------------------------
  453. # pg_cluster: # <CLUSTER> <REQUIRED> : pgsql cluster name
  454. # pg_role: replica # <INSTANCE> <REQUIRED> : pg role : primary, replica, offline
  455. # pg_seq: 0 # <INSTANCE> <REQUIRED> : instance seq number
  456. # pg_instances: {} # <INSTANCE> : define multiple pg instances on node, used by monly & gpsql
  457. # pg_upstream: # <INSTANCE> : replication upstream ip addr
  458. # pg_shard: # <CLUSTER> : pgsql shard name
  459. # pg_sindex: 0 # <CLUSTER> : pgsql shard index
  460. # gp_role: master # <CLUSTER> : gpsql role, master or segment
  461. pg_offline_query: false # <INSTANCE> [FLAG] set to true to enable offline query on this instance (instance level)
  462. pg_backup: false # <INSTANCE> [FLAG] store base backup on this node (instance level, reserved)
  463. pg_weight: 100 # <INSTANCE> [FLAG] default load balance weight (instance level)
  464. pg_hostname: true # [FLAG] reuse postgres identity name as node identity?
  465. pg_preflight_skip: false # [FLAG] skip preflight identity check
  466. #-----------------------------------------------------------------
  467. # PG_BUSINESS
  468. #-----------------------------------------------------------------
  469. # overwrite these variables on <CLUSTER> level
  470. pg_users: [] # business users
  471. pg_databases: [] # business databases
  472. pg_services_extra: [] # extra services
  473. pg_hba_rules_extra: # extra hba rules
  474. # AD HOC FOR CITUS CLUSTER
  475. - title: Allow unrestricted access to nodes in the local network. The following ranges
  476. role: common
  477. rules:
  478. - host all all 10.0.0.0/8 trust
  479. - title: Also allow the host unrestricted access to connect to itself
  480. role: common
  481. rules:
  482. - host all all 127.0.0.1/32 trust
  483. - host all all ::1/128 trust
  484. pgbouncer_hba_rules_extra: [] # extra pgbouncer hba rules
  485. # WARNING: change these in production environment!
  486. pg_admin_username: dbuser_dba
  487. pg_admin_password: DBUser.DBA
  488. pg_monitor_username: dbuser_monitor
  489. pg_monitor_password: DBUser.Monitor
  490. pg_replication_username: replicator
  491. pg_replication_password: DBUser.Replicator
  492. #-----------------------------------------------------------------
  493. # PG_INSTALL
  494. #-----------------------------------------------------------------
  495. pg_dbsu: postgres # os user for database, postgres by default (unwise to change it)
  496. pg_dbsu_uid: 26 # os dbsu uid and gid, 26 for default postgres users and groups
  497. pg_dbsu_sudo: limit # dbsu sudo privilege: none|limit|all|nopass, limit by default
  498. pg_dbsu_home: /var/lib/pgsql # postgresql home directory
  499. pg_dbsu_ssh_exchange: true # exchange postgres dbsu ssh key among same cluster ?
  500. pg_version: 14 # default postgresql version to be installed
  501. pgdg_repo: false # add pgdg official repo before install (in case of no local repo available)
  502. pg_add_repo: false # add postgres relate repo before install ?
  503. pg_bin_dir: /usr/pgsql/bin # postgres binary dir, default is /usr/pgsql/bin, which use /usr/pgsql -> /usr/pgsql-{ver}
  504. pg_packages: # postgresql related packages. `${pg_version} will be replaced by `pg_version`
  505. - postgresql${pg_version}* # postgresql kernel packages
  506. - postgis32_${pg_version}* # postgis
  507. - citus_${pg_version}* # citus
  508. - timescaledb-2-postgresql-${pg_version} # timescaledb
  509. - pgbouncer pg_exporter pgbadger pg_activity node_exporter consul haproxy vip-manager
  510. - patroni patroni-consul patroni-etcd python3 python3-psycopg2 python36-requests python3-etcd
  511. - python3-consul python36-urllib3 python36-idna python36-pyOpenSSL python36-cryptography
  512. pg_extensions: # postgresql extensions, `${pg_version} will be replaced by actual `pg_version`
  513. - pg_repack_${pg_version} pg_qualstats_${pg_version} pg_stat_kcache_${pg_version} pg_stat_monitor_${pg_version} wal2json_${pg_version}
  514. # - ogr_fdw${pg_version} mysql_fdw_${pg_version} redis_fdw_${pg_version} mongo_fdw${pg_version} hdfs_fdw_${pg_version}
  515. # - count_distinct${version} ddlx_${version} geoip${version} orafce${version}
  516. # - hypopg_${version} ip4r${version} jsquery_${version} logerrors_${version} periods_${version} pg_auto_failover_${version} pg_catcheck${version}
  517. # - pg_fkpart${version} pg_jobmon${version} pg_partman${version} pg_prioritize_${version} pg_track_settings${version} pgaudit15_${version}
  518. # - pgcryptokey${version} pgexportdoc${version} pgimportdoc${version} pgmemcache-${version} pgmp${version} pgq-${version} pgquarrel pgrouting_${version}
  519. # - pguint${version} pguri${version} prefix${version} safeupdate_${version} semver${version} table_version${version} tdigest${version}
  520. #-----------------------------------------------------------------
  521. # PG_BOOTSTRAP
  522. #-----------------------------------------------------------------
  523. pg_safeguard: false # true will disable pg_clean at all, even for pgsql-remove.yml
  524. pg_clean: true # true will clean running postgres during pgsql init (DANGEROUS)
  525. pg_data: /pg/data # postgres data directory (soft link)
  526. pg_fs_main: /data # primary data disk mount point /pg -> {{ pg_fs_main }}/postgres/{{ pg_instance }}
  527. pg_fs_bkup: /data/backups # backup disk mount point /pg/* -> {{ pg_fs_bkup }}/postgres/{{ pg_instance }}/*
  528. pg_dummy_filesize: 64MiB # /pg/dummy hold some disk space for emergency use
  529. pg_listen: '0.0.0.0' # postgres listen address, '0.0.0.0' (all ipv4 addr) by default
  530. pg_port: 5432 # postgres port, 5432 by default
  531. pg_localhost: /var/run/postgresql # localhost unix socket dir for connection
  532. patroni_enabled: true # if not enabled, no postgres cluster will be created
  533. patroni_mode: default # pause|default|remove
  534. pg_dcs_type: consul # which dcs to use: consul or etcd or raft
  535. pg_namespace: /pg # top level key namespace in dcs
  536. patroni_port: 8008 # default patroni port
  537. patroni_watchdog_mode: automatic # watchdog mode: off|automatic|required
  538. pg_conf: tiny.yml # pgsql template: {oltp|olap|crit|tiny}.yml
  539. pg_libs: 'citus, timescaledb, pg_stat_statements, auto_explain' # extensions to be loaded
  540. pg_delay: 0 # apply delay for standby cluster leader
  541. pg_checksum: false # enable data checksum by default
  542. pg_encoding: UTF8 # database cluster encoding, UTF8 by default
  543. pg_locale: C # database cluster local, C by default
  544. pg_lc_collate: C # database cluster collate, C by default
  545. pg_lc_ctype: en_US.UTF8 # database character type, en_US.UTF8 by default (for i18n full-text search)
  546. pgbouncer_enabled: true # if not enabled, pgbouncer will not be created
  547. pgbouncer_port: 6432 # pgbouncer port, 6432 by default
  548. pgbouncer_poolmode: transaction # pooling mode: session|transaction|statement, transaction pooling by default
  549. pgbouncer_max_db_conn: 100 # max connection to single database, DO NOT set this larger than postgres max conn or db connlimit
  550. #-----------------------------------------------------------------
  551. # PG_PROVISION
  552. #-----------------------------------------------------------------
  553. pg_provision: true # whether provisioning postgres cluster
  554. pg_init: pg-init # init script for cluster template
  555. pg_default_roles:
  556. - { name: dbrole_readonly , login: false , comment: role for global read-only access } # production read-only role
  557. - { name: dbrole_readwrite , login: false , roles: [dbrole_readonly], comment: role for global read-write access } # production read-write role
  558. - { name: dbrole_offline , login: false , comment: role for restricted read-only access (offline instance) } # restricted-read-only role
  559. - { name: dbrole_admin , login: false , roles: [pg_monitor, dbrole_readwrite] , comment: role for object creation } # production DDL change role
  560. - { name: postgres , superuser: true , comment: system superuser } # system dbsu, name is designated by `pg_dbsu`
  561. - { name: dbuser_dba , superuser: true , roles: [dbrole_admin] , comment: system admin user } # admin dbsu, name is designated by `pg_admin_username`
  562. - { name: replicator , replication: true , bypassrls: true , roles: [pg_monitor, dbrole_readonly] , comment: system replicator } # replicator
  563. - { name: dbuser_monitor , roles: [pg_monitor, dbrole_readonly] , comment: system monitor user , parameters: {log_min_duration_statement: 1000 } } # monitor user
  564. - { name: dbuser_stats , password: DBUser.Stats , roles: [dbrole_offline] , comment: business offline user for offline queries and ETL } # ETL user
  565. pg_default_privileges: # - privileges - #
  566. - GRANT USAGE ON SCHEMAS TO dbrole_readonly
  567. - GRANT SELECT ON TABLES TO dbrole_readonly
  568. - GRANT SELECT ON SEQUENCES TO dbrole_readonly
  569. - GRANT EXECUTE ON FUNCTIONS TO dbrole_readonly
  570. - GRANT USAGE ON SCHEMAS TO dbrole_offline
  571. - GRANT SELECT ON TABLES TO dbrole_offline
  572. - GRANT SELECT ON SEQUENCES TO dbrole_offline
  573. - GRANT EXECUTE ON FUNCTIONS TO dbrole_offline
  574. - GRANT INSERT, UPDATE, DELETE ON TABLES TO dbrole_readwrite
  575. - GRANT USAGE, UPDATE ON SEQUENCES TO dbrole_readwrite
  576. - GRANT TRUNCATE, REFERENCES, TRIGGER ON TABLES TO dbrole_admin
  577. - GRANT CREATE ON SCHEMAS TO dbrole_admin
  578. pg_default_schemas: [ monitor ] # default schemas to be created
  579. pg_default_extensions: # default extensions to be created
  580. - { name: 'pg_stat_statements', schema: 'monitor' }
  581. - { name: 'pgstattuple', schema: 'monitor' }
  582. - { name: 'pg_qualstats', schema: 'monitor' }
  583. - { name: 'pg_buffercache', schema: 'monitor' }
  584. - { name: 'pageinspect', schema: 'monitor' }
  585. - { name: 'pg_prewarm', schema: 'monitor' }
  586. - { name: 'pg_visibility', schema: 'monitor' }
  587. - { name: 'pg_freespacemap', schema: 'monitor' }
  588. - { name: 'pg_repack', schema: 'monitor' }
  589. - name: postgres_fdw
  590. - name: file_fdw
  591. - name: btree_gist
  592. - name: btree_gin
  593. - name: pg_trgm
  594. - name: intagg
  595. - name: intarray
  596. pg_reload: true # reload postgres after hba changes
  597. pg_hba_rules: # postgres host-based authentication rules
  598. - title: allow meta node password access
  599. role: common
  600. rules:
  601. - host all all 10.10.10.10/32 md5
  602. - title: allow intranet admin password access
  603. role: common
  604. rules:
  605. - host all +dbrole_admin 10.0.0.0/8 md5
  606. - host all +dbrole_admin 172.16.0.0/12 md5
  607. - host all +dbrole_admin 192.168.0.0/16 md5
  608. - title: allow intranet password access
  609. role: common
  610. rules:
  611. - host all all 10.0.0.0/8 md5
  612. - host all all 172.16.0.0/12 md5
  613. - host all all 192.168.0.0/16 md5
  614. - title: allow local read/write (local production user via pgbouncer)
  615. role: common
  616. rules:
  617. - local all +dbrole_readonly md5
  618. - host all +dbrole_readonly 127.0.0.1/32 md5
  619. - title: allow offline query (ETL,SAGA,Interactive) on offline instance
  620. role: offline
  621. rules:
  622. - host all +dbrole_offline 10.0.0.0/8 md5
  623. - host all +dbrole_offline 172.16.0.0/12 md5
  624. - host all +dbrole_offline 192.168.0.0/16 md5
  625. pgbouncer_hba_rules: # pgbouncer host-based authentication rules
  626. - title: local password access
  627. role: common
  628. rules:
  629. - local all all md5
  630. - host all all 127.0.0.1/32 md5
  631. - title: intranet password access
  632. role: common
  633. rules:
  634. - host all all 10.0.0.0/8 md5
  635. - host all all 172.16.0.0/12 md5
  636. - host all all 192.168.0.0/16 md5
  637. #-----------------------------------------------------------------
  638. # PG_EXPORTER
  639. #-----------------------------------------------------------------
  640. pg_exporter_enabled: true # setup pg_exporter on instance
  641. pg_exporter_config: pg_exporter.yml # use fast cache exporter for demo
  642. pg_exporter_port: 9630 # pg_exporter listen port
  643. pg_exporter_params: 'sslmode=disable' # url query parameters for pg_exporter
  644. pg_exporter_url: '' # optional, overwrite auto-generate postgres connstr
  645. pg_exporter_auto_discovery: true # optional, discovery available database on target instance ?
  646. pg_exporter_exclude_database: 'template0,template1,postgres' # optional, comma separated list of database that WILL NOT be monitored when auto-discovery enabled
  647. pg_exporter_include_database: '' # optional, comma separated list of database that WILL BE monitored when auto-discovery enabled, empty string will disable include mode
  648. pg_exporter_options: '--log.level=info --log.format="logger:syslog?appname=pg_exporter&local=7"'
  649. pgbouncer_exporter_enabled: true # setup pgbouncer_exporter on instance (if you don't have pgbouncer, disable it)
  650. pgbouncer_exporter_port: 9631 # pgbouncer_exporter listen port
  651. pgbouncer_exporter_url: '' # optional, overwrite auto-generate pgbouncer connstr
  652. pgbouncer_exporter_options: '--log.level=info --log.format="logger:syslog?appname=pgbouncer_exporter&local=7"'
  653. #-----------------------------------------------------------------
  654. # PG_SERVICE
  655. #-----------------------------------------------------------------
  656. pg_services: # how to expose postgres service in cluster?
  657. - name: primary # service name {{ pg_cluster }}-primary
  658. src_ip: "*"
  659. src_port: 5433
  660. dst_port: pgbouncer # 5433 route to pgbouncer
  661. check_url: /primary # primary health check, success when instance is primary
  662. selector: "[]" # select all instance as primary service candidate
  663. - name: replica # service name {{ pg_cluster }}-replica
  664. src_ip: "*"
  665. src_port: 5434
  666. dst_port: pgbouncer
  667. check_url: /read-only # read-only health check. (including primary)
  668. selector: "[]" # select all instance as replica service candidate
  669. selector_backup: "[? pg_role == `primary` || pg_role == `offline` ]"
  670. - name: default # service's actual name is {{ pg_cluster }}-default
  671. src_ip: "*" # service bind ip address, * for all, vip for cluster virtual ip address
  672. src_port: 5436 # bind port, mandatory
  673. dst_port: postgres # target port: postgres|pgbouncer|port_number , pgbouncer(6432) by default
  674. check_method: http # health check method: only http is available for now
  675. check_port: patroni # health check port: patroni|pg_exporter|port_number , patroni by default
  676. check_url: /primary # health check url path, / as default
  677. check_code: 200 # health check http code, 200 as default
  678. selector: "[]" # instance selector
  679. haproxy: # haproxy specific fields
  680. maxconn: 3000 # default front-end connection
  681. balance: roundrobin # load balance algorithm (roundrobin by default)
  682. 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'
  683. - name: offline # service name {{ pg_cluster }}-offline
  684. src_ip: "*"
  685. src_port: 5438
  686. dst_port: postgres
  687. check_url: /replica # offline MUST be a replica
  688. selector: "[? pg_role == `offline` || pg_offline_query ]" # instances with pg_role == 'offline' or instance marked with 'pg_offline_query == true'
  689. selector_backup: "[? pg_role == `replica` && !pg_offline_query]" # replica are used as backup server in offline service
  690. haproxy_enabled: true # enable haproxy on this node?
  691. haproxy_reload: true # reload haproxy after config?
  692. haproxy_auth_enabled: false # enable authentication for haproxy admin?
  693. haproxy_admin_username: admin # default haproxy admin username
  694. haproxy_admin_password: pigsty # default haproxy admin password
  695. haproxy_exporter_port: 9101 # default admin/exporter port
  696. haproxy_client_timeout: 24h # client side connection timeout
  697. haproxy_server_timeout: 24h # server side connection timeout
  698. vip_mode: none # none | l2 | l4 , l4 not implemented yet
  699. vip_reload: true # reload vip after config?
  700. # vip_address: 127.0.0.1 # virtual ip address ip (l2 or l4)
  701. # vip_cidrmask: 24 # virtual ip address cidr mask (l2 only)
  702. # vip_interface: eth0 # virtual ip network interface (l2 only)
  703. # dns_mode: vip # vip|all|selector: how to resolve cluster DNS?
  704. # dns_selector: '[]' # if dns_mode == vip, filter instances been resolved
  705. #================================================================#
  706. # VARS: REDIS #
  707. #================================================================#
  708. # REDIS are not enabled by default
  709. #-----------------------------------------------------------------
  710. # REDIS_IDENTITY
  711. #-----------------------------------------------------------------
  712. #redis_cluster: redis-test # name of this redis cluster @ cluster level
  713. #redis_node: 1 # redis node identifier, integer sequence @ node level
  714. #redis_instances: {} # redis instances definition of this redis node @ node level
  715. #-----------------------------------------------------------------
  716. # REDIS_NODE
  717. #-----------------------------------------------------------------
  718. redis_fs_main: /data # main fs mountpoint for redis data
  719. redis_exporter_enabled: true # install redis exporter on redis nodes?
  720. redis_exporter_port: 9121 # default port for redis exporter
  721. redis_exporter_options: '' # default cli args for redis exporter
  722. #-----------------------------------------------------------------
  723. # REDIS_PROVISION
  724. #-----------------------------------------------------------------
  725. redis_safeguard: false # force redis_clean = abort if true
  726. redis_clean: true # abort|skip|clean if redis server already exists
  727. redis_rmdata: true # remove redis data when purging redis server?
  728. redis_mode: standalone # standalone,cluster,sentinel
  729. redis_conf: redis.conf # config template path (except sentinel)
  730. redis_bind_address: '0.0.0.0' # bind address, empty string turns to inventory_hostname
  731. redis_max_memory: 1GB # max memory used by each redis instance
  732. redis_mem_policy: allkeys-lru # memory eviction policy
  733. redis_password: '' # masterauth & requirepass password, disable by empty string
  734. redis_rdb_save: ['1200 1'] # redis rdb save directives, disable with empty list
  735. redis_aof_enabled: false # redis aof enabled
  736. redis_rename_commands: {} # rename dangerous commands
  737. redis_cluster_replicas: 1 # how many replicas for a master in redis cluster ?
  738. ...

Last modified 2022-06-20: add timescaledb (3c335f4)