Install single instance of PostgreSQL with Ansible

Prerequisites

The following Ansible script installs prerequisites. This includes packages for Python, etc.

---
- name: Update all packages and install prereqs on AWS Linux
  hosts: all
  become: yes  # Required for installing system packages

  tasks:
    - name: Update all current packages
      ansible.builtin.yum:
        name: '*'
        state: latest

    - name: Ensure dnf-plugins-core is installed (for dnf config-manager)
      ansible.builtin.dnf:
        name: dnf-plugins-core
        state: present

    - name: Enable the CRB (CodeReady Builder) repository on Rocky Linux
      ansible.builtin.shell: 
        cmd: dnf config-manager --set-enabled crb
      when: ansible_distribution == "Rocky" and ansible_distribution_major_version == "9"

    - name: Install pip, python3-devel, nano
      ansible.builtin.yum:
        name:
          - nano
          - python3-pip  # Ensures pip is installed
          - python3-devel  # Python development files necessary for psycopg2
          - git
          - htop
          - python3-dnf
          - python3-libselinux
        state: present

    - name: Install system packages for psycopg2
      ansible.builtin.yum:
        name:
          - perl
          - perl-App-cpanminus
          - perl-devel
          - postgresql-devel  # PostgreSQL development files necessary for psycopg2
        state: present
    - name: Install psycopg2 using pip
      ansible.builtin.pip:
        name: psycopg2-binary
        state: present
...

PostgreSQL

The following Ansible script installs a single instance of PostgreSQL via ansible.

---
- name: Install and configure PostgreSQL 16 on Rocky Linux 9.1
  hosts: all
  become: yes

  vars:
    postgres_version: 16
    db_name: "postgres"
    user1:
      name: "dbaadmin"
      password: "dbaadmin1234"
      role: "superuser"
    user2:
      name: "repluser"
      password: "repluser1234"
      role: "replication"

  tasks:
    - name: Ensure all packages are up to date
      yum:
        name: "*"
        state: latest

    - name: Add PostgreSQL repository
      yum_repository:
        name: "postgresql"
        description: "PostgreSQL Repository"
        baseurl: "https://download.postgresql.org/pub/repos/yum/{{ postgres_version }}/redhat/rhel-9-x86_64"
        gpgkey: "https://download.postgresql.org/pub/repos/yum/keys/PGDG-RPM-GPG-KEY-RHEL"
        enabled: yes
        state: present

    - name: Install PostgreSQL and contrib package
      yum:
        name: "postgresql{{ postgres_version }}, postgresql{{ postgres_version }}-server, postgresql{{ postgres_version }}-contrib"
        state: present

    - name: Initialize the PostgreSQL database
      command: "/usr/pgsql-{{ postgres_version }}/bin/postgresql-{{ postgres_version }}-setup initdb"
      args:
        creates: "/var/lib/pgsql/{{ postgres_version }}/data/PG_VERSION"

    - name: Start and enable PostgreSQL service
      systemd:
        name: "postgresql-{{ postgres_version }}"
        enabled: yes
        state: started

    - name: Install dependencies for psycopg2
      yum:
        name:
          - python3
          - python3-devel
          - gcc
          - postgresql{{ postgres_version }}-devel
        state: present

    - name: Install psycopg2 using pip
      pip:
        name: psycopg2
        state: present

    - name: Set up PostgreSQL user accounts
      postgresql_user:
        name: "{{ item.name }}"
        password: "{{ item.password }}"
        role_attr_flags: "{{ item.role }}"
      loop:
        - "{{ user1 }}"
        - "{{ user2 }}"

    - name: Allow replication connections
      lineinfile:
        path: "/var/lib/pgsql/{{ postgres_version }}/data/pg_hba.conf"
        line: "host replication {{ user2.name }} 0.0.0.0/0 md5"
        create: yes

    - name: Configure PostgreSQL for replication and enable pg_stat_statements
      blockinfile:
        path: "/var/lib/pgsql/{{ postgres_version }}/data/postgresql.conf"
        block: |
          listen_addresses = '*'
          wal_level = replica
          max_wal_senders = 10
          archive_mode = on
          archive_command = 'cp %p /var/lib/pgsql/{{ postgres_version }}/data/archive/%f'
          shared_preload_libraries = 'pg_stat_statements'

    - name: Restart PostgreSQL to apply changes
      systemd:
        name: "postgresql-{{ postgres_version }}"
        state: restarted

    - name: Enable pg_stat_statements extension
      postgresql_ext:
        name: pg_stat_statements
        db: "{{ db_name }}"
        state: present
...