Percona is known for its software solutions for most Databases. It gives a single solution with the best and most critical enterprise components from the open-source community.
In terms of monitoring, it provides Percona Monitoring and Management (PMM) tool which is a best-of-breed monitoring solution. It helps you reduce complexity, optimise performance, and improve the security of your business-critical database environments.
Percona is a well-known solution for MySQL databases, including monitoring and performance tools that help manage and gain insight into open source database environments. It also provides an XtraBackup tool for MySQL which ease the problem of incremental backup of DB efficiently.
Percona for PostgreSQL
For PostgreSQL, it gives the best and most critical enterprise components in a single distribution by providing the extensions to solve essential practical tasks efficiently. A few of them are listed below:
- pgAudit provides detailed session or object audit logging via the standard PostgreSQL logging facility
- pgAudit set_user – The
set_userpart ofpgAuditextension provides an additional layer of logging and control when unprivileged users must escalate themselves to superuser or object owner roles in order to perform needed maintenance tasks. - pgBackRest is a backup and restore solution for PostgreSQL
- pg_stat_monitor collects and aggregates statistics for PostgreSQL and provides histogram information.
Percona PostgreSQL with OSM Ansible role
Our OT-OSM Ansible role will automate the installation and configuration of PostgreSQL DB on Ubuntu 18 and above.
Running this role will perform the following tasks on Ansible hosts:
- Install PostgreSQL with Percona support for version 11 or above
- Cluster Configuration for PostgreSQL on machines
- Create User/Database with custom privileges provided by the user
- Add extension tools supported by Percona
Points to note before running role on target machines
Variables
Role has defined variables at default or vars location to externalise some configuration and make role more reusable.
At default
- PostgreSQL configuration
Variables used for PostgreSQL connection settings and version
- Database creation configuration
Variables used to create DB with a set of options used in PostgreSQL like Collate, Ctype, max_concurrent_connection etc.
- User creation configuration
Variables used to create users in PostgreSQL.
- Replication configuration
Variables used to configure replication in DB
At vars
- PostgreSQL extension details
Variables used to define a list of Percona extensions for PostgreSQL
Usages
There are multiple ways of executing the role according to the requirement
- To run complete role
# ansible-playbook -i hosts site.yml
This will install PostgreSQL and configure replication on servers with database and user creation when set true with values defined at defaults/main.yml
- To create database in PostgreSQL
# ansible-playbook -i hosts site.yml --tags "create_database"
This will create DB in PostgreSQL with details mentioned in list at defaults/main.yml
- To create users in PostgreSQL
ansible-playbook -i hosts site.yml --tags "create_user"
This will create users in PostgreSQL with name mentioned in the list at defaults/main.yml
Conclusion
The scope of role is the basic implementation of PostgreSQL Database with best practices for installation, configuration, read replica and user creation. For more information, you can go through the README.md.
Further, we will upgrade role with cluster failover and CIS Compliant configuration.
Let us know in the comment section about your experience with role and you can also look at other Ansible roles published by Opstree OSM at
Logo References
https://galaxy.ansible.com/
https://www.postgresql.org/
https://www.percona.com/
Blog Pundit: Bhupender Singh and Sanjeev Pandey
Opstree is an End to End DevOps solution provider
Connect Us