aboutsummaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
authorPaul B <paul.bonaud@capitainetrain.com>2018-08-31 11:49:09 +0200
committerPaul B <paul.bonaud@capitainetrain.com>2018-08-31 12:00:24 +0200
commitd0bc90e08c29e881c388c6803ed9c49dff1f1776 (patch)
treee5f633a7461f3d339ef89758fc7bb3f1b91563d3
downloadansible-postgresql-role-d0bc90e08c29e881c388c6803ed9c49dff1f1776.tar.gz
ansible-postgresql-role-d0bc90e08c29e881c388c6803ed9c49dff1f1776.tar.zst
ansible-postgresql-role-d0bc90e08c29e881c388c6803ed9c49dff1f1776.zip
Initial commit open sourcing Postgresql Ansible role1.0.0
Diffstat
-rw-r--r--.travis.yml25
-rw-r--r--LICENSE21
-rw-r--r--README.md103
-rw-r--r--defaults/main.yml18
-rw-r--r--meta/main.yml20
-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
-rw-r--r--templates/logrotate-postgresql-common.j216
-rw-r--r--templates/pgBadger.sh.j210
-rw-r--r--templates/pg_hba.conf.j2107
-rwxr-xr-xtemplates/pg_upgrade.sh.j2175
-rw-r--r--templates/postgresql.10.conf.j2694
-rw-r--r--templates/postgresql.9.1.conf.j2589
-rw-r--r--templates/postgresql.9.4.conf.j2647
-rw-r--r--templates/postgresql.9.5.conf.j2657
-rw-r--r--templates/postgresql.9.6.conf.j2685
-rw-r--r--templates/recovery.conf.j28
-rwxr-xr-xtemplates/standby-clone.sh.j244
-rw-r--r--test/hosts0
-rw-r--r--test/main.yml154
-rw-r--r--test/requirements.txt1
-rw-r--r--test/requirements.yml3
l---------test/roles/postgresql1
31 files changed, 4301 insertions, 0 deletions
diff --git a/.travis.yml b/.travis.yml
new file mode 100644
index 0000000..1ffbc33
--- /dev/null
+++ b/.travis.yml
@@ -0,0 +1,25 @@
1---
2language: python
3python: "3.5"
4
5# Use the new container infrastructure
6sudo: false
7
8# Install pip
9addons:
10 apt:
11 packages:
12 - python-pip
13
14install:
15 # Install ansible
16 - pip install ansible
17 # Check ansible version
18 - ansible --version
19
20script:
21 # Basic role syntax check
22 - ansible-playbook test/main.yml -i test --syntax-check
23
24notifications:
25 webhooks: https://galaxy.ansible.com/api/v1/notifications/
diff --git a/LICENSE b/LICENSE
new file mode 100644
index 0000000..82a74ff
--- /dev/null
+++ b/LICENSE
@@ -0,0 +1,21 @@
1The MIT License
2
3Copyright (c) 2018 Trainline SAS
4
5Permission is hereby granted, free of charge, to any person obtaining a copy
6of this software and associated documentation files (the "Software"), to deal
7in the Software without restriction, including without limitation the rights
8to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
9copies of the Software, and to permit persons to whom the Software is
10furnished to do so, subject to the following conditions:
11
12The above copyright notice and this permission notice shall be included in
13all copies or substantial portions of the Software.
14
15THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
16IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
17FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
18AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
19LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
20OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
21THE SOFTWARE.
diff --git a/README.md b/README.md
new file mode 100644
index 0000000..f07f2d4
--- /dev/null
+++ b/README.md
@@ -0,0 +1,103 @@
1## PostgreSQL Ansible role [![Build Status](https://travis-ci.org/trainline-eu/postgresql.svg?branch=master)](https://travis-ci.org/trainline-eu/postgresql)
2
3Ansible role which installs and configures PostgreSQL clusters, databases and users.
4
5#### Installation
6
7This role has been tested on Ansible 2.3.0 and higher.
8
9To install:
10
11```
12ansible-galaxy install trainline-eu.postgresql
13```
14
15
16#### Dependencies
17
18No dependencies
19
20Recommended dependencies:
21- trainline-eu.barman
22
23#### Compatibility matrix
24
25This table lists the tested version of OS/PostgreSQL couples.
26
27| Distribution / PostgreSQL | 9.1 | 9.4 | 9.5 | 9.6 | 10 | 11 |
28| ------------------------- |:---:|:---:|:---:|:---:|:---:|:---:|
29| Debian 8.x | :no_entry: :white_check_mark:| :white_check_mark:| :white_check_mark:| :white_check_mark:| :white_check_mark:| :grey_question:|
30| Debian 9.x | :no_entry: :white_check_mark:| :white_check_mark:| :white_check_mark:| :white_check_mark:| :white_check_mark:| :grey_question:|
31
32- :white_check_mark: - tested, works fine
33- :grey_question: - will work in the future (help out if you can)
34- :interrobang: - maybe works, not tested
35- :no_entry: - PostgreSQL has reached EOL
36
37
38#### Variables
39
40```yaml
41# Basic settings
42postgres_listen_addresses: [ '127.0.0.1' ] # Optional
43postgres_log_dir: '/home/postgres-logs' # Optional
44postgres_data_dir: '/home/postgres' # Optional
45postgres_clusters: # Mandatory
46 - version: 10 # Mandatory
47 name: 'main' # Mandatory
48 port: 5432 # Mandatory
49 checksums: True # Optional
50 fsync_enabled: False # Optional
51 archive_enabled: False # Optional
52 # List of users to be created (optional)
53 users:
54 - username: 'replicator' # Mandatory
55 password: 'SuperSecret' # Mandatory
56 permissions: 'REPLICATION' # Mandatory
57 # List of databases to be created (optional)
58 databases:
59 - dbname: my_database # Mandatory
60 owner: john # Mandatory
61
62# Postgres config (Optional)
63postgres_log_line_prefix: '%m [%p] database: %d host: %h user: %u '
64postgres_datestyle: 'iso, dmy'
65postgres_locale_formats: fr_FR.UTF-8
66postgres_text_search_config: pg_catalog.french
67
68# Postgres pg_hba config (optional)
69postgres_allowed_hosts:
70 - user: all
71 range: 10.0.0.0/24
72postgres_replication_hosts:
73 - user: replicator
74 range: 10.0.0.0/24
75```
76
77#### Testing
78
79This project comes with a `test/main.yml` testing playbook. It uses `Docker` to provision containers locally and sets up a 3 node postgresql cluster with a barman server.
80
81Coverage of this playbook is probably not complete but at least it's there.
82
83If you are contributing, please first test your changes in a new playbook in the `test/` directory within docker containers, (using the targeted distribution), and if possible, ensure your change is covered in the tests found in [.travis.yml](./.travis.yml).
84
85#### License
86
87Licensed under the MIT License. See the [LICENSE](./LICENSE) file for details.
88
89
90#### Thanks
91
92Creators:
93- [Gaëtan Duchaussois](https://twitter.com/gduchaussois)
94- [Théophile Helleboid](https://twitter.com/chtitux)
95- [Paul Bonaud](https://twitter.com/paulRb_r)
96
97Maintainers:
98- [Théophile Helleboid](https://twitter.com/chtitux)
99- [Paul Bonaud](https://twitter.com/paulRb_r)
100
101#### Feedback, bug-reports, requests, ...
102
103Are [welcome](https://github.com/trainline-eu/postgresql/issues)!
diff --git a/defaults/main.yml b/defaults/main.yml
new file mode 100644
index 0000000..59db662
--- /dev/null
+++ b/defaults/main.yml
@@ -0,0 +1,18 @@
1---
2postgres_listen_addresses:
3 - '127.0.0.1'
4postgres_clusters: []
5# postgres_data_dir: ~
6# postgres_log_dir: ~
7# postgres_pgbadger_server: ~
8postgres_backup_enabled: false
9#------------------------------------------------------------#
10#------------ Postgresql Configuration defaults -------------#
11#- WARNING: defaults can change with major version upgrades -#
12postgres_log_line_prefix: '%m [%p] %q%u@%d '
13postgres_datestyle: 'iso, mdy'
14postgres_locale_system: C.UTF-8
15postgres_locale_formats: C.UTF-8
16postgres_text_search_config: pg_catalog.english
17#------------------------------------------------------------#
18
diff --git a/meta/main.yml b/meta/main.yml
new file mode 100644
index 0000000..e0a9f49
--- /dev/null
+++ b/meta/main.yml
@@ -0,0 +1,20 @@
1
2galaxy_info:
3 author: Trainline SAS
4 description: "Install, configure and manage PostgreSQL clusters"
5 license: MIT
6
7 min_ansible_version: 2.0
8
9 platforms:
10 - name: Ubuntu
11 versions:
12 - xenial
13 - name: Debian
14 versions:
15 - jessie
16 - stretch
17
18 galaxy_tags: ['postgresql', 'cluster', 'ha', 'database', 'replication']
19
20dependencies: []
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
diff --git a/templates/logrotate-postgresql-common.j2 b/templates/logrotate-postgresql-common.j2
new file mode 100644
index 0000000..06245b6
--- /dev/null
+++ b/templates/logrotate-postgresql-common.j2
@@ -0,0 +1,16 @@
1{{ postgres_log_dir|default('/var/log/postgresql') }}/*.log
2{
3 daily
4 rotate 60
5 copytruncate
6 delaycompress
7 compress
8 notifempty
9 missingok
10 su root root
11{% if postgres_pgbadger_server is defined and postgres_pgbadger_server %}
12 lastaction
13 /root/scripts/pgBadger.sh
14 endscript
15{% endif -%}
16}
diff --git a/templates/pgBadger.sh.j2 b/templates/pgBadger.sh.j2
new file mode 100644
index 0000000..93a2247
--- /dev/null
+++ b/templates/pgBadger.sh.j2
@@ -0,0 +1,10 @@
1#!/bin/bash
2# {{ ansible_managed }}
3
4{% for cluster in postgres_clusters %}
5install --mode=0644 --owner=pgbadger {{ postgres_log_dir | default("/var/log/postgresql") }}/postgresql-{{ cluster.version }}-{{ cluster.name }}.log.1 ~pgbadger/
6sudo -H -u pgbadger scp ~pgbadger/postgresql-{{ cluster.version }}-{{ cluster.name }}.log.1 {{ postgres_pgbadger_server }}:postgresql-{{ ansible_nodename }}-{{ cluster.version }}-{{ cluster.name }}.log.1
7sudo -H -u pgbadger ssh {{ postgres_pgbadger_server }} ./update.sh {{ ansible_nodename }}-{{ cluster.version }}-{{ cluster.name }}
8{% else %}
9# No servers are configured for sending logs to pgbadger
10{% endfor %}
diff --git a/templates/pg_hba.conf.j2 b/templates/pg_hba.conf.j2
new file mode 100644
index 0000000..930ed2a
--- /dev/null
+++ b/templates/pg_hba.conf.j2
@@ -0,0 +1,107 @@
1# {{ ansible_managed }}
2# PostgreSQL Client Authentication Configuration File
3# ===================================================
4#
5# Refer to the "Client Authentication" section in the PostgreSQL
6# documentation for a complete description of this file. A short
7# synopsis follows.
8#
9# This file controls: which hosts are allowed to connect, how clients
10# are authenticated, which PostgreSQL user names they can use, which
11# databases they can access. Records take one of these forms:
12#
13# local DATABASE USER METHOD [OPTIONS]
14# host DATABASE USER ADDRESS METHOD [OPTIONS]
15# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
16# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
17#
18# (The uppercase items must be replaced by actual values.)
19#
20# The first field is the connection type: "local" is a Unix-domain
21# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
22# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
23# plain TCP/IP socket.
24#
25# DATABASE can be "all", "sameuser", "samerole", "replication", a
26# database name, or a comma-separated list thereof. The "all"
27# keyword does not match "replication". Access to replication
28# must be enabled in a separate record (see example below).
29#
30# USER can be "all", a user name, a group name prefixed with "+", or a
31# comma-separated list thereof. In both the DATABASE and USER fields
32# you can also write a file name prefixed with "@" to include names
33# from a separate file.
34#
35# ADDRESS specifies the set of hosts the record matches. It can be a
36# host name, or it is made up of an IP address and a CIDR mask that is
37# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
38# specifies the number of significant bits in the mask. A host name
39# that starts with a dot (.) matches a suffix of the actual host name.
40# Alternatively, you can write an IP address and netmask in separate
41# columns to specify the set of hosts. Instead of a CIDR-address, you
42# can write "samehost" to match any of the server's own IP addresses,
43# or "samenet" to match any address in any subnet that the server is
44# directly connected to.
45#
46# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
47# "ident", "peer", "pam", "ldap", "radius" or "cert". Note that
48# "password" sends passwords in clear text; "md5" is preferred since
49# it sends encrypted passwords.
50#
51# OPTIONS are a set of options for the authentication in the format
52# NAME=VALUE. The available options depend on the different
53# authentication methods -- refer to the "Client Authentication"
54# section in the documentation for a list of which options are
55# available for which authentication methods.
56#
57# Database and user names containing spaces, commas, quotes and other
58# special characters must be quoted. Quoting one of the keywords
59# "all", "sameuser", "samerole" or "replication" makes the name lose
60# its special character, and just match a database or username with
61# that name.
62#
63# This file is read on server startup and when the postmaster receives
64# a SIGHUP signal. If you edit the file on a running system, you have
65# to SIGHUP the postmaster for the changes to take effect. You can
66# use "pg_ctl reload" to do that.
67
68# Put your actual configuration here
69# ----------------------------------
70#
71# If you want to allow non-local connections, you need to add more
72# "host" records. In that case you will also need to make PostgreSQL
73# listen on a non-local interface via the listen_addresses
74# configuration parameter, or via the -i or -h command line switches.
75
76
77
78
79# DO NOT DISABLE!
80# If you change this first entry you will need to make sure that the
81# database superuser can access the database using some other method.
82# Noninteractive access to all databases is required during automatic
83# maintenance (custom daily cronjobs, replication, and similar tasks).
84#
85# Database administrative login by Unix domain socket
86local all postgres peer
87
88# TYPE DATABASE USER ADDRESS METHOD
89
90# "local" is for Unix domain socket connections only
91local all all peer
92# IPv4 local connections:
93host all all 127.0.0.1/32 md5
94{% for host in postgres_allowed_hosts %}
95host all {{ host.user }} {{ host.range }} md5
96{% endfor %}
97
98# IPv6 local connections:
99host all all ::1/128 md5
100# Allow replication connections from localhost, by a user with the
101# replication privilege.
102#local replication postgres peer
103#host replication postgres 127.0.0.1/32 md5
104#host replication postgres ::1/128 md5
105{% for host in postgres_replication_hosts %}
106hostssl replication {{ host.user }} {{ host.range }} md5
107{% endfor %}
diff --git a/templates/pg_upgrade.sh.j2 b/templates/pg_upgrade.sh.j2
new file mode 100755
index 0000000..e4f87e1
--- /dev/null
+++ b/templates/pg_upgrade.sh.j2
@@ -0,0 +1,175 @@
1#!/bin/bash -ex
2# {{ ansible_managed }}
3#
4# WARNING: Please read both the documentation and this script if you want to run it
5# Authors:
6# - Théophile Helleboid
7# - Paul Bonaud
8# Description:
9# This helper tries to strictly follow the official documentation of PostgreSQL
10# from the pg_upgrade page: https://www.postgresql.org/docs/9.5/static/pgupgrade.html
11# Usage:
12# WARNING: Please read both the documentation and this script if you want to run it
13# 1. edit this file and remove the "DRY_RUN=1" line
14# 2. execute this script as the postgres admin user
15
16{% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
17PGBOUNCER_PG_URI={{ postgres_pgbouncer_uri }}
18{% endif %}
19DATABASE_NAME={{ postgres_dbname }}
20DATABASE_PORT={{ postgres_new_cluster_port }}
21
22OLD_CLUSTER_VERSION={{ postgres_old_cluster_version }}
23OLD_CLUSTER_NAME={{ postgres_old_cluster_name }}
24OLD_CLUSTER_DATADIR=/var/lib/postgresql/$OLD_CLUSTER_VERSION/$OLD_CLUSTER_NAME
25NEW_CLUSTER_VERSION={{ postgres_new_cluster_version }}
26NEW_CLUSTER_NAME={{ postgres_new_cluster_name }}
27NEW_CLUSTER_DATADIR=/var/lib/postgresql/$NEW_CLUSTER_VERSION/$NEW_CLUSTER_NAME
28
29STANDBY_SERVER={{ postgres_standby_server }}
30STANDBY_OLD_CLUSTER_NAME={{ postgres_standby_old_cluster_name }}
31STANDBY_OLD_CLUSTER_DATADIR=/var/lib/postgresql/$OLD_CLUSTER_VERSION/$STANDBY_OLD_CLUSTER_NAME
32STANDBY_NEW_CLUSTER_NAME={{ postgres_standby_new_cluster_name }}
33STANDBY_NEW_CLUSTER_DATADIR=/var/lib/postgresql/$NEW_CLUSTER_VERSION/$STANDBY_NEW_CLUSTER_NAME
34
35DRY_RUN=1
36
37if [ "$(whoami)" != "postgres" ]; then
38 echo "ERROR: you must run this script as the 'postgres' admin local user"
39 exit 1
40fi
41
42{% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
43# Test the configuration is correcly deployed on pgbouncer
44psql $PGBOUNCER_PG_URI --command 'show databases;' | grep "^ $DATABASE_NAME "
45# Test connection to pgbouncer is OK
46psql $PGBOUNCER_PG_URI --command 'show pools;'
47{% endif %}
48
49if [ ! -f /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin/pg_upgrade ]; then
50 echo "ERROR: /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin/pg_upgrade is not available on this machine."
51 exit 1
52fi
53# We need ssh access to the standby server
54ssh $STANDBY_SERVER "ls -A $STANDBY_OLD_CLUSTER_DATADIR 2>&1>/dev/null"
55ssh $STANDBY_SERVER "ls -A $STANDBY_NEW_CLUSTER_DATADIR 2>&1>/dev/null"
56if [ ! -d $OLD_CLUSTER_DATADIR ] || [ ! -d $NEW_CLUSTER_DATADIR ]; then
57 echo "ERROR: Make sure both old cluster and new cluster data dirs exists"
58 echo "ERROR: on both primary and standby servers ($STANDBY_SERVER)."
59 echo "ERROR: old_cluster datadir: $OLD_CLUSTER_DATADIR"
60 echo "ERROR: new_cluster datadir: $NEW_CLUSTER_DATADIR"
61 #exit 1
62fi
63
64echo "WARNING: You are about to:"
65{% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
66echo "WARNING: * PAUSE connections to $DATABASE_NAME database."
67{% endif %}
68echo "WARNING: * STOP the primary database in $OLD_CLUSTER_VERSION version (and standbys)"
69echo "WARNING: * dry-run the pg_upgrade from $OLD_CLUSTER_VERSION to $NEW_CLUSTER_VERSION"
70echo "WARNING: * run the pg_upgrade in place with hardlinks"
71echo "WARNING: * START the primary database in $NEW_CLUSTER_VERSION version (and standbys)"
72{% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
73echo "WARNING: * RESUME connections to $DATABASE_NAME."
74{% endif %}
75echo "================"
76read -p "Are you sure? (y/n)" -n 1 -r
77echo "================"
78if [[ -z $DRY_RUN ]] && [[ $REPLY =~ ^[Yy]$ ]]; then
79 {% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
80 # Pause the databases;
81 psql $PGBOUNCER_PG_URI --command "PAUSE $DATABASE_NAME;"
82 {% endif %}
83
84 # Stop the $OLD_CLUSTER_VERSION server
85 pg_ctlcluster $OLD_CLUSTER_VERSION $OLD_CLUSTER_NAME stop -m fast
86
87 # Get latest checkpoint location of primary
88 echo "INFO: Primary PG controldata with latest checkpoint location"
89 /usr/lib/postgresql/$OLD_CLUSTER_VERSION/bin/pg_controldata $OLD_CLUSTER_DATADIR | head -n8
90
91 # Wait for standbys to catch up latest checkpoint
92 # TODO: how long to wait?
93 echo "Waiting for 9 seconds..."
94 sleep 9
95 echo "INFO: Standby PG controldata with latest checkpoint location (after shutdown)"
96 ssh $STANDBY_SERVER pg_ctlcluster $OLD_CLUSTER_VERSION $STANDBY_OLD_CLUSTER_NAME stop -m fast
97 ssh $STANDBY_SERVER /usr/lib/postgresql/$OLD_CLUSTER_VERSION/bin/pg_controldata $STANDBY_OLD_CLUSTER_DATADIR | head -n8
98
99 echo "WARNING: 'Latest Checkpoint location' values should match on primary and on standby"
100 echo "================"
101 read -p "Do they match? PLEASE ANSWER (y/n)" -n 1 -r
102 echo "================"
103
104 if [[ $REPLY =~ ^[Yy]$ ]]; then
105 # Test to upgrade the data
106 time /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin/pg_upgrade --check --link \
107 -b /usr/lib/postgresql/$OLD_CLUSTER_VERSION/bin \
108 -B /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin \
109 -d $OLD_CLUSTER_DATADIR \
110 -D $NEW_CLUSTER_DATADIR \
111 -o " -c config_file=/etc/postgresql/$OLD_CLUSTER_VERSION/$OLD_CLUSTER_NAME/postgresql.conf" \
112 -O " -c config_file=/etc/postgresql/$NEW_CLUSTER_VERSION/$NEW_CLUSTER_NAME/postgresql.conf"
113
114 # Really upgrade the data
115 time /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin/pg_upgrade --link \
116 -b /usr/lib/postgresql/$OLD_CLUSTER_VERSION/bin \
117 -B /usr/lib/postgresql/$NEW_CLUSTER_VERSION/bin \
118 -d $OLD_CLUSTER_DATADIR \
119 -D $NEW_CLUSTER_DATADIR \
120 -o " -c config_file=/etc/postgresql/$OLD_CLUSTER_VERSION/$OLD_CLUSTER_NAME/postgresql.conf" \
121 -O " -c config_file=/etc/postgresql/$NEW_CLUSTER_VERSION/$NEW_CLUSTER_NAME/postgresql.conf"
122
123 ## Upgrade standby server
124
125 ### Making sure new datadir is empty
126 if [ -z "$(ssh $STANDBY_SERVER ls -A $STANDBY_NEW_CLUSTER_DATADIR)" ]; then
127 echo "INFO: New cluster datadir is empty on standby server. Good."
128 else
129 echo "WARNING: New cluster datadir is NOT empty on standby server. Deleting the content of $STANDBY_NEW_CLUSTER_DATADIR on $STANDBY_SERVER now..."
130 ssh $STANDBY_SERVER rm -rf $STANDBY_NEW_CLUSTER_DATADIR
131 fi
132
133 ### Save configuration files
134 TMPDIR=$(ssh $STANDBY_SERVER mktemp -d -t pg_upgrade_XXXX)
135 ssh $STANDBY_SERVER mv $STANDBY_OLD_CLUSTER_DATADIR/recovery.conf $TMPDIR
136
137 ### Upgrade (via rsync)
138 rsync --archive --delete --hard-links --size-only --no-inc-recursive $OLD_CLUSTER_DATADIR $NEW_CLUSTER_DATADIR $STANDBY_SERVER:$(dirname $STANDBY_NEW_CLUSTER_DATADIR)
139
140 ### Restore saved configured files
141 ssh $STANDBY_SERVER mv $TMPDIR/recovery.conf $STANDBY_NEW_CLUSTER_DATADIR/
142 ssh $STANDBY_SERVER rm -rf $TMPDIR
143
144 # Start the $NEW_CLUSTER_VERSION primary server
145 pg_ctlcluster $NEW_CLUSTER_VERSION $NEW_CLUSTER_NAME start
146
147 # Test local primary is accepting connections
148 psql --port=$DATABASE_PORT --dbname=$DATABASE_NAME --command="SELECT NOW();"
149
150 {% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
151 # Resume the connexions
152 psql $PGBOUNCER_PG_URI --command "RESUME $DATABASE_NAME;"
153 {% endif %}
154
155 # Start the $NEW_CLUSTER_VERSION standby server
156 ssh $STANDBY_SERVER pg_ctlcluster $NEW_CLUSTER_VERSION $STANDBY_NEW_CLUSTER_NAME start
157
158 echo "DONE. Upgrade of PG cluster from $OLD_CLUSTER_VERSION/$OLD_CLUSTER_NAME to $NEW_CLUSTER_VESION/$NEW_CLUSTER_NAME is finished! Well done!"
159 else
160 # Start the $OLD_CLUSTER_VERSION primary server
161 pg_ctlcluster $OLD_CLUSTER_VERSION $OLD_CLUSTER_NAME start
162 # Start the $OLD_CLUSTER_VERSION standby server
163 ssh $STANDBY_SERVER pg_ctlcluster $OLD_CLUSTER_VERSION $OLD_CLUSTER_NAME start
164
165 # Test local primary is accepting connections
166 psql --dbname=$DATABASE_NAME --command="SELECT NOW();"
167
168 {% if postgres_pgbouncer_uri is defined and postgres_pgbouncer_uri %}
169 # Resume the connexions
170 psql $PGBOUNCER_PG_URI --command "RESUME $DATABASE_NAME;"
171 {% endif %}
172 fi
173else
174 echo "INFO: You didn't want to continue or was in 'dry-run' mode"
175fi
diff --git a/templates/postgresql.10.conf.j2 b/templates/postgresql.10.conf.j2
new file mode 100644
index 0000000..588020f
--- /dev/null
+++ b/templates/postgresql.10.conf.j2
@@ -0,0 +1,694 @@
1# {{ ansible_managed }}
2# -----------------------------
3# PostgreSQL configuration file
4# -----------------------------
5#
6# This file consists of lines of the form:
7#
8# name = value
9#
10# (The "=" is optional.) Whitespace may be used. Comments are introduced with
11# "#" anywhere on a line. The complete list of parameter names and allowed
12# values can be found in the PostgreSQL documentation.
13#
14# The commented-out settings shown in this file represent the default values.
15# Re-commenting a setting is NOT sufficient to revert it to the default value;
16# you need to reload the server.
17#
18# This file is read on server startup and when the server receives a SIGHUP
19# signal. If you edit the file on a running system, you have to SIGHUP the
20# server for the changes to take effect, run "pg_ctl reload", or execute
21# "SELECT pg_reload_conf()". Some parameters, which are marked below,
22# require a server shutdown and restart to take effect.
23#
24# Any parameter can also be given as a command-line option to the server, e.g.,
25# "postgres -c log_connections=on". Some parameters can be changed at run time
26# with the "SET" SQL command.
27#
28# Memory units: kB = kilobytes Time units: ms = milliseconds
29# MB = megabytes s = seconds
30# GB = gigabytes min = minutes
31# TB = terabytes h = hours
32# d = days
33
34
35#------------------------------------------------------------------------------
36# FILE LOCATIONS
37#------------------------------------------------------------------------------
38
39# The default values of these variables are driven from the -D command-line
40# option or PGDATA environment variable, represented here as ConfigDir.
41
42data_directory = '/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}' # use data in another directory
43 # (change requires restart)
44hba_file = '/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/pg_hba.conf' # host-based authentication file
45 # (change requires restart)
46ident_file = '/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/pg_ident.conf' # ident configuration file
47 # (change requires restart)
48
49# If external_pid_file is not explicitly set, no extra PID file is written.
50external_pid_file = '/var/run/postgresql/{{ postgres_version }}-{{ postgres_cluster_name }}.pid' # write an extra PID file
51 # (change requires restart)
52
53
54#------------------------------------------------------------------------------
55# CONNECTIONS AND AUTHENTICATION
56#------------------------------------------------------------------------------
57
58# - Connection Settings -
59
60listen_addresses = '{{ postgres_listen_addresses | reject("none") | join(",") }}' # what IP address(es) to listen on;
61 # comma-separated list of addresses;
62 # defaults to 'localhost'; use '*' for all
63 # (change requires restart)
64port = {{ postgres_port }} # (change requires restart)
65max_connections = 400 # (change requires restart)
66# Note: Increasing max_connections costs ~400 bytes of shared memory per
67# connection slot, plus lock space (see max_locks_per_transaction).
68#superuser_reserved_connections = 3 # (change requires restart)
69unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
70 # (change requires restart)
71#unix_socket_group = '' # (change requires restart)
72#unix_socket_permissions = 0777 # begin with 0 to use octal notation
73 # (change requires restart)
74#bonjour = off # advertise server via Bonjour
75 # (change requires restart)
76#bonjour_name = '' # defaults to the computer name
77 # (change requires restart)
78
79# - Security and Authentication -
80
81#authentication_timeout = 1min # 1s-600s
82ssl = on
83#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
84#ssl_prefer_server_ciphers = on
85#ssl_ecdh_curve = 'prime256v1'
86#ssl_dh_params_file = ''
87# ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
88# ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
89#ssl_ca_file = ''
90#ssl_crl_file = ''
91#password_encryption = md5 # md5 or scram-sha-256
92#db_user_namespace = off
93#row_security = on
94
95# GSSAPI using Kerberos
96#krb_server_keyfile = ''
97#krb_caseins_users = off
98
99# - TCP Keepalives -
100# see "man 7 tcp" for details
101
102#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
103 # 0 selects the system default
104#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
105 # 0 selects the system default
106#tcp_keepalives_count = 0 # TCP_KEEPCNT;
107 # 0 selects the system default
108
109
110#------------------------------------------------------------------------------
111# RESOURCE USAGE (except WAL)
112#------------------------------------------------------------------------------
113
114# - Memory -
115
116{% if ansible_memtotal_mb > 65536 %}
117shared_buffers = 16GB # min 128kB
118{% else %}
119shared_buffers = {{ ansible_memtotal_mb // 4 }}MB # min 128kB
120{% endif %}
121 # (change requires restart)
122#huge_pages = try # on, off, or try
123 # (change requires restart)
124temp_buffers = 24MB # min 800kB
125#max_prepared_transactions = 0 # zero disables the feature
126 # (change requires restart)
127# Caution: it is not advisable to set max_prepared_transactions nonzero unless
128# you actively intend to use prepared transactions.
129work_mem = 512MB # min 64kB
130maintenance_work_mem = 1024MB # min 1MB
131#replacement_sort_tuples = 150000 # limits use of replacement selection sort
132#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
133#max_stack_depth = 2MB # min 100kB
134dynamic_shared_memory_type = posix # the default is the first option
135 # supported by the operating system:
136 # posix
137 # sysv
138 # windows
139 # mmap
140 # use none to disable dynamic shared memory
141 # (change requires restart)
142
143# - Disk -
144
145#temp_file_limit = -1 # limits per-process temp file space
146 # in kB, or -1 for no limit
147
148# - Kernel Resource Usage -
149
150#max_files_per_process = 1000 # min 25
151 # (change requires restart)
152#shared_preload_libraries = '' # (change requires restart)
153
154# - Cost-Based Vacuum Delay -
155
156#vacuum_cost_delay = 0 # 0-100 milliseconds
157#vacuum_cost_page_hit = 1 # 0-10000 credits
158#vacuum_cost_page_miss = 10 # 0-10000 credits
159#vacuum_cost_page_dirty = 20 # 0-10000 credits
160#vacuum_cost_limit = 200 # 1-10000 credits
161
162# - Background Writer -
163
164bgwriter_delay = 2000ms # 10-10000ms between rounds
165#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
166#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round
167#bgwriter_flush_after = 512kB # measured in pages, 0 disables
168
169# - Asynchronous Behavior -
170
171#effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
172#max_worker_processes = 8 # (change requires restart)
173#max_parallel_workers_per_gather = 2 # taken from max_parallel_workers
174#max_parallel_workers = 8 # maximum number of max_worker_processes that
175 # can be used in parallel queries
176#old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate
177 # (change requires restart)
178#backend_flush_after = 0 # measured in pages, 0 disables
179
180
181#------------------------------------------------------------------------------
182# WRITE AHEAD LOG
183#------------------------------------------------------------------------------
184
185# - Settings -
186
187wal_level = logical # minimal, replica, or logical
188 # (change requires restart)
189{% if postgres_fsync_enabled %}
190#fsync = on # flush data to disk for crash safety
191 # (turning this off can cause
192 # unrecoverable data corruption)
193#synchronous_commit = on # synchronization level;
194# off, local, remote_write, remote_apply, or on
195{% else %}
196fsync = off # turns forced synchronization on or off
197synchronous_commit = off # synchronization level;
198{% endif %}
199#wal_sync_method = fsync # the default is the first option
200 # supported by the operating system:
201 # open_datasync
202 # fdatasync (default on Linux)
203 # fsync
204 # fsync_writethrough
205 # open_sync
206#full_page_writes = on # recover from partial page writes
207#wal_compression = off # enable compression of full-page writes
208wal_log_hints = on # also do full page writes of non-critical updates
209 # (change requires restart)
210#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
211 # (change requires restart)
212#wal_writer_delay = 200ms # 1-10000 milliseconds
213#wal_writer_flush_after = 1MB # measured in pages, 0 disables
214
215#commit_delay = 0 # range 0-100000, in microseconds
216#commit_siblings = 5 # range 1-1000
217
218# - Checkpoints -
219
220#checkpoint_timeout = 5min # range 30s-1d
221#max_wal_size = 1GB
222#min_wal_size = 80MB
223#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
224#checkpoint_flush_after = 256kB # measured in pages, 0 disables
225#checkpoint_warning = 30s # 0 disables
226
227# - Archiving -
228
229{% if postgres_archive_enabled %}
230archive_mode = on
231archive_command = 'rsync -a %p barman@{{ postgres_barman_server }}:/var/lib/barman/{{ barman_directory }}/incoming/%f'
232{% else %}
233archive_mode = off
234archive_command = ''
235{% endif %}
236
237#archive_mode = off # enables archiving; off, on, or always
238 # (change requires restart)
239#archive_command = '' # command to use to archive a logfile segment
240 # placeholders: %p = path of file to archive
241 # %f = file name only
242 # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
243#archive_timeout = 0 # force a logfile segment switch after this
244 # number of seconds; 0 disables
245
246
247#------------------------------------------------------------------------------
248# REPLICATION
249#------------------------------------------------------------------------------
250
251# - Sending Server(s) -
252
253# Set these on the master and on any standby that will send replication data.
254
255max_wal_senders = 10 # max number of walsender processes
256 # (change requires restart)
257wal_keep_segments = 16 # in logfile segments, 16MB each; 0 disables
258#wal_sender_timeout = 60s # in milliseconds; 0 disables
259
260{% if postgres_max_replication_slots is defined %}
261max_replication_slots = {{ postgres_max_replication_slots }} # max number of replication slots
262{% else %}
263#max_replication_slots = 0 # max number of replication slots
264{% endif %}
265 # (change requires restart)
266#track_commit_timestamp = off # collect timestamp of transaction commit
267 # (change requires restart)
268
269# - Master Server -
270
271# These settings are ignored on a standby server.
272
273#synchronous_standby_names = '' # standby servers that provide sync rep
274 # method to choose sync standbys, number of sync standbys,
275 # and comma-separated list of application_name
276 # from standby(s); '*' = all
277#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
278
279# - Standby Servers -
280
281# These settings are ignored on a master server.
282
283hot_standby = on # "off" disallows queries during recovery
284 # (change requires restart)
285#max_standby_archive_delay = 30s # max delay before canceling queries
286 # when reading WAL from archive;
287 # -1 allows indefinite delay
288max_standby_streaming_delay = {{ postgres_extra_config.max_standby_streaming_delay | default('600s') }} # max delay before canceling queries
289 # when reading streaming WAL;
290 # -1 allows indefinite delay
291#wal_receiver_status_interval = 10s # send replies at least this often
292 # 0 disables
293#hot_standby_feedback = off # send info from standby to prevent
294 # query conflicts
295#wal_receiver_timeout = 60s # time that receiver waits for
296 # communication from master
297 # in milliseconds; 0 disables
298#wal_retrieve_retry_interval = 5s # time to wait before retrying to
299 # retrieve WAL after a failed attempt
300
301# - Subscribers -
302
303# These settings are ignored on a publisher.
304
305#max_logical_replication_workers = 4 # taken from max_worker_processes
306 # (change requires restart)
307#max_sync_workers_per_subscription = 2 # taken from max_logical_replication_workers
308
309
310#------------------------------------------------------------------------------
311# QUERY TUNING
312#------------------------------------------------------------------------------
313
314# - Planner Method Configuration -
315
316#enable_bitmapscan = on
317#enable_hashagg = on
318#enable_hashjoin = on
319#enable_indexscan = on
320#enable_indexonlyscan = on
321#enable_material = on
322#enable_mergejoin = on
323#enable_nestloop = on
324#enable_seqscan = on
325#enable_sort = on
326#enable_tidscan = on
327
328# - Planner Cost Constants -
329
330#seq_page_cost = 1.0 # measured on an arbitrary scale
331{% if postgres_specific_random_page_cost is defined -%}
332# Random page cost is an arbitrary value relative to the seq_page_cost conf (default 1.0)
333# On SSD disk it is better to lower the default value from 4.0 to 1.0 for instance.
334random_page_cost = {{ postgres_specific_random_page_cost }} # same scale as above
335{% else -%}
336#random_page_cost = 4.0 # same scale as above
337{% endif %}
338#cpu_tuple_cost = 0.01 # same scale as above
339#cpu_index_tuple_cost = 0.005 # same scale as above
340#cpu_operator_cost = 0.0025 # same scale as above
341#parallel_tuple_cost = 0.1 # same scale as above
342#parallel_setup_cost = 1000.0 # same scale as above
343#min_parallel_table_scan_size = 8MB
344#min_parallel_index_scan_size = 512kB
345#effective_cache_size = 4GB
346effective_cache_size = {{ ansible_memtotal_mb // 2 }}MB
347
348# - Genetic Query Optimizer -
349
350#geqo = on
351#geqo_threshold = 12
352#geqo_effort = 5 # range 1-10
353#geqo_pool_size = 0 # selects default based on effort
354#geqo_generations = 0 # selects default based on effort
355#geqo_selection_bias = 2.0 # range 1.5-2.0
356#geqo_seed = 0.0 # range 0.0-1.0
357
358# - Other Planner Options -
359
360#default_statistics_target = 100 # range 1-10000
361#constraint_exclusion = partition # on, off, or partition
362#cursor_tuple_fraction = 0.1 # range 0.0-1.0
363#from_collapse_limit = 8
364#join_collapse_limit = 8 # 1 disables collapsing of explicit
365 # JOIN clauses
366#force_parallel_mode = off
367
368
369#------------------------------------------------------------------------------
370# ERROR REPORTING AND LOGGING
371#------------------------------------------------------------------------------
372
373# - Where to Log -
374
375#log_destination = 'stderr' # Valid values are combinations of
376 # stderr, csvlog, syslog, and eventlog,
377 # depending on platform. csvlog
378 # requires logging_collector to be on.
379
380# This is used when logging to stderr:
381#logging_collector = off # Enable capturing of stderr and csvlog
382 # into log files. Required to be on for
383 # csvlogs.
384 # (change requires restart)
385
386# These are only used if logging_collector is on:
387#log_directory = 'log' # directory where log files are written,
388 # can be absolute or relative to PGDATA
389#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
390 # can include strftime() escapes
391#log_file_mode = 0600 # creation mode for log files,
392 # begin with 0 to use octal notation
393#log_truncate_on_rotation = off # If on, an existing log file with the
394 # same name as the new log file will be
395 # truncated rather than appended to.
396 # But such truncation only occurs on
397 # time-driven rotation, not on restarts
398 # or size-driven rotation. Default is
399 # off, meaning append to existing files
400 # in all cases.
401#log_rotation_age = 1d # Automatic rotation of logfiles will
402 # happen after that time. 0 disables.
403#log_rotation_size = 10MB # Automatic rotation of logfiles will
404 # happen after that much log output.
405 # 0 disables.
406
407# These are relevant when logging to syslog:
408#syslog_facility = 'LOCAL0'
409#syslog_ident = 'postgres'
410#syslog_sequence_numbers = on
411#syslog_split_messages = on
412
413# This is only relevant when logging to eventlog (win32):
414# (change requires restart)
415#event_source = 'PostgreSQL'
416
417# - When to Log -
418
419#client_min_messages = notice # values in order of decreasing detail:
420 # debug5
421 # debug4
422 # debug3
423 # debug2
424 # debug1
425 # log
426 # notice
427 # warning
428 # error
429
430#log_min_messages = warning # values in order of decreasing detail:
431 # debug5
432 # debug4
433 # debug3
434 # debug2
435 # debug1
436 # info
437 # notice
438 # warning
439 # error
440 # log
441 # fatal
442 # panic
443
444#log_min_error_statement = error # values in order of decreasing detail:
445 # debug5
446 # debug4
447 # debug3
448 # debug2
449 # debug1
450 # info
451 # notice
452 # warning
453 # error
454 # log
455 # fatal
456 # panic (effectively off)
457
458log_min_duration_statement = 10000 # -1 is disabled, 0 logs all statements
459 # and their durations, > 0 logs only
460 # statements running at least this number
461 # of milliseconds
462
463
464# - What to Log -
465
466#debug_print_parse = off
467#debug_print_rewritten = off
468#debug_print_plan = off
469#debug_pretty_print = on
470#log_checkpoints = off
471#log_connections = off
472#log_disconnections = off
473#log_duration = off
474#log_error_verbosity = default # terse, default, or verbose messages
475#log_hostname = off
476
477log_line_prefix = '{{ postgres_log_line_prefix }}'
478#log_line_prefix = '%m [%p] %q%u@%d ' # special values:
479 # %a = application name
480 # %u = user name
481 # %d = database name
482 # %r = remote host and port
483 # %h = remote host
484 # %p = process ID
485 # %t = timestamp without milliseconds
486 # %m = timestamp with milliseconds
487 # %n = timestamp with milliseconds (as a Unix epoch)
488 # %i = command tag
489 # %e = SQL state
490 # %c = session ID
491 # %l = session line number
492 # %s = session start timestamp
493 # %v = virtual transaction ID
494 # %x = transaction ID (0 if none)
495 # %q = stop here in non-session
496 # processes
497 # %% = '%'
498 # e.g. '<%u%%%d> '
499#log_lock_waits = off # log lock waits >= deadlock_timeout
500#log_statement = 'none' # none, ddl, mod, all
501#log_replication_commands = off
502#log_temp_files = -1 # log temporary files equal or larger
503 # than the specified size in kilobytes;
504 # -1 disables, 0 logs all temp files
505log_timezone = 'UTC'
506
507
508# - Process Title -
509
510cluster_name = '{{ postgres_version }}/{{ postgres_cluster_name }}' # added to process titles if nonempty
511 # (change requires restart)
512#update_process_title = on
513
514
515#------------------------------------------------------------------------------
516# RUNTIME STATISTICS
517#------------------------------------------------------------------------------
518
519# - Query/Index Statistics Collector -
520
521#track_activities = on
522#track_counts = on
523#track_io_timing = off
524#track_functions = none # none, pl, all
525#track_activity_query_size = 1024 # (change requires restart)
526stats_temp_directory = '/var/run/postgresql/{{ postgres_version }}-{{ postgres_cluster_name }}.pg_stat_tmp'
527
528
529# - Statistics Monitoring -
530
531#log_parser_stats = off
532#log_planner_stats = off
533#log_executor_stats = off
534#log_statement_stats = off
535
536
537#------------------------------------------------------------------------------
538# AUTOVACUUM PARAMETERS
539#------------------------------------------------------------------------------
540
541#autovacuum = on # Enable autovacuum subprocess? 'on'
542 # requires track_counts to also be on.
543#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
544 # their durations, > 0 logs only
545 # actions running at least this number
546 # of milliseconds.
547#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
548 # (change requires restart)
549#autovacuum_naptime = 1min # time between autovacuum runs
550#autovacuum_vacuum_threshold = 50 # min number of row updates before
551 # vacuum
552#autovacuum_analyze_threshold = 50 # min number of row updates before
553 # analyze
554#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
555#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
556#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
557 # (change requires restart)
558#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
559 # before forced vacuum
560 # (change requires restart)
561#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
562 # autovacuum, in milliseconds;
563 # -1 means use vacuum_cost_delay
564#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
565 # autovacuum, -1 means use
566 # vacuum_cost_limit
567
568
569#------------------------------------------------------------------------------
570# CLIENT CONNECTION DEFAULTS
571#------------------------------------------------------------------------------
572
573# - Statement Behavior -
574
575#search_path = '"$user", public' # schema names
576#default_tablespace = '' # a tablespace name, '' uses the default
577#temp_tablespaces = '' # a list of tablespace names, '' uses
578 # only default tablespace
579#check_function_bodies = on
580#default_transaction_isolation = 'read committed'
581#default_transaction_read_only = off
582#default_transaction_deferrable = off
583#session_replication_role = 'origin'
584#statement_timeout = 0 # in milliseconds, 0 is disabled
585#lock_timeout = 0 # in milliseconds, 0 is disabled
586#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
587#vacuum_freeze_min_age = 50000000
588#vacuum_freeze_table_age = 150000000
589#vacuum_multixact_freeze_min_age = 5000000
590#vacuum_multixact_freeze_table_age = 150000000
591#bytea_output = 'hex' # hex, escape
592#xmlbinary = 'base64'
593#xmloption = 'content'
594#gin_fuzzy_search_limit = 0
595#gin_pending_list_limit = 4MB
596
597# - Locale and Formatting -
598
599datestyle = '{{ postgres_datestyle }}'
600#intervalstyle = 'postgres'
601#timezone = '(defaults to server environment setting)'
602{% if postgres_timezone is defined %}
603timezone = '{{ postgres_timezone }}'
604{% endif %}
605#timezone_abbreviations = 'Default' # Select the set of available time zone
606 # abbreviations. Currently, there are
607 # Default
608 # Australia (historical usage)
609 # India
610 # You can create your own file in
611 # share/timezonesets/.
612#extra_float_digits = 0 # min -15, max 3
613#client_encoding = sql_ascii # actually, defaults to database
614 # encoding
615
616# These settings are initialized by initdb, but they can be changed.
617lc_messages = '{{ postgres_locale_system }}' # locale for system error message
618 # strings
619lc_monetary = '{{ postgres_locale_formats }}' # locale for monetary formatting
620lc_numeric = '{{ postgres_locale_formats }}' # locale for number formatting
621lc_time = '{{ postgres_locale_formats }}' # locale for time formatting
622
623# default configuration for text search
624default_text_search_config = '{{ postgres_text_search_config }}'
625
626# - Other Defaults -
627
628#dynamic_library_path = '$libdir'
629#local_preload_libraries = ''
630#session_preload_libraries = ''
631
632
633#------------------------------------------------------------------------------
634# LOCK MANAGEMENT
635#------------------------------------------------------------------------------
636
637#deadlock_timeout = 1s
638#max_locks_per_transaction = 64 # min 10
639 # (change requires restart)
640#max_pred_locks_per_transaction = 64 # min 10
641 # (change requires restart)
642#max_pred_locks_per_relation = -2 # negative values mean
643 # (max_pred_locks_per_transaction
644 # / -max_pred_locks_per_relation) - 1
645#max_pred_locks_per_page = 2 # min 0
646
647
648#------------------------------------------------------------------------------
649# VERSION/PLATFORM COMPATIBILITY
650#------------------------------------------------------------------------------
651
652# - Previous PostgreSQL Versions -
653
654#array_nulls = on
655#backslash_quote = safe_encoding # on, off, or safe_encoding
656#default_with_oids = off
657#escape_string_warning = on
658#lo_compat_privileges = off
659#operator_precedence_warning = off
660#quote_all_identifiers = off
661#standard_conforming_strings = on
662#synchronize_seqscans = on
663
664# - Other Platforms and Clients -
665
666#transform_null_equals = off
667
668
669#------------------------------------------------------------------------------
670# ERROR HANDLING
671#------------------------------------------------------------------------------
672
673#exit_on_error = off # terminate session on any error?
674#restart_after_crash = on # reinitialize after backend crash?
675
676
677#------------------------------------------------------------------------------
678# CONFIG FILE INCLUDES
679#------------------------------------------------------------------------------
680
681# These options allow settings to be loaded from files other than the
682# default postgresql.conf.
683
684#include_dir = 'conf.d' # include files ending in '.conf' from
685 # directory 'conf.d'
686#include_if_exists = 'exists.conf' # include file only if it exists
687#include = 'special.conf' # include file
688
689
690#------------------------------------------------------------------------------
691# CUSTOMIZED OPTIONS
692#------------------------------------------------------------------------------
693
694# Add settings for extensions here
diff --git a/templates/postgresql.9.1.conf.j2 b/templates/postgresql.9.1.conf.j2
new file mode 100644
index 0000000..e15dfed
--- /dev/null
+++ b/templates/postgresql.9.1.conf.j2
@@ -0,0 +1,589 @@
1# {{ ansible_managed }}
2# -----------------------------
3# PostgreSQL configuration file
4# -----------------------------
5#
6# This file consists of lines of the form:
7#
8# name = value
9#
10# (The "=" is optional.) Whitespace may be used. Comments are introduced with
11# "#" anywhere on a line. The complete list of parameter names and allowed
12# values can be found in the PostgreSQL documentation.
13#
14# The commented-out settings shown in this file represent the default values.
15# Re-commenting a setting is NOT sufficient to revert it to the default value;
16# you need to reload the server.
17#
18# This file is read on server startup and when the server receives a SIGHUP
19# signal. If you edit the file on a running system, you have to SIGHUP the
20# server for the changes to take effect, or use "pg_ctl reload". Some
21# parameters, which are marked below, require a server shutdown and restart to
22# take effect.
23#
24# Any parameter can also be given as a command-line option to the server, e.g.,
25# "postgres -c log_connections=on". Some parameters can be changed at run time
26# with the "SET" SQL command.
27#
28# Memory units: kB = kilobytes Time units: ms = milliseconds
29# MB = megabytes s = seconds
30# GB = gigabytes min = minutes
31# h = hours
32# d = days
33
34
35#------------------------------------------------------------------------------
36# FILE LOCATIONS
37#------------------------------------------------------------------------------
38
39# The default values of these variables are driven from the -D command-line
40# option or PGDATA environment variable, represented here as ConfigDir.
41
42data_directory = '/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}' # use data in another directory
43 # (change requires restart)
44hba_file = '/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/pg_hba.conf' # host-based authentication file
45 # (change requires restart)
46ident_file = '/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/pg_ident.conf' # ident configuration file
47 # (change requires restart)
48
49# If external_pid_file is not explicitly set, no extra PID file is written.
50external_pid_file = '/var/run/postgresql/{{ postgres_version }}-{{ postgres_cluster_name }}.pid' # write an extra PID file
51 # (change requires restart)
52
53
54#------------------------------------------------------------------------------
55# CONNECTIONS AND AUTHENTICATION
56#------------------------------------------------------------------------------
57
58# - Connection Settings -
59
60listen_addresses = '{{ postgres_listen_addresses | reject("none") | join(",") }}' # what IP address(es) to listen on;
61 # comma-separated list of addresses;
62 # defaults to 'localhost', '*' = all
63 # (change requires restart)
64port = {{ postgres_port }} # (change requires restart)
65max_connections = 400 # (change requires restart)
66# Note: Increasing max_connections costs ~400 bytes of shared memory per
67# connection slot, plus lock space (see max_locks_per_transaction).
68#superuser_reserved_connections = 3 # (change requires restart)
69unix_socket_directory = '/var/run/postgresql' # (change requires restart)
70#unix_socket_group = '' # (change requires restart)
71#unix_socket_permissions = 0777 # begin with 0 to use octal notation
72 # (change requires restart)
73#bonjour = off # advertise server via Bonjour
74 # (change requires restart)
75#bonjour_name = '' # defaults to the computer name
76 # (change requires restart)
77
78# - Security and Authentication -
79
80#authentication_timeout = 1min # 1s-600s
81ssl = true # (change requires restart)
82#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
83 # (change requires restart)
84#ssl_renegotiation_limit = 512MB # amount of data between renegotiations
85#password_encryption = on
86#db_user_namespace = off
87
88# Kerberos and GSSAPI
89#krb_server_keyfile = ''
90#krb_srvname = 'postgres' # (Kerberos only)
91#krb_caseins_users = off
92
93# - TCP Keepalives -
94# see "man 7 tcp" for details
95
96#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
97 # 0 selects the system default
98#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
99 # 0 selects the system default
100#tcp_keepalives_count = 0 # TCP_KEEPCNT;
101 # 0 selects the system default
102
103
104#------------------------------------------------------------------------------
105# RESOURCE USAGE (except WAL)
106#------------------------------------------------------------------------------
107
108# - Memory -
109
110shared_buffers = 512MB # min 128kB
111 # (change requires restart)
112temp_buffers = 24MB # min 800kB
113#max_prepared_transactions = 0 # zero disables the feature
114 # (change requires restart)
115# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
116# per transaction slot, plus lock space (see max_locks_per_transaction).
117# It is not advisable to set max_prepared_transactions nonzero unless you
118# actively intend to use prepared transactions.
119#work_mem = 1MB # min 64kB
120#maintenance_work_mem = 16MB # min 1MB
121#max_stack_depth = 2MB # min 100kB
122
123work_mem = 512MB
124maintenance_work_mem = 1024MB
125
126
127# - Kernel Resource Usage -
128
129#max_files_per_process = 1000 # min 25
130 # (change requires restart)
131#shared_preload_libraries = '' # (change requires restart)
132
133# - Cost-Based Vacuum Delay -
134
135#vacuum_cost_delay = 0ms # 0-100 milliseconds
136#vacuum_cost_page_hit = 1 # 0-10000 credits
137#vacuum_cost_page_miss = 10 # 0-10000 credits
138#vacuum_cost_page_dirty = 20 # 0-10000 credits
139#vacuum_cost_limit = 200 # 1-10000 credits
140
141# - Background Writer -
142
143bgwriter_delay = 2000ms # 10-10000ms between rounds
144#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
145#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round
146
147# - Asynchronous Behavior -
148
149#effective_io_concurrency = 1 # 1-1000. 0 disables prefetching
150
151
152#------------------------------------------------------------------------------
153# WRITE AHEAD LOG
154#------------------------------------------------------------------------------
155
156# - Settings -
157
158#barman and replication
159#wal_level = 'archive' # For PostgreSQL >= 9.0
160wal_level = 'hot_standby' # For PostgreSQL >= 9.0
161
162{% if postgres_archive_enabled %}
163archive_mode = on
164archive_command = 'rsync -a %p barman@{{ postgres_barman_server }}:/var/lib/barman/{{ barman_directory }}/incoming/%f'
165{% else %}
166archive_mode = off
167archive_command = ''
168{% endif %}
169
170max_wal_senders = 5
171checkpoint_segments = 8
172wal_keep_segments = 16
173hot_standby = on
174wal_level = hot_standby # minimal, archive, or hot_standby
175
176 # (change requires restart)
177{% if postgres_fsync_enabled %}
178#fsync = on # turns forced synchronization on or off
179#synchronous_commit = on # synchronization level;
180{% else %}
181fsync = off # turns forced synchronization on or off
182synchronous_commit = off # synchronization level;
183{% endif %}
184#wal_sync_method = fsync # the default is the first option
185 # supported by the operating system:
186 # open_datasync
187 # fdatasync (default on Linux)
188 # fsync
189 # fsync_writethrough
190 # open_sync
191#full_page_writes = on # recover from partial page writes
192#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
193 # (change requires restart)
194#wal_writer_delay = 200ms # 1-10000 milliseconds
195
196#commit_delay = 0 # range 0-100000, in microseconds
197#commit_siblings = 5 # range 1-1000
198
199# - Checkpoints -
200
201#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
202#checkpoint_timeout = 5min # range 30s-1h
203#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
204#checkpoint_warning = 30s # 0 disables
205
206# - Archiving -
207
208#archive_mode = off # allows archiving to be done
209 # (change requires restart)
210#archive_command = '' # command to use to archive a logfile segment
211#archive_timeout = 0 # force a logfile segment switch after this
212 # number of seconds; 0 disables
213
214
215#------------------------------------------------------------------------------
216# REPLICATION
217#------------------------------------------------------------------------------
218
219# - Master Server -
220
221# These settings are ignored on a standby server
222
223#max_wal_senders = 0 # max number of walsender processes
224 # (change requires restart)
225#wal_sender_delay = 1s # walsender cycle time, 1-10000 milliseconds
226#wal_keep_segments = 0 # in logfile segments, 16MB each; 0 disables
227#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
228#replication_timeout = 60s # in milliseconds; 0 disables
229#synchronous_standby_names = '' # standby servers that provide sync rep
230 # comma-separated list of application_name
231 # from standby(s); '*' = all
232
233# - Standby Servers -
234
235# These settings are ignored on a master server
236
237#hot_standby = off # "on" allows queries during recovery
238 # (change requires restart)
239#max_standby_archive_delay = 30s # max delay before canceling queries
240 # when reading WAL from archive;
241 # -1 allows indefinite delay
242max_standby_streaming_delay = 600s
243#max_standby_streaming_delay = 30s # max delay before canceling queries
244 # when reading streaming WAL;
245 # -1 allows indefinite delay
246#wal_receiver_status_interval = 10s # send replies at least this often
247 # 0 disables
248#hot_standby_feedback = off # send info from standby to prevent
249 # query conflicts
250
251
252#------------------------------------------------------------------------------
253# QUERY TUNING
254#------------------------------------------------------------------------------
255
256# - Planner Method Configuration -
257
258#enable_bitmapscan = on
259#enable_hashagg = on
260#enable_hashjoin = on
261#enable_indexscan = on
262#enable_material = on
263#enable_mergejoin = on
264#enable_nestloop = on
265#enable_seqscan = on
266#enable_sort = on
267#enable_tidscan = on
268
269# - Planner Cost Constants -
270
271#seq_page_cost = 1.0 # measured on an arbitrary scale
272#random_page_cost = 4.0 # same scale as above
273#cpu_tuple_cost = 0.01 # same scale as above
274#cpu_index_tuple_cost = 0.005 # same scale as above
275#cpu_operator_cost = 0.0025 # same scale as above
276#effective_cache_size = 128MB
277
278# - Genetic Query Optimizer -
279
280#geqo = on
281#geqo_threshold = 12
282#geqo_effort = 5 # range 1-10
283#geqo_pool_size = 0 # selects default based on effort
284#geqo_generations = 0 # selects default based on effort
285#geqo_selection_bias = 2.0 # range 1.5-2.0
286#geqo_seed = 0.0 # range 0.0-1.0
287
288# - Other Planner Options -
289
290#default_statistics_target = 100 # range 1-10000
291#constraint_exclusion = partition # on, off, or partition
292#cursor_tuple_fraction = 0.1 # range 0.0-1.0
293#from_collapse_limit = 8
294#join_collapse_limit = 8 # 1 disables collapsing of explicit
295 # JOIN clauses
296
297
298#------------------------------------------------------------------------------
299# ERROR REPORTING AND LOGGING
300#------------------------------------------------------------------------------
301
302# - Where to Log -
303
304#log_destination = 'stderr' # Valid values are combinations of
305 # stderr, csvlog, syslog, and eventlog,
306 # depending on platform. csvlog
307 # requires logging_collector to be on.
308
309# This is used when logging to stderr:
310#logging_collector = off # Enable capturing of stderr and csvlog
311 # into log files. Required to be on for
312 # csvlogs.
313 # (change requires restart)
314
315# These are only used if logging_collector is on:
316#log_directory = 'pg_log' # directory where log files are written,
317 # can be absolute or relative to PGDATA
318#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
319 # can include strftime() escapes
320#log_file_mode = 0600 # creation mode for log files,
321 # begin with 0 to use octal notation
322#log_truncate_on_rotation = off # If on, an existing log file with the
323 # same name as the new log file will be
324 # truncated rather than appended to.
325 # But such truncation only occurs on
326 # time-driven rotation, not on restarts
327 # or size-driven rotation. Default is
328 # off, meaning append to existing files
329 # in all cases.
330#log_rotation_age = 1d # Automatic rotation of logfiles will
331 # happen after that time. 0 disables.
332#log_rotation_size = 10MB # Automatic rotation of logfiles will
333 # happen after that much log output.
334 # 0 disables.
335
336# These are relevant when logging to syslog:
337#syslog_facility = 'LOCAL0'
338#syslog_ident = 'postgres'
339
340#silent_mode = off # Run server silently.
341 # DO NOT USE without syslog or
342 # logging_collector
343 # (change requires restart)
344
345
346# - When to Log -
347
348client_min_messages = notice # values in order of decreasing detail:
349 # debug5
350 # debug4
351 # debug3
352 # debug2
353 # debug1
354 # log
355 # notice
356 # warning
357 # error
358
359log_min_messages = warning # values in order of decreasing detail:
360 # debug5
361 # debug4
362 # debug3
363 # debug2
364 # debug1
365 # info
366 # notice
367 # warning
368 # error
369 # log
370 # fatal
371 # panic
372
373log_min_error_statement = error # values in order of decreasing detail:
374 # debug5
375 # debug4
376 # debug3
377 # debug2
378 # debug1
379 # info
380 # notice
381 # warning
382 # error
383 # log
384 # fatal
385 # panic (effectively off)
386
387log_min_duration_statement = 10000 # -1 is disabled, 0 logs all statements
388 # and their durations, > 0 logs only
389 # statements running at least this number
390 # of milliseconds
391
392
393# - What to Log -
394
395#debug_print_parse = off
396#debug_print_rewritten = off
397#debug_print_plan = off
398#debug_pretty_print = on
399#log_checkpoints = off
400#log_connections = off
401#log_disconnections = off
402#log_duration = off
403#log_error_verbosity = default # terse, default, or verbose messages
404#log_hostname = off
405
406log_line_prefix = '{{ postgres_log_line_prefix }}'
407#log_line_prefix = '%t ' # special values:
408 # %a = application name
409 # %u = user name
410 # %d = database name
411 # %r = remote host and port
412 # %h = remote host
413 # %p = process ID
414 # %t = timestamp without milliseconds
415 # %m = timestamp with milliseconds
416 # %i = command tag
417 # %e = SQL state
418 # %c = session ID
419 # %l = session line number
420 # %s = session start timestamp
421 # %v = virtual transaction ID
422 # %x = transaction ID (0 if none)
423 # %q = stop here in non-session
424 # processes
425 # %% = '%'
426 # e.g. '<%u%%%d> '
427#log_lock_waits = off # log lock waits >= deadlock_timeout
428#log_statement = 'none' # none, ddl, mod, all
429#log_temp_files = -1 # log temporary files equal or larger
430 # than the specified size in kilobytes;
431 # -1 disables, 0 logs all temp files
432log_timezone = 'GMT'
433
434
435#------------------------------------------------------------------------------
436# RUNTIME STATISTICS
437#------------------------------------------------------------------------------
438
439# - Query/Index Statistics Collector -
440
441#track_activities = on
442#track_counts = on
443#track_functions = none # none, pl, all
444#track_activity_query_size = 1024 # (change requires restart)
445#update_process_title = on
446stats_temp_directory = '/var/run/postgresql/{{ postgres_version }}-{{ postgres_cluster_name }}.pg_stat_tmp'
447
448
449# - Statistics Monitoring -
450
451#log_parser_stats = off
452#log_planner_stats = off
453#log_executor_stats = off
454#log_statement_stats = off
455
456
457#------------------------------------------------------------------------------
458# AUTOVACUUM PARAMETERS
459#------------------------------------------------------------------------------
460
461autovacuum = on # Enable autovacuum subprocess? 'on'
462 # requires track_counts to also be on.
463#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
464 # their durations, > 0 logs only
465 # actions running at least this number
466 # of milliseconds.
467#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
468 # (change requires restart)
469#autovacuum_naptime = 1min # time between autovacuum runs
470#autovacuum_vacuum_threshold = 50 # min number of row updates before
471 # vacuum
472#autovacuum_analyze_threshold = 50 # min number of row updates before
473 # analyze
474#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
475#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
476#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
477 # (change requires restart)
478#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
479 # autovacuum, in milliseconds;
480 # -1 means use vacuum_cost_delay
481#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
482 # autovacuum, -1 means use
483 # vacuum_cost_limit
484
485
486#------------------------------------------------------------------------------
487# CLIENT CONNECTION DEFAULTS
488#------------------------------------------------------------------------------
489
490# - Statement Behavior -
491
492#search_path = '"$user",public' # schema names
493#default_tablespace = '' # a tablespace name, '' uses the default
494#temp_tablespaces = '' # a list of tablespace names, '' uses
495 # only default tablespace
496#check_function_bodies = on
497#default_transaction_isolation = 'read committed'
498#default_transaction_read_only = off
499#default_transaction_deferrable = off
500#session_replication_role = 'origin'
501#statement_timeout = 0 # in milliseconds, 0 is disabled
502#vacuum_freeze_min_age = 50000000
503#vacuum_freeze_table_age = 150000000
504#bytea_output = 'hex' # hex, escape
505#xmlbinary = 'base64'
506#xmloption = 'content'
507
508# - Locale and Formatting -
509
510datestyle = '{{ postgres_datestyle }}'
511#intervalstyle = 'postgres'
512#timezone = '(defaults to server environment setting)'
513{% if postgres_timezone is defined %}
514timezone = '{{ postgres_timezone }}'
515{% endif %}
516#timezone_abbreviations = 'Default' # Select the set of available time zone
517 # abbreviations. Currently, there are
518 # Default
519 # Australia (historical usage)
520 # India
521 # You can create your own file in
522 # share/timezonesets/.
523#extra_float_digits = 0 # min -15, max 3
524#client_encoding = sql_ascii # actually, defaults to database
525 # encoding
526
527# These settings are initialized by initdb, but they can be changed.
528lc_messages = '{{ postgres_locale_system }}' # locale for system error message
529 # strings
530lc_monetary = '{{ postgres_locale_formats }}' # locale for monetary formatting
531lc_numeric = '{{ postgres_locale_formats }}' # locale for number formatting
532lc_time = '{{ postgres_locale_formats }}' # locale for time formatting
533
534# default configuration for text search
535default_text_search_config = '{{ postgres_text_search_config }}'
536
537# - Other Defaults -
538
539#dynamic_library_path = '$libdir'
540#local_preload_libraries = ''
541
542
543#------------------------------------------------------------------------------
544# LOCK MANAGEMENT
545#------------------------------------------------------------------------------
546
547#deadlock_timeout = 1s
548#max_locks_per_transaction = 64 # min 10
549 # (change requires restart)
550# Note: Each lock table slot uses ~270 bytes of shared memory, and there are
551# max_locks_per_transaction * (max_connections + max_prepared_transactions)
552# lock table slots.
553#max_pred_locks_per_transaction = 64 # min 10
554 # (change requires restart)
555
556#------------------------------------------------------------------------------
557# VERSION/PLATFORM COMPATIBILITY
558#------------------------------------------------------------------------------
559
560# - Previous PostgreSQL Versions -
561
562#array_nulls = on
563#backslash_quote = safe_encoding # on, off, or safe_encoding
564#default_with_oids = off
565#escape_string_warning = on
566#lo_compat_privileges = off
567#quote_all_identifiers = off
568#sql_inheritance = on
569#standard_conforming_strings = on
570#synchronize_seqscans = on
571
572# - Other Platforms and Clients -
573
574#transform_null_equals = off
575
576
577#------------------------------------------------------------------------------
578# ERROR HANDLING
579#------------------------------------------------------------------------------
580
581#exit_on_error = off # terminate session on any error?
582#restart_after_crash = on # reinitialize after backend crash?
583
584
585#------------------------------------------------------------------------------
586# CUSTOMIZED OPTIONS
587#------------------------------------------------------------------------------
588
589#custom_variable_classes = '' # list of custom variable class names
diff --git a/templates/postgresql.9.4.conf.j2 b/templates/postgresql.9.4.conf.j2
new file mode 100644
index 0000000..c251bfa
--- /dev/null
+++ b/templates/postgresql.9.4.conf.j2
@@ -0,0 +1,647 @@
1# {{ ansible_managed }}
2# -----------------------------
3# PostgreSQL configuration file
4# -----------------------------
5#
6# This file consists of lines of the form:
7#
8# name = value
9#
10# (The "=" is optional.) Whitespace may be used. Comments are introduced with
11# "#" anywhere on a line. The complete list of parameter names and allowed
12# values can be found in the PostgreSQL documentation.
13#
14# The commented-out settings shown in this file represent the default values.
15# Re-commenting a setting is NOT sufficient to revert it to the default value;
16# you need to reload the server.
17#
18# This file is read on server startup and when the server receives a SIGHUP
19# signal. If you edit the file on a running system, you have to SIGHUP the
20# server for the changes to take effect, or use "pg_ctl reload". Some
21# parameters, which are marked below, require a server shutdown and restart to
22# take effect.
23#
24# Any parameter can also be given as a command-line option to the server, e.g.,
25# "postgres -c log_connections=on". Some parameters can be changed at run time
26# with the "SET" SQL command.
27#
28# Memory units: kB = kilobytes Time units: ms = milliseconds
29# MB = megabytes s = seconds
30# GB = gigabytes min = minutes
31# TB = terabytes h = hours
32# d = days
33
34
35#------------------------------------------------------------------------------
36# FILE LOCATIONS
37#------------------------------------------------------------------------------
38
39# The default values of these variables are driven from the -D command-line
40# option or PGDATA environment variable, represented here as ConfigDir.
41
42data_directory = '/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}' # use data in another directory
43 # (change requires restart)
44hba_file = '/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/pg_hba.conf' # host-based authentication file
45 # (change requires restart)
46ident_file = '/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/pg_ident.conf' # ident configuration file
47 # (change requires restart)
48
49# If external_pid_file is not explicitly set, no extra PID file is written.
50external_pid_file = '/var/run/postgresql/{{ postgres_version }}-{{ postgres_cluster_name }}.pid' # write an extra PID file
51 # (change requires restart)
52
53
54#------------------------------------------------------------------------------
55# CONNECTIONS AND AUTHENTICATION
56#------------------------------------------------------------------------------
57
58# - Connection Settings -
59
60listen_addresses = '{{ postgres_listen_addresses | reject("none") | join(",") }}' # what IP address(es) to listen on;
61 # comma-separated list of addresses;
62 # defaults to 'localhost'; use '*' for all
63 # (change requires restart)
64port = {{ postgres_port }} # (change requires restart)
65max_connections = 400 # (change requires restart)
66# Note: Increasing max_connections costs ~400 bytes of shared memory per
67# connection slot, plus lock space (see max_locks_per_transaction).
68#superuser_reserved_connections = 3 # (change requires restart)
69unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
70 # (change requires restart)
71#unix_socket_group = '' # (change requires restart)
72#unix_socket_permissions = 0777 # begin with 0 to use octal notation
73 # (change requires restart)
74#bonjour = off # advertise server via Bonjour
75 # (change requires restart)
76#bonjour_name = '' # defaults to the computer name
77 # (change requires restart)
78
79# - Security and Authentication -
80
81#authentication_timeout = 1min # 1s-600s
82ssl = true # (change requires restart)
83#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
84 # (change requires restart)
85#ssl_prefer_server_ciphers = on # (change requires restart)
86#ssl_ecdh_curve = 'prime256v1' # (change requires restart)
87#ssl_renegotiation_limit = 512MB # amount of data between renegotiations
88ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' # (change requires restart)
89ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' # (change requires restart)
90#ssl_ca_file = '' # (change requires restart)
91#ssl_crl_file = '' # (change requires restart)
92#password_encryption = on
93#db_user_namespace = off
94
95# GSSAPI using Kerberos
96#krb_server_keyfile = ''
97#krb_caseins_users = off
98
99# - TCP Keepalives -
100# see "man 7 tcp" for details
101
102#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
103 # 0 selects the system default
104#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
105 # 0 selects the system default
106#tcp_keepalives_count = 0 # TCP_KEEPCNT;
107 # 0 selects the system default
108
109
110#------------------------------------------------------------------------------
111# RESOURCE USAGE (except WAL)
112#------------------------------------------------------------------------------
113
114# - Memory -
115
116{% if ansible_memtotal_mb > 65536 %}
117shared_buffers = 16GB # min 128kB
118{% else %}
119shared_buffers = {{ ansible_memtotal_mb // 4 }}MB # min 128kB
120{% endif %}
121 # (change requires restart)
122#huge_pages = try # on, off, or try
123 # (change requires restart)
124#temp_buffers = 8MB # min 800kB
125#max_prepared_transactions = 0 # zero disables the feature
126 # (change requires restart)
127# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
128# per transaction slot, plus lock space (see max_locks_per_transaction).
129# It is not advisable to set max_prepared_transactions nonzero unless you
130# actively intend to use prepared transactions.
131#work_mem = 4MB # min 64kB
132#maintenance_work_mem = 64MB # min 1MB
133#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
134#max_stack_depth = 2MB # min 100kB
135dynamic_shared_memory_type = posix # the default is the first option
136 # supported by the operating system:
137 # posix
138 # sysv
139 # windows
140 # mmap
141 # use none to disable dynamic shared memory
142
143# - Disk -
144
145#temp_file_limit = -1 # limits per-session temp file space
146 # in kB, or -1 for no limit
147
148# - Kernel Resource Usage -
149
150#max_files_per_process = 1000 # min 25
151 # (change requires restart)
152#shared_preload_libraries = '' # (change requires restart)
153
154# - Cost-Based Vacuum Delay -
155
156#vacuum_cost_delay = 0 # 0-100 milliseconds
157#vacuum_cost_page_hit = 1 # 0-10000 credits
158#vacuum_cost_page_miss = 10 # 0-10000 credits
159#vacuum_cost_page_dirty = 20 # 0-10000 credits
160#vacuum_cost_limit = 200 # 1-10000 credits
161
162# - Background Writer -
163
164#bgwriter_delay = 200ms # 10-10000ms between rounds
165#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
166#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round
167
168# - Asynchronous Behavior -
169
170#effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
171#max_worker_processes = 8
172
173
174#------------------------------------------------------------------------------
175# WRITE AHEAD LOG
176#------------------------------------------------------------------------------
177
178# - Settings -
179
180wal_level = logical # minimal, archive, hot_standby, or logical
181 # (change requires restart)
182
183{% if postgres_fsync_enabled %}
184#fsync = on # turns forced synchronization on or off
185#synchronous_commit = on # synchronization level;
186{% else %}
187fsync = off # turns forced synchronization on or off
188synchronous_commit = off # synchronization level;
189{% endif %}
190 # off, local, remote_write, or on
191#wal_sync_method = fsync # the default is the first option
192 # supported by the operating system:
193 # open_datasync
194 # fdatasync (default on Linux)
195 # fsync
196 # fsync_writethrough
197 # open_sync
198#full_page_writes = on # recover from partial page writes
199wal_log_hints = on # also do full page writes of non-critical updates
200 # (change requires restart)
201#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
202 # (change requires restart)
203#wal_writer_delay = 200ms # 1-10000 milliseconds
204
205#commit_delay = 0 # range 0-100000, in microseconds
206#commit_siblings = 5 # range 1-1000
207
208# - Checkpoints -
209
210#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
211#checkpoint_timeout = 5min # range 30s-1h
212#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
213#checkpoint_warning = 30s # 0 disables
214
215# - Archiving -
216
217{% if postgres_archive_enabled %}
218archive_mode = on
219archive_command = 'rsync -a %p barman@{{ postgres_barman_server }}:/var/lib/barman/{{ barman_directory }}/incoming/%f'
220{% else %}
221archive_mode = off
222archive_command = ''
223{% endif %}
224
225#archive_mode = off # enables archiving; off, on, or always
226 # (change requires restart)
227#archive_command = '' # command to use to archive a logfile segment
228 # placeholders: %p = path of file to archive
229 # %f = file name only
230 # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
231#archive_timeout = 0 # force a logfile segment switch after this
232 # number of seconds; 0 disables
233
234
235#------------------------------------------------------------------------------
236# REPLICATION
237#------------------------------------------------------------------------------
238
239# - Sending Server(s) -
240
241# Set these on the master and on any standby that will send replication data.
242
243max_wal_senders = 5 # max number of walsender processes
244 # (change requires restart)
245wal_keep_segments = 16 # in logfile segments, 16MB each; 0 disables
246#wal_sender_timeout = 60s # in milliseconds; 0 disables
247
248{% if postgres_max_replication_slots is defined %}
249max_replication_slots = {{ postgres_max_replication_slots }} # max number of replication slots
250{% else %}
251#max_replication_slots = 0 # max number of replication slots
252{% endif %}
253 # (change requires restart)
254
255# - Master Server -
256
257# These settings are ignored on a standby server.
258
259#synchronous_standby_names = '' # standby servers that provide sync rep
260 # comma-separated list of application_name
261 # from standby(s); '*' = all
262#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
263
264# - Standby Servers -
265
266# These settings are ignored on a master server.
267
268hot_standby = on # "on" allows queries during recovery
269 # (change requires restart)
270#max_standby_archive_delay = 30s # max delay before canceling queries
271 # when reading WAL from archive;
272 # -1 allows indefinite delay
273max_standby_streaming_delay = {{ postgres_extra_config.max_standby_streaming_delay | default('600s') }} # max delay before canceling queries
274 # when reading streaming WAL;
275 # -1 allows indefinite delay
276#wal_receiver_status_interval = 10s # send replies at least this often
277 # 0 disables
278#hot_standby_feedback = off # send info from standby to prevent
279 # query conflicts
280#wal_receiver_timeout = 60s # time that receiver waits for
281 # communication from master
282 # in milliseconds; 0 disables
283
284
285#------------------------------------------------------------------------------
286# QUERY TUNING
287#------------------------------------------------------------------------------
288
289# - Planner Method Configuration -
290
291#enable_bitmapscan = on
292#enable_hashagg = on
293#enable_hashjoin = on
294#enable_indexscan = on
295#enable_indexonlyscan = on
296#enable_material = on
297#enable_mergejoin = on
298#enable_nestloop = on
299#enable_seqscan = on
300#enable_sort = on
301#enable_tidscan = on
302
303# - Planner Cost Constants -
304
305#seq_page_cost = 1.0 # measured on an arbitrary scale
306{% if postgres_specific_random_page_cost is defined -%}
307# Random page cost is an arbitrary value relative to the seq_page_cost conf (default 1.0)
308# On SSD disk it is better to lower the default value from 4.0 to 1.0 for instance.
309random_page_cost = {{ postgres_specific_random_page_cost }} # same scale as above
310{% else -%}
311#random_page_cost = 4.0 # same scale as above
312{% endif %}
313#cpu_tuple_cost = 0.01 # same scale as above
314#cpu_index_tuple_cost = 0.005 # same scale as above
315#cpu_operator_cost = 0.0025 # same scale as above
316effective_cache_size = {{ ansible_memtotal_mb // 2 }}MB
317
318# - Genetic Query Optimizer -
319
320#geqo = on
321#geqo_threshold = 12
322#geqo_effort = 5 # range 1-10
323#geqo_pool_size = 0 # selects default based on effort
324#geqo_generations = 0 # selects default based on effort
325#geqo_selection_bias = 2.0 # range 1.5-2.0
326#geqo_seed = 0.0 # range 0.0-1.0
327
328# - Other Planner Options -
329
330#default_statistics_target = 100 # range 1-10000
331#constraint_exclusion = partition # on, off, or partition
332#cursor_tuple_fraction = 0.1 # range 0.0-1.0
333#from_collapse_limit = 8
334#join_collapse_limit = 8 # 1 disables collapsing of explicit
335 # JOIN clauses
336
337
338#------------------------------------------------------------------------------
339# ERROR REPORTING AND LOGGING
340#------------------------------------------------------------------------------
341
342# - Where to Log -
343
344#log_destination = 'stderr' # Valid values are combinations of
345 # stderr, csvlog, syslog, and eventlog,
346 # depending on platform. csvlog
347 # requires logging_collector to be on.
348
349# This is used when logging to stderr:
350#logging_collector = off # Enable capturing of stderr and csvlog
351 # into log files. Required to be on for
352 # csvlogs.
353 # (change requires restart)
354
355# These are only used if logging_collector is on:
356#log_directory = 'pg_log' # directory where log files are written,
357 # can be absolute or relative to PGDATA
358#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
359 # can include strftime() escapes
360#log_file_mode = 0600 # creation mode for log files,
361 # begin with 0 to use octal notation
362#log_truncate_on_rotation = off # If on, an existing log file with the
363 # same name as the new log file will be
364 # truncated rather than appended to.
365 # But such truncation only occurs on
366 # time-driven rotation, not on restarts
367 # or size-driven rotation. Default is
368 # off, meaning append to existing files
369 # in all cases.
370#log_rotation_age = 1d # Automatic rotation of logfiles will
371 # happen after that time. 0 disables.
372#log_rotation_size = 10MB # Automatic rotation of logfiles will
373 # happen after that much log output.
374 # 0 disables.
375
376# These are relevant when logging to syslog:
377#syslog_facility = 'LOCAL0'
378#syslog_ident = 'postgres'
379
380# This is only relevant when logging to eventlog (win32):
381#event_source = 'PostgreSQL'
382
383# - When to Log -
384
385#client_min_messages = notice # values in order of decreasing detail:
386 # debug5
387 # debug4
388 # debug3
389 # debug2
390 # debug1
391 # log
392 # notice
393 # warning
394 # error
395
396#log_min_messages = warning # values in order of decreasing detail:
397 # debug5
398 # debug4
399 # debug3
400 # debug2
401 # debug1
402 # info
403 # notice
404 # warning
405 # error
406 # log
407 # fatal
408 # panic
409
410#log_min_error_statement = error # values in order of decreasing detail:
411 # debug5
412 # debug4
413 # debug3
414 # debug2
415 # debug1
416 # info
417 # notice
418 # warning
419 # error
420 # log
421 # fatal
422 # panic (effectively off)
423
424log_min_duration_statement = 10000 # -1 is disabled, 0 logs all statements
425 # and their durations, > 0 logs only
426 # statements running at least this number
427 # of milliseconds
428
429
430# - What to Log -
431
432#debug_print_parse = off
433#debug_print_rewritten = off
434#debug_print_plan = off
435#debug_pretty_print = on
436#log_checkpoints = off
437#log_connections = off
438#log_disconnections = off
439#log_duration = off
440#log_error_verbosity = default # terse, default, or verbose messages
441#log_hostname = off
442
443log_line_prefix = '{{ postgres_log_line_prefix }}'
444#log_line_prefix = '%t ' # special values:
445 # %a = application name
446 # %u = user name
447 # %d = database name
448 # %r = remote host and port
449 # %h = remote host
450 # %p = process ID
451 # %t = timestamp without milliseconds
452 # %m = timestamp with milliseconds
453 # %i = command tag
454 # %e = SQL state
455 # %c = session ID
456 # %l = session line number
457 # %s = session start timestamp
458 # %v = virtual transaction ID
459 # %x = transaction ID (0 if none)
460 # %q = stop here in non-session
461 # processes
462 # %% = '%'
463 # e.g. '<%u%%%d> '
464#log_lock_waits = off # log lock waits >= deadlock_timeout
465#log_statement = 'none' # none, ddl, mod, all
466#log_temp_files = -1 # log temporary files equal or larger
467 # than the specified size in kilobytes;
468 # -1 disables, 0 logs all temp files
469log_timezone = 'GMT'
470
471
472#------------------------------------------------------------------------------
473# RUNTIME STATISTICS
474#------------------------------------------------------------------------------
475
476# - Query/Index Statistics Collector -
477
478#track_activities = on
479#track_counts = on
480#track_io_timing = off
481#track_functions = none # none, pl, all
482#track_activity_query_size = 1024 # (change requires restart)
483stats_temp_directory = '/var/run/postgresql/{{ postgres_version }}-{{ postgres_cluster_name }}.pg_stat_tmp'
484
485
486# - Statistics Monitoring -
487
488#log_parser_stats = off
489#log_planner_stats = off
490#log_executor_stats = off
491#log_statement_stats = off
492
493
494#------------------------------------------------------------------------------
495# AUTOVACUUM PARAMETERS
496#------------------------------------------------------------------------------
497
498#autovacuum = on # Enable autovacuum subprocess? 'on'
499 # requires track_counts to also be on.
500#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
501 # their durations, > 0 logs only
502 # actions running at least this number
503 # of milliseconds.
504#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
505 # (change requires restart)
506#autovacuum_naptime = 1min # time between autovacuum runs
507#autovacuum_vacuum_threshold = 50 # min number of row updates before
508 # vacuum
509#autovacuum_analyze_threshold = 50 # min number of row updates before
510 # analyze
511#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
512#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
513#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
514 # (change requires restart)
515#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
516 # before forced vacuum
517 # (change requires restart)
518#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
519 # autovacuum, in milliseconds;
520 # -1 means use vacuum_cost_delay
521#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
522 # autovacuum, -1 means use
523 # vacuum_cost_limit
524
525
526#------------------------------------------------------------------------------
527# CLIENT CONNECTION DEFAULTS
528#------------------------------------------------------------------------------
529
530# - Statement Behavior -
531
532#search_path = '"$user",public' # schema names
533#default_tablespace = '' # a tablespace name, '' uses the default
534#temp_tablespaces = '' # a list of tablespace names, '' uses
535 # only default tablespace
536#check_function_bodies = on
537#default_transaction_isolation = 'read committed'
538#default_transaction_read_only = off
539#default_transaction_deferrable = off
540#session_replication_role = 'origin'
541#statement_timeout = 0 # in milliseconds, 0 is disabled
542#lock_timeout = 0 # in milliseconds, 0 is disabled
543#vacuum_freeze_min_age = 50000000
544#vacuum_freeze_table_age = 150000000
545#vacuum_multixact_freeze_min_age = 5000000
546#vacuum_multixact_freeze_table_age = 150000000
547#bytea_output = 'hex' # hex, escape
548#xmlbinary = 'base64'
549#xmloption = 'content'
550
551# - Locale and Formatting -
552
553datestyle = '{{ postgres_datestyle }}'
554#intervalstyle = 'postgres'
555#timezone = '(defaults to server environment setting)'
556{% if postgres_timezone is defined %}
557timezone = '{{ postgres_timezone }}'
558{% endif %}
559#timezone_abbreviations = 'Default' # Select the set of available time zone
560 # abbreviations. Currently, there are
561 # Default
562 # Australia (historical usage)
563 # India
564 # You can create your own file in
565 # share/timezonesets/.
566#extra_float_digits = 0 # min -15, max 3
567#client_encoding = sql_ascii # actually, defaults to database
568 # encoding
569
570# These settings are initialized by initdb, but they can be changed.
571lc_messages = '{{ postgres_locale_system }}' # locale for system error message
572 # strings
573lc_monetary = '{{ postgres_locale_formats }}' # locale for monetary formatting
574lc_numeric = '{{ postgres_locale_formats }}' # locale for number formatting
575lc_time = '{{ postgres_locale_formats }}' # locale for time formatting
576
577# default configuration for text search
578default_text_search_config = '{{ postgres_text_search_config }}'
579
580# - Other Defaults -
581
582#dynamic_library_path = '$libdir'
583#local_preload_libraries = ''
584#session_preload_libraries = ''
585
586
587#------------------------------------------------------------------------------
588# LOCK MANAGEMENT
589#------------------------------------------------------------------------------
590
591#deadlock_timeout = 1s
592#max_locks_per_transaction = 64 # min 10
593 # (change requires restart)
594# Note: Each lock table slot uses ~270 bytes of shared memory, and there are
595# max_locks_per_transaction * (max_connections + max_prepared_transactions)
596# lock table slots.
597#max_pred_locks_per_transaction = 64 # min 10
598 # (change requires restart)
599
600
601#------------------------------------------------------------------------------
602# VERSION/PLATFORM COMPATIBILITY
603#------------------------------------------------------------------------------
604
605# - Previous PostgreSQL Versions -
606
607#array_nulls = on
608#backslash_quote = safe_encoding # on, off, or safe_encoding
609#default_with_oids = off
610#escape_string_warning = on
611#lo_compat_privileges = off
612#quote_all_identifiers = off
613#sql_inheritance = on
614#standard_conforming_strings = on
615#synchronize_seqscans = on
616
617# - Other Platforms and Clients -
618
619#transform_null_equals = off
620
621
622#------------------------------------------------------------------------------
623# ERROR HANDLING
624#------------------------------------------------------------------------------
625
626#exit_on_error = off # terminate session on any error?
627#restart_after_crash = on # reinitialize after backend crash?
628
629
630#------------------------------------------------------------------------------
631# CONFIG FILE INCLUDES
632#------------------------------------------------------------------------------
633
634# These options allow settings to be loaded from files other than the
635# default postgresql.conf.
636
637#include_dir = 'conf.d' # include files ending in '.conf' from
638 # directory 'conf.d'
639#include_if_exists = 'exists.conf' # include file only if it exists
640#include = 'special.conf' # include file
641
642
643#------------------------------------------------------------------------------
644# CUSTOMIZED OPTIONS
645#------------------------------------------------------------------------------
646
647# Add settings for extensions here
diff --git a/templates/postgresql.9.5.conf.j2 b/templates/postgresql.9.5.conf.j2
new file mode 100644
index 0000000..5f666bf
--- /dev/null
+++ b/templates/postgresql.9.5.conf.j2
@@ -0,0 +1,657 @@
1# {{ ansible_managed }}
2# -----------------------------
3# PostgreSQL configuration file
4# -----------------------------
5#
6# This file consists of lines of the form:
7#
8# name = value
9#
10# (The "=" is optional.) Whitespace may be used. Comments are introduced with
11# "#" anywhere on a line. The complete list of parameter names and allowed
12# values can be found in the PostgreSQL documentation.
13#
14# The commented-out settings shown in this file represent the default values.
15# Re-commenting a setting is NOT sufficient to revert it to the default value;
16# you need to reload the server.
17#
18# This file is read on server startup and when the server receives a SIGHUP
19# signal. If you edit the file on a running system, you have to SIGHUP the
20# server for the changes to take effect, or use "pg_ctl reload". Some
21# parameters, which are marked below, require a server shutdown and restart to
22# take effect.
23#
24# Any parameter can also be given as a command-line option to the server, e.g.,
25# "postgres -c log_connections=on". Some parameters can be changed at run time
26# with the "SET" SQL command.
27#
28# Memory units: kB = kilobytes Time units: ms = milliseconds
29# MB = megabytes s = seconds
30# GB = gigabytes min = minutes
31# TB = terabytes h = hours
32# d = days
33
34
35#------------------------------------------------------------------------------
36# FILE LOCATIONS
37#------------------------------------------------------------------------------
38
39# The default values of these variables are driven from the -D command-line
40# option or PGDATA environment variable, represented here as ConfigDir.
41
42data_directory = '/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}' # use data in another directory
43 # (change requires restart)
44hba_file = '/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/pg_hba.conf' # host-based authentication file
45 # (change requires restart)
46ident_file = '/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/pg_ident.conf' # ident configuration file
47 # (change requires restart)
48
49# If external_pid_file is not explicitly set, no extra PID file is written.
50external_pid_file = '/var/run/postgresql/{{ postgres_version }}-{{ postgres_cluster_name }}.pid' # write an extra PID file
51 # (change requires restart)
52
53
54#------------------------------------------------------------------------------
55# CONNECTIONS AND AUTHENTICATION
56#------------------------------------------------------------------------------
57
58# - Connection Settings -
59
60listen_addresses = '{{ postgres_listen_addresses | reject("none") | join(",") }}' # what IP address(es) to listen on;
61 # comma-separated list of addresses;
62 # defaults to 'localhost'; use '*' for all
63 # (change requires restart)
64port = {{ postgres_port }} # (change requires restart)
65max_connections = 400 # (change requires restart)
66# Note: Increasing max_connections costs ~400 bytes of shared memory per
67# connection slot, plus lock space (see max_locks_per_transaction).
68#superuser_reserved_connections = 3 # (change requires restart)
69unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
70 # (change requires restart)
71#unix_socket_group = '' # (change requires restart)
72#unix_socket_permissions = 0777 # begin with 0 to use octal notation
73 # (change requires restart)
74#bonjour = off # advertise server via Bonjour
75 # (change requires restart)
76#bonjour_name = '' # defaults to the computer name
77 # (change requires restart)
78
79# - Security and Authentication -
80
81#authentication_timeout = 1min # 1s-600s
82ssl = true # (change requires restart)
83#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
84 # (change requires restart)
85#ssl_prefer_server_ciphers = on # (change requires restart)
86#ssl_ecdh_curve = 'prime256v1' # (change requires restart)
87#ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' # (change requires restart)
88# Default will be $PGDATA/server.pem
89#ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' # (change requires restart)
90# Default will be $PGDATA/server.key
91#ssl_ca_file = '' # (change requires restart)
92#ssl_crl_file = '' # (change requires restart)
93#password_encryption = on
94#db_user_namespace = off
95#row_security = on
96
97# GSSAPI using Kerberos
98#krb_server_keyfile = ''
99#krb_caseins_users = off
100
101# - TCP Keepalives -
102# see "man 7 tcp" for details
103
104#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
105 # 0 selects the system default
106#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
107 # 0 selects the system default
108#tcp_keepalives_count = 0 # TCP_KEEPCNT;
109 # 0 selects the system default
110
111
112#------------------------------------------------------------------------------
113# RESOURCE USAGE (except WAL)
114#------------------------------------------------------------------------------
115
116# - Memory -
117
118{% if ansible_memtotal_mb > 65536 %}
119shared_buffers = 16GB # min 128kB
120{% else %}
121shared_buffers = {{ ansible_memtotal_mb // 4 }}MB # min 128kB
122{% endif %}
123 # (change requires restart)
124temp_buffers = 24MB # min 800kB
125#max_prepared_transactions = 0 # zero disables the feature
126 # (change requires restart)
127# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
128# per transaction slot, plus lock space (see max_locks_per_transaction).
129# It is not advisable to set max_prepared_transactions nonzero unless you
130# actively intend to use prepared transactions.
131work_mem = 512MB # min 64kB
132maintenance_work_mem = 1024MB # min 1MB
133#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
134#max_stack_depth = 2MB # min 100kB
135dynamic_shared_memory_type = posix # the default is the first option
136 # supported by the operating system:
137 # posix
138 # sysv
139 # windows
140 # mmap
141 # use none to disable dynamic shared memory
142
143# - Disk -
144
145#temp_file_limit = -1 # limits per-session temp file space
146 # in kB, or -1 for no limit
147
148# - Kernel Resource Usage -
149
150#max_files_per_process = 1000 # min 25
151 # (change requires restart)
152#shared_preload_libraries = '' # (change requires restart)
153
154# - Cost-Based Vacuum Delay -
155
156#vacuum_cost_delay = 0 # 0-100 milliseconds
157#vacuum_cost_page_hit = 1 # 0-10000 credits
158#vacuum_cost_page_miss = 10 # 0-10000 credits
159#vacuum_cost_page_dirty = 20 # 0-10000 credits
160#vacuum_cost_limit = 200 # 1-10000 credits
161
162# - Background Writer -
163
164bgwriter_delay = 2000ms # 10-10000ms between rounds
165#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
166#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers scanned/round
167
168# - Asynchronous Behavior -
169
170#effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
171#max_worker_processes = 8
172
173
174#------------------------------------------------------------------------------
175# WRITE AHEAD LOG
176#------------------------------------------------------------------------------
177
178# - Settings -
179
180wal_level = logical # minimal, archive, hot_standby, or logical
181 # (change requires restart)
182
183{% if postgres_fsync_enabled %}
184#fsync = on # turns forced synchronization on or off
185#synchronous_commit = on # synchronization level;
186{% else %}
187fsync = off # turns forced synchronization on or off
188synchronous_commit = off # synchronization level;
189{% endif %}
190 # off, local, remote_write, or on
191#wal_sync_method = fsync # the default is the first option
192 # supported by the operating system:
193 # open_datasync
194 # fdatasync (default on Linux)
195 # fsync
196 # fsync_writethrough
197 # open_sync
198#full_page_writes = on # recover from partial page writes
199#wal_compression = off # enable compression of full-page writes
200wal_log_hints = on # also do full page writes of non-critical updates
201 # (change requires restart)
202#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
203 # (change requires restart)
204#wal_writer_delay = 200ms # 1-10000 milliseconds
205
206#commit_delay = 0 # range 0-100000, in microseconds
207#commit_siblings = 5 # range 1-1000
208
209# - Checkpoints -
210
211#checkpoint_timeout = 5min # range 30s-1h
212#max_wal_size = 1GB
213#min_wal_size = 80MB
214#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
215#checkpoint_warning = 30s # 0 disables
216
217# - Archiving -
218
219{% if postgres_archive_enabled %}
220archive_mode = on
221archive_command = 'rsync -a %p barman@{{ postgres_barman_server }}:/var/lib/barman/{{ barman_directory }}/incoming/%f'
222{% else %}
223archive_mode = off
224archive_command = ''
225{% endif %}
226
227#archive_mode = off # enables archiving; off, on, or always
228 # (change requires restart)
229#archive_command = '' # command to use to archive a logfile segment
230 # placeholders: %p = path of file to archive
231 # %f = file name only
232 # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
233#archive_timeout = 0 # force a logfile segment switch after this
234 # number of seconds; 0 disables
235
236
237#------------------------------------------------------------------------------
238# REPLICATION
239#------------------------------------------------------------------------------
240
241# - Sending Server(s) -
242
243# Set these on the master and on any standby that will send replication data.
244
245max_wal_senders = 5 # max number of walsender processes
246 # (change requires restart)
247wal_keep_segments = 16 # in logfile segments, 16MB each; 0 disables
248#wal_sender_timeout = 60s # in milliseconds; 0 disables
249
250{% if postgres_max_replication_slots is defined %}
251max_replication_slots = {{ postgres_max_replication_slots }} # max number of replication slots
252{% else %}
253#max_replication_slots = 0 # max number of replication slots
254{% endif %}
255 # (change requires restart)
256#track_commit_timestamp = off # collect timestamp of transaction commit
257 # (change requires restart)
258
259# - Master Server -
260
261# These settings are ignored on a standby server.
262
263#synchronous_standby_names = '' # standby servers that provide sync rep
264 # comma-separated list of application_name
265 # from standby(s); '*' = all
266#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
267
268# - Standby Servers -
269
270# These settings are ignored on a master server.
271
272hot_standby = on # "on" allows queries during recovery
273 # (change requires restart)
274#max_standby_archive_delay = 30s # max delay before canceling queries
275 # when reading WAL from archive;
276 # -1 allows indefinite delay
277max_standby_streaming_delay = {{ postgres_extra_config.max_standby_streaming_delay | default('600s') }} # max delay before canceling queries
278 # when reading streaming WAL;
279 # -1 allows indefinite delay
280#wal_receiver_status_interval = 10s # send replies at least this often
281 # 0 disables
282#hot_standby_feedback = off # send info from standby to prevent
283 # query conflicts
284#wal_receiver_timeout = 60s # time that receiver waits for
285 # communication from master
286 # in milliseconds; 0 disables
287#wal_retrieve_retry_interval = 5s # time to wait before retrying to
288 # retrieve WAL after a failed attempt
289
290
291#------------------------------------------------------------------------------
292# QUERY TUNING
293#------------------------------------------------------------------------------
294
295# - Planner Method Configuration -
296
297#enable_bitmapscan = on
298#enable_hashagg = on
299#enable_hashjoin = on
300#enable_indexscan = on
301#enable_indexonlyscan = on
302#enable_material = on
303#enable_mergejoin = on
304#enable_nestloop = on
305#enable_seqscan = on
306#enable_sort = on
307#enable_tidscan = on
308
309# - Planner Cost Constants -
310
311#seq_page_cost = 1.0 # measured on an arbitrary scale
312{% if postgres_specific_random_page_cost is defined -%}
313# Random page cost is an arbitrary value relative to the seq_page_cost conf (default 1.0)
314# On SSD disk it is better to lower the default value from 4.0 to 1.0 for instance.
315random_page_cost = {{ postgres_specific_random_page_cost }} # same scale as above
316{% else -%}
317#random_page_cost = 4.0 # same scale as above
318{% endif %}
319#cpu_tuple_cost = 0.01 # same scale as above
320#cpu_index_tuple_cost = 0.005 # same scale as above
321#cpu_operator_cost = 0.0025 # same scale as above
322effective_cache_size = {{ ansible_memtotal_mb // 2 }}MB
323
324# - Genetic Query Optimizer -
325
326#geqo = on
327#geqo_threshold = 12
328#geqo_effort = 5 # range 1-10
329#geqo_pool_size = 0 # selects default based on effort
330#geqo_generations = 0 # selects default based on effort
331#geqo_selection_bias = 2.0 # range 1.5-2.0
332#geqo_seed = 0.0 # range 0.0-1.0
333
334# - Other Planner Options -
335
336#default_statistics_target = 100 # range 1-10000
337#constraint_exclusion = partition # on, off, or partition
338#cursor_tuple_fraction = 0.1 # range 0.0-1.0
339#from_collapse_limit = 8
340#join_collapse_limit = 8 # 1 disables collapsing of explicit
341 # JOIN clauses
342
343
344#------------------------------------------------------------------------------
345# ERROR REPORTING AND LOGGING
346#------------------------------------------------------------------------------
347
348# - Where to Log -
349
350#log_destination = 'stderr' # Valid values are combinations of
351 # stderr, csvlog, syslog, and eventlog,
352 # depending on platform. csvlog
353 # requires logging_collector to be on.
354
355# This is used when logging to stderr:
356#logging_collector = off # Enable capturing of stderr and csvlog
357 # into log files. Required to be on for
358 # csvlogs.
359 # (change requires restart)
360
361# These are only used if logging_collector is on:
362#log_directory = 'pg_log' # directory where log files are written,
363 # can be absolute or relative to PGDATA
364#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
365 # can include strftime() escapes
366#log_file_mode = 0600 # creation mode for log files,
367 # begin with 0 to use octal notation
368#log_truncate_on_rotation = off # If on, an existing log file with the
369 # same name as the new log file will be
370 # truncated rather than appended to.
371 # But such truncation only occurs on
372 # time-driven rotation, not on restarts
373 # or size-driven rotation. Default is
374 # off, meaning append to existing files
375 # in all cases.
376#log_rotation_age = 1d # Automatic rotation of logfiles will
377 # happen after that time. 0 disables.
378#log_rotation_size = 10MB # Automatic rotation of logfiles will
379 # happen after that much log output.
380 # 0 disables.
381
382# These are relevant when logging to syslog:
383#syslog_facility = 'LOCAL0'
384#syslog_ident = 'postgres'
385
386# This is only relevant when logging to eventlog (win32):
387#event_source = 'PostgreSQL'
388
389# - When to Log -
390
391#client_min_messages = notice # values in order of decreasing detail:
392 # debug5
393 # debug4
394 # debug3
395 # debug2
396 # debug1
397 # log
398 # notice
399 # warning
400 # error
401
402#log_min_messages = warning # values in order of decreasing detail:
403 # debug5
404 # debug4
405 # debug3
406 # debug2
407 # debug1
408 # info
409 # notice
410 # warning
411 # error
412 # log
413 # fatal
414 # panic
415
416#log_min_error_statement = error # values in order of decreasing detail:
417 # debug5
418 # debug4
419 # debug3
420 # debug2
421 # debug1
422 # info
423 # notice
424 # warning
425 # error
426 # log
427 # fatal
428 # panic (effectively off)
429
430log_min_duration_statement = 10000 # -1 is disabled, 0 logs all statements
431 # and their durations, > 0 logs only
432 # statements running at least this number
433 # of milliseconds
434
435
436# - What to Log -
437
438#debug_print_parse = off
439#debug_print_rewritten = off
440#debug_print_plan = off
441#debug_pretty_print = on
442#log_checkpoints = off
443#log_connections = off
444#log_disconnections = off
445#log_duration = off
446#log_error_verbosity = default # terse, default, or verbose messages
447#log_hostname = off
448
449log_line_prefix = '{{ postgres_log_line_prefix }}'
450#log_line_prefix = '%t ' # special values:
451 # %a = application name
452 # %u = user name
453 # %d = database name
454 # %r = remote host and port
455 # %h = remote host
456 # %p = process ID
457 # %t = timestamp without milliseconds
458 # %m = timestamp with milliseconds
459 # %i = command tag
460 # %e = SQL state
461 # %c = session ID
462 # %l = session line number
463 # %s = session start timestamp
464 # %v = virtual transaction ID
465 # %x = transaction ID (0 if none)
466 # %q = stop here in non-session
467 # processes
468 # %% = '%'
469 # e.g. '<%u%%%d> '
470#log_lock_waits = off # log lock waits >= deadlock_timeout
471#log_statement = 'none' # none, ddl, mod, all
472#log_replication_commands = off
473#log_temp_files = -1 # log temporary files equal or larger
474 # than the specified size in kilobytes;
475 # -1 disables, 0 logs all temp files
476log_timezone = 'GMT'
477
478
479#------------------------------------------------------------------------------
480# RUNTIME STATISTICS
481#------------------------------------------------------------------------------
482
483# - Query/Index Statistics Collector -
484
485#track_activities = on
486#track_counts = on
487#track_io_timing = off
488#track_functions = none # none, pl, all
489#track_activity_query_size = 1024 # (change requires restart)
490stats_temp_directory = '/var/run/postgresql/{{ postgres_version }}-{{ postgres_cluster_name }}.pg_stat_tmp'
491
492
493# - Statistics Monitoring -
494
495#log_parser_stats = off
496#log_planner_stats = off
497#log_executor_stats = off
498#log_statement_stats = off
499
500
501#------------------------------------------------------------------------------
502# AUTOVACUUM PARAMETERS
503#------------------------------------------------------------------------------
504
505#autovacuum = on # Enable autovacuum subprocess? 'on'
506 # requires track_counts to also be on.
507#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
508 # their durations, > 0 logs only
509 # actions running at least this number
510 # of milliseconds.
511#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
512 # (change requires restart)
513#autovacuum_naptime = 1min # time between autovacuum runs
514#autovacuum_vacuum_threshold = 50 # min number of row updates before
515 # vacuum
516#autovacuum_analyze_threshold = 50 # min number of row updates before
517 # analyze
518#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
519#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
520#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
521 # (change requires restart)
522#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
523 # before forced vacuum
524 # (change requires restart)
525#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
526 # autovacuum, in milliseconds;
527 # -1 means use vacuum_cost_delay
528#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
529 # autovacuum, -1 means use
530 # vacuum_cost_limit
531
532
533#------------------------------------------------------------------------------
534# CLIENT CONNECTION DEFAULTS
535#------------------------------------------------------------------------------
536
537# - Statement Behavior -
538
539#search_path = '"$user", public' # schema names
540#default_tablespace = '' # a tablespace name, '' uses the default
541#temp_tablespaces = '' # a list of tablespace names, '' uses
542 # only default tablespace
543#check_function_bodies = on
544#default_transaction_isolation = 'read committed'
545#default_transaction_read_only = off
546#default_transaction_deferrable = off
547#session_replication_role = 'origin'
548#statement_timeout = 0 # in milliseconds, 0 is disabled
549#lock_timeout = 0 # in milliseconds, 0 is disabled
550#vacuum_freeze_min_age = 50000000
551#vacuum_freeze_table_age = 150000000
552#vacuum_multixact_freeze_min_age = 5000000
553#vacuum_multixact_freeze_table_age = 150000000
554#bytea_output = 'hex' # hex, escape
555#xmlbinary = 'base64'
556#xmloption = 'content'
557#gin_fuzzy_search_limit = 0
558#gin_pending_list_limit = 4MB
559
560# - Locale and Formatting -
561
562datestyle = '{{ postgres_datestyle }}'
563#intervalstyle = 'postgres'
564#timezone = '(defaults to server environment setting)'
565{% if postgres_timezone is defined %}
566timezone = '{{ postgres_timezone }}'
567{% endif %}
568#timezone_abbreviations = 'Default' # Select the set of available time zone
569 # abbreviations. Currently, there are
570 # Default
571 # Australia (historical usage)
572 # India
573 # You can create your own file in
574 # share/timezonesets/.
575#extra_float_digits = 0 # min -15, max 3
576#client_encoding = sql_ascii # actually, defaults to database
577 # encoding
578
579# These settings are initialized by initdb, but they can be changed.
580lc_messages = '{{ postgres_locale_system }}' # locale for system error message
581 # strings
582lc_monetary = '{{ postgres_locale_formats }}' # locale for monetary formatting
583lc_numeric = '{{ postgres_locale_formats }}' # locale for number formatting
584lc_time = '{{ postgres_locale_formats }}' # locale for time formatting
585
586# default configuration for text search
587default_text_search_config = '{{ postgres_text_search_config }}'
588
589# - Other Defaults -
590
591#dynamic_library_path = '$libdir'
592#local_preload_libraries = ''
593#session_preload_libraries = ''
594
595
596#------------------------------------------------------------------------------
597# LOCK MANAGEMENT
598#------------------------------------------------------------------------------
599
600#deadlock_timeout = 1s
601#max_locks_per_transaction = 64 # min 10
602 # (change requires restart)
603# Note: Each lock table slot uses ~270 bytes of shared memory, and there are
604# max_locks_per_transaction * (max_connections + max_prepared_transactions)
605# lock table slots.
606#max_pred_locks_per_transaction = 64 # min 10
607 # (change requires restart)
608
609
610#------------------------------------------------------------------------------
611# VERSION/PLATFORM COMPATIBILITY
612#------------------------------------------------------------------------------
613
614# - Previous PostgreSQL Versions -
615
616#array_nulls = on
617#backslash_quote = safe_encoding # on, off, or safe_encoding
618#default_with_oids = off
619#escape_string_warning = on
620#lo_compat_privileges = off
621#operator_precedence_warning = off
622#quote_all_identifiers = off
623#sql_inheritance = on
624#standard_conforming_strings = on
625#synchronize_seqscans = on
626
627# - Other Platforms and Clients -
628
629#transform_null_equals = off
630
631
632#------------------------------------------------------------------------------
633# ERROR HANDLING
634#------------------------------------------------------------------------------
635
636#exit_on_error = off # terminate session on any error?
637#restart_after_crash = on # reinitialize after backend crash?
638
639
640#------------------------------------------------------------------------------
641# CONFIG FILE INCLUDES
642#------------------------------------------------------------------------------
643
644# These options allow settings to be loaded from files other than the
645# default postgresql.conf.
646
647#include_dir = 'conf.d' # include files ending in '.conf' from
648 # directory 'conf.d'
649#include_if_exists = 'exists.conf' # include file only if it exists
650#include = 'special.conf' # include file
651
652
653#------------------------------------------------------------------------------
654# CUSTOMIZED OPTIONS
655#------------------------------------------------------------------------------
656
657# Add settings for extensions here
diff --git a/templates/postgresql.9.6.conf.j2 b/templates/postgresql.9.6.conf.j2
new file mode 100644
index 0000000..4c756f2
--- /dev/null
+++ b/templates/postgresql.9.6.conf.j2
@@ -0,0 +1,685 @@
1# {{ ansible_managed }}
2# -----------------------------
3# PostgreSQL configuration file
4# -----------------------------
5#
6# This file consists of lines of the form:
7#
8# name = value
9#
10# (The "=" is optional.) Whitespace may be used. Comments are introduced with
11# "#" anywhere on a line. The complete list of parameter names and allowed
12# values can be found in the PostgreSQL documentation.
13#
14# The commented-out settings shown in this file represent the default values.
15# Re-commenting a setting is NOT sufficient to revert it to the default value;
16# you need to reload the server.
17#
18# This file is read on server startup and when the server receives a SIGHUP
19# signal. If you edit the file on a running system, you have to SIGHUP the
20# server for the changes to take effect, or use "pg_ctl reload". Some
21# parameters, which are marked below, require a server shutdown and restart to
22# take effect.
23#
24# Any parameter can also be given as a command-line option to the server, e.g.,
25# "postgres -c log_connections=on". Some parameters can be changed at run time
26# with the "SET" SQL command.
27#
28# Memory units: kB = kilobytes Time units: ms = milliseconds
29# MB = megabytes s = seconds
30# GB = gigabytes min = minutes
31# TB = terabytes h = hours
32# d = days
33
34
35#------------------------------------------------------------------------------
36# FILE LOCATIONS
37#------------------------------------------------------------------------------
38
39# The default values of these variables are driven from the -D command-line
40# option or PGDATA environment variable, represented here as ConfigDir.
41
42data_directory = '/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}' # use data in another directory
43 # (change requires restart)
44hba_file = '/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/pg_hba.conf' # host-based authentication file
45 # (change requires restart)
46ident_file = '/etc/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/pg_ident.conf' # ident configuration file
47 # (change requires restart)
48
49# If external_pid_file is not explicitly set, no extra PID file is written.
50external_pid_file = '/var/run/postgresql/{{ postgres_version }}-{{ postgres_cluster_name }}.pid' # write an extra PID file
51 # (change requires restart)
52
53
54#------------------------------------------------------------------------------
55# CONNECTIONS AND AUTHENTICATION
56#------------------------------------------------------------------------------
57
58# - Connection Settings -
59
60listen_addresses = '{{ postgres_listen_addresses | reject("none") | join(",") }}' # what IP address(es) to listen on;
61 # comma-separated list of addresses;
62 # defaults to 'localhost'; use '*' for all
63 # (change requires restart)
64port = {{ postgres_port }} # (change requires restart)
65max_connections = 400 # (change requires restart)
66# Note: Increasing max_connections costs ~400 bytes of shared memory per
67# connection slot, plus lock space (see max_locks_per_transaction).
68#superuser_reserved_connections = 3 # (change requires restart)
69unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
70 # (change requires restart)
71#unix_socket_group = '' # (change requires restart)
72#unix_socket_permissions = 0777 # begin with 0 to use octal notation
73 # (change requires restart)
74#bonjour = off # advertise server via Bonjour
75 # (change requires restart)
76#bonjour_name = '' # defaults to the computer name
77 # (change requires restart)
78
79# - Security and Authentication -
80
81#authentication_timeout = 1min # 1s-600s
82ssl = true # (change requires restart)
83#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
84 # (change requires restart)
85#ssl_prefer_server_ciphers = on # (change requires restart)
86#ssl_ecdh_curve = 'prime256v1' # (change requires restart)
87#ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' # (change requires restart)
88# Default will be $PGDATA/server.pem
89#ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' # (change requires restart)
90# Default will be $PGDATA/server.key
91#ssl_ca_file = '' # (change requires restart)
92#ssl_crl_file = '' # (change requires restart)
93#password_encryption = on
94#db_user_namespace = off
95#row_security = on
96
97# GSSAPI using Kerberos
98#krb_server_keyfile = ''
99#krb_caseins_users = off
100
101# - TCP Keepalives -
102# see "man 7 tcp" for details
103
104#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
105 # 0 selects the system default
106#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
107 # 0 selects the system default
108#tcp_keepalives_count = 0 # TCP_KEEPCNT;
109 # 0 selects the system default
110
111
112#------------------------------------------------------------------------------
113# RESOURCE USAGE (except WAL)
114#------------------------------------------------------------------------------
115
116# - Memory -
117
118{% if ansible_memtotal_mb > 65536 %}
119shared_buffers = 16GB # min 128kB
120{% else %}
121shared_buffers = {{ ansible_memtotal_mb // 4 }}MB # min 128kB
122{% endif %}
123 # (change requires restart)
124#huge_pages = try # on, off, or try
125 # (change requires restart)
126temp_buffers = 24MB # min 800kB
127#max_prepared_transactions = 0 # zero disables the feature
128 # (change requires restart)
129# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
130# per transaction slot, plus lock space (see max_locks_per_transaction).
131# Caution: it is not advisable to set max_prepared_transactions nonzero unless
132# you actively intend to use prepared transactions.
133work_mem = 512MB # min 64kB
134maintenance_work_mem = 1024MB # min 1MB
135#replacement_sort_tuples = 150000 # limits use of replacement selection sort
136#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
137#max_stack_depth = 2MB # min 100kB
138dynamic_shared_memory_type = posix # the default is the first option
139 # supported by the operating system:
140 # posix
141 # sysv
142 # windows
143 # mmap
144 # use none to disable dynamic shared memory
145
146# - Disk -
147
148#temp_file_limit = -1 # limits per-process temp file space
149 # in kB, or -1 for no limit
150
151# - Kernel Resource Usage -
152
153#max_files_per_process = 1000 # min 25
154 # (change requires restart)
155#shared_preload_libraries = '' # (change requires restart)
156
157# - Cost-Based Vacuum Delay -
158
159#vacuum_cost_delay = 0 # 0-100 milliseconds
160#vacuum_cost_page_hit = 1 # 0-10000 credits
161#vacuum_cost_page_miss = 10 # 0-10000 credits
162#vacuum_cost_page_dirty = 20 # 0-10000 credits
163#vacuum_cost_limit = 200 # 1-10000 credits
164
165# - Background Writer -
166
167bgwriter_delay = 2000ms # 10-10000ms between rounds
168#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
169#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round
170#bgwriter_flush_after = 512kB # measured in pages, 0 disables
171
172# - Asynchronous Behavior -
173
174#effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
175#max_worker_processes = 8 # (change requires restart)
176#max_parallel_workers_per_gather = 0 # taken from max_worker_processes
177#old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate
178 # (change requires restart)
179#backend_flush_after = 0 # measured in pages, 0 disables
180
181
182#------------------------------------------------------------------------------
183# WRITE AHEAD LOG
184#------------------------------------------------------------------------------
185
186# - Settings -
187
188wal_level = logical # minimal, replica, or logical
189 # (change requires restart)
190
191{% if postgres_fsync_enabled %}
192#fsync = on # flush data to disk for crash safety
193#synchronous_commit = on # synchronization level;
194 # off, local, remote_write, remote_apply, or on
195{% else %}
196fsync = off # turns forced synchronization on or off
197# (turning this off can cause
198# unrecoverable data corruption)
199synchronous_commit = off # synchronization level;
200{% endif %}
201#wal_sync_method = fsync # the default is the first option
202 # supported by the operating system:
203 # open_datasync
204 # fdatasync (default on Linux)
205 # fsync
206 # fsync_writethrough
207 # open_sync
208#full_page_writes = on # recover from partial page writes
209#wal_compression = off # enable compression of full-page writes
210wal_log_hints = on # also do full page writes of non-critical updates
211 # (change requires restart)
212#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
213 # (change requires restart)
214#wal_writer_delay = 200ms # 1-10000 milliseconds
215#wal_writer_flush_after = 1MB # measured in pages, 0 disables
216
217#commit_delay = 0 # range 0-100000, in microseconds
218#commit_siblings = 5 # range 1-1000
219
220# - Checkpoints -
221
222#checkpoint_timeout = 5min # range 30s-1d
223#max_wal_size = 1GB
224#min_wal_size = 80MB
225#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
226#checkpoint_flush_after = 256kB # measured in pages, 0 disables
227#checkpoint_warning = 30s # 0 disables
228
229# - Archiving -
230
231{% if postgres_archive_enabled %}
232archive_mode = on
233archive_command = 'rsync -a %p barman@{{ postgres_barman_server }}:/var/lib/barman/{{ barman_directory }}/incoming/%f'
234{% else %}
235archive_mode = off
236archive_command = ''
237{% endif %}
238
239#archive_mode = off # enables archiving; off, on, or always
240 # (change requires restart)
241#archive_command = '' # command to use to archive a logfile segment
242 # placeholders: %p = path of file to archive
243 # %f = file name only
244 # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
245#archive_timeout = 0 # force a logfile segment switch after this
246 # number of seconds; 0 disables
247
248
249#------------------------------------------------------------------------------
250# REPLICATION
251#------------------------------------------------------------------------------
252
253# - Sending Server(s) -
254
255# Set these on the master and on any standby that will send replication data.
256
257max_wal_senders = 5 # max number of walsender processes
258 # (change requires restart)
259wal_keep_segments = 16 # in logfile segments, 16MB each; 0 disables
260#wal_sender_timeout = 60s # in milliseconds; 0 disables
261
262{% if postgres_max_replication_slots is defined %}
263max_replication_slots = {{ postgres_max_replication_slots }} # max number of replication slots
264{% else %}
265#max_replication_slots = 0 # max number of replication slots
266{% endif %}
267 # (change requires restart)
268#track_commit_timestamp = off # collect timestamp of transaction commit
269 # (change requires restart)
270
271# - Master Server -
272
273# These settings are ignored on a standby server.
274
275#synchronous_standby_names = '' # standby servers that provide sync rep
276 # number of sync standbys and comma-separated list of application_name
277 # from standby(s); '*' = all
278#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
279
280# - Standby Servers -
281
282# These settings are ignored on a master server.
283
284hot_standby = on # "on" allows queries during recovery
285 # (change requires restart)
286#max_standby_archive_delay = 30s # max delay before canceling queries
287 # when reading WAL from archive;
288 # -1 allows indefinite delay
289max_standby_streaming_delay = 600s # max delay before canceling queries
290 # when reading streaming WAL;
291 # -1 allows indefinite delay
292#wal_receiver_status_interval = 10s # send replies at least this often
293 # 0 disables
294#hot_standby_feedback = off # send info from standby to prevent
295 # query conflicts
296#wal_receiver_timeout = 60s # time that receiver waits for
297 # communication from master
298 # in milliseconds; 0 disables
299#wal_retrieve_retry_interval = 5s # time to wait before retrying to
300 # retrieve WAL after a failed attempt
301
302
303#------------------------------------------------------------------------------
304# QUERY TUNING
305#------------------------------------------------------------------------------
306
307# - Planner Method Configuration -
308
309#enable_bitmapscan = on
310#enable_hashagg = on
311#enable_hashjoin = on
312#enable_indexscan = on
313#enable_indexonlyscan = on
314#enable_material = on
315#enable_mergejoin = on
316#enable_nestloop = on
317#enable_seqscan = on
318#enable_sort = on
319#enable_tidscan = on
320
321# - Planner Cost Constants -
322
323#seq_page_cost = 1.0 # measured on an arbitrary scale
324{% if postgres_specific_random_page_cost is defined -%}
325# Random page cost is an arbitrary value relative to the seq_page_cost conf (default 1.0)
326# On SSD disk it is better to lower the default value from 4.0 to 1.0 for instance.
327random_page_cost = {{ postgres_specific_random_page_cost }} # same scale as above
328{% else -%}
329#random_page_cost = 4.0 # same scale as above
330{% endif %}
331#cpu_tuple_cost = 0.01 # same scale as above
332#cpu_index_tuple_cost = 0.005 # same scale as above
333#cpu_operator_cost = 0.0025 # same scale as above
334#parallel_tuple_cost = 0.1 # same scale as above
335#parallel_setup_cost = 1000.0 # same scale as above
336#min_parallel_relation_size = 8MB
337#effective_cache_size = 4GB
338effective_cache_size = {{ ansible_memtotal_mb // 2 }}MB
339
340# - Genetic Query Optimizer -
341
342#geqo = on
343#geqo_threshold = 12
344#geqo_effort = 5 # range 1-10
345#geqo_pool_size = 0 # selects default based on effort
346#geqo_generations = 0 # selects default based on effort
347#geqo_selection_bias = 2.0 # range 1.5-2.0
348#geqo_seed = 0.0 # range 0.0-1.0
349
350# - Other Planner Options -
351
352#default_statistics_target = 100 # range 1-10000
353#constraint_exclusion = partition # on, off, or partition
354#cursor_tuple_fraction = 0.1 # range 0.0-1.0
355#from_collapse_limit = 8
356#join_collapse_limit = 8 # 1 disables collapsing of explicit
357 # JOIN clauses
358#force_parallel_mode = off
359
360
361#------------------------------------------------------------------------------
362# ERROR REPORTING AND LOGGING
363#------------------------------------------------------------------------------
364
365# - Where to Log -
366
367#log_destination = 'stderr' # Valid values are combinations of
368 # stderr, csvlog, syslog, and eventlog,
369 # depending on platform. csvlog
370 # requires logging_collector to be on.
371
372# This is used when logging to stderr:
373#logging_collector = off # Enable capturing of stderr and csvlog
374 # into log files. Required to be on for
375 # csvlogs.
376 # (change requires restart)
377
378# These are only used if logging_collector is on:
379#log_directory = 'pg_log' # directory where log files are written,
380 # can be absolute or relative to PGDATA
381#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
382 # can include strftime() escapes
383#log_file_mode = 0600 # creation mode for log files,
384 # begin with 0 to use octal notation
385#log_truncate_on_rotation = off # If on, an existing log file with the
386 # same name as the new log file will be
387 # truncated rather than appended to.
388 # But such truncation only occurs on
389 # time-driven rotation, not on restarts
390 # or size-driven rotation. Default is
391 # off, meaning append to existing files
392 # in all cases.
393#log_rotation_age = 1d # Automatic rotation of logfiles will
394 # happen after that time. 0 disables.
395#log_rotation_size = 10MB # Automatic rotation of logfiles will
396 # happen after that much log output.
397 # 0 disables.
398
399# These are relevant when logging to syslog:
400#syslog_facility = 'LOCAL0'
401#syslog_ident = 'postgres'
402#syslog_sequence_numbers = on
403#syslog_split_messages = on
404
405# This is only relevant when logging to eventlog (win32):
406#event_source = 'PostgreSQL'
407
408# - When to Log -
409
410#client_min_messages = notice # values in order of decreasing detail:
411 # debug5
412 # debug4
413 # debug3
414 # debug2
415 # debug1
416 # log
417 # notice
418 # warning
419 # error
420
421#log_min_messages = warning # values in order of decreasing detail:
422 # debug5
423 # debug4
424 # debug3
425 # debug2
426 # debug1
427 # info
428 # notice
429 # warning
430 # error
431 # log
432 # fatal
433 # panic
434
435#log_min_error_statement = error # values in order of decreasing detail:
436 # debug5
437 # debug4
438 # debug3
439 # debug2
440 # debug1
441 # info
442 # notice
443 # warning
444 # error
445 # log
446 # fatal
447 # panic (effectively off)
448
449log_min_duration_statement = 10000 # -1 is disabled, 0 logs all statements
450 # and their durations, > 0 logs only
451 # statements running at least this number
452 # of milliseconds
453
454
455# - What to Log -
456
457#debug_print_parse = off
458#debug_print_rewritten = off
459#debug_print_plan = off
460#debug_pretty_print = on
461#log_checkpoints = off
462#log_connections = off
463#log_disconnections = off
464#log_duration = off
465#log_error_verbosity = default # terse, default, or verbose messages
466#log_hostname = off
467
468log_line_prefix = '{{ postgres_log_line_prefix }}'
469#log_line_prefix = '%t ' # special values:
470 # %a = application name
471 # %u = user name
472 # %d = database name
473 # %r = remote host and port
474 # %h = remote host
475 # %p = process ID
476 # %t = timestamp without milliseconds
477 # %m = timestamp with milliseconds
478 # %n = timestamp with milliseconds (as a Unix epoch)
479 # %i = command tag
480 # %e = SQL state
481 # %c = session ID
482 # %l = session line number
483 # %s = session start timestamp
484 # %v = virtual transaction ID
485 # %x = transaction ID (0 if none)
486 # %q = stop here in non-session
487 # processes
488 # %% = '%'
489 # e.g. '<%u%%%d> '
490#log_lock_waits = off # log lock waits >= deadlock_timeout
491#log_statement = 'none' # none, ddl, mod, all
492#log_replication_commands = off
493#log_temp_files = -1 # log temporary files equal or larger
494 # than the specified size in kilobytes;
495 # -1 disables, 0 logs all temp files
496log_timezone = 'UTC'
497
498
499# - Process Title -
500
501cluster_name = '{{ postgres_version }}/{{ postgres_cluster_name }}' # added to process titles if nonempty
502 # (change requires restart)
503#update_process_title = on
504
505
506#------------------------------------------------------------------------------
507# RUNTIME STATISTICS
508#------------------------------------------------------------------------------
509
510# - Query/Index Statistics Collector -
511
512#track_activities = on
513#track_counts = on
514#track_io_timing = off
515#track_functions = none # none, pl, all
516#track_activity_query_size = 1024 # (change requires restart)
517stats_temp_directory = '/var/run/postgresql/{{ postgres_version }}-{{ postgres_cluster_name }}.pg_stat_tmp'
518
519
520# - Statistics Monitoring -
521
522#log_parser_stats = off
523#log_planner_stats = off
524#log_executor_stats = off
525#log_statement_stats = off
526
527
528#------------------------------------------------------------------------------
529# AUTOVACUUM PARAMETERS
530#------------------------------------------------------------------------------
531
532#autovacuum = on # Enable autovacuum subprocess? 'on'
533 # requires track_counts to also be on.
534#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
535 # their durations, > 0 logs only
536 # actions running at least this number
537 # of milliseconds.
538#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
539 # (change requires restart)
540#autovacuum_naptime = 1min # time between autovacuum runs
541#autovacuum_vacuum_threshold = 50 # min number of row updates before
542 # vacuum
543#autovacuum_analyze_threshold = 50 # min number of row updates before
544 # analyze
545#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
546#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
547#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
548 # (change requires restart)
549#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
550 # before forced vacuum
551 # (change requires restart)
552#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
553 # autovacuum, in milliseconds;
554 # -1 means use vacuum_cost_delay
555#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
556 # autovacuum, -1 means use
557 # vacuum_cost_limit
558
559
560#------------------------------------------------------------------------------
561# CLIENT CONNECTION DEFAULTS
562#------------------------------------------------------------------------------
563
564# - Statement Behavior -
565
566#search_path = '"$user", public' # schema names
567#default_tablespace = '' # a tablespace name, '' uses the default
568#temp_tablespaces = '' # a list of tablespace names, '' uses
569 # only default tablespace
570#check_function_bodies = on
571#default_transaction_isolation = 'read committed'
572#default_transaction_read_only = off
573#default_transaction_deferrable = off
574#session_replication_role = 'origin'
575#statement_timeout = 0 # in milliseconds, 0 is disabled
576#lock_timeout = 0 # in milliseconds, 0 is disabled
577#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
578#vacuum_freeze_min_age = 50000000
579#vacuum_freeze_table_age = 150000000
580#vacuum_multixact_freeze_min_age = 5000000
581#vacuum_multixact_freeze_table_age = 150000000
582#bytea_output = 'hex' # hex, escape
583#xmlbinary = 'base64'
584#xmloption = 'content'
585#gin_fuzzy_search_limit = 0
586#gin_pending_list_limit = 4MB
587
588# - Locale and Formatting -
589
590datestyle = '{{ postgres_datestyle }}'
591#intervalstyle = 'postgres'
592#timezone = '(defaults to server environment setting)'
593{% if postgres_timezone is defined %}
594timezone = '{{ postgres_timezone }}'
595{% endif %}
596#timezone_abbreviations = 'Default' # Select the set of available time zone
597 # abbreviations. Currently, there are
598 # Default
599 # Australia (historical usage)
600 # India
601 # You can create your own file in
602 # share/timezonesets/.
603#extra_float_digits = 0 # min -15, max 3
604#client_encoding = sql_ascii # actually, defaults to database
605 # encoding
606
607# These settings are initialized by initdb, but they can be changed.
608lc_messages = '{{ postgres_locale_system }}' # locale for system error message
609 # strings
610lc_monetary = '{{ postgres_locale_formats }}' # locale for monetary formatting
611lc_numeric = '{{ postgres_locale_formats }}' # locale for number formatting
612lc_time = '{{ postgres_locale_formats }}' # locale for time formatting
613
614# default configuration for text search
615default_text_search_config = '{{ postgres_text_search_config }}'
616
617# - Other Defaults -
618
619#dynamic_library_path = '$libdir'
620#local_preload_libraries = ''
621#session_preload_libraries = ''
622
623
624#------------------------------------------------------------------------------
625# LOCK MANAGEMENT
626#------------------------------------------------------------------------------
627
628#deadlock_timeout = 1s
629#max_locks_per_transaction = 64 # min 10
630 # (change requires restart)
631# Note: Each lock table slot uses ~270 bytes of shared memory, and there are
632# max_locks_per_transaction * (max_connections + max_prepared_transactions)
633# lock table slots.
634#max_pred_locks_per_transaction = 64 # min 10
635 # (change requires restart)
636
637
638#------------------------------------------------------------------------------
639# VERSION/PLATFORM COMPATIBILITY
640#------------------------------------------------------------------------------
641
642# - Previous PostgreSQL Versions -
643
644#array_nulls = on
645#backslash_quote = safe_encoding # on, off, or safe_encoding
646#default_with_oids = off
647#escape_string_warning = on
648#lo_compat_privileges = off
649#operator_precedence_warning = off
650#quote_all_identifiers = off
651#sql_inheritance = on
652#standard_conforming_strings = on
653#synchronize_seqscans = on
654
655# - Other Platforms and Clients -
656
657#transform_null_equals = off
658
659
660#------------------------------------------------------------------------------
661# ERROR HANDLING
662#------------------------------------------------------------------------------
663
664#exit_on_error = off # terminate session on any error?
665#restart_after_crash = on # reinitialize after backend crash?
666
667
668#------------------------------------------------------------------------------
669# CONFIG FILE INCLUDES
670#------------------------------------------------------------------------------
671
672# These options allow settings to be loaded from files other than the
673# default postgresql.conf.
674
675#include_dir = 'conf.d' # include files ending in '.conf' from
676 # directory 'conf.d'
677#include_if_exists = 'exists.conf' # include file only if it exists
678#include = 'special.conf' # include file
679
680
681#------------------------------------------------------------------------------
682# CUSTOMIZED OPTIONS
683#------------------------------------------------------------------------------
684
685# Add settings for extensions here
diff --git a/templates/recovery.conf.j2 b/templates/recovery.conf.j2
new file mode 100644
index 0000000..059b234
--- /dev/null
+++ b/templates/recovery.conf.j2
@@ -0,0 +1,8 @@
1# This file is renamed to `recover.done` by postgres once the secondary is promoted
2# {{ ansible_managed }}
3
4standby_mode = 'on'
5restore_command = '/usr/bin/barman-wal-restore --user barman --parallel 8 {{ postgres_barman_server }} {{ postgres_primary.restore_directory }} %f %p'
6primary_conninfo = 'host={{ postgres_primary.host }} port={{ postgres_primary.port }} user={{ postgres_primary.replication_user }} password={{ postgres_primary.replication_password }} sslmode=require'
7trigger_file = '/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/failover.trigger'
8recovery_target_timeline='latest'
diff --git a/templates/standby-clone.sh.j2 b/templates/standby-clone.sh.j2
new file mode 100755
index 0000000..3486342
--- /dev/null
+++ b/templates/standby-clone.sh.j2
@@ -0,0 +1,44 @@
1#!/bin/bash
2# {{ ansible_managed }}
3
4BARMAN_DATABASE=$1
5BARMAN_BACKUP_VERSION=$2
6
7if [[ -z $BARMAN_DATABASE || -z $BARMAN_BACKUP_VERSION ]]
8then
9 echo "Usage : $0 BARMAN_DATABASE BARMAN_BACKUP_VERSION" >&2
10 echo " Example: $0 25kv 20161118T002405" >&2
11 exit 1
12fi
13
14BACKUP_DATE=`date +%s`
15
16echo "accept key if necessary"
17sudo -u postgres ssh barman@{{ postgres_barman_server }} echo ""
18
19echo Stopping PostgreSQL
20pg_ctlcluster {{ postgres_version }} {{ postgres_cluster_name }} stop
21
22echo Cleaning up old cluster directory
23sudo -u postgres mv /var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}{,_$BACKUP_DATE}
24
25echo Creating new directory
26sudo -u postgres mkdir -p /var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}
27
28echo Get previous backup from backups server
29sudo -u postgres \
30 time rsync --progress -pvia --exclude='*.conf' --exclude='server.crt' --exclude='server.key' --delete \
31 --password-file=/var/lib/postgresql/.rsync_pass \
32 rsync://barman@{{ postgres_barman_server }}/backups/$BARMAN_DATABASE/base/$BARMAN_BACKUP_VERSION/data/ \
33 /var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/
34
35echo Restoring .conf and server certificate
36sudo -u postgres cp -a /var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}_$BACKUP_DATE/{*.conf,server.crt,server.key} /var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/
37
38echo Ensure rights are correcly set
39chown -R postgres:postgres /var/lib/postgresql/{{ postgres_version }}/
40chmod 0700 /var/lib/postgresql/{{ postgres_version }}/
41chmod -R o-rwx /var/lib/postgresql/{{ postgres_version }}/
42
43echo Starting PostgreSQL
44sudo pg_ctlcluster {{ postgres_version }} {{ postgres_cluster_name }} start
diff --git a/test/hosts b/test/hosts
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/test/hosts
diff --git a/test/main.yml b/test/main.yml
new file mode 100644
index 0000000..19ff526
--- /dev/null
+++ b/test/main.yml
@@ -0,0 +1,154 @@
1---
2- name: Bring up docker containers
3 hosts: localhost
4 vars: &common_vars
5 postgres_version: 10
6 postgres_barman_directory: 'test-postgres-10'
7 postgres_barman_server: postgres_barman
8 barman_rsync_password: "secret_rsync"
9 barman_rsync_allowed_hosts: 172.17.0.0/24
10 postgres_allowed_hosts:
11 - user: all
12 range: 172.17.0.0/24
13 postgres_replication_hosts:
14 - user: replicator
15 range: 172.17.0.0/24
16 inventory:
17 - name: "{{ postgres_barman_server }}"
18 image: "python:3.7-stretch"
19 - name: postgres_one
20 image: "python:3.7-stretch"
21 links:
22 - "{{ postgres_barman_server }}"
23 - name: postgres_two
24 image: "python:3.7-stretch"
25 links:
26 - "{{ postgres_barman_server }}"
27 - postgres_one
28 - name: postgres_three
29 image: "python:3.7-stretch"
30 links:
31 - "{{ postgres_barman_server }}"
32 - postgres_one
33 roles:
34 - role: provision_docker
35 provision_docker_inventory: "{{inventory}}"
36 provision_docker_use_docker_connection: true
37 tasks:
38 - name: Group primary
39 add_host:
40 hostname: "{{item}}"
41 groups: primary
42 <<: *common_vars
43 postgres_clusters:
44 - version: "{{ postgres_version }}"
45 name: 'main'
46 port: 5432
47 fsync_enabled: True
48 checksums: False
49 archive_enabled: True
50 barman_directory: "{{ postgres_barman_directory }}"
51 users:
52 - username: barman
53 password: "secret_cocktail"
54 permissions: 'SUPERUSER'
55 - username: replicator
56 password: "secret_repli"
57 permissions: 'REPLICATION'
58
59 with_items:
60 - postgres_one
61 tags: provision_docker
62
63 - name: Group secondaries
64 add_host:
65 hostname: "{{item}}"
66 groups: secondary
67 <<: *common_vars
68 postgres_clusters:
69 - version: "{{ postgres_version }}"
70 name: 'main'
71 port: 5432
72 fsync_enabled: True
73 checksums: False
74 archive_enabled: True
75 barman_directory: "{{ postgres_barman_directory }}"
76 primary:
77 host: postgres_one
78 port: 5432
79 restore_directory: "{{ postgres_barman_directory }}"
80 replication_user: "replicator"
81 replication_password: "secret_repli"
82
83 with_items:
84 - postgres_two
85 - postgres_three
86 tags: provision_docker
87
88 - name: Group db-all
89 add_host:
90 hostname: "{{item}}"
91 groups: db-all
92 with_items:
93 - postgres_one
94 - postgres_two
95 - postgres_three
96 tags: provision_docker
97
98 - name: Group barman
99 add_host:
100 hostname: "{{item}}"
101 groups: barman
102 <<: *common_vars
103 barman_databases:
104 - name: "{{ postgres_barman_directory }}"
105 description: 'Main database'
106 primary_host: postgres_one
107 primary_barman_password: "secret_cocktail"
108 backup_method: rsync
109 ansible_group: db-all
110 retention_policy: 'RECOVERY WINDOW OF 7 DAYS'
111 with_items:
112 - postgres_barman
113 tags: provision_docker
114
115- name: Test installing Barman role
116 hosts: barman
117 connection: docker
118 vars: {}
119 pre_tasks: &pre_tasks
120 - name: "Build hosts file"
121 shell: 'echo "{{ hostvars[inventory_hostname].ansible_default_ipv4.address }} {{inventory_hostname}}" >> /etc/hosts'
122 when: hostvars[inventory_hostname].ansible_default_ipv4.address is defined
123
124 - debug: msg="Running on host {{inventory_hostname}} ({{ hostvars[inventory_hostname].ansible_default_ipv4.address }})"
125 roles:
126 - { role: barman, tags: ['barman'] }
127
128- name: Test installing Postgresql role
129 hosts: primary:secondary
130 connection: docker
131 vars:
132 postgres_listen_addresses: "{{ hostvars[inventory_hostname].ansible_default_ipv4.address }}"
133 pre_tasks: *pre_tasks
134 roles:
135 - { role: postgresql, tags: ['postgres'] }
136 tasks:
137 - name: Start postgres cluster
138 service: name=postgresql state=started
139
140 - name: Test connection to Postgres instances.
141 become: true
142 become_user: postgres
143 become_method: su
144 command: psql --port=5432 --command='select NOW()'
145
146
147- name: Test running Postgresql role when database is started
148 connection: docker
149 hosts: primary:secondary
150 vars:
151 postgres_listen_addresses: "{{ hostvars[inventory_hostname].ansible_default_ipv4.address }}"
152 pre_tasks: *pre_tasks
153 roles:
154 - { role: postgresql, tags: ['postgres'] }
diff --git a/test/requirements.txt b/test/requirements.txt
new file mode 100644
index 0000000..d2cbb1a
--- /dev/null
+++ b/test/requirements.txt
@@ -0,0 +1 @@
docker-py
diff --git a/test/requirements.yml b/test/requirements.yml
new file mode 100644
index 0000000..1b1155c
--- /dev/null
+++ b/test/requirements.yml
@@ -0,0 +1,3 @@
1- src: chrismeyersfsu.provision_docker
2 name: provision_docker
3 version: 7a6243dfcf69f9d262877eca487bc14b62ba6960
diff --git a/test/roles/postgresql b/test/roles/postgresql
new file mode 120000
index 0000000..f47fac4
--- /dev/null
+++ b/test/roles/postgresql
@@ -0,0 +1 @@
../../../postgresql \ No newline at end of file
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-21 12:04:10 +0000