PGSQL Playbook

    pgsql

    After completing the infra initialization, users can use to complete the initialization of the database cluster.

    Complete the cluster definition in the Pigsty configuration file and then apply the changes to the environment by executing pgsql.yml.

    This playbook accomplishes the following.

    • Install, deploy, and initialize PostgreSQL, Pgbouncer, Patroni (postgres).
    • Install the PostgreSQL monitor (monitor).
    • Register the database instance to the infra to be monitored (register).

    This playbook can be misused to accidentally delete the database, as initializing the database will erase the existing database.

    The insurance param prevents accidental deletion by allowing automatic aborting or skipping of high-risk operations during initialization when an existing running instance is detected.

    Nevertheless, when using pgsql.yml, double-check that -tags|-t and -limit|-l is correct.

    • It is strongly recommended to add the -l parameter to the execution to limit the scope of the command execution.
    • When performing initialization for a replica, the user must ensure that the primary has completed initialization.
    • If Patroni takes too long to pull up a replica when a cluster is expanded, the Ansible playbook may abort due to a timeout. (However, making the replica will continue, for example, in scenarios where making the replica takes more than one day).
    • It is possible to perform subsequent steps from the -Wait for patroni replica online task via Ansible’s -start-at-task after the replica has been automatically crafted. Please refer to SOP for details.

    SafeGuard

    Pigsty provides a SafeGuard to avoid purging running PostgreSQL instances with fat fingers. There are two parameters.

    • pg_safeguard: Disabled by default, if enabled, running PostgreSQL will not be purged by any circumstance.
    • : disabled by default, pgsql.yml will purge running PostgreSQL during node init.

    When running pg exists, will act as:

    When running pg exists, pgsql-remove.yml will act as:

    An ansible’s tagging mechanism can select a subset of the execution playbook.

    1. ./pgsql.yml --tags=service      # Refreshing the service definition of a cluster

    The common subsets of commands are as follows.

    1. # Infra initialization
    2. ./pgsql.yml --tags=infra        # Complete infra initialization, including machine node initialization and DCS deployment
    5. # Database initialization
    6. ./pgsql.yml --tags=pgsql        # Complete database deployment: database, monitoring, services
    8. ./pgsql.yml --tags=postgres     # Complete database deployment
    9. ./pgsql.yml --tags=monitor      # Complete monitoring deployment
    10. ./pgsql.yml --tags=register     # Registering services to the infra

    Daily management tasks

    Daily management can also be used ./pgsql.yml to modify the state of the cluster. The common command subsets are as follows.

    Database Destruction: Remove existing database cluster or instance, reclaim node: pgsql-remove.yml.

    The is the reverse of and will do the following ：

    • Unregister the database instance from the infra（register）
    • Stop the LB, service component（service）
    • Removal of monitoring system components（monitor）
    • Remove Pgbouncer, Patroni, Postgres（postgres）
    • Remove database dir（rm_pgdata: true）
    • Remove Package（rm_pkgs: true）

    The playbook has two command-line options to remove the database dir and packages (the default destruction does not remove data and packages).

    1. rm_pgdata: false        # remove postgres data? false by default
    2. rm_pgpkgs: false        # uninstall pg_packages? false by default

    Playbook - 图2

    1. ./pgsql-remove.yml -l pg-test          # Destruction pg-test cluster
    2. ./pgsql-remove.yml -l 10.10.10.13      # Destruction instance 10.10.10.13 (pg-test.pg-test-3)
    3. ./pgsql-remove.yml -l 10.10.10.13 -e rm_pgdata=true # Destruction and remove the data dir (slow)
    4. ./pgsql-remove.yml -l 10.10.10.13 -e rm_pkgs=true   # Destruction and remove the installed PG-related packages

    pgsql-createdb

    Created business database: Create a new database in an existing cluster or modify a current database: .

    To ensure that, the author recommends creating a new database in an existing cluster via a playbook or scripting tool.

    • The inventory is consistent with the actual situation.
    • Pgbouncer connection pools are consistent with the database.
    • The data sources registered in Grafana are consistent with the actual situation.

    Daily management

    Please refer to the section for the creation of the database.

    Simplify commands using wrapper scripts:

    1. bin/createdb <pg_cluster> <dbname>

    Playbook - 图4

    Please refer to the section User for the creation of business users.

    1. ./pgsql-createuser.yml -l pg-test -e pg_user=test

    Simplify commands using wrapper scripts.

    Note that the user-specified by pg_user must already be in the definition of the cluster pg_users. Otherwise, an error will be reported.

    pgsql-monly

    Dedicated playbook for performing monitoring deployments. See monly deployments for details.

    Dedicated playbook for deploying MatrixDB. See Deploying MatrixDB Cluster for details.

    Playbook - 图6

    pgsql-migration

    Playbook for automated database migration, still in Beta status. See database cluster migration for details.