Automating PostgreSQL with Ansible: A Hands on Guide

Ansible is one of the popular IT automation tools used to make complex configuration, software provisioning, application deployment, and management tasks easier. It is used in IT operational tasks such as:

• Configuring hosts and services.
• Bootstrapping the host from scratch.

In this guide on PostgreSQL Database Management with Ansible, I will show you how to automate PostgreSQL operations using Ansible.

Prerequisites

Before we start, ensure that we have:

• A running PostgreSQL server.
• Ansible installed on our system.

The manual steps to install PostgreSQL

Step 1: Install Required Packages

The first step is to install the required packages for PostgreSQL. We can use the following command to install the packages:

$ sudo apt-get install postgresql postgresql-contrib

Step 2: Create a Database

The next step is to create a database. We can use the following command to create a database:

$ sudo -u postgres createdb mydb

Step 3: Create a User

The next step is to create a user. We can use the following command to create a user:

$ sudo -u postgres createuser --interactive

Step 4: Grant Permissions

The next step is to grant permissions. We can use the following command to grant permissions:

$ sudo -u postgres psql
postgres=# GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

The automated way with Ansible

Step 1: Create a Playbook

The final step is to create a playbook. We can use the following playbook:

---
- hosts: all
  become: yes

  tasks:
    - name: Install the PostgreSQL server
      apt:
        name:
          - postgresql
          - postgresql-contrib

    - name: Create the database
      become_user: postgres
      become_method: su
      command: createdb mydb

    - name: Create our user
      become_user: postgres
      become_method: su
      command: createuser --interactive

    - name: Grant permissions for the user
      become_user: postgres
      become_method: su
      shell: |
        psql <<EOF
        GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
        EOF

This playbook will install PostgreSQL, create a database, create a user, and grant permissions.

Step 2: Extend the Playbook

Then we can create a role that manages our users and databases.

Here is an example playbook that can be cut up into an Ansible role with ease:

---
- name: PostgreSQL Database and User Management
  hosts: all
  become: yes

  vars:
    postgresql_databases:
      - name: mydb
        owner: myuser
        encoding: UTF8
        lc_collate: en_US.UTF-8
        lc_ctype: en_US.UTF-8

    postgresql_users:
      - name: myuser
        password: mypassword
        encrypted: yes

  tasks:
    - name: Install PostgreSQL packages
      apt:
        name:
          - postgresql
          - postgresql-contrib

    - name: Create databases
      postgresql_db:
        name: "{{ item.name }}"
        owner: "{{ item.owner }}"
        encoding: "{{ item.encoding }}"
        lc_collate: "{{ item.lc_collate }}"
        lc_ctype: "{{ item.lc_ctype }}"
      with_items:
        - "{{ postgresql_databases }}"

    - name: Create users
      postgresql_user:
        name: "{{ item.name }}"
        password: "{{ item.password }}"
        encrypted: "{{ item.encrypted }}"
      with_items:
        - "{{ postgresql_users }}"

The variables postgresql_databases and postgresql_users can be modified to our needs. Because of the sensitive data using Ansible Vault is highly recommended.

Optional Step 3: Create a role from the playbook

As the responsible person for automation we always have to follow the best practices and industry standards. Ansible automation is easily extensible and fast with using roles.

Join the Discussion! I’d love to hear your thoughts and experiences on the topic! Share your insights, ask questions, or engage with fellow readers in the comments section below!