pgsql-db.yml
Rama main · réplica de lectura del repositorio original.
#!/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_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_connlimit: 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
...