How To Manage PostgreSQL Database with Ansible

Welcome to this guide on PostgreSQL Database Management with Ansible. 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.
• Bootsrapping the host from scratch.
• Managing software upgrades and deployment.
• Supports ocherstartion in the cluod infrastructure such as creating RDC and EC2 instances for applications on public clouds.

Working on a database in a production environment using the agile approach with tight deadlines can be a hard experience. This guide demonstrates how easy it is to work on those innumerable steps and prepare Postres for any range of services. Ansible uses OpenSSH libraries to log in to the target host for executing the operational tasks. The managed nodes’ IP addresses or hostnames are mentioned in the Ansible Inventory file. In the inventory file, it is possible to list multiple hosts under a single group. This ensures there is no repetition of the same tasks multiple times for different hosts.

By the end of this guide, you should be able to:

• Install PostgreSQL database.
• Manage PostgreSQL database with Ansible i.e create/remove a database, create/remove/upgrade user creds, privilege management e.t.c.

Step 1: Install Ansible on the Control Node.

First, ensure that you have installed ansible on the control node. The control node is the local machine or node on which you want to run ansible.

### On Ubuntu ###
sudo apt update
sudo apt install software-properties-common
sudo apt-add-repository --yes --update ppa:ansible/ansible
sudo apt install ansible

### On Debian  ###
sudo apt update
sudo apt install ansible

### On RHEL 8/CentOS 8/Rocky Linux 8 ###
sudo dnf install python3 python3-pip -y
sudo yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm
sudo dnf install  --enablerepo epel-playground  ansible
sudo yum install ansible

### On macOS ###
brew install ansible

Verify your ansible installation.

$ which ansible
/usr/bin/ansible

$ ansible --version
ansible [core 2.14.5]
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3/dist-packages/ansible
  ansible collection location = /root/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/bin/ansible
  python version = 3.10.6 (main, Mar 10 2023, 10:55:28) [GCC 11.3.0] (/usr/bin/python3)
  jinja version = 3.0.3
  libyaml = True

Step 2: Create the Ansible Inventory file

In this guide, we will run the Ansible Playbook using the ansible.cfg and the hosts file. This configuration is expected by the playbook that specifies the target machines.

Edit the ansible.cfg file as below.

sudo vi /etc/ansible/ansible.cfg

In the file, add the lines below under defaults.

[defaults]
inventory = hosts

Edit the hosts file.

sudo vi /etc/ansible/hosts

In the file, add the lines below replacing your IP address in the highlighted area. It is possible to put more than one IP address here.

[app1]
192.168.1.18 ansible_ssh_user=your_username

Generate ssh keys for the managed node on your control node replacing the [email protected] with your own

ssh-copy-id username@192.168.1.18

Step 3: Create Ansible Variables file

We will need the reference vars.yml file for our playbook to externalize the configurations. This makes the playbook reusable. In the variables file, we will put the database user, database name, and password.

sudo vi /etc/ansible/vars.yml

In the file, add the lines below.

db_user: admin
db_password: Passw0rd
db_name: testdb

Step 4: Create the Ansible Playbook for PostgreSQL.

The ansible Playbook is a YAML file with the list of all tasks and roles to be executed on the specified host or particular group. We need to install the PostgreSQL database itself along with the python3-psycopg2 Python library which will allow us to use the ansible PostgreSQL modules i.e postgresql_db, postgresql_user, postgresql_pg_hba and postgresql_privs

sudo vi /etc/ansible/postgresql.yml

Then add the lines below.

---
- hosts: all
  become: yes

  vars_files:
    - vars.yml

  pre_tasks:
    - name: "Install packages"
      dnf: "name={{ item }} state=present"
      with_items:
        - postgresql
        - postgresql-server

    - name: "Install Python packages"
      yum: "name={{ item }}  state=present"
      with_items:
        - python3-psycopg2

  tasks:
    - name: "Find out if PostgreSQL is initialized"
      ansible.builtin.stat:
        path: "/var/lib/pgsql/data/pg_hba.conf"
      register: postgres_data

    - name: "Initialize PostgreSQL"
      shell: "postgresql-setup initdb"
      when: not postgres_data.stat.exists
 
    - name: "Start and enable services"
      service: "name={{ item }} state=started enabled=yes"
      with_items:
        - postgresql

  handlers:
    - name: restart postgres
      service: name=postgresql state=restarted

In the playbook, we have made reference to our vars.yml file and specifies the tasks to be run on all hosts in the hosts file created.

In the pre_tasks, all the necessary packages are installed for the target system such as the Python package that allows the Ansible modules to interact.

Step 5: Create the database and the database user

Still, in the Playbook file, we will:

1. Create a database for our application testdb
2. Create a new user for the database
3. Grant access for the user to the created database.
4. Modify the pg_hba.conf file to allow the user to connect with a connection string

  tasks:
    - name: "Create app database"
      postgresql_db:
        state: present
        name: "{{ db_name }}"
      become: yes
      become_user: postgres

    - name: "Create db user"
      postgresql_user:
        state: present
        name: "{{ db_user }}"
        password: "{{ db_password }}"
      become: yes
      become_user: postgres

    - name: "Grant db user access to app db"
      postgresql_privs:
        type: database
        database: "{{ db_name }}"
        roles: "{{ db_user }}"
        grant_option: no
        privs: all
      become: yes
      become_user: postgres

    - name: "Allow md5 connection for the db user"
      postgresql_pg_hba:
        dest: "~/data/pg_hba.conf"
        contype: host
        databases: all
        method: md5
        users: "{{ db_user }}"
        create: true
      become: yes
      become_user: postgres
      notify: restart postgres

Here we use the database name, user, and password as that set in the vars file.

Step 6: Run SQL scripts against the database

Finally, we need to create a table in the database. We will add some dummy data to it by creating an SQL file from an existing database to be imported to our new database. In the playbook file, add the lines below.

    - name: "Add some dummy data to our database"
      become: true
      become_user: postgres
      shell: psql {{ db_name }} < /tmp/dump.sql

Now your final playbook file should look as below.

---
- hosts: all
  become: yes

  vars_files:
    - vars.yml

  pre_tasks:
    - name: "Install packages"
      dnf: "name={{ item }} state=present"
      with_items:
        - postgresql
        - postgresql-server

    - name: "Install packages"
      yum : "name={{ item }} state=present"
      with_items:
        - python3-psycopg2
  tasks:
    - name: "Find out if PostgreSQL is initialized"
      ansible.builtin.stat:
        path: "/var/lib/pgsql/data/pg_hba.conf"
      register: postgres_data

    - name: "Initialize PostgreSQL"
      shell: "postgresql-setup initdb"
      when: not postgres_data.stat.exists
 
    - name: "Start and enable services"
      service: "name={{ item }} state=started enabled=yes"
      with_items:
        - postgresql

    - name: "Create app database"
      postgresql_db:
        state: present
        name: "{{ db_name }}"
      become: yes
      become_user: postgres

    - name: "Create db user"
      postgresql_user:
        state: present
        name: "{{ db_user }}"
        password: "{{ db_password }}"
      become: yes
      become_user: postgres

    - name: "Grant db user access to app db"
      postgresql_privs:
        type: database
        database: "{{ db_name }}"
        roles: "{{ db_user }}"
        grant_option: no
        privs: all
      become: yes
      become_user: postgres

    - name: "Allow md5 connection for the db user"
      postgresql_pg_hba:
        dest: "~/data/pg_hba.conf"
        contype: host
        databases: all
        method: md5
        users: "{{ db_user }}"
        create: true
      become: yes
      become_user: postgres
      notify: restart postgres

    - name: "Add some dummy data to our database"
      become: true
      become_user: postgres
      shell: psql {{ db_name }} < /tmp/dump.sql

  handlers:
    - name: restart postgres
      service: name=postgresql state=restarted 

Step 7: Create the SQL file on the Managed Node

Create the dummy SQL file on your Managed Node as below.

sudo vi /tmp/dump.sql

Now in the file, add some simple commands on how to create a table and add some data to the table.

CREATE TABLE IF NOT EXISTS test (           
  message varchar(255) NOT NULL         
  );          
  INSERT INTO test(message) VALUES('Ansible is fun');
ALTER TABLE test OWNER TO "admin";

Now change the ownership of the file.

sudo chmod -R a+rwX /tmp/dump.sql

Step 6: Run and Test the New Playbook.

Now we are set to run the playbook from our host machine.

ansible-playbook -i hosts postgresql.yml 

If everything is successful, you should see the below output:

PLAY [all] *********************************************************************

TASK [Gathering Facts] *********************************************************
ok: [192.168.1.18]

TASK [Install packages] ********************************************************
ok: [192.168.1.18] => (item=['postgresql', 'postgresql-server'])

TASK [Install packages] ********************************************************
ok: [192.168.1.18] => (item=['python3-psycopg2'])

TASK [Find out if PostgreSQL is initialized] ***********************************
ok: [192.168.1.18]

TASK [Initialize PostgreSQL] ***************************************************
skipping: [192.168.1.18]

TASK [Start and enable services] ***********************************************
ok: [192.168.1.18] => (item=postgresql)

TASK [Create app database] *****************************************************
ok: [192.168.1.18]

TASK [Create db user] **********************************************************
[WARNING]: Module did not set no_log for no_password_changes
ok: [192.168.1.18]

TASK [Grant db user access to app db] ******************************************
ok: [192.168.1.18]

TASK [Allow md5 connection for the db user] ************************************
ok: [192.168.1.18]

TASK [Add some dummy data to our database] *************************************
changed: [192.168.1.18]

PLAY RECAP *********************************************************************
192.168.1.18               : ok=10   changed=1    unreachable=0    failed=0    skipped=1    rescued=0    ignored=0 

Verify the Created database on the Managed Node

Now let us verify if the database testdb has been created and has data in it.

psql testdb -h localhost -U admin

Sample Output:

Password for user admin:Enter the set password 
psql (10.17)
Type "help" for help.

testdb=> \dt
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | test | table | admin
(1 row)

testdb=> select * from test;
    message     
----------------
 Ansible is fun
(1 row)

Recommended books to learn PostgreSQL database administration:

• Best Books To Learn PostgreSQL Database

Conclusion

That marks the end of this guide on PostgreSQL Database Management with Ansible. I hope this guide was helpful.

See more on this page:

• Generate OpenSSL Self-Signed Certificates with Ansible
• Install Ansible AWX on CentOS 8 / Rocky Linux 8
• Best Books To Learn PostgreSQL Database
• Install PostgreSQL 13 on Fedora
• How To Install PostgreSQL 13 on Debian