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

...