Setup Percona Postgresql Through the Awsesome(OSM) Ansible Role

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_user part of pgAudit extension 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s