aboutsummaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
authorPaul B <paul@bonaud.fr>2020-05-06 13:51:59 +0200
committerPaul B <paul@bonaud.fr>2020-05-06 19:36:24 +0200
commit8d1689150f638c6330e321eba7baf3f97678216a (patch)
tree00dbf539552e176496e4029c42b172667086b7e3
parentb9038ad7484a472f1e745906fb0d26530061b58d (diff)
downloadansible-postgresql-role-8d1689150f638c6330e321eba7baf3f97678216a.tar.gz
ansible-postgresql-role-8d1689150f638c6330e321eba7baf3f97678216a.tar.zst
ansible-postgresql-role-8d1689150f638c6330e321eba7baf3f97678216a.zip
core: Add compatibility with PG 12 major version
This PR adds compatibility for postgresql 12 major version. The PG12 configuration is as close as the PG11 one. All defaults have been kept back to avoid breaking backward compatibility within this ansible role. Here is a complete side-by-side diff between the current PG11 conf and the new PG12 conf: ```diff # 0 selects the syste # 0 selects the syste > #tcp_user_timeout = 0 # TCP_USER_TIMEOUT, i > # 0 selects the syste > #ssl_min_protocol_version = 'TLSv1' > #ssl_max_protocol_version = '' > #shared_memory_type = mmap # the default is the > # supported by the op > # mmap > # sysv > # windows > # (change requires re dynamic_shared_memory_type = posix # the default is the dynamic_shared_memory_type = posix # the default is the # supported by the op # supported by the op # posix # posix # sysv # sysv # windows # windows # mmap # mmap # use none to disable < # (change requires re # (change requires re wal_level = logical # minimal, replica, o | wal_level = {{ postgres_wal_level }} # min # (change requires re # (change requires re wal_log_hints = on # also do full page w wal_log_hints = on # also do full page w # (change requires re # (change requires re > #wal_init_zero = on # zero-fill new WAL f > #wal_recycle = on # recycle WAL files # (change requires re # (change requires re > # - Archive Recovery - > > # These are only used in recovery mode. > > {% if postgres_primary %} > {# In PG < 12 versions all the recovery settings were in a se > restore_command = '/usr/bin/barman-wal-restore --user barman > # placeholders: %p = path of > # %f = file nam > # e.g. 'cp /mnt/server/archiv > # (change requires restart) > {% else %} > #restore_command = '' # command to use to restore a > # placeholders: %p = path of > # %f = file nam > # e.g. 'cp /mnt/server/archiv > # (change requires restart) > {% endif %} > #archive_cleanup_command = '' # command to execute at every > #recovery_end_command = '' # command to execute at compl > > # - Recovery Target - > > # Set these only when performing a targeted recovery. > > #recovery_target = '' # 'immediate' to end recovery > # consistent state is reached > # (change requires restart) > #recovery_target_name = '' # the named restore point to > # (change requires restart) > #recovery_target_time = '' # the time stamp up to which > # (change requires restart) > #recovery_target_xid = '' # the transaction ID up to wh > # (change requires restart) > #recovery_target_lsn = '' # the WAL LSN up to which rec > # (change requires restart) > #recovery_target_inclusive = on # Specifies whether to stop: > # just after the specified re > # just before the recovery ta > # (change requires restart) > {% if postgres_primary %} > {# In PG < 12 versions all the recovery settings were in a se > recovery_target_timeline='latest' # 'current', 'latest' > # (change requires restart) > {% else %} > #recovery_target_timeline = 'latest' # 'current', 'latest' > # (change requires restart) > {% endif %} > #recovery_target_action = 'pause' # 'pause', 'promote', > # (change requires restart) max_wal_senders = 10 # max number of walsender pro | #max_wal_senders = 10 # max number of walsender pro # (change requires restart) # (change requires restart) > {% if postgres_primary %} > {# In PG < 12 versions this setting was defined in separate r > primary_conninfo = 'host={{ postgres_primary.host }} port={{ > # (change requires re > {% else %} > #primary_conninfo = '' # connection string t > # (change requires re > {% endif %} > #primary_slot_name = '' # replication slot on > # (change requires re > {% if postgres_primary %} > {# In PG < 12 versions this setting was defined in separate r > promote_trigger_file = '/var/lib/postgresql/{{ postgres_versi > {% else %} > #promote_trigger_file = '' # file name whose pre > {% endif %} # retrieve WAL after # retrieve WAL after > #recovery_min_apply_delay = 0 # minimum delay for a > #plan_cache_mode = auto # auto, force_generic > # force_custom_plan > # debug5 < # debug4 < # debug3 < # debug2 < # debug1 < # log < # notice < # warning < # error < < log_min_duration_statement = 10000 # -1 is disabled, 0 l log_min_duration_statement = 10000 # -1 is disabled, 0 l # and their durations # and their durations # statements running # statements running # of milliseconds # of milliseconds > #log_transaction_sample_rate = 0.0 # Fraction of transac > # are logged regardle > # statements from all > #client_min_messages = notice # values in order of > # debug5 > # debug4 > # debug3 > # debug2 > # debug1 > # log > # notice > # warning > # error # only default tables # only default tables > #default_table_access_method = 'heap' > # selects precise out > #data_sync_retry = off # retry or panic on f > # data? > # (change requires re > # assignments, so they can usefully be given more than once. ```
Diffstat
-rw-r--r--README.md10
-rw-r--r--tasks/postgres-cluster.yml13
-rw-r--r--templates/postgresql.12.conf.j2809
3 files changed, 825 insertions, 7 deletions
diff --git a/README.md b/README.md
index 050fb65..3ab4153 100644
--- a/README.md
+++ b/README.md
@@ -6,7 +6,7 @@ See [role page on Ansible Galaxy](https://galaxy.ansible.com/trainline-eu/ansibl
6 6
7#### Installation 7#### Installation
8 8
9This role has been tested on Ansible 2.3.0 and higher. 9This role has been tested on Ansible 2.5.0 and higher.
10 10
11To install: 11To install:
12 12
@@ -25,10 +25,10 @@ Recommended dependencies:
25 25
26This table lists the tested version of OS/PostgreSQL couples. 26This table lists the tested version of OS/PostgreSQL couples.
27 27
28| Distribution / PostgreSQL | 9.1 | 9.4 | 9.5 | 9.6 | 10 | 11 | 28| Distribution / PostgreSQL | 9.1 | 9.4 | 9.5 | 9.6 | 10 | 11 | 12 |
29| ------------------------- |:---:|:---:|:---:|:---:|:---:|:---:| 29| ------------------------- | :---: | :---: | :---: | :---: | :---: | :---: | :---: |
30| Debian 8.x | :no_entry: :white_check_mark:| :white_check_mark:| :white_check_mark:| :white_check_mark:| :white_check_mark:| :white_check_mark:| 30| Debian 8.x | :no_entry: :white_check_mark: | :no_entry: :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: |
31| Debian 9.x | :no_entry: :white_check_mark:| :white_check_mark:| :white_check_mark:| :white_check_mark:| :white_check_mark:| :white_check_mark:| 31| Debian 9.x | :no_entry: :white_check_mark: | :no_entry: :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: | :white_check_mark: |
32 32
33- :white_check_mark: - tested, works fine 33- :white_check_mark: - tested, works fine
34- :grey_question: - will work in the future (help out if you can) 34- :grey_question: - will work in the future (help out if you can)
diff --git a/tasks/postgres-cluster.yml b/tasks/postgres-cluster.yml
index c052982..a8e96c1 100644
--- a/tasks/postgres-cluster.yml
+++ b/tasks/postgres-cluster.yml
@@ -5,6 +5,7 @@
5 postgres_port: "{{ postgres_cluster.port }}" 5 postgres_port: "{{ postgres_cluster.port }}"
6 postgres_fsync_enabled: "{{ postgres_cluster.fsync_enabled }}" 6 postgres_fsync_enabled: "{{ postgres_cluster.fsync_enabled }}"
7 postgres_archive_enabled: "{{ postgres_cluster.archive_enabled }}" 7 postgres_archive_enabled: "{{ postgres_cluster.archive_enabled }}"
8 postgres_wal_level: "{{ postgres_cluster.wal_level|default('logical') }}"
8 postgres_max_replication_slots: "{{ postgres_cluster.max_replication_slots | default(10) }}" 9 postgres_max_replication_slots: "{{ postgres_cluster.max_replication_slots | default(10) }}"
9 postgres_extra_config: "{{ postgres_cluster.extra_config | default({}) }}" 10 postgres_extra_config: "{{ postgres_cluster.extra_config | default({}) }}"
10 barman_directory: "{{ postgres_cluster.barman_directory | default(None) }}" 11 barman_directory: "{{ postgres_cluster.barman_directory | default(None) }}"
@@ -19,7 +20,7 @@
19 apt: name=postgresql-{{ postgres_version }}-repack 20 apt: name=postgresql-{{ postgres_version }}-repack
20 when: 21 when:
21 - ansible_distribution_release != 'NA' 22 - ansible_distribution_release != 'NA'
22 - postgres_version in ['9.3', '9.4', '9.5', '9.6', '10', '11'] 23 - postgres_version in ['9.3', '9.4', '9.5', '9.6', '10', '11', '12']
23 24
24- name: Set initdb options 25- name: Set initdb options
25 set_fact: postgres_initdb_option="{% if postgres_checksums %}--data-checksums{% endif %}" 26 set_fact: postgres_initdb_option="{% if postgres_checksums %}--data-checksums{% endif %}"
@@ -82,9 +83,17 @@
82 83
83- name: Upload recovery.conf 84- name: Upload recovery.conf
84 template: src=recovery.conf.j2 dest=/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/recovery.conf owner=postgres group=postgres mode=0600 85 template: src=recovery.conf.j2 dest=/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/recovery.conf owner=postgres group=postgres mode=0600
85 when: postgres_primary 86 when: postgres_primary and postgres_version is version('12', '<')
86 no_log: True 87 no_log: True
87 88
89- name: Define secondary nodes as secondaries with corresponding signal files
90 file:
91 path: "{{ item }}"
92 state: touch
93 when: postgres_primary and postgres_version is version('12', '>=')
94 with_items:
95 - /var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/standby.signal
96
88- name: Create log file 97- name: Create log file
89 file: 98 file:
90 path={{ postgres_log_dir }}/postgresql-{{ postgres_version }}-{{ postgres_cluster_name }}.log 99 path={{ postgres_log_dir }}/postgresql-{{ postgres_version }}-{{ postgres_cluster_name }}.log
diff --git a/templates/postgresql.12.conf.j2 b/templates/postgresql.12.conf.j2
new file mode 100644
index 0000000..b49b836
--- /dev/null
+++ b/templates/postgresql.12.conf.j2
@@ -0,0 +1,809 @@
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#superuser_reserved_connections = 3 # (change requires restart)
67unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
68 # (change requires restart)
69#unix_socket_group = '' # (change requires restart)
70#unix_socket_permissions = 0777 # begin with 0 to use octal notation
71 # (change requires restart)
72#bonjour = off # advertise server via Bonjour
73 # (change requires restart)
74#bonjour_name = '' # defaults to the computer name
75 # (change requires restart)
76
77# - TCP settings -
78# see "man 7 tcp" for details
79
80#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
81 # 0 selects the system default
82#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
83 # 0 selects the system default
84#tcp_keepalives_count = 0 # TCP_KEEPCNT;
85 # 0 selects the system default
86#tcp_user_timeout = 0 # TCP_USER_TIMEOUT, in milliseconds;
87 # 0 selects the system default
88
89# - Authentication -
90
91#authentication_timeout = 1min # 1s-600s
92#password_encryption = md5 # md5 or scram-sha-256
93#db_user_namespace = off
94
95# GSSAPI using Kerberos
96#krb_server_keyfile = ''
97#krb_caseins_users = off
98
99# - SSL -
100
101ssl = on
102#ssl_ca_file = ''
103#ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
104#ssl_crl_file = ''
105#ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
106#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
107#ssl_prefer_server_ciphers = on
108#ssl_ecdh_curve = 'prime256v1'
109#ssl_min_protocol_version = 'TLSv1'
110#ssl_max_protocol_version = ''
111#ssl_dh_params_file = ''
112#ssl_passphrase_command = ''
113#ssl_passphrase_command_supports_reload = off
114
115
116#------------------------------------------------------------------------------
117# RESOURCE USAGE (except WAL)
118#------------------------------------------------------------------------------
119
120# - Memory -
121
122{% if ansible_memtotal_mb > 65536 %}
123shared_buffers = 16GB # min 128kB
124{% else %}
125shared_buffers = {{ ansible_memtotal_mb // 4 }}MB # min 128kB
126{% endif %}
127 # (change requires restart)
128#huge_pages = try # on, off, or try
129 # (change requires restart)
130temp_buffers = 24MB # min 800kB
131#max_prepared_transactions = 0 # zero disables the feature
132 # (change requires restart)
133# Caution: it is not advisable to set max_prepared_transactions nonzero unless
134# you actively intend to use prepared transactions.
135work_mem = 512MB # min 64kB
136maintenance_work_mem = 1024MB # min 1MB
137#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
138#max_stack_depth = 2MB # min 100kB
139#shared_memory_type = mmap # the default is the first option
140 # supported by the operating system:
141 # mmap
142 # sysv
143 # windows
144 # (change requires restart)
145dynamic_shared_memory_type = posix # the default is the first option
146 # supported by the operating system:
147 # posix
148 # sysv
149 # windows
150 # mmap
151 # (change requires restart)
152
153# - Disk -
154
155#temp_file_limit = -1 # limits per-process temp file space
156 # in kB, or -1 for no limit
157
158# - Kernel Resources -
159
160#max_files_per_process = 1000 # min 25
161 # (change requires restart)
162
163# - Cost-Based Vacuum Delay -
164
165#vacuum_cost_delay = 0 # 0-100 milliseconds (0 disables)
166#vacuum_cost_page_hit = 1 # 0-10000 credits
167#vacuum_cost_page_miss = 10 # 0-10000 credits
168#vacuum_cost_page_dirty = 20 # 0-10000 credits
169#vacuum_cost_limit = 200 # 1-10000 credits
170
171# - Background Writer -
172
173bgwriter_delay = 2000ms # 10-10000ms between rounds
174#bgwriter_lru_maxpages = 100 # max buffers written/round, 0 disables
175#bgwriter_lru_multiplier = 2.0 # 0-10.0 multiplier on buffers scanned/round
176#bgwriter_flush_after = 512kB # measured in pages, 0 disables
177
178# - Asynchronous Behavior -
179
180#effective_io_concurrency = 1 # 1-1000; 0 disables prefetching
181#max_worker_processes = 8 # (change requires restart)
182#max_parallel_maintenance_workers = 2 # taken from max_parallel_workers
183#max_parallel_workers_per_gather = 2 # taken from max_parallel_workers
184#parallel_leader_participation = on
185#max_parallel_workers = 8 # maximum number of max_worker_processes that
186 # can be used in parallel operations
187#old_snapshot_threshold = -1 # 1min-60d; -1 disables; 0 is immediate
188 # (change requires restart)
189#backend_flush_after = 0 # measured in pages, 0 disables
190
191
192#------------------------------------------------------------------------------
193# WRITE-AHEAD LOG
194#------------------------------------------------------------------------------
195
196# - Settings -
197
198wal_level = {{ postgres_wal_level }} # minimal, replica, or logical
199 # (change requires restart)
200{% if postgres_fsync_enabled %}
201#fsync = on # flush data to disk for crash safety
202 # (turning this off can cause
203 # unrecoverable data corruption)
204#synchronous_commit = on # synchronization level;
205 # off, local, remote_write, remote_apply, or on
206{% else %}
207fsync = off # turns forced synchronization on or off
208synchronous_commit = off # synchronization level;
209{% endif %}
210#wal_sync_method = fsync # the default is the first option
211 # supported by the operating system:
212 # open_datasync
213 # fdatasync (default on Linux)
214 # fsync
215 # fsync_writethrough
216 # open_sync
217#full_page_writes = on # recover from partial page writes
218#wal_compression = off # enable compression of full-page writes
219wal_log_hints = on # also do full page writes of non-critical updates
220 # (change requires restart)
221#wal_init_zero = on # zero-fill new WAL files
222#wal_recycle = on # recycle WAL files
223#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
224 # (change requires restart)
225#wal_writer_delay = 200ms # 1-10000 milliseconds
226#wal_writer_flush_after = 1MB # measured in pages, 0 disables
227
228#commit_delay = 0 # range 0-100000, in microseconds
229#commit_siblings = 5 # range 1-1000
230
231# - Checkpoints -
232
233#checkpoint_timeout = 5min # range 30s-1d
234max_wal_size = 1GB
235min_wal_size = 80MB
236#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
237#checkpoint_flush_after = 256kB # measured in pages, 0 disables
238#checkpoint_warning = 30s # 0 disables
239
240# - Archiving -
241
242{% if postgres_archive_enabled %}
243archive_mode = on
244archive_command = 'rsync -a %p barman@{{ postgres_barman_server }}:/var/lib/barman/{{ barman_directory }}/incoming/%f'
245{% else %}
246archive_mode = off
247archive_command = ''
248{% endif %}
249
250#archive_mode = off # enables archiving; off, on, or always
251 # (change requires restart)
252#archive_command = '' # command to use to archive a logfile segment
253 # placeholders: %p = path of file to archive
254 # %f = file name only
255 # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
256#archive_timeout = 0 # force a logfile segment switch after this
257 # number of seconds; 0 disables
258
259# - Archive Recovery -
260
261# These are only used in recovery mode.
262
263{% if postgres_primary %}
264{# In PG < 12 versions all the recovery settings were in a separate recovery.conf file #}
265restore_command = '/usr/bin/barman-wal-restore --user barman --parallel 8 {{ postgres_barman_server }} {{ postgres_primary.restore_directory }} %f %p' # command to use to restore an archived logfile segment
266 # placeholders: %p = path of file to restore
267 # %f = file name only
268 # e.g. 'cp /mnt/server/archivedir/%f %p'
269 # (change requires restart)
270{% else %}
271#restore_command = '' # command to use to restore an archived logfile segment
272 # placeholders: %p = path of file to restore
273 # %f = file name only
274 # e.g. 'cp /mnt/server/archivedir/%f %p'
275 # (change requires restart)
276{% endif %}
277#archive_cleanup_command = '' # command to execute at every restartpoint
278#recovery_end_command = '' # command to execute at completion of recovery
279
280# - Recovery Target -
281
282# Set these only when performing a targeted recovery.
283
284#recovery_target = '' # 'immediate' to end recovery as soon as a
285 # consistent state is reached
286 # (change requires restart)
287#recovery_target_name = '' # the named restore point to which recovery will proceed
288 # (change requires restart)
289#recovery_target_time = '' # the time stamp up to which recovery will proceed
290 # (change requires restart)
291#recovery_target_xid = '' # the transaction ID up to which recovery will proceed
292 # (change requires restart)
293#recovery_target_lsn = '' # the WAL LSN up to which recovery will proceed
294 # (change requires restart)
295#recovery_target_inclusive = on # Specifies whether to stop:
296 # just after the specified recovery target (on)
297 # just before the recovery target (off)
298 # (change requires restart)
299{% if postgres_primary %}
300{# In PG < 12 versions all the recovery settings were in a separate recovery.conf file #}
301recovery_target_timeline='latest' # 'current', 'latest', or timeline ID
302 # (change requires restart)
303{% else %}
304#recovery_target_timeline = 'latest' # 'current', 'latest', or timeline ID
305 # (change requires restart)
306{% endif %}
307#recovery_target_action = 'pause' # 'pause', 'promote', 'shutdown'
308 # (change requires restart)
309
310#------------------------------------------------------------------------------
311# REPLICATION
312#------------------------------------------------------------------------------
313
314# - Sending Servers -
315
316# Set these on the master and on any standby that will send replication data.
317
318#max_wal_senders = 10 # max number of walsender processes
319 # (change requires restart)
320wal_keep_segments = 16 # in logfile segments; 0 disables
321#wal_sender_timeout = 60s # in milliseconds; 0 disables
322
323{% if postgres_max_replication_slots is defined %}
324max_replication_slots = {{ postgres_max_replication_slots }} # max number of replication slots
325{% else %}
326#max_replication_slots = 10 # max number of replication slots
327{% endif %}
328 # (change requires restart)
329#track_commit_timestamp = off # collect timestamp of transaction commit
330 # (change requires restart)
331
332# - Master Server -
333
334# These settings are ignored on a standby server.
335
336#synchronous_standby_names = '' # standby servers that provide sync rep
337 # method to choose sync standbys, number of sync standbys,
338 # and comma-separated list of application_name
339 # from standby(s); '*' = all
340#vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is delayed
341
342# - Standby Servers -
343
344# These settings are ignored on a master server.
345
346{% if postgres_primary %}
347{# In PG < 12 versions this setting was defined in separate recovery.conf file #}
348primary_conninfo = 'host={{ postgres_primary.host }} port={{ postgres_primary.port }} user={{ postgres_primary.replication_user }} password={{ postgres_primary.replication_password }} sslmode=require' # connection string to sending server
349 # (change requires restart)
350{% else %}
351#primary_conninfo = '' # connection string to sending server
352 # (change requires restart)
353{% endif %}
354#primary_slot_name = '' # replication slot on sending server
355 # (change requires restart)
356{% if postgres_primary %}
357{# In PG < 12 versions this setting was defined in separate recovery.conf file #}
358promote_trigger_file = '/var/lib/postgresql/{{ postgres_version }}/{{ postgres_cluster_name }}/failover.trigger' # file name whose presence ends recovery
359{% else %}
360#promote_trigger_file = '' # file name whose presence ends recovery
361{% endif %}
362hot_standby = on # "off" disallows queries during recovery
363 # (change requires restart)
364#max_standby_archive_delay = 30s # max delay before canceling queries
365 # when reading WAL from archive;
366 # -1 allows indefinite delay
367max_standby_streaming_delay = {{ postgres_extra_config.max_standby_streaming_delay | default('600s') }} # max delay before canceling queries
368 # when reading streaming WAL;
369 # -1 allows indefinite delay
370#wal_receiver_status_interval = 10s # send replies at least this often
371 # 0 disables
372#hot_standby_feedback = off # send info from standby to prevent
373 # query conflicts
374#wal_receiver_timeout = 60s # time that receiver waits for
375 # communication from master
376 # in milliseconds; 0 disables
377#wal_retrieve_retry_interval = 5s # time to wait before retrying to
378 # retrieve WAL after a failed attempt
379#recovery_min_apply_delay = 0 # minimum delay for applying changes during recovery
380
381# - Subscribers -
382
383# These settings are ignored on a publisher.
384
385#max_logical_replication_workers = 4 # taken from max_worker_processes
386 # (change requires restart)
387#max_sync_workers_per_subscription = 2 # taken from max_logical_replication_workers
388
389
390#------------------------------------------------------------------------------
391# QUERY TUNING
392#------------------------------------------------------------------------------
393
394# - Planner Method Configuration -
395
396#enable_bitmapscan = on
397#enable_hashagg = on
398#enable_hashjoin = on
399#enable_indexscan = on
400#enable_indexonlyscan = on
401#enable_material = on
402#enable_mergejoin = on
403#enable_nestloop = on
404#enable_parallel_append = on
405#enable_seqscan = on
406#enable_sort = on
407#enable_tidscan = on
408#enable_partitionwise_join = off
409#enable_partitionwise_aggregate = off
410#enable_parallel_hash = on
411#enable_partition_pruning = on
412
413# - Planner Cost Constants -
414
415#seq_page_cost = 1.0 # measured on an arbitrary scale
416{% if postgres_specific_random_page_cost is defined -%}
417# Random page cost is an arbitrary value relative to the seq_page_cost conf (default 1.0)
418# On SSD disk it is better to lower the default value from 4.0 to 1.0 for instance.
419random_page_cost = {{ postgres_specific_random_page_cost }} # same scale as above
420{% else -%}
421#random_page_cost = 4.0 # same scale as above
422{% endif %}
423#cpu_tuple_cost = 0.01 # same scale as above
424#cpu_index_tuple_cost = 0.005 # same scale as above
425#cpu_operator_cost = 0.0025 # same scale as above
426#parallel_tuple_cost = 0.1 # same scale as above
427#parallel_setup_cost = 1000.0 # same scale as above
428
429#jit_above_cost = 100000 # perform JIT compilation if available
430 # and query more expensive than this;
431 # -1 disables
432#jit_inline_above_cost = 500000 # inline small functions if query is
433 # more expensive than this; -1 disables
434#jit_optimize_above_cost = 500000 # use expensive JIT optimizations if
435 # query is more expensive than this;
436 # -1 disables
437
438#min_parallel_table_scan_size = 8MB
439#min_parallel_index_scan_size = 512kB
440#effective_cache_size = 4GB
441effective_cache_size = {{ ansible_memtotal_mb // 2 }}MB
442
443# - Genetic Query Optimizer -
444
445#geqo = on
446#geqo_threshold = 12
447#geqo_effort = 5 # range 1-10
448#geqo_pool_size = 0 # selects default based on effort
449#geqo_generations = 0 # selects default based on effort
450#geqo_selection_bias = 2.0 # range 1.5-2.0
451#geqo_seed = 0.0 # range 0.0-1.0
452
453# - Other Planner Options -
454
455#default_statistics_target = 100 # range 1-10000
456#constraint_exclusion = partition # on, off, or partition
457#cursor_tuple_fraction = 0.1 # range 0.0-1.0
458#from_collapse_limit = 8
459#join_collapse_limit = 8 # 1 disables collapsing of explicit
460 # JOIN clauses
461#force_parallel_mode = off
462#jit = on # allow JIT compilation
463#plan_cache_mode = auto # auto, force_generic_plan or
464 # force_custom_plan
465
466
467#------------------------------------------------------------------------------
468# REPORTING AND LOGGING
469#------------------------------------------------------------------------------
470
471# - Where to Log -
472
473#log_destination = 'stderr' # Valid values are combinations of
474 # stderr, csvlog, syslog, and eventlog,
475 # depending on platform. csvlog
476 # requires logging_collector to be on.
477
478# This is used when logging to stderr:
479#logging_collector = off # Enable capturing of stderr and csvlog
480 # into log files. Required to be on for
481 # csvlogs.
482 # (change requires restart)
483
484# These are only used if logging_collector is on:
485#log_directory = 'log' # directory where log files are written,
486 # can be absolute or relative to PGDATA
487#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
488 # can include strftime() escapes
489#log_file_mode = 0600 # creation mode for log files,
490 # begin with 0 to use octal notation
491#log_truncate_on_rotation = off # If on, an existing log file with the
492 # same name as the new log file will be
493 # truncated rather than appended to.
494 # But such truncation only occurs on
495 # time-driven rotation, not on restarts
496 # or size-driven rotation. Default is
497 # off, meaning append to existing files
498 # in all cases.
499#log_rotation_age = 1d # Automatic rotation of logfiles will
500 # happen after that time. 0 disables.
501#log_rotation_size = 10MB # Automatic rotation of logfiles will
502 # happen after that much log output.
503 # 0 disables.
504
505# These are relevant when logging to syslog:
506#syslog_facility = 'LOCAL0'
507#syslog_ident = 'postgres'
508#syslog_sequence_numbers = on
509#syslog_split_messages = on
510
511# This is only relevant when logging to eventlog (win32):
512# (change requires restart)
513#event_source = 'PostgreSQL'
514
515# - When to Log -
516
517#log_min_messages = warning # values in order of decreasing detail:
518 # debug5
519 # debug4
520 # debug3
521 # debug2
522 # debug1
523 # info
524 # notice
525 # warning
526 # error
527 # log
528 # fatal
529 # panic
530
531#log_min_error_statement = error # values in order of decreasing detail:
532 # debug5
533 # debug4
534 # debug3
535 # debug2
536 # debug1
537 # info
538 # notice
539 # warning
540 # error
541 # log
542 # fatal
543 # panic (effectively off)
544
545log_min_duration_statement = 10000 # -1 is disabled, 0 logs all statements
546 # and their durations, > 0 logs only
547 # statements running at least this number
548 # of milliseconds
549
550#log_transaction_sample_rate = 0.0 # Fraction of transactions whose statements
551 # are logged regardless of their duration. 1.0 logs all
552 # statements from all transactions, 0.0 never logs.
553
554# - What to Log -
555
556#debug_print_parse = off
557#debug_print_rewritten = off
558#debug_print_plan = off
559#debug_pretty_print = on
560#log_checkpoints = off
561#log_connections = off
562#log_disconnections = off
563#log_duration = off
564#log_error_verbosity = default # terse, default, or verbose messages
565#log_hostname = off
566
567log_line_prefix = '{{ postgres_log_line_prefix }}'
568#log_line_prefix = '%m [%p] %q%u@%d ' # special values:
569 # %a = application name
570 # %u = user name
571 # %d = database name
572 # %r = remote host and port
573 # %h = remote host
574 # %p = process ID
575 # %t = timestamp without milliseconds
576 # %m = timestamp with milliseconds
577 # %n = timestamp with milliseconds (as a Unix epoch)
578 # %i = command tag
579 # %e = SQL state
580 # %c = session ID
581 # %l = session line number
582 # %s = session start timestamp
583 # %v = virtual transaction ID
584 # %x = transaction ID (0 if none)
585 # %q = stop here in non-session
586 # processes
587 # %% = '%'
588 # e.g. '<%u%%%d> '
589#log_lock_waits = off # log lock waits >= deadlock_timeout
590#log_statement = 'none' # none, ddl, mod, all
591#log_replication_commands = off
592#log_temp_files = -1 # log temporary files equal or larger
593 # than the specified size in kilobytes;
594 # -1 disables, 0 logs all temp files
595log_timezone = 'UTC'
596
597#------------------------------------------------------------------------------
598# PROCESS TITLE
599#------------------------------------------------------------------------------
600
601cluster_name = '{{ postgres_version }}/{{ postgres_cluster_name }}' # added to process titles if nonempty
602 # (change requires restart)
603#update_process_title = on
604
605
606#------------------------------------------------------------------------------
607# STATISTICS
608#------------------------------------------------------------------------------
609
610# - Query and Index Statistics Collector -
611
612#track_activities = on
613#track_counts = on
614#track_io_timing = off
615#track_functions = none # none, pl, all
616#track_activity_query_size = 1024 # (change requires restart)
617stats_temp_directory = '/var/run/postgresql/{{ postgres_version }}-{{ postgres_cluster_name }}.pg_stat_tmp'
618
619
620# - Monitoring -
621
622#log_parser_stats = off
623#log_planner_stats = off
624#log_executor_stats = off
625#log_statement_stats = off
626
627
628#------------------------------------------------------------------------------
629# AUTOVACUUM
630#------------------------------------------------------------------------------
631
632#autovacuum = on # Enable autovacuum subprocess? 'on'
633 # requires track_counts to also be on.
634#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
635 # their durations, > 0 logs only
636 # actions running at least this number
637 # of milliseconds.
638#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
639 # (change requires restart)
640#autovacuum_naptime = 1min # time between autovacuum runs
641#autovacuum_vacuum_threshold = 50 # min number of row updates before
642 # vacuum
643#autovacuum_analyze_threshold = 50 # min number of row updates before
644 # analyze
645#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
646#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
647#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
648 # (change requires restart)
649#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
650 # before forced vacuum
651 # (change requires restart)
652#autovacuum_vacuum_cost_delay = 2ms # default vacuum cost delay for
653 # autovacuum, in milliseconds;
654 # -1 means use vacuum_cost_delay
655#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
656 # autovacuum, -1 means use
657 # vacuum_cost_limit
658
659
660#------------------------------------------------------------------------------
661# CLIENT CONNECTION DEFAULTS
662#------------------------------------------------------------------------------
663
664# - Statement Behavior -
665
666#client_min_messages = notice # values in order of decreasing detail:
667 # debug5
668 # debug4
669 # debug3
670 # debug2
671 # debug1
672 # log
673 # notice
674 # warning
675 # error
676#search_path = '"$user", public' # schema names
677#row_security = on
678#default_tablespace = '' # a tablespace name, '' uses the default
679#temp_tablespaces = '' # a list of tablespace names, '' uses
680 # only default tablespace
681#default_table_access_method = 'heap'
682#check_function_bodies = on
683#default_transaction_isolation = 'read committed'
684#default_transaction_read_only = off
685#default_transaction_deferrable = off
686#session_replication_role = 'origin'
687#statement_timeout = 0 # in milliseconds, 0 is disabled
688#lock_timeout = 0 # in milliseconds, 0 is disabled
689#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
690#vacuum_freeze_min_age = 50000000
691#vacuum_freeze_table_age = 150000000
692#vacuum_multixact_freeze_min_age = 5000000
693#vacuum_multixact_freeze_table_age = 150000000
694#vacuum_cleanup_index_scale_factor = 0.1 # fraction of total number of tuples
695 # before index cleanup, 0 always performs
696 # index cleanup
697#bytea_output = 'hex' # hex, escape
698#xmlbinary = 'base64'
699#xmloption = 'content'
700#gin_fuzzy_search_limit = 0
701#gin_pending_list_limit = 4MB
702
703# - Locale and Formatting -
704
705datestyle = '{{ postgres_datestyle }}'
706#intervalstyle = 'postgres'
707#timezone = '(defaults to server environment setting)'
708{% if postgres_timezone is defined %}
709timezone = '{{ postgres_timezone }}'
710{% endif %}
711#timezone_abbreviations = 'Default' # Select the set of available time zone
712 # abbreviations. Currently, there are
713 # Default
714 # Australia (historical usage)
715 # India
716 # You can create your own file in
717 # share/timezonesets/.
718#extra_float_digits = 1 # min -15, max 3; any value >0 actually
719 # selects precise output mode
720#client_encoding = sql_ascii # actually, defaults to database
721 # encoding
722
723# These settings are initialized by initdb, but they can be changed.
724lc_messages = '{{ postgres_locale_system }}' # locale for system error message
725 # strings
726lc_monetary = '{{ postgres_locale_formats }}' # locale for monetary formatting
727lc_numeric = '{{ postgres_locale_formats }}' # locale for number formatting
728lc_time = '{{ postgres_locale_formats }}' # locale for time formatting
729
730# default configuration for text search
731default_text_search_config = '{{ postgres_text_search_config }}'
732
733# - Shared Library Preloading -
734
735#shared_preload_libraries = '' # (change requires restart)
736#local_preload_libraries = ''
737#session_preload_libraries = ''
738#jit_provider = 'llvmjit' # JIT library to use
739
740# - Other Defaults -
741
742#dynamic_library_path = '$libdir'
743
744
745#------------------------------------------------------------------------------
746# LOCK MANAGEMENT
747#------------------------------------------------------------------------------
748
749#deadlock_timeout = 1s
750#max_locks_per_transaction = 64 # min 10
751 # (change requires restart)
752#max_pred_locks_per_transaction = 64 # min 10
753 # (change requires restart)
754#max_pred_locks_per_relation = -2 # negative values mean
755 # (max_pred_locks_per_transaction
756 # / -max_pred_locks_per_relation) - 1
757#max_pred_locks_per_page = 2 # min 0
758
759
760#------------------------------------------------------------------------------
761# VERSION AND PLATFORM COMPATIBILITY
762#------------------------------------------------------------------------------
763
764# - Previous PostgreSQL Versions -
765
766#array_nulls = on
767#backslash_quote = safe_encoding # on, off, or safe_encoding
768#escape_string_warning = on
769#lo_compat_privileges = off
770#operator_precedence_warning = off
771#quote_all_identifiers = off
772#standard_conforming_strings = on
773#synchronize_seqscans = on
774
775# - Other Platforms and Clients -
776
777#transform_null_equals = off
778
779
780#------------------------------------------------------------------------------
781# ERROR HANDLING
782#------------------------------------------------------------------------------
783
784#exit_on_error = off # terminate session on any error?
785#restart_after_crash = on # reinitialize after backend crash?
786#data_sync_retry = off # retry or panic on failure to fsync
787 # data?
788 # (change requires restart)
789
790
791#------------------------------------------------------------------------------
792# CONFIG FILE INCLUDES
793#------------------------------------------------------------------------------
794
795# These options allow settings to be loaded from files other than the
796# default postgresql.conf. Note that these are directives, not variable
797# assignments, so they can usefully be given more than once.
798
799include_dir = 'conf.d' # include files ending in '.conf' from
800 # a directory, e.g., 'conf.d'
801#include_if_exists = '...' # include file only if it exists
802#include = '...' # include file
803
804
805#------------------------------------------------------------------------------
806# CUSTOMIZED OPTIONS
807#------------------------------------------------------------------------------
808
809# Add settings for extensions here
generated by cgit v1.2.3 (git 2.25.1) at 2024-11-21 10:17:41 +0000