/
pgsql-db.yml
executable file
·162 lines (148 loc) · 8.7 KB
/
pgsql-db.yml
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
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File : pgsql-db.yml
# Desc : create database on existing cluster
# Ctime : 2021-02-27
# Mtime : 2022-12-29
# Path : pgsql-db.yml
# Deps : templates/pg-db.sql
# Author : Ruohang Feng (rh@vonng.com)
# License : AGPLv3
#==============================================================#
#--------------------------------------------------------------#
# Usage
#--------------------------------------------------------------#
# 1. Define new database in inventory (cmdb or config)
# `all.children.<pg_cluster>.vars.pg_databases[i]`
#
# 2. Execute this playbook on target cluster with arg dbname
# `pgsql-db.yml -l <pg_cluster> -e dbname=<database.name>
#
# This playbook will:
# 1. create database sql definition on `/pg/tmp/pg-db-{{ database.name }}.sql`
# 2. execute database creation/update sql on cluster leader instance
# 3. register database to grafana datasource when `db.register_datasource`
# 4. update /etc/pgbouncer/database.txt and reload pgbouncer if necessary
#
#--------------------------------------------------------------#
# Utils
#--------------------------------------------------------------#
# Create pgsql database 'dbname' on pgsql cluster 'cls'
# bin/pgsql-db <cls> <dbname>
# bin/pgsql-db pg-meta meta
#--------------------------------------------------------------#
# Example
#--------------------------------------------------------------#
# pg-meta:
# vars:
# pg_databases: # define business databases on this cluster, array of database definition
# - name: meta # REQUIRED, `name` is the only mandatory field of a database definition
# baseline: cmdb.sql # optional, database sql baseline path, (relative path among ansible search path, e.g files/)
# pgbouncer: true # optional, add this database to pgbouncer database list? true by default
# schemas: [pigsty] # optional, additional schemas to be created, array of schema names
# extensions: # optional, additional extensions to be installed: array of `{name[,schema]}`
# - { name: postgis , schema: public } # install postgis on schema `public`
# - { name: timescaledb } # install timescaledb extension
# comment: pigsty meta database # optional, comment string for this database
# owner: postgres # optional, database owner, postgres by default
# template: template1 # optional, which template to use, template1 by default
# encoding: UTF8 # optional, database encoding, UTF8 by default. (MUST same as template database)
# locale: C # optional, database locale, C by default. (MUST same as template database)
# lc_collate: C # optional, database collate, C by default. (MUST same as template database)
# lc_ctype: C # optional, database ctype, C by default. (MUST same as template database)
# tablespace: pg_default # optional, default tablespace, 'pg_default' by default.
# allowconn: true # optional, allow connection, true by default. false will disable connect at all
# revokeconn: false # optional, revoke public connection privilege. false by default. (leave connect with grant option to owner)
# register_datasource: true # optional, register this database to grafana datasources? true by default
# connlimit: -1 # optional, database connection limit, default -1 disable limit
# pool_auth_user: dbuser_meta # optional, all connection to this pgbouncer database will be authenticated by this user
# pool_mode: transaction # optional, pgbouncer pool mode at database level, default transaction
# pool_size: 64 # optional, pgbouncer pool size at database level, default 64
# pool_size_reserve: 32 # optional, pgbouncer pool size reserve at database level, default 32
# pool_size_min: 0 # optional, pgbouncer pool size min at database level, default 0
# pool_max_db_conn: 100 # optional, max database connections at database level, default 100
# - { name: grafana ,owner: dbuser_grafana ,revokeconn: true ,comment: grafana primary database }
# - { name: bytebase ,owner: dbuser_bytebase ,revokeconn: true ,comment: bytebase primary database }
# - { name: kong ,owner: dbuser_kong ,revokeconn: true ,comment: kong the api gateway database }
# - { name: gitea ,owner: dbuser_gitea ,revokeconn: true ,comment: gitea meta database }
# - { name: wiki ,owner: dbuser_wiki ,revokeconn: true ,comment: wiki meta database }
#--------------------------------------------------------------#
- name: PGSQL DB
become: yes
hosts: all
gather_facts: no
tasks:
#----------------------------------------------------------#
# Validate dbname and database definition [preflight]
# ---------------------------------------------------------#
- name: preflight
tags: [ preflight , always ]
connection: local
block:
- name: validate dbname parameter
assert:
that:
- dbname is defined
- dbname != ''
- dbname != 'postgres'
fail_msg: variable 'pg_database' should be specified (-e dbname=<name>)
- name: get database definition
set_fact: db_def={{ pg_databases | json_query(db_def_query) }}
vars: { db_def_query: "[?name=='{{ dbname }}'] | [0]" }
- name: validate database definition
assert:
that:
- db_def is defined
- db_def != None
- db_def != ''
- db_def != {}
fail_msg: define database {{ dbname }} in pg_databases first
- debug:
msg: "{{ db_def }}"
#----------------------------------------------------------#
# Create Postgres Database [postgres]
# ---------------------------------------------------------#
# create database according to database definition
- include_tasks: roles/pgsql/tasks/database.yml
tags: postgres
when: pg_role == 'primary'
vars: { database: "{{ db_def }}" }
#----------------------------------------------------------#
# Create Grafana Datasource [grafana]
# ---------------------------------------------------------#
# register grafana database as grafana datasource
- include_tasks: roles/pgsql/tasks/register_grafana.yml
ignore_errors: yes
tags: [ register, grafana ]
vars: { database: "{{ db_def }}" }
#----------------------------------------------------------#
# Refresh Pgbouncer Database Configuration [pgbouncer]
# ---------------------------------------------------------#
- name: refresh pgbouncer databases
tags: pgbouncer
when: db_def.pgbouncer is not defined or db_def.pgbouncer|bool
block:
- name: render pgbouncer database.txt
copy:
dest: /etc/pgbouncer/database.txt
owner: "{{ pg_dbsu|default('postgres') }}"
group: postgres
mode: 0600
content: |
{% for db in pg_databases %}
{% if 'pgbouncer' not in db or db.pgbouncer|bool %}
{% set connstr = "host=" + pg_localhost|default('/var/run/postgresql')|string %}
{% if pgbouncer_redirect_to is defined and pgbouncer_redirect_to != '' %}{% set db_host = "host=" + pgbouncer_redirect_to %}{% endif %}
{% if 'pool_auth_user' in db %}{% set connstr = connstr + " auth_user=" + db.pool_auth_user|string %}{% endif %}
{% if 'pool_mode' in db %}{% set connstr = connstr + " pool_mode=" + db.pool_mode|string %}{% endif %}
{% if 'pool_size' in db %}{% set connstr = connstr + " pool_size=" + db.pool_size|string %}{% endif %}
{% if 'pool_size_min' in db %}{% set connstr = connstr + " min_pool_size=" + db.pool_size_min|string %}{% endif %}
{% if 'pool_reserve' in db %}{% set connstr = connstr + " reserve_pool=" + db.pool_reserve|string %}{% endif %}
{% if 'pool_connlimit' in db %}{% set connstr = connstr + " max_db_connections=" + db.pool_connlimit|string %}{% endif %}
{{ "%-27s" | format(db.name) }} = {{ connstr }}
{% endif %}
{% endfor %}
- name: reload pgbouncer
systemd: name=pgbouncer state=reloaded enabled=yes daemon_reload=yes
...