Using Ansible to Install and Configure Postgres 14 on Rocky Linux/Alma Linux

In this guide we are going to install and configure Postgresql 14 in Rocky Linux Using Ansible. This guide works for other RHEL 8 based derivatives like Alma Linux and Oracle Linux.

Postgresql is an open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.

Ansible is an open-source software provisioning, configuration management, and application-deployment tool enabling infrastructure as code. It runs on many Unix-like systems, and can configure both Unix-like systems as well as Microsoft Windows.

Check also:

Prerequisites

To follow along, ensure that you have the following:

An updated Rocky Linux server
SSH access to the server with user having sudo access
Latest version of Ansible installed locally

Table of Content

Ensuring that the server is up to date
Installing some packages necessary for the set up
Setting up Postgres 14 repo
Installing Postgres 14
Initializing Postgres Database
Configuring postgres 14
Starting Postgresql Creating a super user in the database server
Setting up ansible handlers
Creating hosts file
The whole playbook
Runing the ansible playbook

Ensuring that the server is up to date

Before proceeding, ensure that the server is up to date using this tasks. We are doing a. dnf update for all packages

1
2
3
4
- name: Upgrade all packages on servers
  dnf:
    name: "*"
    state: latest

Installing necessary packages

Next, let us install some common packages that we will need in our playbook. The `python3-psycopg2x package will be used to connect to the postgres server from ansible.

- name: Install required packages
  dnf:
    name:
      - python3-psycopg2
    state: present

3. Setting up Postgres 14 Repo

Next, let us set up the repo for Postgres 14. The default Rocky Linux 8 repos contains an outdated version of Postgres. Let us set up these repos that will allow us to install Postgres 14. We are also disabling built in postgres module since it contains outdated version of postgres.

- name: Add Postgres 14 repo
  shell: |
    dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  args:
    warn: no

- name: Disable built-in PostgreSQL module
  shell: |
    dnf -qy module disable postgresql
  args:
    warn: no

4. Installing Postgres 14 in Rocky Linux

Now that we have set up the repos, we can install Postgres. Use this task to achieve that

- name: Install postgresql
  dnf:
    name:
      - postgresql14-server
      - postgresql14-contrib
  notify:
    - Enable Postgresql

In the above, we are installing postgresql server and contrib which contains extra functionnality.

5. Initializing Postgres Database

We need to initialize postgres after installation. Use this task to initialize postgres.

- name: Initialize the PostgreSQL database
  shell: |
    /usr/pgsql-14/bin/postgresql-14-setup initdb

6. Configuring Postgres 14

With Postresql install, we would want to do some more configurations.

Let us update the listen address so we can access the service from a remote machine and then change identification to trust:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
- name: Ensure PostgreSQL is listening on *
  lineinfile:
    dest: /var/lib/pgsql/14/data/postgresql.conf
    regexp: '^listen_addresses\s*='
    line: "listen_addresses='*'"
    state: present
  notify: Restart Postgresql

- name: Add new configuration to "pg_hba.conf"
  blockinfile:
    dest: /var/lib/pgsql/14/data/pg_hba.conf
    block: |
      host    all             all             0.0.0.0/0                md5
      host    all             all             ::/0                     md5      
  notify: Restart Postgresql

- name: Change peer identification to trust
  shell: /bin/sed -i '/^local/s/peer/trust/' /var/lib/pgsql/14/data/pg_hba.conf
  args:
    warn: no
  notify: Restart Postgresql

Starting Postgresql Creating a Postgres Super User

Let us also create a user that will allow us to manage database operations. This is like a root user for our DB. We will start by starting the service before doing the user creation.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
- name: Start Postgresql server
  systemd:
    name: postgresql-14
    state: started
  notify:
    - Enable Postgresql

- name: Create a Superuser PostgreSQL database user
  become: yes
  become_user: postgres
  postgresql_user:
    name: "{{ postgres_root_user }}"
    password: "{{ postgres_root_pass }}"
    role_attr_flags: CREATEDB,SUPERUSER,CREATEROLE
    encrypted: yes
    state: present

Set up handlers

Handlers are just like normal tasks in an Ansible playbook but they run only when if the Task contains a snotify directive. It also indicates that it changed something.

Let us set up handlers for out Postgres installation and set up:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
- name: Restart Postgresql
  systemd:
    name: postgresql-14
    state: restarted

- name: Start Postgresql
  systemd:
    name: postgresql-14
    state: started

- name: Stop Postgresql
  systemd:
    name: postgresql-14
    state: stopped

- name: Enable Postgresql
  systemd:
    name: postgresql-14
    enabled: yes

Creating the hosts file

Ansible will execute the tasks against some inventory. The server inventory will be added as a hosts.yaml file that defines how the servers will be reached. This is my hosts file:

1
2
3
4
5
all:
  hosts:
    rockylinuxsrv:
      ansible_ssh_host: 54.186.157.102
      ansible_ssh_user: rocky

The whole playbook

This is the whole playbook. Save it as postgres.yaml

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
---
- name: Install and set up Postgres 14 on Rocky Linux 8
  hosts: rockylinuxsrv
  become: yes
  gather_facts: False
  vars:
    postgres_root_user: root
    postgres_root_pass: GQt5MTyVPuf9vsVWoWDT9YCn
  tasks:
    - name: Upgrade all packages on servers
      dnf:
        name: "*"
        state: latest

    - name: Install required packages
      dnf:
        name:
          - python3-psycopg2
        state: present

    - name: Add Postgres 14 repo
      shell: |
        dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm        
      args:
        warn: no

    - name: Disable built-in PostgreSQL module
      shell: |
        dnf -qy module disable postgresql        
      args:
        warn: no

    - name: Install postgresql
      dnf:
        name:
          - postgresql14-server
          - postgresql14-contrib
      notify:
        - Enable Postgresql

    - name: Initialize the PostgreSQL database
      shell: |
        /usr/pgsql-14/bin/postgresql-14-setup initdb        

    - name: Ensure PostgreSQL is listening on *
      lineinfile:
        dest: /var/lib/pgsql/14/data/postgresql.conf
        regexp: '^listen_addresses\s*='
        line: "listen_addresses='*'"
        state: present
      notify: Restart Postgresql

    - name: Add new configuration to "pg_hba.conf"
      blockinfile:
        dest: /var/lib/pgsql/14/data/pg_hba.conf
        block: |
          host    all             all             0.0.0.0/0                md5
          host    all             all             ::/0                     md5          
      notify: Restart Postgresql

    - name: Change peer identification to trust
      shell: /bin/sed -i '/^local/s/peer/trust/' /var/lib/pgsql/14/data/pg_hba.conf
      args:
        warn: no
      notify: Restart Postgresql

    - name: Start Postgresql server
      systemd:
        name: postgresql-14
        state: started
      notify:
        - Enable Postgresql

    - name: Create a Superuser PostgreSQL database user
      become: yes
      become_user: postgres
      postgresql_user:
        name: "{{ postgres_root_user }}"
        password: "{{ postgres_root_pass }}"
        role_attr_flags: CREATEDB,SUPERUSER,CREATEROLE
        encrypted: yes
        state: present

  handlers:
    - name: Restart Postgresql
      systemd:
        name: postgresql-14
        state: restarted

    - name: Start Postgresql
      systemd:
        name: postgresql-14
        state: started

    - name: Stop Postgresql
      systemd:
        name: postgresql-14
        state: stopped

    - name: Enable Postgresql
      systemd:
        name: postgresql-14
        enabled: yes

10. Running the ansible playbook

To run the ansible playbook, you need ansible installed locally. You can install ansible using OS package manager or if you have python pip you can use it:

sudo pip install ansible

You also need to have ssh access to the server. Ensure that you set up connection to the server. I am using ssh key authentication and ssh works for me using this command:

ssh rocky@10.2.11.9

Now you can run the playbook using this command:

ansible-playbook -i hosts.yaml postgres.yaml -vv

Once the playbook finish executing you can access Postgres installed on the server.