From 886afa0f9a261c239eaad0744878f63db7ee2d68 Mon Sep 17 00:00:00 2001
From: Guilhem Moulin <guilhem@fripost.org>
Date: Tue, 4 Jun 2024 03:14:22 +0200
Subject: Install PostgreSQL and PostGIS.

---
 group_vars/all.yml |  5 +++++
 handlers/main.yml  |  6 ++++++
 tasks/webmap.yml   | 55 ++++++++++++++++++++++++++++++++++++++++++++++++++++++
 3 files changed, 66 insertions(+)

diff --git a/group_vars/all.yml b/group_vars/all.yml
index a0d35c6..5d4474a 100644
--- a/group_vars/all.yml
+++ b/group_vars/all.yml
@@ -7,3 +7,8 @@ webmap_layer_groups:
   - sks
   - st
   - vbk
+
+# PostgreSQL's version number and cluster name
+postgresql:
+  version: 15
+  cluster: main
diff --git a/handlers/main.yml b/handlers/main.yml
index c241ecc..189b880 100644
--- a/handlers/main.yml
+++ b/handlers/main.yml
@@ -46,3 +46,9 @@
 
 - name: Reload nginx
   service: name=nginx.service state=reloaded
+
+- name: Restart PostgreSQL
+  service: name=postgresql.service state=restarted
+
+- name: Reload PostgreSQL
+  service: name=postgresql.service state=reloaded
diff --git a/tasks/webmap.yml b/tasks/webmap.yml
index 9b60359..10a6555 100644
--- a/tasks/webmap.yml
+++ b/tasks/webmap.yml
@@ -111,3 +111,58 @@
   with_items: "{{ webmap_layer_groups }}"
 
 - meta: flush_handlers
+
+
+- name: Install PostgreSQL and PostGIS
+  apt: pkg={{ packages }}
+  vars:
+    packages:
+    - postgresql
+    - postgresql-postgis
+    - postgis
+    # for ansible
+    - python3-psycopg
+
+- name: Generate sv_SE.UTF-8 locales
+  locale_gen: name=sv_SE.UTF-8 state=present
+  # PostgreSQL needs to be restarted to see the new locale
+  notify: Restart PostgreSQL
+
+- name: Start PostgreSQL
+  service: name=postgresql@{{ postgresql.version }}-{{ postgresql.cluster }}.service state=started
+
+- meta: flush_handlers
+
+- name: Create PostgreSQL database
+  become: true
+  # XXX: this creates /var/lib/postgresql/.ansible/tmp
+  become_user: postgres
+  community.postgresql.postgresql_db:
+    name: webmap
+    comment: Backend PostGIS database for KlimatanalysNorr tooling
+    encoding: UTF-8
+    lc_collate: sv_SE.UTF-8
+    lc_ctype: sv_SE.UTF-8
+    locale_provider: icu
+    icu_locale: sv-SE-x-icu
+    template: template0
+    owner: postgres
+
+- name: Create 'postgis' PostgreSQL schema
+  become: true
+  become_user: postgres
+  community.postgresql.postgresql_schema:
+    name: postgis
+    db: webmap
+    owner: postgres
+
+- name: Install 'postgis' PostgreSQL extension to the webmap database in the postgis schema
+  become: true
+  become_user: postgres
+  community.postgresql.postgresql_ext:
+    name: postgis
+    db: webmap
+    schema: postgis
+    comment: Geographic objects support for PostgreSQL
+
+- meta: flush_handlers
-- 
cgit v1.2.3