Automatic PostgreSQL tuning in Ansible

Once upon a time, there was a tool called pgtune, which I heavily used for setting reasonable defaults for automatically deployed PostgreSQL instances.

Unfortunately, this tool is not maintained for a long time, doesn't support new PostgreSQL versions and finally it was removed from Debian and all it's derivates.

I understand, that proper database configuration is result of deep knowledge of your data and queries, but in current world of automated deployment and containers, it is much more better to configure your PostgreSQL instance with some computed values based on current environment, then leaving default values designed for some very low memory system.

Therefore I have started to search Internet for some pgtune alternative and found great, new web based pgtune replacement developed by Alexey Vasiliev (le0pard).

Since I am using Ansible for my systems orchestration, I have decided to rewrite it's computing logic as a new Ansible postgresql_tune module.

Module requires following information to compute results:

• PostgreSQL version
• deployment type
• total memory
• maximal number or connections
• path to resulting PostgreSQL file
• path to resulting sysctl file

Example usage:

- name: Install PostgreSQL configuration
  postgresql_tune:
    db_version: "{{ POSTGRESQL-VERSION }}"
    db_type: dw
    total_memory: "{{ ansible_memtotal_mb }}MB"
    max_connections: "{{ POSTGRESQL-CONNECTIONS }}"
    postgresql_file: "{{ pgsql_config_dir }}/99-postgresql-tune.conf"
    sysctl_file: "/etc/sysctl.d/99-postgresql-tune.conf"
  notify:
    - service postgresql restart
    - sysctl postgresql reload
  become: yes

Source code is here and any feedback is very much appreciated.