---
- name: apt update
apt:
update_cache: true
- name: Install needed Debian packages
apt:
state: present
name:
- libpq-dev # Needed by ansible
- python3-psycopg2 # Needed by ansible
- postgresql
- postgresql-client
- name: Modify default user password
community.postgresql.postgresql_user:
name: postgres
password: "{{ postgres_password }}"
become: yes
become_user: postgres
# I try https://pgtune.leopard.in.ua/ to get this configuration
- name: Configure postgresql
block:
- name: Set listen_addresses value
community.postgresql.postgresql_set:
name: listen_addresses
value: "*"
- name: Set max_connections value
community.postgresql.postgresql_set:
name: max_connections
value: 200
- name: Set shared_buffers value
community.postgresql.postgresql_set:
name: shared_buffers
value: 4GB
become: true
become_user: postgres
- name: Set effective_cache_size value
community.postgresql.postgresql_set:
name: effective_cache_size
value: 12GB
become: true
become_user: postgres
- name: Set maintenance_work_mem value
community.postgresql.postgresql_set:
name: maintenance_work_mem
value: 1GB
- name: Set checkpoint_completion_target value
community.postgresql.postgresql_set:
name: checkpoint_completion_target
value: 0.9
- name: Set wal_buffers value
community.postgresql.postgresql_set:
name: wal_buffers
value: 16MB
- name: Set default_statistics_target value
community.postgresql.postgresql_set:
name: default_statistics_target
value: 100
- name: Set random_page_cost value
community.postgresql.postgresql_set:
name: random_page_cost
value: 4
- name: Set effective_io_concurrency value
community.postgresql.postgresql_set:
name: effective_io_concurrency
value: 2
- name: Set work_mem value
community.postgresql.postgresql_set:
name: work_mem
value: 10485kB
- name: Set huge_pages value
community.postgresql.postgresql_set:
name: huge_pages
value: off
- name: Set min_wal_size value
community.postgresql.postgresql_set:
name: min_wal_size
value: 1GB
- name: Set max_wal_size value
community.postgresql.postgresql_set:
name: max_wal_size
value: 4GB
become: true
become_user: postgres
notify: postgresql
- name: Open postgresql to specific hosts
block:
- name: Open postgresql access for trusted ips
community.postgresql.postgresql_pg_hba:
dest: "/etc/postgresql/{{ version }}/main/pg_hba.conf"
contype: host
users: all
databases: all
source: "{{ item }}"
method: md5
state: present
loop: "{{ trusted_ips }}"
- name: Gather facts from all hosts
setup:
delegate_to: "{{ item }}"
delegate_facts: true
when: hostvars[item]['ansible_default_ipv4'] is not defined
loop: "{{ groups['all'] }}"
- name: Open postgresql access for our infra
community.postgresql.postgresql_pg_hba:
dest: "/etc/postgresql/{{ version }}/main/pg_hba.conf"
contype: host
users: all
databases: all
source: "{{ hostvars[item]['ansible_facts']['default_ipv4']['address'] }}"
method: md5
state: present
loop: "{{ groups['all'] }}"
notify: postgresql
# If changed, need to reboot server
- name: Fine-tune swappiness
copy:
dest: '/etc/sysctl.d/99-swappiness.conf'
content: |
vm.swappiness=1
- name: Configure database backup cron
template:
src: postgresql.j2
dest: /etc/cron.d/postgresql
mode: 0600