How to Use Ansible to Install and Configure Postgres 14 on Debian 11 and 12

In this guide we are going to install and configure Postgresql 14 in Debian Using Ansible. This guide is tested on Debian 11 and 12.

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 up to date Debian 11 or Debian 12 server
SSH access to the server with user having sudo access
Latest version of Ansible installed locally

Ensuring that the server is up to date

Before proceeding, ensure that the server is up to date using these tasks. First we refresh the repos then we upgrade all packages.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
- name: Update apt repo and cache on all Debian/Ubuntu boxes
  apt:
    update_cache: yes
    force_apt_get: yes
    cache_valid_time: 3600

- name: Upgrade all packages on servers
  apt:
    upgrade: dist
    force_apt_get: yes

Installing necessary packages

Next, let us install some common packages that we will need in our playbook.

1
2
3
4
5
6
7
8
- name: Install required packages
  apt:
    name:
      - gnupg2
      - wget
      - python3-psycopg2
      - acl
    state: latest

Setting up Postgres 14 Repo

Next, let us set up the repo for Postgres 14. The default Debian 11 and 12 repos contains an outdated version of Postgres. Let us set up these repos that will allow us to install Postgres 14.

1
2
3
4
5
6
- name: Set up Postgres 14 repo
  shell: |
    echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -    
  args:
    warn: no

Installing Postgres 14 in Debian

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

1
2
3
4
5
6
- name: Install postgresql
  apt:
    name: postgresql
    update_cache: yes
  notify:
    - Enable Postgresql

In the above, we are installing postgres but doing a cache update (equivalent to apt-get update) before that.

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: /etc/postgresql/14/main/postgresql.conf
    regexp: '^listen_addresses\s*='
    line: "listen_addresses='*'"
    state: present
  notify: Restart Postgresql

- name: Add new configuration to "pg_hba.conf"
  blockinfile:
    dest: /etc/postgresql/14/main/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/' /etc/postgresql/14/main/pg_hba.conf
  args:
    warn: no
  notify: Restart 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:

1
2
3
4
5
6
7
8
9
- 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 “notify” 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
    state: restarted

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

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

- name: Enable Postgresql
  systemd:
    name: postgresql
    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
    debiansrv:
      ansible_ssh_host: 10.2.11.9
      ansible_ssh_user: admin

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
---
- name: Install and set up Postgres 14 in Debian 11 and 12
  hosts: debiansrv
  become: yes
  gather_facts: False
  vars:
    postgres_root_user: root
    postgres_root_pass: GQt5MTyVPuf9vsVWoWDT9YCn
    allow_world_readable_tmpfiles: true
  tasks:
    - name: Update apt repo and cache on all Debian/Ubuntu boxes
      apt:
        update_cache: yes
        force_apt_get: yes
        cache_valid_time: 3600

    - name: Upgrade all packages on servers
      apt:
        upgrade: dist
        force_apt_get: yes

    - name: Install required packages
      apt:
        name:
          - gnupg2
          - wget
          - python3-psycopg2
          - acl
        state: latest

    - name: Set up Postgres 14 repo
      shell: |
        echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
        wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -        
      args:
        warn: no

    - name: Install postgresql
      apt:
        name: postgresql
        update_cache: yes
      notify:
        - Enable Postgresql

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

    - name: Add new configuration to "pg_hba.conf"
      blockinfile:
        dest: /etc/postgresql/14/main/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/' /etc/postgresql/14/main/pg_hba.conf
      args:
        warn: no
      notify: Restart 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
        state: restarted

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

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

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

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:

1
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:

1
ssh admin@10.2.11.9

Now you can run the playbook using this command:

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

Once the playbook finish executing you can access Postgres installed on the server. That is it!