108 lines
7.5 KiB
YAML
108 lines
7.5 KiB
YAML
---
|
|
#--------------------------------------------------------------#
|
|
# Drop Business Database [pg_db_drop]
|
|
#--------------------------------------------------------------#
|
|
# state: absent -> drop only
|
|
# state: recreate -> drop then create
|
|
# skip system databases: postgres, template0, template1
|
|
#--------------------------------------------------------------#
|
|
- name: drop database {{ database.name }}
|
|
tags: [ pg_db, pg_db_drop ]
|
|
ignore_errors: true # should we fail here?
|
|
when:
|
|
- pg_role == 'primary'
|
|
- database.state is defined and database.state in ['absent', 'recreate']
|
|
- database.name not in ['postgres', 'template0', 'template1']
|
|
become_user: "{{ pg_dbsu|default('postgres') }}"
|
|
shell: |
|
|
{{ pg_bin_dir|default('/usr/pgsql/bin') }}/psql -h {{ pg_localhost|default('/var/run/postgresql') }} -p {{ pg_port|default(5432) }} -d postgres -AXtwe <<-'EOSQL'
|
|
{% if database.is_template is defined and database.is_template %}
|
|
ALTER DATABASE "{{ database.name }}" IS_TEMPLATE false;
|
|
{% endif %}
|
|
{% if pg_version|default(18)|int >= 13 %}
|
|
DROP DATABASE IF EXISTS "{{ database.name }}" WITH (FORCE);
|
|
{% else %}
|
|
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '{{ database.name }}' AND pid <> pg_backend_pid();
|
|
DROP DATABASE IF EXISTS "{{ database.name }}";
|
|
{% endif %}
|
|
EOSQL
|
|
args: { executable: /bin/bash }
|
|
|
|
|
|
#--------------------------------------------------------------#
|
|
# Generate Business Database Config [pg_db_config]
|
|
#--------------------------------------------------------------#
|
|
- name: generate postgres business database config
|
|
tags: [ pg_db , pg_db_config ]
|
|
when: pg_role == 'primary' and (database.state is not defined or database.state in ['create', 'recreate'])
|
|
block:
|
|
|
|
- name: render sql for database {{ database.name }}
|
|
tags: pg_db_config
|
|
template: src="pg-db.sql" dest=/pg/tmp/pg-db-{{ database.name }}.sql owner={{ pg_dbsu|default('postgres') }} group=postgres mode=0640
|
|
|
|
- name: copy baseline for database {{ database.name }}
|
|
tags: pg_db_config
|
|
when: database.baseline is defined
|
|
copy: src="{{ database.baseline }}" dest=/pg/tmp/pg-db-{{ database.name }}-baseline.sql owner={{ pg_dbsu|default('postgres') }} group=postgres mode=0640
|
|
|
|
|
|
#--------------------------------------------------------------#
|
|
# Create Business Database [pg_db_create]
|
|
#--------------------------------------------------------------#
|
|
- name: create postgres business database
|
|
tags: [ pg_db, pg_db_create ]
|
|
when: pg_role == 'primary' and (database.state is not defined or database.state in ['create', 'recreate'])
|
|
vars:
|
|
# which database to connect for admin operations (use template1 when template is postgres)
|
|
admin_db: "{{ 'template1' if (database.template is defined and database.template == 'postgres') else 'postgres' }}"
|
|
# need to kill template connections? (yes if using user database as template)
|
|
need_kill: "{{ database.template is defined and database.template is not none and database.template not in ['', 'template0', 'template1'] }}"
|
|
block:
|
|
|
|
- name: create database {{ database.name }}
|
|
tags: pg_db_create
|
|
ignore_errors: true # should we fail here?
|
|
become_user: "{{ pg_dbsu|default('postgres') }}"
|
|
shell: |
|
|
{% if database.state is not defined or database.state != 'recreate' %}
|
|
# check if database already exists (skip for recreate)
|
|
db_exists=$({{ pg_bin_dir|default('/usr/pgsql/bin') }}/psql -h {{ pg_localhost|default('/var/run/postgresql') }} -p {{ pg_port|default(5432) }} -d postgres -AXtwq -c "SELECT 1 FROM pg_database WHERE datname = '{{ database.name }}' LIMIT 1;")
|
|
if [[ -n "${db_exists}" ]]; then echo "database {{ database.name }} already exists, skip"; exit 0; fi
|
|
{% endif %}
|
|
{{ pg_bin_dir|default('/usr/pgsql/bin') }}/psql -h {{ pg_localhost|default('/var/run/postgresql') }} -p {{ pg_port|default(5432) }} -d {{ admin_db }} -AXtw <<-'EOSQL'
|
|
{% if need_kill %}SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '{{ database.template }}' AND pid <> pg_backend_pid();{% endif %}
|
|
CREATE DATABASE "{{ database.name }}"
|
|
{%- if database.owner is defined and database.owner is not none and database.owner != '' %} OWNER "{{ database.owner }}"{% endif %}
|
|
{%- if database.template is defined and database.template is not none and database.template != '' %} TEMPLATE "{{ database.template }}"{% endif %}
|
|
{%- if database.encoding is defined and database.encoding is not none and database.encoding != '' %} ENCODING '{{ database.encoding }}'{% endif %}
|
|
{%- if database.locale is defined and database.locale is not none and database.locale != '' %} LOCALE '{{ database.locale }}'{% endif %}
|
|
{%- if database.lc_collate is defined and database.lc_collate is not none and database.lc_collate != '' %} LC_COLLATE '{{ database.lc_collate }}'{% endif %}
|
|
{%- if database.lc_ctype is defined and database.lc_ctype is not none and database.lc_ctype != '' %} LC_CTYPE '{{ database.lc_ctype }}'{% endif %}
|
|
{%- if database.tablespace is defined and database.tablespace is not none and database.tablespace != '' %} TABLESPACE "{{ database.tablespace }}"{% endif %}
|
|
{%- if database.is_template is defined and database.is_template is not none %} IS_TEMPLATE {{ database.is_template|lower }}{% endif %}
|
|
{%- if database.strategy is defined and database.strategy is not none and database.strategy != '' and pg_version|default(18)|int >= 15 %} STRATEGY {{ database.strategy|upper }}{% endif %}
|
|
{%- if database.locale_provider is defined and database.locale_provider is not none and database.locale_provider != '' and pg_version|default(18)|int >= 15 %} LOCALE_PROVIDER '{{ database.locale_provider }}'{% endif %}
|
|
{%- if database.icu_locale is defined and database.icu_locale is not none and database.icu_locale != '' and pg_version|default(18)|int >= 15 %} ICU_LOCALE '{{ database.icu_locale }}'{% endif %}
|
|
{%- if database.icu_rules is defined and database.icu_rules is not none and database.icu_rules != '' and pg_version|default(18)|int >= 16 %} ICU_RULES '{{ database.icu_rules }}'{% endif %}
|
|
{%- if database.builtin_locale is defined and database.builtin_locale is not none and database.builtin_locale != '' and pg_version|default(18)|int >= 17 %} BUILTIN_LOCALE '{{ database.builtin_locale }}'{% endif %}
|
|
;
|
|
EOSQL
|
|
args: { executable: /bin/bash }
|
|
|
|
- name: provision database {{ database.name }}
|
|
tags: pg_db_create
|
|
ignore_errors: true
|
|
become_user: "{{ pg_dbsu|default('postgres') }}"
|
|
shell: |
|
|
{{ pg_bin_dir|default('/usr/pgsql/bin') }}/psql "{{ database.name }}" -h {{ pg_localhost|default('/var/run/postgresql') }} -p {{ pg_port|default(5432) }} -AXtwf /pg/tmp/pg-db-{{ database.name }}.sql >> /pg/tmp/pg-db-{{ database.name }}.log 2>&1
|
|
|
|
- name: load database {{ database.name }} baseline
|
|
tags: [ pg_db_create, pg_db_baseline ]
|
|
ignore_errors: true
|
|
become_user: "{{ pg_dbsu|default('postgres') }}"
|
|
shell: |
|
|
{{ pg_bin_dir|default('/usr/pgsql/bin') }}/psql "{{ database.name }}" -h {{ pg_localhost|default('/var/run/postgresql') }} -p {{ pg_port|default(5432) }} -AXtwf /pg/tmp/pg-db-{{ database.name }}-baseline.sql >> /pg/tmp/pg-db-{{ database.name }}-baseline.log 2>&1
|
|
when: database.baseline is defined
|
|
|
|
... |