aboutsummaryrefslogtreecommitdiffhomepage
path: root/tasks
diff options
context:
space:
mode:
Diffstat (limited to 'tasks')
-rw-r--r--tasks/main.yml80
-rw-r--r--tasks/postgres-cluster.yml85
-rw-r--r--tasks/postgres-common-postinstall.yml46
-rw-r--r--tasks/postgres-common-preinstall.yml23
-rw-r--r--tasks/postgres-data-directory.yml10
-rw-r--r--tasks/postgres-database.yml20
-rw-r--r--tasks/postgres-log-directory.yml13
-rw-r--r--tasks/postgres-pgupgrades.yml12
-rw-r--r--tasks/postgres-standby-barman.yml12
-rw-r--r--tasks/postgres-user.yml22
10 files changed, 323 insertions, 0 deletions
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 @@
1---
2- include: postgres-common-preinstall.yml
3 tags:
4 - postgres
5
6- include: postgres-log-directory.yml
7 tags:
8 - postgres
9 when:
10 - postgres_log_dir is defined and postgres_log_dir
11
12- include: postgres-data-directory.yml
13 tags:
14 - postgres
15 when: postgres_data_dir is defined and postgres_data_dir
16
17- name: fail if no clusters are defined
18 fail:
19 msg: "You probably forgot to define a PostgreSQL cluster on your host (`postgres_clusters` variable). Stopping run of the PostgreSQL role."
20 when: (postgres_clusters is not defined) or (postgres_clusters|length == 0)
21
22- include: postgres-cluster.yml
23 postgres_version={{ item.version }}
24 postgres_cluster_name={{ item.name }}
25 postgres_port={{ item.port }}
26 postgres_fsync_enabled={{ item.fsync_enabled }}
27 postgres_archive_enabled={{ item.archive_enabled }}
28 postgres_max_replication_slots={{ item.max_replication_slots | default(10) }}
29 postgres_extra_config={{ item.extra_config | default({}) }}
30 barman_directory={{ item.barman_directory | default(None) }}
31 postgres_primary={{ item.primary | default(None) }}
32 postgres_checksums={{ item.checksums | default(True) }}
33 with_items: "{{ postgres_clusters }}"
34 tags:
35 - postgres
36
37- include: postgres-common-postinstall.yml
38 tags:
39 - postgres
40
41- include: postgres-user.yml
42 postgres_username={{ item.1.username }}
43 postgres_password={{ item.1.password }}
44 postgres_port={{ item.0.port }}
45 postgres_permissions={{ item.1.permissions | default('') }}
46 with_subelements:
47 - "{{ postgres_clusters }}"
48 - users
49 - skip_missing: true
50 tags:
51 - postgres
52 - postgres-users
53
54- include: postgres-database.yml
55 postgres_dbname={{ item.1.dbname }}
56 postgres_owner={{ item.1.owner }}
57 postgres_port={{ item.0.port }}
58 with_subelements:
59 - "{{ postgres_clusters }}"
60 - databases
61 - skip_missing: true
62 tags:
63 - postgres
64 - postgres-databases
65
66- include: postgres-pgupgrades.yml
67 postgres_dbname={{ item.dbname }}
68 postgres_pgbouncer_uri={{ item.pgbouncer_uri|default(None) }}
69 postgres_old_cluster_version={{ item.old_cluster_version }}
70 postgres_old_cluster_name={{ item.old_cluster_name }}
71 postgres_new_cluster_version={{ item.new_cluster_version }}
72 postgres_new_cluster_name={{ item.new_cluster_name }}
73 postgres_standby_server={{ item.standby_server }}
74 postgres_standby_old_cluster_name={{ item.standby_old_cluster_name|default(item.old_cluster_name) }}
75 postgres_standby_new_cluster_name={{ item.standby_new_cluster_name|default(item.new_cluster_name) }}
76 with_items: "{{ postgres_pgupgrades }}"
77 when: postgres_pgupgrades is defined
78 tags:
79 - postgres
80 - 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 @@
1- name: Install postgresql version {{ postgres_version }}
2 apt: name=postgresql-{{ postgres_version }}
3 when: ansible_distribution_release != 'NA'
4
5- name: Install pg_repack version {{ postgres_version }}
6 apt: name=postgresql-{{ postgres_version }}-repack
7 when:
8 - ansible_distribution_release != 'NA'
9 - postgres_version in ['9.3', '9.4', '9.5', '9.6', '10']
10
11- name: Set initdb options
12 set_fact: postgres_initdb_option="{% if postgres_checksums %}--data-checksums{% endif %}"
13
14- name: Create postgres cluster {{ postgres_version }}/{{ postgres_cluster_name }} if needed
15 command: /usr/bin/pg_createcluster {{ postgres_version }} {{ postgres_cluster_name }} -- {{ postgres_initdb_option }}
16 args:
17 creates: /var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/
18
19- name: Check postgres server key
20 stat: path=/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/server.key
21 register: server_key
22
23- name: Copy snakeoil key in postgres dir
24 copy:
25 src=/etc/ssl/private/ssl-cert-snakeoil.key
26 dest=/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/server.key
27 remote_src=true
28 owner=postgres
29 group=postgres
30 mode="0600"
31 when: not server_key.stat.exists or server_key.stat.islnk
32
33- name: Check postgres server certificate
34 stat: path=/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/server.crt
35 register: server_crt
36
37- name: Copy snakeoil certificate in postgres dir
38 copy:
39 src=/etc/ssl/certs/ssl-cert-snakeoil.pem
40 dest=/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/server.crt
41 remote_src=true
42 owner=postgres
43 group=postgres
44 mode="0644"
45 when: not server_crt.stat.exists or server_crt.stat.islnk
46
47- name: Add standby clone from barman script
48 include: postgres-standby-barman.yml
49 when: postgres_barman_server is defined
50
51- name: Determine SSD or rotational disks
52 raw: 'lsblk --noheadings --nodeps --raw --output=rota | grep -q 1'
53 check_mode: no
54 ignore_errors: yes
55 register: rotational_disk
56
57- name: Set specific random page cost for SSDs
58 set_fact:
59 postgres_specific_random_page_cost: "1.0"
60 when:
61 - rotational_disk.failed is defined
62 - rotational_disk.failed
63
64- name: Upload postgresql.conf
65 template: src=postgresql.{{ postgres_version }}.conf.j2 dest=/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/postgresql.conf
66
67- name: Upload pg_hba.conf
68 template: src=pg_hba.conf.j2 dest=/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/pg_hba.conf
69
70- name: Upload recovery.conf
71 template: src=recovery.conf.j2 dest=/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/recovery.conf owner=postgres group=postgres mode=0600
72 when: postgres_primary
73 no_log: True
74
75- name: Create log file
76 file:
77 path={{ postgres_log_dir }}/postgresql-{{ postgres_version }}-{{ postgres_cluster_name }}.log
78 state=touch
79 owner=postgres
80 group=adm
81 when: postgres_log_dir is defined and postgres_log_dir
82
83- name: Create log symlink for pg_lsclusters
84 file: dest=/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/log src={{ postgres_log_dir }}/postgresql-{{ postgres_version }}-{{ postgres_cluster_name }}.log state=link
85 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 @@
1- user: name=postgres append=yes groups=ssl-cert
2
3- name: Creates .ssh directory
4 file: path=/var/lib/postgresql/.ssh state=directory owner=postgres group=postgres mode=0700
5
6- name: Create postgres SSH key
7 command: ssh-keygen -b 4096 -f /var/lib/postgresql/.ssh/id_rsa -N "" -q
8 become: yes
9 become_user: postgres
10 become_method: su
11 args:
12 creates: /var/lib/postgresql/.ssh/id_rsa
13
14- slurp:
15 src: /var/lib/postgresql/.ssh/id_rsa.pub
16 register: db_server_pub_key
17
18- name: Allow SSH access on barman server
19 authorized_key:
20 user: "barman"
21 key: "{{ db_server_pub_key['content']|b64decode }}"
22 state: present
23 delegate_to: "{{ postgres_barman_server_public|default(postgres_barman_server) }}"
24 when: postgres_barman_server is defined
25
26- name: Copy logrotate configuration for postgresql
27 template: src=logrotate-postgresql-common.j2 dest=/etc/logrotate.d/postgresql-common owner=root group=root mode=0644
28
29- name: Create /root/scripts needed directory
30 file: path=/root/scripts/ state=directory
31
32- name: Create pgbadger user
33 user: name=pgbadger home=/home/pgbadger
34 when: postgres_pgbadger_server is defined and postgres_pgbadger_server
35
36- name: Copy pbBadger script
37 template: src=pgBadger.sh.j2 dest=/root/scripts/pgBadger.sh owner=root group=root mode=0755
38 when: postgres_pgbadger_server is defined and postgres_pgbadger_server
39
40- name: Ensure dedicated log directory is owned by postgres
41 file: path={{ postgres_log_dir }} state=directory owner=postgres group=postgres
42 when: postgres_log_dir is defined and postgres_log_dir
43
44- name: Ensure dedicated data directory is owned by postgres
45 file: path={{ postgres_data_dir }} state=directory owner=postgres group=postgres
46 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 @@
1- name: Add Postgres repository key
2 apt_key: id=7FCC7D46ACCC4CF8 url=https://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc state=present
3 when: ansible_distribution_release != 'NA'
4
5- name: Add Postgres repository
6 apt_repository: repo="deb http://apt.postgresql.org/pub/repos/apt/ {{ ansible_distribution_release }}-pgdg main"
7 when: ansible_distribution_release != 'NA'
8
9- name: Increase shmmax sysctl paramater
10 sysctl: name="kernel.shmmax" value=586162176 sysctl_set=yes state=present reload=yes
11
12- name: Install rsync
13 apt: name=rsync
14 when: ansible_distribution_release != 'NA'
15
16- name: Install time
17 apt: name=time state=present
18
19- name: Install barman-cli
20 apt: name=barman-cli state=present
21
22- name: Install pg-activity
23 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 @@
1- name: Create dedicated data directory
2 file: path={{ postgres_data_dir }} state=directory owner=postgres group=postgres
3
4- name: Check /var/lib/postgresql directory
5 stat: path=/var/lib/postgresql/
6 register: var_lib_postgresql
7
8- name: Create a symlink between regular data directory and dedicated one
9 file: src={{ postgres_data_dir }} dest=/var/lib/postgresql state=link
10 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 @@
1- name: Test if the PostgreSQL server is up
2 become: true
3 become_user: postgres
4 become_method: su
5 command: psql --port={{ postgres_port }} --command='select NOW()'
6 register: postgres_up
7 ignore_errors: yes
8 check_mode: no
9 changed_when: false
10
11- name: Create PostgreSQL database
12 become: true
13 become_user: postgres
14 become_method: su
15 postgresql_db:
16 port: "{{ postgres_port }}"
17 name: "{{ postgres_dbname }}"
18 owner: "{{ postgres_owner }}"
19 when:
20 - 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 @@
1- name: Create dedicated log directory
2 file: path={{ postgres_log_dir }} state=directory
3
4- name: Check /var/log/postgresql
5 stat: path=/var/log/postgresql
6 register: var_log_postgresql
7
8- name: Move /var/log/postgresql directory if not a link
9 command: mv /var/log/postgresql /var/log/old-postgresql
10 when: var_log_postgresql.stat.isdir is defined and var_log_postgresql.stat.isdir
11
12- name: Create a symlink so regular log path points to dedicated log directory
13 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 @@
1---
2- name: Find matching new cluster
3 set_fact:
4 postgres_new_cluster: "{{ postgres_clusters|selectattr('name','equalto',postgres_new_cluster_name)|selectattr('version','equalto',postgres_new_cluster_version|float)|list|first }}"
5
6- name: Extract database port of new matching cluster
7 set_fact:
8 postgres_new_cluster_port: "{{ (postgres_new_cluster|default({'port': 5432}))['port'] }}"
9
10- name: Upload pg_upgrade.sh script
11 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
12 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 @@
1---
2- name: Copy secondary script
3 template: src=standby-clone.sh.j2 dest=/root/standby-clone-{{ postgres_version }}-{{ postgres_cluster_name }}.sh mode=0755
4
5- name: Copy rsync password file
6 copy:
7 content: "{{ barman_rsync_password }}"
8 dest: /var/lib/postgresql/.rsync_pass
9 owner: postgres
10 group: postgres
11 mode: 0400
12 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 @@
1- name: Test if the PostgreSQL server is up
2 become: true
3 become_user: postgres
4 become_method: su
5 command: psql --port={{ postgres_port }} --command='select NOW()'
6 register: postgres_up
7 ignore_errors: yes
8 check_mode: no
9 changed_when: false
10
11- name: Create PostgreSQL user
12 become: true
13 become_user: postgres
14 become_method: su
15 postgresql_user:
16 port: "{{ postgres_port }}"
17 name: "{{ postgres_username }}"
18 password: "{{ postgres_password }}"
19 encrypted: true
20 role_attr_flags: "{{ postgres_permissions }}"
21 when:
22 - postgres_up.rc == 0
generated by cgit v1.2.3 (git 2.25.1) at 2024-06-01 16:42:01 +0000