Create Database

# 在 pg-test 集群创建名为 test 的数据库
./pgsql-createdb.yml -l pg-test -e pg_database=test

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


#=============================================================================#
# How to create database ?
#   1. define database in your configuration file! <cluster>.vars.pg_databases
#   2. execute this playbook with pg_database set to your new database.name
#   3. run playbook on target cluster
# It essentially does:
#   1. create sql file in /pg/tmp/pg-db-{{ database.name }}.sql
#   2. create database on primary instance with that sql
#   3. if {{ database.pgbouncer }}, add to all cluster members and reload
#=============================================================================#

- name: Create Database In Cluster
  become: yes
  hosts: all
  gather_facts: no
  vars:

    ##################################################################################
    # IMPORTANT: Change this or use cli-arg to specify target database in inventory  #
    ##################################################################################
    pg_database: test

  tasks:
    #------------------------------------------------------------------------------
    # pre-flight check: validate pg_database and database definition
    # ------------------------------------------------------------------------------
    - name: Preflight
      block:
        - name: Check parameter pg_database
          connection: local
          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
          connection: local
          set_fact:
            pg_database_definition={{ pg_databases | json_query(pg_database_definition_query) }}
          vars:
            pg_database_definition_query: "[?name=='{{ pg_database }}'] | [0]"

        # print database definition
        - debug:
            msg: "{{ pg_database_definition }}"

        - 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

    #------------------------------------------------------------------------------
    # Create database on cluster primary and add pgbouncer entry to cluster members
    #------------------------------------------------------------------------------
    # create database according to database definition
    - include_tasks: roles/postgres/tasks/createdb.yml
      vars:
        database: "{{ pg_database_definition }}"


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


...

./pgsql-createdb.yml -l pg-test -e pg_database=test

$ ./pgsql-createdb.yml -l pg-test -e pg_database=test
[WARNING]: Invalid characters were found in group names but not replaced, use -vvvv to see details

PLAY [Create Database In Cluster] *************************************************************************************************************************************************

TASK [Check parameter pg_database] ************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.12] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.13] => {
    "changed": false,
    "msg": "All assertions passed"
}

TASK [Fetch database definition] **************************************************************************************************************************************************
ok: [10.10.10.11]
ok: [10.10.10.12]
ok: [10.10.10.13]

TASK [debug] **********************************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "msg": {
        "name": "test"
    }
}
ok: [10.10.10.12] => {
    "msg": {
        "name": "test"
    }
}
ok: [10.10.10.13] => {
    "msg": {
        "name": "test"
    }
}

TASK [Check database definition] **************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.12] => {
    "changed": false,
    "msg": "All assertions passed"
}
ok: [10.10.10.13] => {
    "changed": false,
    "msg": "All assertions passed"
}

TASK [include_tasks] **************************************************************************************************************************************************************
included: /Volumes/Data/pigsty/roles/postgres/tasks/createdb.yml for 10.10.10.11, 10.10.10.12, 10.10.10.13

TASK [debug] **********************************************************************************************************************************************************************
ok: [10.10.10.11] => {
    "msg": {
        "name": "test"
    }
}
skipping: [10.10.10.12]
skipping: [10.10.10.13]

TASK [Render database test creation sql] ******************************************************************************************************************************************
skipping: [10.10.10.12]
skipping: [10.10.10.13]
changed: [10.10.10.11]

TASK [Render database test baseline sql] ******************************************************************************************************************************************
skipping: [10.10.10.11]
skipping: [10.10.10.12]
skipping: [10.10.10.13]

TASK [Execute database test creation command] *************************************************************************************************************************************
skipping: [10.10.10.12]
skipping: [10.10.10.13]
changed: [10.10.10.11]

TASK [Execute database test creation sql] *****************************************************************************************************************************************
skipping: [10.10.10.12]
skipping: [10.10.10.13]
changed: [10.10.10.11]

TASK [Execute database test creation sql] *****************************************************************************************************************************************
skipping: [10.10.10.11]
skipping: [10.10.10.12]
skipping: [10.10.10.13]

TASK [Add pgbouncer busniess database] ********************************************************************************************************************************************
changed: [10.10.10.11]
changed: [10.10.10.13]
changed: [10.10.10.12]

TASK [Reload pgbouncer to add database] *******************************************************************************************************************************************
changed: [10.10.10.11]
changed: [10.10.10.13]
changed: [10.10.10.12]

PLAY RECAP ************************************************************************************************************************************************************************
10.10.10.11                : ok=11   changed=5    unreachable=0    failed=0    skipped=2    rescued=0    ignored=0
10.10.10.12                : ok=7    changed=2    unreachable=0    failed=0    skipped=6    rescued=0    ignored=0
10.10.10.13                : ok=7    changed=2    unreachable=0    failed=0    skipped=6    rescued=0    ignored=0

playbook: ./pgsql-createdb.yml

  play #1 (all): Create Database In Cluster	TAGS: []
    tasks:
      Check parameter pg_database	TAGS: []
      Fetch database definition	TAGS: []
      debug	TAGS: []
      Check database definition	TAGS: []
      include_tasks	TAGS: []
      Reload pgbouncer to add database	TAGS: [pgbouncer_reload]