#!/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

...