#!/usr/bin/env ansible-playbook
---
#==============================================================#
# File      :   pgsql-createdb.yml
# Ctime     :   2021-02-27
# Mtime     :   2021-07-06
# Desc      :   create database on existing cluster
# Deps      :   templates/pg-db.sql
# Path      :   pgsql-createdb.yml
# Copyright (C) 2018-2021 Ruohang Feng ([email protected])
#==============================================================#

#==============================================================#
# How to create new database on existing postgres cluster ?    #
#==============================================================#
#  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 pg_database
#      `pgsql-createdb.yml -l <pg_cluster> -e pg_database=<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. update /etc/pgbouncer/database.txt and reload pgbouncer if necessary
#   4. register database to grafana instance
#=============================================================================#
- name: Create new postgres database
  become: yes
  hosts: all
  gather_facts: no
  vars:

    # TODO (IMPORTANT): OVERWRITE THIS WITH CLI-ARG: `-e pg_database=<database.name>`
    pg_database: meta           # database NAME that will be created on target cluster
    register_datasource: true   # register newly created database to grafana ?

  tasks:
    #------------------------------------------------------------------------------
    # Preflight Check: validate pg_database and database definition
    # ------------------------------------------------------------------------------
    - name: Preflight
      tags: preflight
      connection: local
      block:
        - name: Validate pg_database
          assert:
            that:
              - pg_database is defined
              - pg_database != ''
              - pg_database != 'postgres'
            fail_msg: variable 'pg_database' should be specified to create target database

        - name: Fetch database definition
          set_fact:
            pg_database_definition={{ pg_databases | json_query(pg_database_definition_query) }}
          vars:
            pg_database_definition_query: "[?name=='{{ pg_database }}'] | [0]"

        - name: Check database definition
          assert:
            that:
              - pg_database_definition is defined
              - pg_database_definition != None
              - pg_database_definition != ''
              - pg_database_definition != {}
            fail_msg: database definition for {{ pg_database }} should exists in pg_databases

        - debug:
            msg: "{{ pg_database_definition }}"

    #------------------------------------------------------------------------------
    # Create database on cluster leader (inventory pg_role == 'primary')
    #------------------------------------------------------------------------------
    # create database according to database definition
    - include_tasks: roles/postgres/tasks/createdb.yml
      tags: createdb
      vars: { database: "{{ pg_database_definition }}" }

    #------------------------------------------------------------------------------
    # Reload Pgbouncer if Necessary (among entire cluster)
    #------------------------------------------------------------------------------
    - name: Reload pgbouncer to add database
      when: pg_database_definition.pgbouncer is not defined or pg_database_definition.pgbouncer|bool
      tags: reload
      systemd: name=pgbouncer state=reloaded enabled=yes daemon_reload=yes

    #------------------------------------------------------------------------------
    # Create datasource on grafana
    #------------------------------------------------------------------------------
    - name: Render datasource definition on meta node
      tags: datasource
      delegate_to: meta
      when: register_datasource|bool
      copy:
        dest: "/etc/pigsty/datasources/{{ insdb }}.json"
        content: |
          {
            "type": "postgres",
            "access": "proxy",
            "name": "{{ insdb }}",
            "url": "{{ inventory_hostname }}:{{ pg_port }}",
            "user": "{{ pg_monitor_username }}",
            "password": "{{ pg_monitor_password }}",
            "database": "{{ datname }}",
            "typeLogoUrl": "",
            "basicAuth": false,
            "basicAuthUser": "",
            "basicAuthPassword": "",
            "withCredentials": false,
            "isDefault": false,
            "jsonData": {
              "connMaxLifetime": 3600,
              "maxIdleConns": 1,
              "maxOpenConns": 8,
              "postgresVersion": {{ pg_version }}00,
              "sslmode": "disable",
              "tlsAuth": false,
              "tlsAuthWithCACert": false
            }
          }
        mode: 0600
      vars:
        datname: "{{ pg_database }}"
        insdb: "{{ pg_cluster }}-{{ pg_seq }}.{{ pg_database }}"

    - name: Load grafana datasource on meta node
      tags: datasource
      delegate_to: meta
      when: register_datasource|bool
      shell: |
        curl -X DELETE "{{ grafana_endpoint }}/api/datasources/name/{{ insdb }}" -u "{{ grafana_admin_username }}:{{ grafana_admin_password }}"  -H 'Content-Type: application/json' || true
        curl -X POST   "{{ grafana_endpoint }}/api/datasources/" -u "{{ grafana_admin_username }}:{{ grafana_admin_password }}"  -H 'Content-Type: application/json' -d @/etc/pigsty/datasources/{{ insdb }}.json || true
      vars:
        datname: "{{ pg_database }}"
        insdb: "{{ pg_cluster }}-{{ pg_seq }}.{{ datname }}"


...