pigsty/pgsql-db.yml at main · pgsty/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
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
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File      :   pgsql-db.yml
# Desc      :   create database on existing cluster
# Ctime     :   2021-02-27
# Mtime     :   2025-12-29
# Path      :   pgsql-db.yml
# Deps      :   templates/pg-db.sql
# Docs      :   https://pigsty.io/docs/pgsql/playbook
# License   :   Apache-2.0 @ https://pigsty.io/docs/about/license/
# Copyright :   2018-2026  Ruohang Feng / Vonng (rh@vonng.com)
#==============================================================#


#--------------------------------------------------------------#
# 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
#         state: create                   # optional, cloud be create (default), absent (drop db), recreate (drop then recreate)
#         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, current user if not specified
#         template: template1            # optional, template database to use, template1 by default
#         strategy: FILE_COPY            # optional, clone strategy: FILE_COPY or WAL_LOG (PG15+), default to PG's default
#         encoding: UTF8                 # optional, inherited from template / cluster if not defined (UTF8)
#         locale: C                      # optional, inherited from template / cluster if not defined (C)
#         lc_collate: C                  # optional, inherited from template / cluster if not defined (C)
#         lc_ctype: C                    # optional, inherited from template / cluster if not defined (C)
#         locale_provider: libc          # optional, locale provider: libc, icu, builtin (PG15+)
#         icu_locale: en-US              # optional, icu locale for icu locale provider (PG15+)
#         icu_rules: ''                  # optional, icu rules for icu locale provider (PG16+)
#         builtin_locale: C.UTF-8        # optional, builtin locale for builtin locale provider (PG17+)
#         tablespace: pg_default         # optional, default tablespace, pg_default by default
#         is_template: false             # optional, mark database as template, allowing clone by any user with CREATEDB privilege
#         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_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_connlimit: 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: true
  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 or Drop Postgres Database                [postgres]
    #----------------------------------------------------------#
    # create or drop database according to database definition
    # when db_def.state == 'absent', the database will be dropped
    - 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 (skip if state=absent)
    - include_tasks: roles/pg_monitor/tasks/register_grafana.yml
      ignore_errors: true
      tags: [ register, grafana, add_ds ]
      when: db_def.state is not defined or db_def.state != 'absent'
      vars: { database: "{{ db_def }}" }

    #----------------------------------------------------------#
    # Remove Grafana Datasource                     [unregister]
    #----------------------------------------------------------#
    # unregister grafana database datasource (only when state=absent)
    - include_tasks: roles/pg_remove/tasks/grafana.yml
      ignore_errors: true
      tags: [ unregister, grafana, rm_ds ]
      when: db_def.state is defined and db_def.state == 'absent'
      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: |
              # pgbouncer database list
              {% for db in pg_databases %}
              {% if ('pgbouncer' not in db or db.pgbouncer|bool) and (db.state is not defined or db.state != 'absent') %}
              {% if pgbouncer_redirect_to is defined and pgbouncer_redirect_to != '' %}{% set connstr = "host=" + pgbouncer_redirect_to|string %}{% else %}{% set connstr = "host=" + pg_localhost|default('/var/run/postgresql')|string %}{% 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

...