||Deploy a PostgreSQL cluster, or cluster expand||
||Destroy PostgreSQL cluster, or cluster downsize||
||Create PostgreSQL business users||
||Create a PostgreSQL Business Database||
||Monly mode, with access to existing PostgreSQL instances or RDS||
||Generate PostgreSQL semi-automatic database migration solution (Beta)||
||Reuse the PG to deploy a set of MatrixDB clusters (Beta)||
After completing the infra initialization, users can use
pgsql.yml 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 # Perform cluster initialization on all machines in the list (Danger!) ./pgsql.yml -l pg-test # Perform cluster initialization on the machines under the pg-test group (recommended!) ./pgsql.yml -l pg-meta,pg-test # Initialize both pg-meta and pg-test clusters ./pgsql.yml -l 10.10.10.11 # Initialize the instance on the machine 10.10.10.11
This playbook accomplishes the following.
- Install, deploy, and initialize PostgreSQL, Pgbouncer, Patroni (
- Install the PostgreSQL monitor (
- Install and deploy Haproxy and VIP, expose services (
- Register the database instance to the infra to be monitored (
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
-limit|-l is correct.
- It is strongly recommended to add the
-lparameter 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.
Patronitakes 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 onlinetask via Ansible’s
-start-at-taskafter the replica has been automatically crafted. Please refer to SOP for details.
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.
pg_clean: disabled by default,
pgsql.ymlwill purge running PostgreSQL during node init.
When running pg exists,
pgsql.yml 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.
For example, if you want to perform only service initialization, you can use the following command.
./pgsql.yml --tags=service # Refreshing the service definition of a cluster
The common subsets of commands are as follows.
# Infra initialization ./pgsql.yml --tags=infra # Complete infra initialization, including machine node initialization and DCS deployment # Database initialization ./pgsql.yml --tags=pgsql # Complete database deployment: database, monitoring, services ./pgsql.yml --tags=postgres # Complete database deployment ./pgsql.yml --tags=monitor # Complete monitoring deployment ./pgsql.yml --tags=service # Complete load balancing deployment（Haproxy & VIP） ./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.
./pgsql.yml --tags=node_admin # Create an admin user on the target node # If the current administrator does not have ssh to the target node, you can use another user with ssh to create an administrator (enter the password) ./pgsql.yml --tags=node_admin -e ansible_user=other_admin -k ./pgsql.yml --tags=pg_scripts # Update the /pg/bin/ directory script ./pgsql.yml --tags=pg_hba # Regenerate and apply cluster HBA rules ./pgsql.yml --tags=pgbouncer # Reset Pgbouncer ./pgsql.yml --tags=pg_user # Full volume refresh business users ./pgsql.yml --tags=pg_db # Full volume refresh of business database ./pgsql.yml --tags=register_consul # Register the Consul service locally with the target instance (local execution) ./pgsql.yml --tags=register_prometheus # Register monitoring objects in Prometheus (proxy to all Meta nodes for execution) ./pgsql.yml --tags=register_grafana # Register monitoring objects in Grafana (only once) ./pgsql.yml --tags=register_nginx # Register a LB with Nginx (proxy to all Meta nodes for execution) # Redeploy monitoring using binary installation ./pgsql.yml --tags=monitor -e exporter_install=binary # Refresh the service definition of the cluster (changes in cluster membership or service definition) ./pgsql.yml --tags=haproxy_config,haproxy_reload
Database Destruction: Remove existing database cluster or instance, reclaim node:
pgsql-remove.yml is the reverse of
pgsql.yml and will do the following ：
- Unregister the database instance from the infra（
- Stop the LB, service component（
- Removal of monitoring system components（
- Remove Pgbouncer, Patroni, Postgres（
- Remove database dir（
- Remove Package（
The playbook has two command-line options to remove the database dir and packages (the default destruction does not remove data and packages).
rm_pgdata: false # remove postgres data? false by default rm_pgpkgs: false # uninstall pg_packages? false by default
./pgsql-remove.yml -l pg-test # Destruction pg-test cluster ./pgsql-remove.yml -l 10.10.10.13 # Destruction instance 10.10.10.13 (pg-test.pg-test-3) ./pgsql-remove.yml -l 10.10.10.13 -e rm_pgdata=true # Destruction and remove the data dir (slow) ./pgsql-remove.yml -l 10.10.10.13 -e rm_pkgs=true # Destruction and remove the installed PG-related packages
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.
Please refer to the section Database for the creation of the database.
# Create a database named test in the pg-test cluster ./pgsql-createdb.yml -l pg-test -e pg_database=test
Simplify commands using wrapper scripts:
bin/createdb <pg_cluster> <dbname>
Create business users: Create a new user or modify an existing user in an existing cluster：
Please refer to the section User for the creation of business users.
# Create a user named test in the pg-test cluster ./pgsql-createuser.yml -l pg-test -e pg_user=test
Simplify commands using wrapper scripts.
bin/createuser <pg_cluster> <username>
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.
Dedicated playbook for performing monitoring deployments. See monly deployments for details.
Dedicated playbook for deploying MatrixDB. See Deploying MatrixDB Cluster for details.
Playbook for automated database migration, still in Beta status. See database cluster migration for details.
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.