From d0bc90e08c29e881c388c6803ed9c49dff1f1776 Mon Sep 17 00:00:00 2001
From: Paul B <paul.bonaud@capitainetrain.com>
Date: Fri, 31 Aug 2018 11:49:09 +0200
Subject: Initial commit open sourcing Postgresql Ansible role

---
 tasks/main.yml                        | 80 +++++++++++++++++++++++++++++++++
 tasks/postgres-cluster.yml            | 85 +++++++++++++++++++++++++++++++++++
 tasks/postgres-common-postinstall.yml | 46 +++++++++++++++++++
 tasks/postgres-common-preinstall.yml  | 23 ++++++++++
 tasks/postgres-data-directory.yml     | 10 +++++
 tasks/postgres-database.yml           | 20 +++++++++
 tasks/postgres-log-directory.yml      | 13 ++++++
 tasks/postgres-pgupgrades.yml         | 12 +++++
 tasks/postgres-standby-barman.yml     | 12 +++++
 tasks/postgres-user.yml               | 22 +++++++++
 10 files changed, 323 insertions(+)
 create mode 100644 tasks/main.yml
 create mode 100644 tasks/postgres-cluster.yml
 create mode 100644 tasks/postgres-common-postinstall.yml
 create mode 100644 tasks/postgres-common-preinstall.yml
 create mode 100644 tasks/postgres-data-directory.yml
 create mode 100644 tasks/postgres-database.yml
 create mode 100644 tasks/postgres-log-directory.yml
 create mode 100644 tasks/postgres-pgupgrades.yml
 create mode 100644 tasks/postgres-standby-barman.yml
 create mode 100644 tasks/postgres-user.yml

(limited to 'tasks')

diff --git a/tasks/main.yml b/tasks/main.yml
new file mode 100644
index 0000000..ad6b5bb
--- /dev/null
+++ b/tasks/main.yml
@@ -0,0 +1,80 @@
+---
+- include: postgres-common-preinstall.yml
+  tags:
+    - postgres
+
+- include: postgres-log-directory.yml
+  tags:
+    - postgres
+  when:
+   - postgres_log_dir is defined and postgres_log_dir
+
+- include: postgres-data-directory.yml
+  tags:
+    - postgres
+  when: postgres_data_dir is defined and postgres_data_dir
+
+- name: fail if no clusters are defined
+  fail:
+    msg: "You probably forgot to define a PostgreSQL cluster on your host (`postgres_clusters` variable). Stopping run of the PostgreSQL role."
+  when: (postgres_clusters is not defined) or (postgres_clusters|length == 0)
+
+- include: postgres-cluster.yml
+    postgres_version={{ item.version }}
+    postgres_cluster_name={{ item.name }}
+    postgres_port={{ item.port }}
+    postgres_fsync_enabled={{ item.fsync_enabled }}
+    postgres_archive_enabled={{ item.archive_enabled }}
+    postgres_max_replication_slots={{ item.max_replication_slots | default(10) }}
+    postgres_extra_config={{ item.extra_config | default({}) }}
+    barman_directory={{ item.barman_directory | default(None) }}
+    postgres_primary={{ item.primary | default(None) }}
+    postgres_checksums={{ item.checksums | default(True) }}
+  with_items: "{{ postgres_clusters }}"
+  tags:
+    - postgres
+
+- include: postgres-common-postinstall.yml
+  tags:
+    - postgres
+
+- include: postgres-user.yml
+    postgres_username={{ item.1.username }}
+    postgres_password={{ item.1.password }}
+    postgres_port={{ item.0.port }}
+    postgres_permissions={{ item.1.permissions | default('') }}
+  with_subelements:
+    - "{{ postgres_clusters }}"
+    - users
+    - skip_missing: true
+  tags:
+    - postgres
+    - postgres-users
+
+- include: postgres-database.yml
+    postgres_dbname={{ item.1.dbname }}
+    postgres_owner={{ item.1.owner }}
+    postgres_port={{ item.0.port }}
+  with_subelements:
+    - "{{ postgres_clusters }}"
+    - databases
+    - skip_missing: true
+  tags:
+    - postgres
+    - postgres-databases
+
+- include: postgres-pgupgrades.yml
+    postgres_dbname={{ item.dbname }}
+    postgres_pgbouncer_uri={{ item.pgbouncer_uri|default(None) }}
+    postgres_old_cluster_version={{ item.old_cluster_version }}
+    postgres_old_cluster_name={{ item.old_cluster_name }}
+    postgres_new_cluster_version={{ item.new_cluster_version }}
+    postgres_new_cluster_name={{ item.new_cluster_name }}
+    postgres_standby_server={{ item.standby_server }}
+    postgres_standby_old_cluster_name={{ item.standby_old_cluster_name|default(item.old_cluster_name) }}
+    postgres_standby_new_cluster_name={{ item.standby_new_cluster_name|default(item.new_cluster_name) }}
+  with_items: "{{ postgres_pgupgrades }}"
+  when: postgres_pgupgrades is defined
+  tags:
+    - postgres
+    - postgres-pgupgrades
diff --git a/tasks/postgres-cluster.yml b/tasks/postgres-cluster.yml
new file mode 100644
index 0000000..83958d2
--- /dev/null
+++ b/tasks/postgres-cluster.yml
@@ -0,0 +1,85 @@
+- name: Install postgresql version {{ postgres_version }}
+  apt: name=postgresql-{{ postgres_version }}
+  when: ansible_distribution_release != 'NA'
+
+- name: Install pg_repack version {{ postgres_version }}
+  apt: name=postgresql-{{ postgres_version }}-repack
+  when:
+   - ansible_distribution_release != 'NA'
+   - postgres_version in ['9.3', '9.4', '9.5', '9.6', '10']
+
+- name: Set initdb options
+  set_fact: postgres_initdb_option="{% if postgres_checksums %}--data-checksums{% endif %}"
+
+- name: Create postgres cluster {{ postgres_version }}/{{ postgres_cluster_name }} if needed
+  command: /usr/bin/pg_createcluster {{ postgres_version }} {{ postgres_cluster_name }} -- {{ postgres_initdb_option }}
+  args:
+    creates: /var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/
+
+- name: Check postgres server key
+  stat: path=/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/server.key
+  register: server_key
+
+- name: Copy snakeoil key in postgres dir
+  copy:
+    src=/etc/ssl/private/ssl-cert-snakeoil.key
+    dest=/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/server.key
+    remote_src=true
+    owner=postgres
+    group=postgres
+    mode="0600"
+  when: not server_key.stat.exists or server_key.stat.islnk
+
+- name: Check postgres server certificate
+  stat: path=/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/server.crt
+  register: server_crt
+
+- name: Copy snakeoil certificate in postgres dir
+  copy:
+    src=/etc/ssl/certs/ssl-cert-snakeoil.pem
+    dest=/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/server.crt
+    remote_src=true
+    owner=postgres
+    group=postgres
+    mode="0644"
+  when: not server_crt.stat.exists or server_crt.stat.islnk
+
+- name: Add standby clone from barman script
+  include: postgres-standby-barman.yml
+  when: postgres_barman_server is defined
+
+- name: Determine SSD or rotational disks
+  raw: 'lsblk --noheadings --nodeps --raw --output=rota | grep -q 1'
+  check_mode: no
+  ignore_errors: yes
+  register: rotational_disk
+
+- name: Set specific random page cost for SSDs
+  set_fact:
+    postgres_specific_random_page_cost: "1.0"
+  when:
+    - rotational_disk.failed is defined
+    - rotational_disk.failed
+
+- name: Upload postgresql.conf
+  template: src=postgresql.{{ postgres_version }}.conf.j2 dest=/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/postgresql.conf
+
+- name: Upload pg_hba.conf
+  template: src=pg_hba.conf.j2 dest=/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/pg_hba.conf
+
+- name: Upload recovery.conf
+  template: src=recovery.conf.j2 dest=/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/recovery.conf owner=postgres group=postgres mode=0600
+  when: postgres_primary
+  no_log: True
+
+- name: Create log file
+  file:
+    path={{ postgres_log_dir }}/postgresql-{{ postgres_version }}-{{ postgres_cluster_name }}.log
+    state=touch
+    owner=postgres
+    group=adm
+  when: postgres_log_dir is defined and postgres_log_dir
+
+- name: Create log symlink for pg_lsclusters
+  file: dest=/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/log src={{ postgres_log_dir }}/postgresql-{{ postgres_version }}-{{ postgres_cluster_name }}.log state=link
+  when: postgres_log_dir is defined and postgres_log_dir
diff --git a/tasks/postgres-common-postinstall.yml b/tasks/postgres-common-postinstall.yml
new file mode 100644
index 0000000..277fafd
--- /dev/null
+++ b/tasks/postgres-common-postinstall.yml
@@ -0,0 +1,46 @@
+- user: name=postgres append=yes groups=ssl-cert
+
+- name: Creates .ssh directory
+  file: path=/var/lib/postgresql/.ssh state=directory owner=postgres group=postgres mode=0700
+
+- name: Create postgres SSH key
+  command: ssh-keygen -b 4096 -f /var/lib/postgresql/.ssh/id_rsa -N "" -q
+  become: yes
+  become_user: postgres
+  become_method: su
+  args:
+    creates: /var/lib/postgresql/.ssh/id_rsa
+
+- slurp:
+    src: /var/lib/postgresql/.ssh/id_rsa.pub
+  register: db_server_pub_key
+
+- 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
+
+- name: Copy logrotate configuration for postgresql
+  template: src=logrotate-postgresql-common.j2 dest=/etc/logrotate.d/postgresql-common owner=root group=root mode=0644
+
+- name: Create /root/scripts needed directory
+  file: path=/root/scripts/ state=directory
+
+- name: Create pgbadger user
+  user: name=pgbadger home=/home/pgbadger
+  when: postgres_pgbadger_server is defined and postgres_pgbadger_server
+
+- name: Copy pbBadger script
+  template: src=pgBadger.sh.j2 dest=/root/scripts/pgBadger.sh owner=root group=root mode=0755
+  when: postgres_pgbadger_server is defined and postgres_pgbadger_server
+
+- name: Ensure dedicated log directory is owned by postgres
+  file: path={{ postgres_log_dir }} state=directory owner=postgres group=postgres
+  when: postgres_log_dir is defined and postgres_log_dir
+
+- name: Ensure dedicated data directory is owned by postgres
+  file: path={{ postgres_data_dir }} state=directory owner=postgres group=postgres
+  when: postgres_data_dir is defined and postgres_data_dir
diff --git a/tasks/postgres-common-preinstall.yml b/tasks/postgres-common-preinstall.yml
new file mode 100644
index 0000000..32bcf58
--- /dev/null
+++ b/tasks/postgres-common-preinstall.yml
@@ -0,0 +1,23 @@
+- name: Add Postgres repository key
+  apt_key: id=7FCC7D46ACCC4CF8 url=https://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc state=present
+  when: ansible_distribution_release != 'NA'
+
+- name: Add Postgres repository
+  apt_repository: repo="deb http://apt.postgresql.org/pub/repos/apt/ {{ ansible_distribution_release }}-pgdg main"
+  when: ansible_distribution_release != 'NA'
+
+- name: Increase shmmax sysctl paramater
+  sysctl: name="kernel.shmmax" value=586162176 sysctl_set=yes state=present reload=yes
+
+- name: Install rsync
+  apt: name=rsync
+  when: ansible_distribution_release != 'NA'
+
+- name: Install time
+  apt: name=time state=present
+
+- name: Install barman-cli
+  apt: name=barman-cli state=present
+
+- name: Install pg-activity
+  apt: name=pg-activity state=present
diff --git a/tasks/postgres-data-directory.yml b/tasks/postgres-data-directory.yml
new file mode 100644
index 0000000..02b6df8
--- /dev/null
+++ b/tasks/postgres-data-directory.yml
@@ -0,0 +1,10 @@
+- name: Create dedicated data directory
+  file: path={{ postgres_data_dir }} state=directory owner=postgres group=postgres
+
+- name: Check /var/lib/postgresql directory
+  stat: path=/var/lib/postgresql/
+  register: var_lib_postgresql
+
+- name: Create a symlink between regular data directory and dedicated one
+  file: src={{ postgres_data_dir }} dest=/var/lib/postgresql state=link
+  when: not var_lib_postgresql.stat.exists
diff --git a/tasks/postgres-database.yml b/tasks/postgres-database.yml
new file mode 100644
index 0000000..c7b09a8
--- /dev/null
+++ b/tasks/postgres-database.yml
@@ -0,0 +1,20 @@
+- name: Test if the PostgreSQL server is up
+  become: true
+  become_user: postgres
+  become_method: su
+  command: psql --port={{ postgres_port }} --command='select NOW()'
+  register: postgres_up
+  ignore_errors: yes
+  check_mode: no
+  changed_when: false
+
+- name: Create PostgreSQL database
+  become: true
+  become_user: postgres
+  become_method: su
+  postgresql_db:
+    port:             "{{ postgres_port }}"
+    name:             "{{ postgres_dbname }}"
+    owner:            "{{ postgres_owner }}"
+  when:
+    - postgres_up.rc == 0
diff --git a/tasks/postgres-log-directory.yml b/tasks/postgres-log-directory.yml
new file mode 100644
index 0000000..df7ede0
--- /dev/null
+++ b/tasks/postgres-log-directory.yml
@@ -0,0 +1,13 @@
+- name: Create dedicated log directory
+  file: path={{ postgres_log_dir }} state=directory
+
+- name: Check /var/log/postgresql
+  stat: path=/var/log/postgresql
+  register: var_log_postgresql
+
+- name: Move /var/log/postgresql directory if not a link
+  command: mv /var/log/postgresql /var/log/old-postgresql
+  when: var_log_postgresql.stat.isdir is defined and var_log_postgresql.stat.isdir
+
+- name: Create a symlink so regular log path points to dedicated log directory
+  file: src={{ postgres_log_dir }} dest=/var/log/postgresql state=link
diff --git a/tasks/postgres-pgupgrades.yml b/tasks/postgres-pgupgrades.yml
new file mode 100644
index 0000000..a0b17b4
--- /dev/null
+++ b/tasks/postgres-pgupgrades.yml
@@ -0,0 +1,12 @@
+---
+- name: Find matching new cluster
+  set_fact:
+    postgres_new_cluster: "{{ postgres_clusters|selectattr('name','equalto',postgres_new_cluster_name)|selectattr('version','equalto',postgres_new_cluster_version|float)|list|first }}"
+
+- name: Extract database port of new matching cluster
+  set_fact:
+    postgres_new_cluster_port: "{{ (postgres_new_cluster|default({'port': 5432}))['port'] }}"
+
+- name: Upload pg_upgrade.sh script
+  template: src=pg_upgrade.sh.j2 dest=/var/lib/postgresql/pg_upgrade_{{ postgres_old_cluster_version }}_{{ postgres_new_cluster_version }}_{{ postgres_dbname }} owner=postgres group=postgres mode=0700
+  no_log: True
diff --git a/tasks/postgres-standby-barman.yml b/tasks/postgres-standby-barman.yml
new file mode 100644
index 0000000..793b942
--- /dev/null
+++ b/tasks/postgres-standby-barman.yml
@@ -0,0 +1,12 @@
+---
+- name: Copy secondary script
+  template: src=standby-clone.sh.j2 dest=/root/standby-clone-{{ postgres_version }}-{{ postgres_cluster_name }}.sh mode=0755
+
+- name: Copy rsync password file
+  copy:
+    content: "{{ barman_rsync_password }}"
+    dest: /var/lib/postgresql/.rsync_pass
+    owner: postgres
+    group: postgres
+    mode: 0400
+  no_log: True
diff --git a/tasks/postgres-user.yml b/tasks/postgres-user.yml
new file mode 100644
index 0000000..308dab8
--- /dev/null
+++ b/tasks/postgres-user.yml
@@ -0,0 +1,22 @@
+- name: Test if the PostgreSQL server is up
+  become: true
+  become_user: postgres
+  become_method: su
+  command: psql --port={{ postgres_port }} --command='select NOW()'
+  register: postgres_up
+  ignore_errors: yes
+  check_mode: no
+  changed_when: false
+
+- name: Create PostgreSQL user
+  become: true
+  become_user: postgres
+  become_method: su
+  postgresql_user:
+    port:             "{{ postgres_port }}"
+    name:             "{{ postgres_username }}"
+    password:         "{{ postgres_password }}"
+    encrypted:        true
+    role_attr_flags:  "{{ postgres_permissions }}"
+  when:
+    - postgres_up.rc == 0
-- 
cgit v1.2.3