homelab/roles/postgresql/tasks/main.yml

---
- name: Create docker network
  become: true
  community.docker.docker_network:
    name: postgresql
- name: Create docker volume
  become: true
  community.docker.docker_volume:
    name: postgresql
- name: Copy postgresql config
  become: true
  ansible.builtin.copy:
    src: postgresql.conf
    dest: /var/lib/docker/volumes/postgresql/_data/postgresql.conf
    mode: '0664'
    owner: root
    group: root
  register: pgconf
- name: Create postgresql container
  become: true
  community.docker.docker_container:
    name: postgresql
    image: postgres:16.4-bookworm
    networks:
      - name: postgresql
    volumes:
      - postgresql:/var/lib/postgresql/data
    env:
      POSTGRES_USER: postgres
      POSTGRES_DB: postgres
      POSTGRES_PASSWORD: "{{ users.postgres }}"
    restart_policy: unless-stopped
    recreate: "{{ pgconf.changed }}"
- name: Create a function to manage users
  become: true
  community.docker.docker_container_exec:
    container: postgresql
    argv:
      - /bin/bash 
      - "-c"
      - echo "{{ lookup('file', 'create-user.sql') }}" | psql -U postgres
- name: Create roles
  become: true
  community.docker.docker_container_exec:
    container: postgresql
    argv:
      - /bin/bash
      - "-c"
      - echo "SELECT create_user_if_not_exists('{{ item.key }}', '{{ item.value }}')" | psql -U postgres
  no_log: True
  loop:
    "{{ users | dict2items }}"
- name: Create databases
  become: true
  community.docker.docker_container_exec:
    container: postgresql
    argv: ["sh", "-c", "createdb {{ item.key }} -O {{ item.key }} -U postgres || exit 0"]
  no_log: True
  loop: "{{ users | dict2items }}"
- name: Grant pg_monitor role to exporter
  become: true
  community.docker.docker_container_exec:
    container: postgresql
    argv: ["sh", "-c", "echo 'GRANT pg_monitor TO exporter' | psql -U postgres"]