Install PostgreSQL in Debian-like systems - Ansible modules apt, stat, shell, service

How to automate the installation of PostgreSQL on Debian-like systems: installing the necessary packages and dependency, initializing the configuration, starting and enabling service on boot using Ansible Playbook and apt, stat, shell, and service modules.

June 8, 2022

Access the Complete Video Course and Learn Quick Ansible by 200+ Practical Lessons

#ansible #apt #module #deb #ubuntu #postgresql #database #debian

How to Install PostgreSQL with Ansible in RedHat-like systems?

I’m going to show you a live demo and some simple Ansible code. I’m Luca Berton and welcome to today’s episode of Ansible Pilot.

Ansible Install PostgreSQL in Debian-like systems

Install server, client, utils => ansible.builtin.apt
Initialize db => ansible.builtin.stat, ansible.builtin.shell
Start and Enable at boot => ansible.builtin.service

In order to install PostgreSQL on a Debian-like system, you need to perform three steps.

The first step is to install the packages to perform server, client, and utils. You are going to use the ansible.builtin.apt Ansible module.

These include the distribution-related binaries, libraries, and documentation for your Debian-like system, Ubuntu as well.

The second step is to initialize the PostgreSQL database. There is a command-line utility that you could execute using the Ansible ansible.builtin.shell module. The effective command executed is /usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/main (suppose you are using version 14).

This code is executed only if needed, the conditional check was performed by the ansible.builtin.stat module.

The third step is to Start and Enable the PostgreSQL service at boot using the ansible.builtin.service Ansible module.

demo

Let’s jump into a real-life playbook to install PostgreSQL in Debian-like systems with Ansible.

I’m going to show you how to install the PostgreSQL server, client utilities, and the Python libraries to manage the DBMS.

The second step is to perform the initial PostgreSQL database initialization, only if data were not present.

After these steps, you’re able to start the service and enable it at boot time.

code

---
- name: postgresql demo
  hosts: all
  become: true
  vars:
    postgresql_version: "14"
    postgresql_bin_path: "/usr/lib/postgresql/{{ postgresql_version }}/bin"
    postgresql_data_dir: "/var/lib/postgresql/{{ postgresql_version }}/main"
    ansible_ssh_pipelining: true
  tasks:
    - name: Install packages
      ansible.builtin.apt:
        name:
          - postgresql
          - postgresql-contrib
          - libpq-dev
          - python3-psycopg2
        state: present

    - name: Check if PostgreSQL is initialized
      ansible.builtin.stat:
        path: "{{ postgresql_data_dir }}/pg_hba.conf"
      register: postgres_data

    - name: Empty data dir
      ansible.builtin.file:
        path: "{{ postgresql_data_dir }}"
        state: absent
      when: not postgres_data.stat.exists

    - name: Initialize PostgreSQL
      ansible.builtin.shell: "{{ postgresql_bin_path }}/initdb -D {{ postgresql_data_dir }}"
      become: true
      become_user: postgres
      when: not postgres_data.stat.exists

    - name: Start and enable service
      ansible.builtin.service:
        name: postgresql
        state: started
        enabled: true

execution

$ ansible-playbook -i virtualmachines/ubuntu2204/inventory postgresql/install_debian.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [ubuntu.example.com]
TASK [Install packages] ***********************************************************************************
changed: [ubuntu.example.com]
TASK [Check if PostgreSQL is initialized] *****************************************************************
ok: [ubuntu.example.com]
TASK [Empty data dir] *************************************************************************************
changed: [ubuntu.example.com]
TASK [Initialize PostgreSQL] ******************************************************************************
[WARNING]: Module remote_tmp /var/lib/postgresql/.ansible/tmp did not exist and was created with a mode of
0700, this may cause issues when running as another user. To avoid this, create the remote_tmp dir with
the correct permissions manually
changed: [ubuntu.example.com]
TASK [Start and enable service] ***************************************************************************
ok: [ubuntu.example.com]
PLAY RECAP ************************************************************************************************
ubuntu.example.com         : ok=6    changed=3    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

idempotency

$ ansible-playbook -i virtualmachines/ubuntu2204/inventory postgresql/install_debian.yml
PLAY [postgresql demo] ************************************************************************************
TASK [Gathering Facts] ************************************************************************************
ok: [ubuntu.example.com]
TASK [Install packages] ***********************************************************************************
ok: [ubuntu.example.com]
TASK [Check if PostgreSQL is initialized] *****************************************************************
ok: [ubuntu.example.com]
TASK [Empty data dir] *************************************************************************************
skipping: [ubuntu.example.com]
TASK [Initialize PostgreSQL] ******************************************************************************
skipping: [ubuntu.example.com]
TASK [Start and enable service] ***************************************************************************
ok: [ubuntu.example.com]
PLAY RECAP ************************************************************************************************
ubuntu.example.com         : ok=4    changed=0    unreachable=0    failed=0    skipped=2    rescued=0    ignored=0

before execution

$ ssh [email protected]
Welcome to Ubuntu 22.04 LTS (GNU/Linux 5.15.0-25-generic x86_64)
* Documentation:  https://help.ubuntu.com
 * Management:     https://landscape.canonical.com
 * Support:        https://ubuntu.com/advantage
System information as of Wed Jun  8 10:28:25 UTC 2022
System load:  0.4248046875      Processes:               100
  Usage of /:   3.5% of 38.71GB   Users logged in:         0
  Memory usage: 19%               IPv4 address for enp0s3: 10.0.2.15
  Swap usage:   0%                IPv4 address for enp0s8: 192.168.178.10
0 updates can be applied immediately.
The list of available updates is more than a week old.
To check for new updates run: sudo apt update
Last login: Wed Jun  8 10:28:25 2022 from 192.168.178.26
$ sudo su
root@ubuntu:/home/devops# apt list postgresql
Listing... Done
postgresql/jammy 14+238 all
root@ubuntu:/home/devops# apt list python3-psycopg2
Listing... Done
python3-psycopg2/jammy 2.9.2-1build2 amd64
root@ubuntu:/home/devops# ls -al /var/lib/postgresql/14/main/pg_hba.conf
ls: cannot access '/var/lib/postgresql/14/main/pg_hba.conf': No such file or directory
root@ubuntu:/home/devops# systemctl status postgresql
Unit postgresql.service could not be found.
root@ubuntu:/home/devops#

after execution

$ ssh [email protected]
Welcome to Ubuntu 22.04 LTS (GNU/Linux 5.15.0-25-generic x86_64)
* Documentation:  https://help.ubuntu.com
 * Management:     https://landscape.canonical.com
 * Support:        https://ubuntu.com/advantage
System information as of Wed Jun  8 10:31:53 UTC 2022
System load:  0.15771484375     Processes:               99
  Usage of /:   4.1% of 38.71GB   Users logged in:         0
  Memory usage: 24%               IPv4 address for enp0s3: 10.0.2.15
  Swap usage:   0%                IPv4 address for enp0s8: 192.168.178.10
0 updates can be applied immediately.
Last login: Wed Jun  8 10:28:55 2022 from 192.168.178.26
$ sudo su
root@ubuntu:/home/devops# apt list postgresql
Listing... Done
postgresql/jammy,now 14+238 all [installed]
root@ubuntu:/home/devops# apt list python3-psycopg2
Listing... Done
python3-psycopg2/jammy,now 2.9.2-1build2 amd64 [installed]
root@ubuntu:/home/devops# ls -al /var/lib/postgresql/14/main/pg_hba.conf 
-rw------- 1 postgres postgres 4789 Jun  8 10:31 /var/lib/postgresql/14/main/pg_hba.conf
root@ubuntu:/home/devops# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2022-06-08 10:31:23 UTC; 1min 18s ago
    Process: 4506 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 4506 (code=exited, status=0/SUCCESS)
        CPU: 1ms
Jun 08 10:31:23 ubuntu systemd[1]: Starting PostgreSQL RDBMS...
Jun 08 10:31:23 ubuntu systemd[1]: Finished PostgreSQL RDBMS.
root@ubuntu:/home/devops#

Recap

Now you know how to Install PostgreSQL in Debian-like Linux systems. Subscribe to the YouTube channel, Medium, and Website, X (formerly Twitter) to not miss the next episode of the Ansible Pilot.