Helping the Database Team with Automation

Stateful applications, by definition, must save data persistently. So, when we talk about stateful applications, data will come into the picture, and hence, database servers. Choose any supported database software, depending on the type of data you want to store. This includes the number of transactions, the performance that’s required for your application, high availability and failover support, and many other factors. However, there are more important concerns, such as preparing the datastore, installing the necessary dependencies, packages or libraries. In terms of maintenance, this will be a continuous process as we need to take care of backups, data dumps, snapshots, and restoration in case of failure.

Ansible can help you in such situations. There are hundreds of Ansible database modules available that can help you implement your database automation tasks, including database installation, deployment, managing tables, managing...

Technical requirements

The following are the technical requirements to proceed with this chapter.

• A Linux machine for the Ansible control node (with internet access)
• A Linux machine for installing and configuring the PostgreSQL server
• Basic knowledge about databases (PostgreSQL) and servers

All the Ansible code, playbooks, commands, and snippets for this chapter can be found in this book’s GitHub repository at https://github.com/PacktPublishing/Ansible-for-Real-life-Automation/tree/main/Chapter-08.

Ansible for database operations

Database operations not only involve deploying database servers but also counting the day-to-day operations, such as managing databases, tables, database users, permissions or access. Ansible can talk to most of the well-known database services using the appropriate Ansible modules, as shown in the following diagram:

Figure 8.1 – Ansible database automation

Some of the most common database automation use cases are as follows:

• Deploying standalone database servers
• Configure high availability (HA) database clusters
• Creating databases and tables
• Managing user accounts
• Managing permissions
• Managing database and server access
• Backup and restore operations
• Implementing data replication and mirroring
• Automated database failovers

With the help of Ansible database collections and modules, we can automate most of these operations. In the next section, you will learn how to install...

Installing database servers

If you are a database administrator or if you know how database servers work, then you know the pain and struggle of managing and maintaining the services and data as per the application’s requirements. Since the introduction of virtualized and cloud-based platforms, provisioning virtual machines, disks, and other resources has become less of a headache. However, we still need automated options to provision database servers and database instances. There are single-click deployment solutions from public cloud service providers (CSPs) known as managed database solutions but in most cases, we do not have much control and transparency over such services if we have more strict requirements. Hence, organizations are forced to use self-hosted database servers and follow manual deployment and management processes.

In Chapter 7, Managing Your Virtualization and Cloud Platforms, you learned how to automate infrastructure provisioning, including virtual...

Creating and managing databases using Ansible

The community collection for PostgreSQL comes with more than 20 modules and a few plugins. It is possible to use these modules and plugins to automate PostgreSQL database operations, including creating, dropping, and updating databases, tables, users, and other resources in the database server.

Ansible community.postgresql prerequisites

If you are accessing PostgreSQL from a remote node (for example, an Ansible control node), then you need to install the psycopg2 Python library on this machine to use these PostgreSQL modules:

$ pip install psycopg2

In the next section, we will execute tasks from the database node itself (node1) using Ansible. This library is not required as the database server has already been configured with the required dependencies.

In the next section, you will learn how to manage database operations using Ansible and the community.postgresql collection.

Managing the database life cycle

In this...

Automating PostgreSQL operations

With the help of the modules in the community.postgresql collection, it is possible to automate more database maintenance and operations. Let’s take a closer look.

Managing PostgreSQL remote access

Database servers are accessed by applications on remote nodes and this access needs to be configured appropriately and securely. For a test environment, allow wildcard entries (for example, 0.0.0.0/0), but this is not a recommended practice for production servers. You need to configure the correct IP address or hostname to allow or restrict access to the database. This operation can be automated using the community.postgresql.postgresql_pg_hba module, as follows:

Figure 8.30 – Grant user access to database

Managing the pg_hba entries using Ansible will allow you to handle the entire life cycle of the database and its access.

Next, we will learn how to take automated database backups.

Database backup and...

Automating a password reset using ITSM and Ansible

With the help of ITSM tools and Red Hat Ansible Automation Platform (or community Ansible AWX), it is possible to implement zero-touch automation use cases such as database user password resets, database provisioning, and so on. Users will interact with the ITSM tool, and the tool will interact with Ansible Automation Platform to implement the task implementation, as shown in the following figure:

Figure 8.34 – ITSM and Ansible Automation Platform integration for database operations

This programmatic automation is one of the best features of Ansible and helps organizations scale their automation landscape by integrating with existing tools and software.

Use customized forms or ticketing systems in the ITSM tool, as shown in the following screenshot:

Figure 8.35 – Jira ticket and its details

The ITSM tool, such as Jira or ServiceNow, can also be configured with custom...

Summary

In this chapter, you learned about how Ansible can help you install database servers and manage database operations such as creating databases, creating tables, assigning user permissions, taking database backups, and configuring pg_hba. You also learned about the integration opportunities that are provided by the ITSM tools for implementing zero-touch automation with Ansible Automation Platform.

In the next chapter, you will learn how to integrate Ansible with your DevOps practices for deployment, rolling updates, IaC provisioning, and more.

Further reading

To learn more about the topics that were covered in this chapter, please visit the following links:

• How to send emails using Ansible and Gmail: https://www.techbeatly.com/ansible-gmail
• Using Ansible to deploy Microsoft SQL Server 2019 on Red Hat Enterprise Linux 8: https://www.redhat.com/sysadmin/mssql-linux-easy
• Community.Postgresql collection: https://docs.ansible.com/ansible/latest/collections/community/postgresql/index.html
• Ansible Database modules: https://docs.ansible.com/ansible/2.9/modules/list_of_database_modules.html
• Automating IT Service Management with ServiceNow and Red Hat Ansible Automation Platform: https://www.ansible.com/integrations/it-service-management/servicenow
• MongoDB Collection for Ansible: https://galaxy.ansible.com/community/mongodb