Ansible configuration for docker postgresql

This is a skeleton ansible playbook for configuring dockerised postgres using the module community.general.postgresql_set

This chooses settings based on the host memory of the remote server using ansible_memtotal_mb

the docker plugin needs a port to access the postgres server.

Your playbook could look like this:

# ansible-playbook -i inventory -v yourplaybook.yml
# you need to add community.general to the local machine's ansible setup
# https://docs.ansible.com/ansible/latest/collections/community/general/
# ansible needs to be 2.9.10 or later


---

- name: Snippets regarding postgres key parameter configuration
  hosts: all
  become: true
  remote_user: root
  tasks:
  - name: debug info
    debug:
      #msg: "Host {{ ansible_hostname }} has {{ ansible_memtotal_mb }} MB Memory and {{ ansible_processor_cores }} CPU Cores."
      msg: "Suggested shared_buffers_size: {{ansible_memtotal_mb * 0.25}} and rounded  {{(ansible_memtotal_mb * 0.25)|int}} "


- name: Calculate variables
  hosts: all
  become: true
  remote_user: root
  vars:
    - postgres_max_connections: 100  #standard default
    - postgres_shared_buffer: "{{ (ansible_memtotal_mb*0.25)|int }}MB"
    - postgres_work_mem: "{{ (ansible_memtotal_mb*0.25/postgres_max_connections)|int }}MB"
    - postgres_maintenance_work_mem: "{{ (ansible_memtotal_mb*0.05)|int }}MB"
    - postgres_effective_cache_size: "{{ (ansible_memtotal_mb*0.75)|int }}MB"
    
    
- name: Start db container, something like this
docker_container:
  image: postgres:{{ postgres_version | default('10.11') }}
  name: YOURDB
  volumes: /var/docker/YOURAPP/db:/var/lib/postgresql/data/
  restart_policy: always
  state: started
  ports:
    - "5433:5432"  #needed for the ansible configuration plugin
  env:
    POSTGRES_PASSWORD: "{{ db_password }}"
    POSTGRES_USER: <...>
    POSTGRES_NAME: <...>  networks_cli_compatible: true
  
- name: Set postgres config
  community.general.postgresql_set:
    db: postgres
    login_host: localhost
    login_user: django_api_sync
    login_password: "{{ db_password }}"
    port: 5433
    name: "{{item.name}}"
    value: "{{item.value}}"
  with_items:
    - {name: 'shared_buffers', value: '{{postgres_shared_buffer}}'}
    - {name: 'max_connections', value: '{{postgres_max_connections}}'}
    - {name: 'work_mem', value: '{{postgres_work_mem}}'}
    - {name: 'maintenance_work_mem', value: '{{postgres_maintenance_work_mem}}'}
    - {name: 'effective_cache_size', value: '{{postgres_effective_cache_size}}'}
  register: set
  
- name: restart postgres
community.general.docker_container:
  name: django_api_sync_db
  restart: yes
  state: started
when: setup_postgres