From 77c98f47dfcf2ff89d01bf56868baf521e508ff9 Mon Sep 17 00:00:00 2001
From: Chris Lovering <chris.lovering.95@gmail.com>
Date: Thu, 16 May 2024 21:25:49 +0100
Subject: Add a postgresql.conf file template

---
 ansible/roles/postgres/files/postgresql.conf | 51 ++++++++++++++++++++++++++++
 ansible/roles/postgres/tasks/main.yml        | 12 +++++++
 2 files changed, 63 insertions(+)
 create mode 100644 ansible/roles/postgres/files/postgresql.conf

(limited to 'ansible/roles/postgres')

diff --git a/ansible/roles/postgres/files/postgresql.conf b/ansible/roles/postgres/files/postgresql.conf
new file mode 100644
index 0000000..2074044
--- /dev/null
+++ b/ansible/roles/postgres/files/postgresql.conf
@@ -0,0 +1,51 @@
+data_directory = '/var/lib/postgresql/15/main'
+hba_file = '/etc/postgresql/15/main/pg_hba.conf'
+ident_file = '/etc/postgresql/15/main/pg_ident.conf'
+external_pid_file = '/var/run/postgresql/15-main.pid'
+listen_addresses = '89.58.26.118,localhost'
+port = 5432
+unix_socket_directories = '/var/run/postgresql'
+
+ssl = on
+ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
+ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
+dynamic_shared_memory_type = posix
+
+log_line_prefix = '%m [%p] %q%u@%d '
+log_timezone = 'Etc/UTC'
+datestyle = 'iso, mdy'
+timezone = 'Etc/UTC'
+lc_messages = 'en_US.UTF-8'
+lc_monetary = 'en_US.UTF-8'
+lc_numeric = 'en_US.UTF-8'
+lc_time = 'en_US.UTF-8'
+default_text_search_config = 'pg_catalog.english'
+include_dir = 'conf.d'
+
+cluster_name = 'schweinehund'
+
+# The following is from https://pgtune.leopard.in.ua/
+# DB Version: 15
+# OS Type: linux
+# DB Type: mixed
+# Total Memory (RAM): 64 GB
+# CPUs num: 10
+# Connections num: 100
+# Data Storage: ssd
+max_connections = 100
+shared_buffers = 16GB
+effective_cache_size = 48GB
+maintenance_work_mem = 2GB
+checkpoint_completion_target = 0.9
+wal_buffers = 16MB
+default_statistics_target = 100
+random_page_cost = 1.1
+effective_io_concurrency = 200
+work_mem = 20971kB
+huge_pages = try
+min_wal_size = 1GB
+max_wal_size = 4GB
+max_worker_processes = 10
+max_parallel_workers_per_gather = 4
+max_parallel_workers = 10
+max_parallel_maintenance_workers = 4
diff --git a/ansible/roles/postgres/tasks/main.yml b/ansible/roles/postgres/tasks/main.yml
index ea6565b..850a633 100644
--- a/ansible/roles/postgres/tasks/main.yml
+++ b/ansible/roles/postgres/tasks/main.yml
@@ -71,3 +71,15 @@
   become_user: "{{ postgres_user }}"
   tags:
     - role::postgres
+
+- name: Import postgresql.conf
+  copy:
+    src: postgresql.conf
+    dest: /etc/postgresql/{{ postgres_version }}/main/postgresql.conf
+    owner: postgres
+    group: postgres
+    mode: "0644"
+  tags:
+    - role::postgres
+  notify:
+    - Restart the postgres service
-- 
cgit v1.2.3