Ansible PostgreSQL

An Ansible collection is a curated package of a content that are bundled together including modules, roles, and plugins for easy distribution and use. It allows us to extend Ansible’s functionality and share a reusable automation code with the community.

One of the powerful modules for you as a database administrator is the “ansible.community.postgresql” collection. This collection offers various modules for managing and interacting with PostgreSQL databases, users, roles, and more.

In this tutorial, we will explore this collection and learn more about the modules it provides. We will also demonstrate the module usage with tons of examples so you can understand how this collection can aid in your automation tasks.

The Basic

PostgreSQL is a powerful open-source relational database system. When we talk about managing a PostgreSQL database, it can entail various tasks such as creating a database, creating new users, dropping roles, assigning permissions, configuration replication, and so much more.

Luckily, with Ansible, we can use the PostgreSQL collection to automate such tasks without manual intervention.

Available Modules

The “ansible.community.postgresql” collection offers multiple modules that perform different actions on the PostgreSQL server. The following are some of the most common and useful modules that you need to know.

• postgresql_copy module – This module facilitates a seamless data transfer between a file/program and a PostgreSQL table.
• postgresql_db module – It allows us to add or remove the PostgreSQL databases from a remote host effortlessly.
• postgresql_ext module – This module simplifies the process of adding or removing the PostgreSQL extensions from a database.
• postgresql_idx module – Using this module, we can easily create or drop the indexes from a PostgreSQL database.
• postgresql_info module – This module allows us to gather essential information about the PostgreSQL servers.
• postgresql_lang module – This module helps us to efficiently manage the procedural languages within a PostgreSQL database, performing tasks such as addition, removal, or changes.
• postgresql_membership module – Simplifying the management of PostgreSQL roles within groups, this module enables an easy addition or removal of roles.
• postgresql_owner module – We can utilize this module to change the owner of a PostgreSQL database object effortlessly.
• postgresql_pg_hba module – We use this module to add, remove, or modify a rule in a pg_hba file, streamlining the process.
• postgresql_ping module – This module allows us to check the availability of a remote PostgreSQL server efficiently.
• postgresql_privs module – This module makes managing privileges on PostgreSQL database objects easy by enabling us to grant or revoke them.
• postgresql_publication module – This module enables us to perform the tasks like adding, updating, or removing the PostgreSQL publications effortlessly.
• postgresql_query module – This allows us to perform PostgreSQL queries on the server effortlessly.
• postgresql_schema module – This module simplifies the process of adding or removing the PostgreSQL schemas.
• postgresql_script module – With this module, we can quickly run the PostgreSQL statements from a given file.
• postgresql_sequence module – We can conveniently create, drop, or alter a PostgreSQL sequence with this module.
• postgresql_set module – This module allows us to change a PostgreSQL server configuration parameter easily.
• postgresql_slot module – This module makes managing replication slots in a PostgreSQL database efficient.
• postgresql_subscription module – This module enables us to perform tasks like adding, updating, or removing the PostgreSQL subscriptions.
• postgresql_table module – We can create, drop, or modify the PostgreSQL tables using this module.
• postgresql_tablespace module – This module streamlines adding or removing of PostgreSQL tablespaces from the remote hosts.
• postgresql_user module –Using this module, we can easily manage the roles in the PostgreSQL server by allowing us to create, alter, or remove the roles from the server.
• postgresql_user_obj_stat_info module – Finally, this module allows us to quickly and efficiently gather statistics about the PostgreSQL user objects.

The given modules are the modules provided by the PostgreSQL collection for Ansible. Keep in mind that these modules can be removed or added in the future. Feel free to reference the docs.

Examples:

Let us explore some basic examples on how to use some of the provided modules in the PostgreSQL collection.

Example 1: Creating a Database

The first step is creating a PostgreSQL database. We can use the postgresql_db module as demonstrated in the following playbook example:

This should create a new database called “my_database” with the defined parameters such as the encoding, collation, ctype, and template.

Example 2: Dropping a Database

To remove an existing database from the server, use the following playbook example:

This should remove the “my_database” from the server.

Example 3: Creating a New User

Users are also a fundamental part of any database, especially in PostgreSQL. To create a new PostgreSQL user, we can use the postgresql_user module as follows:

As you can guess, the provided playbook connects to the “my_database” database, creates a new user called “linuxhint” with the specified password, and grants all permission to the user on the specified table.

Example 4: Removing a User

To remove a user, simply set the state as absent in the postgresql_user module as shown in the following:

Example 5: Gathering the PostgreSQL Server Information

To get a detailed information about the PostgreSQL server, we can utilize the postgresql_info module as demonstrated in the following example:

You can also exclude an information about a given object as follows:

Example 6: Pinging the PostgreSQL Server

We also have a simple module that allows us to ping and check the server’s status. We can use it as follows:

NOTE: Before using the modules in the “ansible.community.postgresql” collections, ensure that you have psycopg2 installed on your system.

Similarly, you can use the login_user and the login_password parameters if you need to specify the custom authentication details for the server.

Conclusion

As you learned in this tutorial, the Ansible PostgreSQL collection offers a hefty and valuable list of modules that can handle nearly all the tasks of administering a PostgreSQL server. Although this guide covered only the basics of this collection, you can reference the docs for more details.