# Playbook > control primitives --- How to manage PostgreSQL cluster with ansible playbooks Pigsty has a series of playbooks for PostgreSQL: - [`pgsql.yml`](/docs/pgsql/playbook/#pgsqlyml) : Init HA PostgreSQL clusters or add new replicas. - [`pgsql-rm.yml`](/docs/pgsql/playbook/#pgsql-rmyml) : Remove PostgreSQL cluster, or remove replicas - [`pgsql-db.yml`](/docs/pgsql/playbook/#pgsql-dbyml) : Add a new business database to existing PostgreSQL cluster - [`pgsql-user.yml`](/docs/pgsql/playbook/#pgsql-useryml) : Add new business user to existing PostgreSQL cluster - [`pgsql-pitr.yml`](/docs/pgsql/playbook/#pgsql-pitryml) : Perform point-in-time recovery on existing PostgreSQL cluster - [`pgsql-monitor.yml`](/docs/pgsql/playbook/#pgsql-monitoryml) : Monitor remote PostgreSQL instance with local exporters - [`pgsql-migration.yml`](/docs/pgsql/playbook/#pgsql-migrationyml) : Generate Migration manual & scripts for existing PostgreSQL ------ ### Safeguard If you are afraid of accidentally deleting your PostgreSQL cluster, you can enable the safeguard feature. Setting the [`pg_safeguard`](/docs/pgsql/param#pg_safeguard) parameter to `true` will stop the [`pgsql-rm.yml`](#pgsql-rmyml) from running. **Before Pigsty v3.5, pgsql.yml can nuke your database with fat finger, use with caution!** Pigsty v3.5 remove the pg purge logic from [`pgsql.yml`](#pgsqlyml), So the only way to remove a PostgreSQL now is running [`pgsql-rm.yml`](#pgsql-rmyml). ------ ## `pgsql.yml` The [`pgsql.yml`](https://github.com/pgsty/pigsty/blob/v3.7.0/pgsql.yml) is used for init HA PostgreSQL clusters or adding new replicas. [![asciicast](/img/asciinema/pgsql.svg)](https://asciinema.org/a/566417) **This playbook contains the following subtasks**: ```yaml pg_install : # install postgres packages & extensions - pg_dbsu : # setup os user sudo for postgres dbsu - pg_dbsu_create : # exchange dbsu ssh keys - pg_dbsu_sudo : # exchange dbsu ssh keys - pg_ssh : # exchange dbsu ssh keys - pg_pkg : # install postgres packages - pg_ext : # install postgres extension packages - pg_link : # link pgsql version bin to /usr/pgsql - pg_path : # add pgsql bin to system path - pg_dir : # create postgres directories and setup fhs - pg_bin : # sync /pg/bin scripts - pg_alias : # write pgsql/psql alias - pg_dummy : # create dummy placeholder file ``` ```yaml pg_bootstrap : # bootstrap postgres cluster - pg_config : # generate postgres config - pg_conf : # generate patroni config - pg_key : # generate pgsodium key - pg_pitr_conf : # generate optional pitr config - pg_cert : # issues certificates for postgres - pg_cert_private : # check pg private key existence - pg_cert_issue : # signing pg server certs - pg_cert_copy : # copy key & certs to pg node - pg_launch : # launch patroni primary & replicas (patroni) - pg_watchdog : # grant watchdog permission to postgres - pg_primary : # launch patroni/postgres primary - pg_init : # init pg cluster with roles/templates - pg_pass : # write .pgpass file to pg home - pg_replica : # launch patroni/postgres replicas - pg_hba : # generate pg HBA rules - patroni_reload : # reload patroni config - pg_patroni : # pause or remove patroni if necessary ``` ```yaml pg_provision : # provision postgres business users & databases - pg_user : # provision postgres business users - pg_user_config : # render create user sql - pg_user_create : # create user on postgres - pg_db : # provision postgres business databases - pg_db_config : # render create database sql - pg_db_create : # create database on postgres ``` ```yaml pg_backup : # init postgres PITR backup - pgbackrest : # setup pgbackrest for backup - pgbackrest_config : # generate pgbackrest config - pgbackrest_init : # init pgbackrest repo - pgbackrest_backup : # make a initial backup after bootstrap ``` ```yaml pg_access : # init postgres service access, pool, dns, vip, svc - pgbouncer : # deploy a pgbouncer sidecar with postgres - pgbouncer_dir : # create pgbouncer directories - pgbouncer_config : # generate pgbouncer config - pgbouncer_hba : # generate pgbouncer hba config - pgbouncer_user : # generate pgbouncer userlist - pgbouncer_launch : # launch pgbouncer pooling service - pgbouncer_reload : # reload pgbouncer config - pg_vip : # bind vip to pgsql primary with vip-manager - pg_vip_config : # generate config for vip-manager - pg_vip_launch : # launch vip-manager to bind vip - pg_dns : # register dns name to infra dnsmasq - pg_dns_ins : # register pg instance name - pg_dns_cls : # register pg cluster name - pg_service : # expose pgsql service with haproxy - pg_service_config : # generate local haproxy config for pg services - pg_service_reload : # expose postgres services with haproxy ``` ```yaml pg_monitor : # setup pgsql monitor and register to infra - pg_exporter : # config & launch pg_exporter - pgbouncer_exporter : # config & launch pgbouncer_exporter - pgbackrest_exporter : # config & launch pgbackrest_exporter - register_prometheus : # register pg as prometheus monitor targets - register_grafana : # register pg database as grafana datasource ``` **Administration Tasks that use this playbook** - [`Create Cluster`](/docs/pgsql/admin#create-cluster) - [`Append Replica`](/docs/pgsql/admin#append-replica) - [`Reload Service`](/docs/pgsql/admin#reload-service) - [`Reload HBARule`](/docs/pgsql/admin#reload-hbarule) **Init Primary before Replicas** - you may have to run [`Reload HBARule`](/docs/pgsql/admin#reload-hbarule) and [`Append Replica`](/docs/pgsql/admin#append-replica) after replica init. - The wrap script `pgsql-add` will do this, check SOP: [Add Instance](/docs/pgsql/admin#append-replica) for details. - If you run this on the entire cluster, you don’t have to worry about this. **Init Upstream cluster before Standby Clusters** - If you are initializing a [standby cluster](/docs/pgsql/config#standby-cluster), you should make sure the upstream cluster is already initialized. ------ ## `pgsql-rm.yml` The playbook [`pgsql-rm.yml`](https://github.com/pgsty/pigsty/blob/v3.7.0/pgsql-rm.yml) can remove PostgreSQL cluster, or specific replicas from cluster. [![asciicast](/img/asciinema/pgsql-rm.svg)](https://asciinema.org/a/566418) **This playbook contains the following subtasks**: ```yaml pg_monitor : # remove registration in prometheus, grafana, nginx - prometheus : # remove monitor target from prometheus - grafana : # remove datasource from grafana - pg_exporter : # remove pg_exporter (postgres monitoring) - pgbouncer_exporter : # remove pgbouncer_exporter (pgbouncer monitoring) - pgbackrest_exporter : # remove pgbackrest_exporter (pgbackrest monitoring) ``` ```yaml pg_access : # remove pg service access - dns : # remove pg dns records - vip : # remove vip-manager - pg_service : # remove pg service from haproxy - pgbouncer : # remove pgbouncer connection middleware ``` ```yaml postgres : # remove postgres instances - pg_replica : # remove all replicas - pg_primary : # remove primary instance - pg_meta : # remove metadata from dcs ``` ```yaml pg_backup : # remove backup repo (disable with `pg_rm_backup=false`) pg_data : # remove postgres data (disable with `pg_rm_data=false`) pg_pkg : # uninstall pg packages (disable with `pg_rm_pkg=false`) - pg_ext : # uninstall postgres extensions alone ``` Some arguments can affect the behavior of this playbook: ```bash # remove pgsql cluster `pg-test` ./pgsql-rm.yml # remove all the postgres clusters (VERY DANGEROUS) ./pgsql-rm.yml -l pg-test # remove the cluster `pg-test` ./pgsql-rm.yml -e pg_safeguard=false # force disable safeguard, run this playbook anyway ./pgsql-rm.yml -e pg_rm_data=false # keep the data directory, do not remove it (keep the data) ./pgsql-rm.yml -e pg_rm_backup=false # do not purge postgres data by default (keep the backup repo) ./pgsql-rm.yml -e pg_rm_pkg=false # do not uninstall postgres packages by default (keep the packages) ``` **Administration Tasks that use this playbook** - [`Remove Replica`](/docs/pgsql/admin#remove-replica) - [`Remove Cluster`](/docs/pgsql/admin#remove-cluster) **Some notes about this playbook** **Do not run this playbook on single cluster primary directly when there are still replicas** - otherwise, the rest replicas will trigger automatic failover. - It won’t be a problem if you remove all replicas before removing primary. - If you run this on the entire cluster, you don’t have to worry about this. **Reload service after removing replicas from cluster** - It is a dead server, so it won’t affect the cluster service. - But you should [reload service](/docs/pgsql/admin#reload-service) in time to ensure the consistency between the environment and the config inventory. - When a replica is removed, it is still in the configuration file of the haproxy load balancer. ------ ## `pgsql-db.yml` The playbook [`pgsql-db.yml`](https://github.com/pgsty/pigsty/blob/v3.7.0/pgsql-db.yml) can add new business database to existing PostgreSQL cluster. Check admin SOP: [`Create Database`](/docs/pgsql/admin#create-database) ------ ## `pgsql-user.yml` The playbook [`pgsql-user.yml`](https://github.com/pgsty/pigsty/blob/v3.7.0/pgsql-user.yml) can add new business user to existing PostgreSQL cluster. Check admin SOP: [`Create User`](/docs/pgsql/admin#create-user) ------ ## `pgsql-pitr.yml` The playbook [`pgsql-pitr.yml`](https://github.com/pgsty/pigsty/blob/v3.7.0/pgsql-pitr.yml) can perform point-in-time recovery on existing PostgreSQL cluster. Check admin SOP: [`Restore`](/docs/pgsql/backup/restore) ------ ## `pgsql-monitor.yml` The playbook [`pgsql-monitor.yml`](https://github.com/pgsty/pigsty/blob/v3.7.0/pgsql-monitor.yml) can monitor remote postgres instance with local exporters. Check admin SOP: [`Monitor Postgres`](/docs/pgsql/monitor/#monitor-rds) ------ ## `pgsql-migration.yml` The playbook [`pgsql-migration.yml`](https://github.com/pgsty/pigsty/blob/v3.7.0/pgsql-migration.yml) can generate migration manual & scripts for existing PostgreSQL cluster. Check admin SOP: [Migration](/docs/pgsql/migration/)