Merge pull request #6 from paulRbr/pg-extensions1.4.0

feat(extensions): adds creation of extension on databases if needed

8 files changed, 85 insertions, 19 deletions

diff --git a/.travis.yml b/.travis.yml
index 7a72289..ddf6fb0 100644
--- a/.travis.yml
+++ b/.travis.yml
@@ -11,9 +11,13 @@ addons:
     packages:
     - python-pip
 
+# Tests are using docker to spawn fake instances
+services:
+  - docker
+
 install:
   # Install ansible
-  - pip install ansible
+  - pip install ansible docker-py docker
   # Check ansible version
   - ansible --version
   - ansible-galaxy install -r test/requirements.yml -p test/roles/
@@ -21,6 +25,11 @@ install:
 script:
   # Basic role syntax check
   - ansible-playbook test/main.yml -i test/hosts --syntax-check
+  # Run the tests
+  # FAILS - ansible-playbook test/main.yml -i test/hosts
+  # Even if Docker and docker-py module are present, the tests fail on Travis
+  # The error on Travis is:
+  # => {"changed": false, "item": {"image": "python:3.7-stretch", "links": ["postgres_barman", "postgres_one"], "name": "postgres_three"}, "msg": "Failed to import docker or docker-py - No module named docker. Try `pip install docker` or `pip install docker-py` (Python 2.6)"}
 
 notifications:
   webhooks: https://galaxy.ansible.com/api/v1/notifications/
diff --git a/README.md b/README.md
index 515535b..06622c8 100644
--- a/README.md
+++ b/README.md
@@ -14,7 +14,6 @@ To install:
 ansible-galaxy install trainline-eu.ansible_postgresql_role
 ```
 
-
 #### Dependencies
 
 No dependencies
@@ -36,7 +35,6 @@ This table lists the tested version of OS/PostgreSQL couples.
 - :interrobang: - maybe works, not tested
 - :no_entry: - PostgreSQL has reached EOL
 
-
 #### Variables
 
 ```yaml
@@ -60,6 +58,9 @@ postgres_clusters:                         # Mandatory
     databases:
       - dbname: my_database                    # Mandatory
         owner: john                            # Mandatory
+        extensions:                            # Optional
+          - names: [ 'postgis', 'postgis_topology' ]
+            apt_deps: [ 'postgresql-11-postgis' ]
 
 # Postgres config (Optional)
 postgres_log_line_prefix: '%m [%p] database: %d host: %h user: %u '
diff --git a/tasks/main.yml b/tasks/main.yml
index 2fa7883..3d1892c 100644
--- a/tasks/main.yml
+++ b/tasks/main.yml
@@ -46,6 +46,7 @@
 - include: postgres-database.yml
     postgres_dbname={{ item.1.dbname }}
     postgres_owner={{ item.1.owner }}
+    postgres_extensions={{ item.1.extensions|default([]) }}
     postgres_port={{ item.0.port }}
   with_subelements:
     - "{{ postgres_clusters }}"
diff --git a/tasks/postgres-common-postinstall.yml b/tasks/postgres-common-postinstall.yml
index 277fafd..715782c 100644
--- a/tasks/postgres-common-postinstall.yml
+++ b/tasks/postgres-common-postinstall.yml
@@ -15,13 +15,25 @@
     src: /var/lib/postgresql/.ssh/id_rsa.pub
   register: db_server_pub_key
 
+- name: Check if barman is installed on barman server
+  getent:
+    database: passwd
+    key: "barman"
+  delegate_to: "{{ postgres_barman_server_public|default(postgres_barman_server) }}"
+  when: postgres_barman_server is defined
+  register: barman_user_exists
+  ignore_errors: true
+  changed_when: false
+
 - name: Allow SSH access on barman server
   authorized_key:
     user: "barman"
     key: "{{ db_server_pub_key['content']|b64decode }}"
     state: present
   delegate_to: "{{ postgres_barman_server_public|default(postgres_barman_server) }}"
-  when: postgres_barman_server is defined
+  when:
+    - postgres_barman_server is defined
+    - barman_user_exists is succeeded
 
 - name: Copy logrotate configuration for postgresql
   template: src=logrotate-postgresql-common.j2 dest=/etc/logrotate.d/postgresql-common owner=root group=root mode=0644
diff --git a/tasks/postgres-database-extensions.yml b/tasks/postgres-database-extensions.yml
new file mode 100644
index 0000000..edc4e8d
--- /dev/null
+++ b/tasks/postgres-database-extensions.yml
@@ -0,0 +1,23 @@
+---
+- name: "Install required packages for 'postgres_ext' ansible module"
+  apt:
+    name:
+      - libpq-dev
+      - python-psycopg2
+
+- name: Install system dependencies of extensions
+  apt:
+    name: "{{ postgres_extension.apt_deps | default([]) }}"
+    cache_valid_time: 86400
+
+- name: "CREATE EXTENSION on database {{ postgres_dbname }}"
+  become: true
+  become_user: postgres
+  become_method: su
+  postgresql_ext:
+    name: "{{ postgres_extension_name }}"
+    db: "{{ postgres_dbname }}"
+    port: "{{ postgres_port }}"
+  loop: "{{ postgres_extension.names | default([]) }}"
+  loop_control:
+    loop_var: postgres_extension_name
diff --git a/tasks/postgres-database.yml b/tasks/postgres-database.yml
index c7b09a8..c3537ea 100644
--- a/tasks/postgres-database.yml
+++ b/tasks/postgres-database.yml
@@ -18,3 +18,10 @@
     owner:            "{{ postgres_owner }}"
   when:
     - postgres_up.rc == 0
+
+- name: Create database extensions
+  include_tasks: postgres-database-extensions.yml
+  loop: "{{ postgres_extensions| default([]) }}"
+  loop_control:
+    loop_var: postgres_extension
+  when: postgres_up.rc == 0
diff --git a/test/main.yml b/test/main.yml
index 6258199..a4834be 100644
--- a/test/main.yml
+++ b/test/main.yml
@@ -49,12 +49,25 @@
             archive_enabled: True
             barman_directory: "{{ postgres_barman_directory }}"
             users:
+              - username: tester
+                password: tester
               - username: barman
                 password: "secret_cocktail"
                 permissions: 'SUPERUSER'
               - username: replicator
                 password: "secret_repli"
                 permissions: 'REPLICATION'
+            databases:
+              - dbname: test_database
+                owner: tester
+                extensions:
+                  - description: PostGis
+                    apt_deps:
+                      - "postgresql-{{ postgres_version }}-postgis"
+                    names:
+                      - postgis
+                      - postgis_topology
+                      - btree_gist
 
       with_items:
         - postgres_one
@@ -104,18 +117,19 @@
           - name: "{{ postgres_barman_directory }}"
             description: 'Main database'
             primary_host: postgres_one
-            primary_barman_password: "secret_cocktail"
+            postgres_barman_password: "secret_cocktail"
             backup_method: rsync
-            ansible_group: db-all
+            standby_hosts: "{{ groups['db-all'][1:] }}"
             retention_policy: 'RECOVERY WINDOW OF 7 DAYS'
       with_items:
         - postgres_barman
       tags: provision_docker
 
-- name: Test installing Barman role
-  hosts: barman
+- name: Test installing Postgresql role
+  hosts: primary:secondary
   connection: docker
-  vars: {}
+  vars:
+    postgres_listen_addresses: "{{ hostvars[inventory_hostname].ansible_default_ipv4.address }}"
   pre_tasks: &pre_tasks
      - name: "Build hosts file"
        shell: 'echo "{{ hostvars[inventory_hostname].ansible_default_ipv4.address }} {{inventory_hostname}}" >> /etc/hosts'
@@ -123,15 +137,6 @@
 
      - debug: msg="Running on host {{inventory_hostname}} ({{ hostvars[inventory_hostname].ansible_default_ipv4.address }})"
   roles:
-    - { role: barman, tags: ['barman'] }
-
-- name: Test installing Postgresql role
-  hosts: primary:secondary
-  connection: docker
-  vars:
-    postgres_listen_addresses: "{{ hostvars[inventory_hostname].ansible_default_ipv4.address }}"
-  pre_tasks: *pre_tasks
-  roles:
     - { role: ansible-postgresql-role, tags: ['postgres'] }
   tasks:
     - name: Start postgres cluster
@@ -144,6 +149,14 @@
       command: psql --port=5432 --command='select NOW()'
 
 
+- name: Test installing Barman role
+  hosts: barman
+  connection: docker
+  vars: {}
+  pre_tasks: *pre_tasks
+  roles:
+    - { role: barman, tags: ['barman'] }
+
 - name: Test running Postgresql role when database is started
   connection: docker
   hosts: primary:secondary
diff --git a/test/requirements.yml b/test/requirements.yml
index 4c72838..f9d7099 100644
--- a/test/requirements.yml
+++ b/test/requirements.yml
@@ -3,4 +3,4 @@
   version: 7a6243dfcf69f9d262877eca487bc14b62ba6960
 - src: trainline-eu.ansible_barman_role
   name: barman
-  version: 1.0.0
+  version: 1.1.0