pigsty/pgsql.yml at main · zu1kbackup/pigsty · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File      :   pgsql.yml
# Desc      :   init postgres cluster/instance
# Ctime     :   2020-05-12
# Mtime     :   2025-12-12
# Path      :   pgsql.yml
# Docs      :   https://doc.pgsty.com/pgsql/playbook#pgsqlyml
# License   :   Apache-2.0 @ https://pigsty.io/docs/about/license/
# Copyright :   2018-2026  Ruohang Feng / Vonng (rh@vonng.com)
#==============================================================#
- name: PGSQL
  become: yes
  hosts: all
  gather_facts: no
  roles:
    - { role: node_id    ,tags: [id,node-id] } # get node identity (always)
    - { role: pg_id      ,tags: [id,pg-id]   } # get pgsql identity (always)
    - { role: pgsql      ,tags: pgsql        } # init postgres cluster/instance
    - { role: pg_monitor ,tags: monitor      } # init postgres monitoring


#--------------------------------------------------------------#
# Usage
#--------------------------------------------------------------#
#  You have to pgsql-rm before running this on initialized hosts
#
#  Init a cluster  (e.g. pg-meta)
#    ./pgsql.yml -l pg-meta
#
#  Add/ReInit a replica to cluster (e.g. add 10.10.10.13 of pg-test)
#    ./pgsql.yml -l 10.10.10.13   # refresh pg_services after expansion
#    ./pgsql.yml -l 'pg-test,!10.10.10.13' -t pg_service
#
#  Refresh Postgres Cluster Service (e.g. pg-test)
#    ./pgsql.yml -l pg-test --tags=pg_service
#
#  Reload Postgres/Pgbouncer HBA Rules (e.g. pg-test)
#    ./pgsql.yml -l pg-test --tags=pg_hba,pgbouncer_hba,pgbouncer_reload -e pg_reload=true
#
#--------------------------------------------------------------#
# Utils
#--------------------------------------------------------------#
#
#  bin/pgsql-add pg-meta             # init pgsql cluster pg-meta
#  bin/pgsql-add 10.10.10.10         # init pgsql instance 10.10.10.10
#  bin/pgsql-add pg-test 10.10.10.13 # add 10.10.10.13 to cluster pg-test (with service refresh)
#  bin/pgsql-svc pg-test             # refresh haproxy service of pg-test (when membership changed)
#  bin/pgsql-hba pg-test             # reload pg/pgb hba rules of pg-test (when role/hba changed)
#
#--------------------------------------------------------------#
# Tasks
#--------------------------------------------------------------#
# pg_install              : install postgres packages & extensions
#   - pg_dbsu             : setup os user sudo for postgres dbsu
#     - pg_dbsu_create    : exchange dbsu ssh keys
#     - pg_dbsu_sudo      : exchange dbsu ssh keys
#     - pg_ssh            : exchange dbsu ssh keys
#   - pg_pkg              : install postgres packages
#     - pg_pre            : pre-install task
#     - pg_ext            : install postgres extension packages
#     - pg_post           : post-install task
#   - pg_link             : link pgsql version bin to /usr/pgsql
#   - pg_path             : add pgsql bin to system path
#   - pg_dir              : create postgres directories and setup fhs
#   - pg_bin              : sync /pg/bin scripts
#   - pg_alias            : write pgsql/psql alias
#   - pg_dummy            : create dummy placeholder file
#
# pg_bootstrap            : bootstrap postgres cluster
#   - pg_config           : generate postgres config
#     - pg_conf           : generate patroni config
#     - pg_key            : generate pgsodium key
#   - pg_cert             : issues certificates for postgres
#     - pg_cert_private   : check pg private key existence
#     - pg_cert_issue     : signing pg server certs
#     - pg_cert_copy      : copy key & certs to pg node
#   - pg_launch           : launch patroni primary & replicas  (patroni)
#     - pg_watchdog       : grant watchdog permission to postgres
#     - pg_primary        : launch patroni/postgres primary
#     - pg_init           : init pg cluster with roles/templates
#     - pg_pass           : write .pgpass file to pg home
#     - pg_replica        : launch patroni/postgres replicas
#     - pg_hba            : generate pg HBA rules
#     - patroni_reload    : reload patroni config
#     - pg_patroni        : pause or remove patroni if necessary
#
# pg_provision            : provision postgres business users & databases
#  - pg_user              : provision postgres business users
#     - pg_user_config    : render create user sql
#     - pg_user_create    : create user on postgres
#  - pg_db                : provision postgres business databases
#     - pg_db_config      : render create database sql
#     - pg_db_create      : create database on postgres
#
# pg_backup               : init postgres PITR backup
#   - pgbackrest          : setup pgbackrest for backup
#     - pgbackrest_config : generate pgbackrest config
#     - pgbackrest_init   : init pgbackrest repo
#     - pgbackrest_backup : make a initial backup after bootstrap
#
# pg_access               : init postgres service access, pool, dns, vip, svc
#  - pgbouncer            : deploy a pgbouncer sidecar with postgres
#    - pgbouncer_dir      : create pgbouncer directories
#    - pgbouncer_config   : generate pgbouncer config
#      -  pgbouncer_hba   : generate pgbouncer hba config
#      -  pgbouncer_user  : generate pgbouncer userlist
#    -  pgbouncer_launch  : launch pgbouncer pooling service
#    -  pgbouncer_reload  : reload pgbouncer config
#  - pg_vip               : bind vip to pgsql primary with vip-manager
#    - pg_vip_config      : generate config for vip-manager
#    - pg_vip_launch      : launch vip-manager to bind vip
#  - pg_dns               : register dns name to infra dnsmasq
#    - pg_dns_ins         : register pg instance name
#    - pg_dns_cls         : register pg cluster name
#  - pg_service           : expose pgsql service with haproxy
#    - pg_service_config  : generate local haproxy config for pg services
#    - pg_service_reload  : expose postgres services with haproxy
#
# pg_monitor              : setup pgsql monitor and register to infra
#   - pg_exporter         : config & launch pg_exporter
#   - pgbouncer_exporter  : config & launch pgbouncer_exporter
#   - pgbackrest_exporter : config & launch pgbackrest_exporter
#   - pg_register         : register pgsql to monitoring/logging/datasource
#     - add_metrics       : register pg as victoria monitor targets
#     - add_logs          : register pg as vector logging source
#     - add_ds            : register pg database as grafana datasource
#--------------------------------------------------------------#
...