Ansible Collection for PostgreSQL and EDB Components

In this blog post, we will do a quick introduction of an Ansible Collection we have built with one goal: “Making PostgreSQL Easier.”
 

Motivation

Depending on the requirements, architectures for PostgreSQL generally involve more than just a database instance: backup and recovery, service high-availability, connection pooling, monitoring, etc. The deployment and configuration of all the components required by the target architecture can take a lot of time. In DevOps processes, where the same environment gets set up repeatedly for development, test, and production manual configuration is impossible.

In addition to being time-consuming, manually deploying multiple environments in the same way can lead to mistakes: the more manual tasks you have, the more the risk of error increases.

It is precisely to address these concerns that over the past ten years, a multitude of infrastructure as code (IaC) tools have emerged and are now key components of most IT toolboxes. Ansible is one of the most popular deployment automation tools.

Therefore, we decided to build and release under an open source license the Ansible collection edb_postgres. This Ansible collection will help DBAs and users in the deployment of individual components or write a playbook to deploy a complete architecture.

As an Open Source project, we welcome contributions. Source code is available on GitHub

 

Ansible Roles

The edb_postgres collection brings the following capabilities, which we packaged into many roles:

  • setup_repo
    This role can help in the configuration of EDB and community package repositories.
  • install_dbserver
    The install_dbserver can help in deploying EDB Enterprise Postgres Advanced Server and PostgreSQL.
  • init_dbserver
    This role helps in initializing PostgreSQL on the Primary server. In other words, DBA can use this to create a single node of PostgreSQL.
  • setup_replication
    This role helps to build the Hot-Standby instances using streaming replication with the primary.
  • setup_efm
    This role helps deploy the EDB Failover Manager for management High-Availability of Postgres instances and automate the failover/switchover.
  • setup_pemserver and setup_pemagent 
    These roles help configure the Postgres Enterprise Manager (PEM) server and PEM agent deployment on PostgreSQL nodes.
  • setup_pgpbouncer, setup_pgpool2, manage_pgpool2 and manage_pgbouncer
    Connection pooler: The pgbouncer and Pgpool II installation can be easily configured using these roles and helps manage pgBouncer and Pgpool parameters.
  • setup_barmanserver and setup_barman  
    These roles help in the deployment and management of Barman. Barman is a tool that helps DBA for backup and recovery of the Postgres cluster.
  • manage_dbserver
    This role helps DBA to manage the following in PostgreSQL. 
  1. Setting Postgres parameters, 
  2. Creating/Destroying databases, 
  3. Enabling/Disabling extensions, 
  4. Creating/dropping users/roles
  5. Management of privileges and roles, etc.
  • autotuning
    This role helps in tuning the Postgres configuration and system based on available resources.

For more information on the above roles, please refer to the GitHub repository.

 

Reference Architecture and edb_postgres collection

We have designed the edb_postgres collection in such a way that it gives flexibility around the deployment of the different reference architectures pattern. With the Ansible collection, a DBA can compose their own Ansible playbooks matching their target reference architectures.

Some of the well-tested reference architectures with the collection are given below:

  1. Single node PostgreSQL with monitoring and backup & recovery.
  2. Multi-node highly available PostgreSQL with monitoring and backup & recovery
  3. Multi-node high available PostgreSQL with connection poolers like Pgpool II and pgbouncer, monitoring and backup & recovery.

There are other possible architectures that DBA can achieve quickly with this Ansible collection.

Following is a link where we maintain the tested reference architectures. If you have any new architecture requests or want to contribute, feel free to submit your contribution.
 

edb_postgres in Ansible Galaxy

Ansible Galaxy is an online platform where the developers can share their Ansible roles or collections, making them freely available for all. Downloading a collection from Ansible Galaxy requires you to use the ansible-galaxy tool, which comes with ansible installation. edb_postgres latest release is available on Ansible Galaxy.

To download and install the last edb_postgres release from Ansible Galaxy, the command below must be used:

$ ansible-galaxy collection install edb_devops.edb_postgres

Once the Ansible collection has been installed, the roles are ready to use, but users need to include our collection in the playbook file. Following sample code shows how to refer to the ansible collection:

---
- hosts: postgres
  ...
  collections:
    - edb_devops.edb_postgres

Examples of Ansible playbook and inventory files can be found on the Github repository here and here.

 

More coming

The edb_postgres Ansible collection offers many capabilities, but we plan to add more to continue to make Postgres easier.

Some of the features coming:

  • Debian and Ubuntu support
  • Backup and recovery using pgBackRest
  • and more…

Our edb_postgres ansible collection works very well with Ansible Tower. For more information on the integration with Ansible Tower, please refer to the blog post, Ansible Tower and Postgres Reference Architecture.

We welcome your feedback and contribution to EDB Ansible collections in the journey of Making PostgreSQL Easier!
 

Julien Tachoires

Julien Tachoires is a PostgreSQL Performance Engineer at EDB. His core areas of expertise include PostgreSQL performance, deployment automation, High Availability, and development. Prior to working at EDB, Julien was CTO of the French PostgreSQL leader company, working with ...