forked from ANXS/postgresql
-
Notifications
You must be signed in to change notification settings - Fork 0
/
main.yml
839 lines (710 loc) · 36.4 KB
/
main.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
# file: postgresql/defaults/main.yml
# Basic settings
postgresql_version: 16
postgresql_version_terse: "{{ postgresql_version | replace('.', '') }}" # Short version of the postgresql_version, used in some path and filenames
postgresql_encoding: "UTF-8"
postgresql_data_checksums: false
postgresql_pwfile: ""
postgresql_locale_parts:
- "en_US" # Locale
- "UTF-8" # Encoding
postgresql_locale: "{{ postgresql_locale_parts | join('.') }}"
postgresql_ctype_parts:
- "en_US" # Locale
- "UTF-8" # Encoding
postgresql_ctype: "{{ postgresql_ctype_parts | join('.') }}"
postgresql_env:
LC_ALL: "{{ postgresql_locale }}"
LC_LCTYPE: "{{ postgresql_locale }}"
postgresql_admin_user: "postgres"
postgresql_default_auth_method: "peer"
postgresql_default_auth_method_hosts: "{{ 'scram-sha-256' if postgresql_version is version_compare('14', '>=') else 'md5' }}" # (>=14.0 scram-sha-256 is default value)
# The description that will use to customize process or service description
postgresql_service_description: "PostgreSQL RDBMS"
# The user/group that will run postgresql process or service
postgresql_service_user: "{{ postgresql_admin_user }}"
postgresql_service_user_pgsql_profile: false
postgresql_service_group: "{{ postgresql_admin_user }}"
postgresql_cluster_name: "main" # postgres default is ''
postgresql_cluster_reset: false
postgresql_database_owner: "{{ postgresql_admin_user }}"
# Extensions
postgresql_ext_install_contrib: no
postgresql_ext_install_dev_headers: no
postgresql_ext_install_postgis: no
postgresql_ext_install_extra_packages: no
# PostGIS
postgresql_postgis_release_compatibility:
10: "3.1"
12: "3.1"
13: "3.1"
14: "3.2"
15: "3.2"
16: "3.4"
postgresql_ext_postgis_version: "{{ postgresql_postgis_release_compatibility.get(postgresql_version) }}"
postgresql_ext_postgis_version_terse: "{{ postgresql_ext_postgis_version | replace('.','') }}"
postgresql_ext_postgis_deps:
- libgeos-c1
- "postgresql-{{ postgresql_version }}-postgis-{{ postgresql_ext_postgis_version }}"
- "postgresql-{{ postgresql_version }}-postgis-scripts"
# Foreign Data Wrapper(s)
postgresql_fdw_mysql: no
postgresql_fdw_ogr: no
# List of databases to be created (optional)
postgresql_databases: []
# List of database extensions to be created (optional)
postgresql_database_extensions: []
# List of users to be created (optional)
postgresql_users: []
# List of schemas to be created (optional)
postgresql_database_schemas: []
# List of user privileges to be applied (optional)
postgresql_user_privileges: []
# pg_hba.conf
postgresql_pg_hba_default:
- { type: local, database: all, user: all, address: "", method: "{{ postgresql_default_auth_method }}", comment: '"local" is for Unix domain socket connections only' }
- { type: host, database: all, user: all, address: "127.0.0.1/32", method: "{{ postgresql_default_auth_method_hosts }}", comment: "IPv4 local connections:" }
- { type: host, database: all, user: all, address: "::1/128", method: "{{ postgresql_default_auth_method_hosts }}", comment: "IPv6 local connections:" }
- { type: local, database: all, user: "{{ postgresql_admin_user }}", address: "", method: "peer map=root_as_{{ postgresql_admin_user }}", comment: "Local root Unix user, passwordless access" }
postgresql_pg_hba_md5_hosts: []
postgresql_pg_hba_passwd_hosts: []
postgresql_pg_hba_trust_hosts: []
postgresql_pg_hba_custom: []
# pg_ident.conf
postgresql_pg_ident:
- comment: "root is allowed to login as {{ postgresql_admin_user }}"
mapname: "root_as_{{ postgresql_admin_user }}"
system_username: "{{ postgresql_admin_user }}"
pg_username: "{{ postgresql_admin_user }}"
# postgresql.conf
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# Location of postgres configuration files here
postgresql_conf_directory: "/etc/postgresql/{{ postgresql_version }}/{{ postgresql_cluster_name }}"
# HBA (Host Based Authentication) file
postgresql_hba_file: "{{ postgresql_conf_directory }}/pg_hba.conf"
# Ident configuration file
postgresql_ident_file: "{{ postgresql_conf_directory }}/pg_ident.conf"
# Use data in another directory
postgresql_varlib_directory_name: "postgresql"
postgresql_data_directory: "/var/lib/{{ postgresql_varlib_directory_name }}/{{ postgresql_version }}/{{ postgresql_cluster_name }}"
postgresql_wal_directory: ""
postgresql_wal_segsize: ""
postgresql_pid_directory: "/var/run/postgresql"
# If external_pid_file is not explicitly set, on extra PID file is written
postgresql_external_pid_file: "{{ postgresql_pid_directory }}/{{ postgresql_version }}-{{ postgresql_cluster_name }}.pid"
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
postgresql_listen_addresses:
- "localhost"
postgresql_port: 5432
postgresql_max_connections: 100
postgresql_reserved_connections: 0 # (>= 16)
postgresql_superuser_reserved_connections: 3
postgresql_unix_socket_directory: "" # (<= 9.2)
postgresql_unix_socket_directories: # (>= 9.3)
- "{{ postgresql_pid_directory }}"
postgresql_unix_socket_group: ""
postgresql_unix_socket_permissions: "0777" # begin with 0 to use octal notation
postgresql_bonjour: off # advertise server via Bonjour
postgresql_bonjour_name: "" # defaults to the computer name
# - TCP settings -
# TCP Keepalives, 0 selects the system default (in seconds)
postgresql_tcp_keepalives_idle: 0
postgresql_tcp_keepalives_interval: 0
postgresql_tcp_keepalives_count: 0
postgresql_tcp_user_timeout: 0 # (>= 12)
postgresql_client_connection_check_interval: 0 # (>= 14)
# - Authentication -
postgresql_authentication_timeout: 60s # 1s-600s
postgresql_password_encryption: "{{ 'scram-sha-256' if postgresql_version is version_compare('14', '>=') else 'md5' }}" # (>=14.0 set to scram-sha-256 for best security)
posgresql_scram_iterations: 4096 # (>= 16)
postgresql_db_user_namespace: off
# GSSAPI using Kerberos
postgresql_krb_server_keyfile: "{{ 'FILE:${sysconfdir}/krb5.keytab' if postgresql_version is version_compare('14', '>=') else '' }}"
postgresql_krb_srvname: "postgres" # (<= 9.3)
postgresql_krb_caseins_users: off
postgresql_gss_accept_delegation: off # (>= 16)
# - SSL -
postgresql_ssl: off
postgresql_ssl_ciphers:
- "HIGH"
- "MEDIUM"
- "+3DES"
- "!aNULL"
postgresql_ssl_prefer_server_ciphers: on
postgresql_ssl_ecdh_curve: "prime256v1"
postgresql_ssl_min_protocol_version: "TLSv1.2" # (>= 12)
postgresql_ssl_max_protocol_version: "" # (>= 12)
postgresql_ssl_dh_params_file: "" # (>= 10)
postgresql_ssl_passphrase_command: "" # (>= 11)
postgresql_ssl_passphrase_command_supports_reload: off # (>= 11)
postgresql_ssl_renegotiation_limit: 512MB # amount of data between renegotiations
postgresql_ssl_cert_file: "/etc/ssl/certs/ssl-cert-snakeoil.pem" # (>= 9.2)
postgresql_ssl_key_file: "/etc/ssl/private/ssl-cert-snakeoil.key" # (>= 9.2)
postgresql_ssl_ca_file: "" # (>= 9.2)
postgresql_ssl_crl_file: "" # (>= 9.2)
postgresql_ssl_crl_dir: "" # (>= 14)
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
postgresql_shared_buffers: 128MB # min 128kB
postgresql_huge_pages: "try" # on, off, or try
postgresql_huge_page_size: 0 # (>= 14)
postgresql_temp_buffers: 8MB # min 800kB
postgresql_max_prepared_transactions: 0 # zero disables the feature
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
postgresql_work_mem: 4MB # min 64kB
postgresql_hash_mem_multiplier: "{{ 2.0 if postgresql_version is version_compare('15', '>=') else 1.0 }}" # (>= 13)
postgresql_maintenance_work_mem: 64MB # min 1MB
postgresql_replacement_sort_tuples: 150000 # (>= 9.6) limits use of replacement selection sort
postgresql_autovacuum_work_mem: -1 # min 1MB, or -1 to use maintenance_work_mem
postgresql_logical_decoding_work_mem: 64MB # (>= 13)
postgresql_max_stack_depth: 2MB # min 100kB
postgresql_shared_memory_type: "mmap" # (>= 12)
postgresql_dynamic_shared_memory_type: "posix" # the default is the first option
# supported by the operating system:
# posix
# sysv
# windows
# mmap
# use none to disable dynamic shared memory
postgresql_min_dynamic_shared_memory: 0MB # (>= 14) (change requires restart)
postgres_vacuum_buffer_usage_limit: 256kB # (>= 16) size of vacuum and analyze buffer access strategy ring;
# 0 to disable vacuum buffer access strategy;
# range 128kB to 16GB
# - Disk -
# limits per-process temp file space in kB, or -1 for no limit (>= 9.2)
postgresql_temp_file_limit: -1
# - Kernel Resources -
postgresql_max_files_per_process: 1000 # min 25, (>= 13) min 64
# - Cost-Based Vacuum Delay -
postgresql_vacuum_cost_delay: 0 # 0-100 milliseconds
postgresql_vacuum_cost_page_hit: 1 # 0-10000 credits
postgresql_vacuum_cost_page_miss: "{{ 2 if postgresql_version is version_compare('14', '>=') else 10 }}" # 0-10000 credits
postgresql_vacuum_cost_page_dirty: 20 # 0-10000 credits
postgresql_vacuum_cost_limit: 200 # 1-10000 credits
# - Background Writer -
postgresql_bgwriter_delay: 200ms # 10-10000ms between rounds
postgresql_bgwriter_lru_maxpages: 100 # 0-1000 max buffers written/round
postgresql_bgwriter_lru_multiplier: 2.0 # 0-10.0 multiplier on buffers scanned/round
postgresql_bgwriter_flush_after: 512kB # measured in pages, 0 disables
# - Asynchronous Behavior -
postgresql_backend_flush_after: 0 # (>= 9.6) 0 disables, default is 0
postgresql_effective_io_concurrency: 1 # 1-1000; 0 disables prefetching
postgresql_maintenance_io_concurrency: 10 # (>= 13)
postgresql_max_worker_processes: 8 # (change requires restart)
postgresql_max_parallel_workers_per_gather: 2 # (>= 9.6) taken from max_worker_processes
postgresql_max_parallel_maintenance_workers: 2 # (>= 11) taken from max_parallel_workers
postgresql_max_parallel_workers: 8 # (>= 10)
postgresql_parallel_leader_participation: on # (>= 11)
postgresql_old_snapshot_threshold: -1 # (>= 9.6) 1min-60d; -1 disables; 0 is immediate
# (change requires restart)
#------------------------------------------------------------------------------
# WRITE-AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
postgresql_wal_level: "replica" # minimal, archive (<= 9.5), hot_standby (<= 9.5), replica (>= 9.6), or logical
postgresql_fsync: on # flush data to disk for crash safety
# (turning this off can cause
# unrecoverable data corruption)
postgresql_synchronous_commit: "on"
postgresql_wal_sync_method: "fsync"
postgresql_full_page_writes: on
postgresql_wal_log_hints: off # also do full page writes of non-critical updates
postgresql_wal_compression: off # (>= 9.5)
postgresql_wal_init_zero: on # zero-fill new WAL files
postgresql_wal_recycle: on # recycle WAL files
postgresql_wal_buffers: -1 # min 32kB, -1 sets based on shared_buffers
postgresql_wal_writer_delay: 200ms # 1-10000 milliseconds
postgresql_wal_writer_flush_after: 1MB # (>= 9.6) 0 disables
postgresql_wal_skip_threshold: 2MB # (>= 13)
postgresql_commit_delay: 0 # range 0-100000, in microseconds
postgresql_commit_siblings: 5 # range 1-1000
# - Checkpoints -
postgresql_max_wal_size: 1GB # (>= 9.5)
postgresql_min_wal_size: 80MB # (>= 9.5)
postgresql_checkpoint_flush_after: 0 # (>= 9.6) 0 disables,
# default is 256kB on linux, 0 otherwise
postgresql_checkpoint_timeout: 5min # range 30s-1d
postgresql_checkpoint_completion_target: "{{ 0.9 if postgresql_version is version_compare('14', '>=') else 0.5 }}" # checkpoint target duration, 0.0 - 1.0
postgresql_checkpoint_warning: 30s # 0 disables
# - Prefetching during recovery -
postgresql_recovery_prefetch: try # (>= 15)
postgresql_wal_decode_buffer_size: 512kB # (>= 15)
# - Archiving -
postgresql_archive_mode: "off"
postgresql_archive_library: "" # (>= 15)
# Command to use to archive a logfile segment.
# Placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
postgresql_archive_command: ""
# force a logfile segment switch after this
postgresql_archive_timeout: 0
# - Archive Recovery -
# These are only used in recovery mode.
postgresql_restore_command: "" # (>= 12)
postgresql_archive_cleanup_command: "" # (>= 12)
postgresql_recovery_end_command: "" # (>= 12)
# - Recovery Target -
# Set these only when performing a targeted recovery.
postgresql_recovery_target: "" # (>= 12)
postgresql_recovery_target_name: "" # (>= 12)
postgresql_recovery_target_time: "" # (>= 12)
postgresql_recovery_target_xid: "" # (>= 12)
postgresql_recovery_target_lsn: "" # (>= 12)
postgresql_recovery_target_inclusive: "" # (>= 12)
postgresql_recovery_target_timeline: "latest" # (>= 12)
postgresql_recovery_target_action: "pause" # (>= 12)
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Sending Servers -
# Set these on the primary and on any standby that will send replication data.
# max number of walsender processes
postgresql_max_wal_senders: 10
postgresql_max_replication_slots: 0 # max number of replication slots
postgresql_wal_sender_delay: 1s # walsender cycle time, 1-10000 milliseconds (<= 9.1)
postgresql_wal_keep_segments: 0 # in logfile segments, 16MB each; 0 disables
postgresql_wal_keep_size: 0 # (>= 13)
postgresql_max_slot_wal_keep_size: -1 # (>= 13)
postgresql_replication_timeout: 60s # in milliseconds; 0 disables (<= 9.2)
postgresql_wal_sender_timeout: 60s # in milliseconds; 0 disables (>= 9.3)
postgresql_track_commit_timestamp: off # (>= 9.5)
# - Primary Server -
# These settings are ignored on a standby server.
# standby servers that provide sync rep.
# number of sync standbys (>= 9.6) and comma-separated list of application_name from standby(s)
postgresql_synchronous_standby_names: [] # '*' means 'all'
postgresql_synchronous_standby_num_sync: "" # >= 9.6 (NOTE: If you use the ANY/ALL syntax in v10, then note the new variable below)
postgresql_synchronous_standby_choose_sync: "FIRST" # >= 10
# number of xacts by which cleanup is delayed
postgresql_vacuum_defer_cleanup_age: 0 # (<= 15)
# - Standby Servers -
# These settings are ignored on a primary server.
postgresql_primary_conninfo: "" # (>= 12)
postgresql_primary_slot_name: "" # (>= 12)
postgresql_promote_trigger_file: "" # (>= 12, <= 15)
# "on" allows queries during recovery
postgresql_hot_standby: on
# max delay before canceling queries when reading WAL from archive
postgresql_max_standby_archive_delay: 30s # -1 allows indefinite delay
# max delay before canceling queries when reading streaming WAL;
postgresql_max_standby_streaming_delay: 30s # -1 allows indefinite delay
postgresql_wal_receiver_create_temp_slot: off # (>= 13)
# send replies at least this often
postgresql_wal_receiver_status_interval: 10s # 0 disables
# send info from standby to prevent query conflicts
postgresql_hot_standby_feedback: off
#time that receiver waits for communication from master in milliseconds
postgresql_wal_receiver_timeout: 60s
# time to wait before retrying to retrieve WAL after a failed attempt
postgresql_wal_retrieve_retry_interval: 5s # (>= 9.5)
postgresql_recovery_min_apply_delay: 0 # (>= 12)
# - Subscribers - (>= 10)
# These settings are ignored on a publisher.
postgresql_max_logical_replication_workers: 4 # (>= 10) taken from max_worker_processes
# (change requires restart)
postgresql_max_sync_workers_per_subscription: 2 # (>= 10) taken from max_logical_replication_workers
postgresql_max_parallel_apply_workers_per_subscription: 2 # (>= 16) taken from max_logical_replication_workers
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
postgresql_enable_async_append: on # (>= 14)
postgresql_enable_bitmapscan: on
postgresql_enable_gathermerge: on # (>= 14)
postgresql_enable_hashagg: on
postgresql_enable_hashjoin: on
postgresql_enable_incremental_sort: on # (>= 13)
postgresql_enable_indexscan: on
postgresql_enable_indexonlyscan: on
postgresql_enable_material: on
postgresql_enable_memoize: on # (>= 14)
postgresql_enable_mergejoin: on
postgresql_enable_nestloop: on
postgresql_enable_parallel_append: on # (>= 11)
postgresql_enable_parallel_hash: on # (>= 11)
postgresql_enable_partition_pruning: on # (>= 11)
postgresql_enable_partitionwise_join: off # (>= 11)
postgresql_enable_partitionwise_aggregate: off # (>= 11)
postgres_enable_presorted_aggregate: on # (>= 16)
postgresql_enable_seqscan: on
postgresql_enable_sort: on
postgresql_enable_tidscan: on
# - Planner Cost Constants -
postgresql_seq_page_cost: 1.0 # measured on an arbitrary scale
postgresql_random_page_cost: 4.0 # same scale as above
postgresql_cpu_tuple_cost: 0.01 # same scale as above
postgresql_cpu_index_tuple_cost: 0.005 # same scale as above
postgresql_cpu_operator_cost: 0.0025 # same scale as above
postgresql_parallel_tuple_cost: 0.1 # same scale as above (>= 9.6)
postgresql_parallel_setup_cost: 1000.0 # same scale as above (>= 9.6)
postgresql_min_parallel_relation_size: 8MB # (= 9.6) replaced by below two parameters in 10
postgresql_min_parallel_table_scan_size: 8MB # (>= 10)
postgresql_min_parallel_index_scan_size: 512kB # (>= 10)
postgresql_effective_cache_size: 4GB
postgresql_jit_above_cost: 100000 # (>= 11) perform JIT compilation if available
# and query more expensive than this;
# -1 disables
postgresql_jit_inline_above_cost: 500000 # (>= 11) inline small functions if query is
# more expensive than this; -1 disables
postgresql_jit_optimize_above_cost: 500000 # (>= 11) use expensive JIT optimizations if
# query is more expensive than this;
# -1 disables
# - Genetic Query Optimizer -
postgresql_geqo: on
postgresql_geqo_threshold: 12
postgresql_geqo_effort: 5 # range 1-10
postgresql_geqo_pool_size: 0 # selects default based on effort
postgresql_geqo_generations: 0 # selects default based on effort
postgresql_geqo_selection_bias: 2.0 # range 1.5-2.0
postgresql_geqo_seed: 0.0 # range 0.0-1.0
# - Other Planner Options -
postgresql_default_statistics_target: 100 # range 1-10000
postgresql_constraint_exclusion: "partition" # on, off, or partition
postgresql_cursor_tuple_fraction: 0.1 # range 0.0-1.0
postgresql_from_collapse_limit: 8
postgresql_jit: on # (>= 11: off, >= 12: on)
postgresql_join_collapse_limit: 8 # 1 disables collapsing of explicit
postgresql_force_parallel_mode: off # on, off, regress (>= 9.6 <= 13)
postgresql_plan_cache_mode: "auto" # (>= 12)
postgresql_recursive_worktable_factor: 10.0 # (>= 15) range 0.001-1000000
#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
# Valid values are combinations of stderr, csvlog, syslog, and eventlog.
# depending on platform. Csvlog requires logging_collector to be on.
postgresql_log_destination: "stderr"
# Enable capturing of stderr and csvlog into log files.
# Required to be on for csvlogs.
postgresql_logging_collector: off
# These are only used if logging_collector is on:
# Directory where log files are written, can be absolute or relative to PGDATA
postgresql_log_directory: "log"
# Log file name pattern, can include strftime() escapes
postgresql_log_filename: "postgresql-%Y-%m-%d_%H%M%S.log"
postgresql_log_file_mode: "0600" # begin with 0 to use octal notation
# If on, an existing log file with the same name as the new log file will be
# truncated rather than appended to. But such truncation only occurs on
# time-driven rotation, not on restarts or size-driven rotation. Default is
# off, meaning append to existing files in all cases.
postgresql_log_truncate_on_rotation: off
# Automatic rotation of logfiles will happen after that time.
postgresql_log_rotation_age: 1d
# Automatic rotation of logfiles will happen after that much log output.
postgresql_log_rotation_size: 10MB
# These are relevant when logging to syslog:
postgresql_syslog_facility: "LOCAL0"
postgresql_syslog_ident: "postgres"
postgresql_syslog_sequence_numbers: on # (>= 9.6)
postgresql_syslog_split_messages: on # (>= 9.6)
# This is only relevant when logging to eventlog (win32) (>= 9.2):
postgresql_event_source: "PostgreSQL"
# - When to Log -
# Values in order of decreasing detail:
# - debug5
# - debug4
# - debug3
# - debug2
# - debug1
# - info
# - notice
# - warning
# - error
# - log
# - fatal
# - panic
postgresql_log_min_messages: "warning"
# Values in order of decreasing detail:
# - debug5
# - debug4
# - debug3
# - debug2
# - debug1
# - info
# - notice
# - warning
# - error
# - log
# - fatal
# - panic (effectively off)
postgresql_log_min_error_statement: "error"
# -1 is disabled, 0 logs all statements and their durations, > 0 logs only
# statements running at least this number of milliseconds
postgresql_log_min_duration_statement: -1
postgresql_log_min_duration_sample: -1 # (>= 13)
postgresql_log_statement_sample_rate: 1.0 # (>= 13)
postgresql_log_transaction_sample_rate: 0.0 # (>= 12)
postgresql_log_startup_progress_interval: 10s # (>= 15)
# - What to Log -
postgresql_debug_print_parse: off
postgresql_debug_print_rewritten: off
postgresql_debug_print_plan: off
postgresql_debug_pretty_print: on
postgresql_log_autovacuum_min_duration: "{{ '10min' if postgresql_version is version_compare('15', '>=') else -1 }}"
postgresql_log_checkpoints: "{{ 'on' if postgresql_version is version_compare('15', '>=') else 'off' }}"
postgresql_log_connections: off
postgresql_log_disconnections: off
postgresql_log_duration: off
postgresql_log_error_verbosity: "default" # terse, default, or verbose messages
postgresql_log_hostname: off
# Special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %b = backend type (>= 13)
# %p = process ID
# %P = process ID of parallel group leader (>= 14)
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %n = timestamp with milliseconds (as a Unix epoch)
# %Q = query ID (0 if none or not computed) (>= 14)
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
postgresql_log_line_prefix: "%m [%p] "
# log lock waits >= deadlock_timeout
postgresql_log_lock_waits: off
postgresql_log_recovery_conflict_waits: off # (>= 14)
postgresql_log_parameter_max_length: -1 # (>= 13)
postgresql_log_parameter_max_length_on_error: 0 # (>= 13)
postgresql_log_statement: "none" # none, ddl, mod, all
postgresql_log_replication_commands: off
# log temporary files equal or larger
postgresql_log_temp_files: -1
postgresql_log_timezone: "UTC"
#------------------------------------------------------------------------------
# PROCESS TITLE
#------------------------------------------------------------------------------
postgresql_update_process_title: on # (>= 9.2)
#------------------------------------------------------------------------------
# STATISTICS
#------------------------------------------------------------------------------
# - Cumulative Query and Index Statistics -
postgresql_track_activities: on
postgresql_track_activity_query_size: 1024
postgresql_track_counts: on
postgresql_track_io_timing: off # (>= 9.2)
postgresql_track_wal_io_timing: off # (>= 14)
postgresql_track_functions: "none" # none, pl, all
postgresql_stats_fetch_consistency: cache # (>= 15) cache, none, snapshot
postgresql_stats_temp_directory: "pg_stat_tmp" # (<= 14)
# - Monitoring -
postgresql_compute_query_id: auto # (>= 14) auto, on, off
postgresql_log_parser_stats: off
postgresql_log_planner_stats: off
postgresql_log_executor_stats: off
postgresql_log_statement_stats: off
#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------
# Enable autovacuum subprocess? 'on' requires track_counts to also be on.
postgresql_autovacuum: on
# -1 disables, 0 logs all actions and their durations, > 0 logs only
# actions running at least this number of milliseconds.
# max number of autovacuum subprocesses
postgresql_autovacuum_max_workers: 3
# time between autovacuum runs
postgresql_autovacuum_naptime: 1min
# min number of row updates before vacuum
postgresql_autovacuum_vacuum_threshold: 50
postgresql_autovacuum_vacuum_insert_threshold: 1000 # (>= 13)
# min number of row updates before analyze
postgresql_autovacuum_analyze_threshold: 50
# fraction of table size before vacuum
postgresql_autovacuum_vacuum_scale_factor: 0.2
postgresql_autovacuum_vacuum_insert_scale_factor: 0.2 # (>= 13)
# fraction of table size before analyze
postgresql_autovacuum_analyze_scale_factor: 0.1
# maximum XID age before forced vacuum
postgresql_autovacuum_freeze_max_age: 200000000
# maximum Multixact age before forced vacuum (>= 9.3)
postgresql_autovacuum_multixact_freeze_max_age: 400000000
# default vacuum cost delay for autovacuum, in milliseconds
postgresql_autovacuum_vacuum_cost_delay: 2ms # (<= 11: 20ms, >=12 2ms)
# default vacuum cost limit for autovacuum,
postgresql_autovacuum_vacuum_cost_limit: -1
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Statement Behavior -
# Values in order of decreasing detail:
# - debug5
# - debug4
# - debug3
# - debug2
# - debug1
# - log
# - notice
# - warning
# - error
postgresql_client_min_messages: "notice"
postgresql_search_path: # schema names
- '"$user"'
- "public"
postgresql_row_security: on # (>= 9.5)
postgresql_default_tablespace: "" # a tablespace name, "" uses the default
postgresql_default_toast_compression: "pglz" # (>= 14) 'pglz' or 'lz4'
postgresql_temp_tablespaces: [] # a list of tablespace names
postgresql_default_table_access_method: "heap" # (>= 12)
postgresql_tablespaces_dirs: [] # a list of directories for tablespaces to be created
postgresql_check_function_bodies: on
postgresql_default_transaction_isolation: "read committed"
postgresql_default_transaction_read_only: off
postgresql_default_transaction_deferrable: off
postgresql_session_replication_role: "origin"
postgresql_statement_timeout: 0 # in milliseconds, 0 is disabled
postgresql_lock_timeout: 0 # in milliseconds, 0 is disabled (>= 9.3)
postgresql_idle_in_transaction_session_timeout: 0 # in milliseconds, 0 is disabled (>= 9.6)
postgresql_idle_session_timeout: 0 # in milliseconds, 0 is disabled (>= 14)
postgresql_vacuum_freeze_table_age: 150000000
postgresql_vacuum_freeze_min_age: 50000000
postgresql_vacuum_failsafe_age: 1600000000 # (>= 14)
postgresql_vacuum_multixact_freeze_table_age: 150000000 # (>= 9.3)
postgresql_vacuum_multixact_freeze_min_age: 5000000 # (>= 9.3)
postgresql_vacuum_multixact_failsafe_age: 1600000000 # (>= 14)
postgresql_vacuum_cleanup_index_scale_factor: 0.1 # (>= 11 and <= 13)
postgresql_bytea_output: "hex" # hex, escape
postgresql_xmlbinary: "base64"
postgresql_xmloption: "content"
postgresql_gin_pending_list_limit: 4MB # (>= 9.5)
postgresql_createrole_self_grant: '' # (>= 16) 'set', 'inherit' or 'set, inherit'
# - Locale and Formatting -
postgresql_datestyle:
- "iso"
- "mdy"
postgresql_intervalstyle: "postgres"
postgresql_timezone: "UTC" # postgres default is GMT
# Select the set of available time zone abbreviations. Currently, there are:
# Default
# Australia
# India
# You can create your own file in `share/timezonesets/`.
postgresql_timezone_abbreviations: "Default"
# min -15, max 3; (>= 12) any value >0 actually selects precise output mode
postgresql_extra_float_digits: "{{ 1 if postgresql_version is version_compare('12', '>=') else 0 }}"
postgresql_client_encoding: false # actually defaults to database encoding
# 'sql_ascii', 'UTF8', ...
# These settings are initialized by initdb, but they can be changed.
# locale for system error message
postgresql_lc_messages: "{{ postgresql_locale }}"
# locale for monetary formatting
postgresql_lc_monetary: "{{ postgresql_locale }}"
# locale for number formatting
postgresql_lc_numeric: "{{ postgresql_locale }}"
# locale for time formatting
postgresql_lc_time: "{{ postgresql_locale }}"
postgresql_icu_validation_level: "warning" # (>= 16) report ICU locale validation
# errors at the given level
# default configuration for text search
postgresql_default_text_search_config: "pg_catalog.english" # default is 'pg_catalog.simple'
# - Shared Library Preloading -
postgresql_local_preload_libraries: []
postgresql_session_preload_libraries: []
postgresql_shared_preload_libraries: []
postgresql_jit_provider: "llvmjit" # (>= 11)
# - Other Defaults -
postgresql_dynamic_library_path: "$libdir"
postgresql_extension_destdir: "" # prepend path when loading extensions
# and shared objects (added by Debian)
postgresql_gin_fuzzy_search_limit: 0
#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
postgresql_deadlock_timeout: 1s
postgresql_max_locks_per_transaction: 64 # min 10
# Note: Each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.
postgresql_max_pred_locks_per_transaction: 64 # min 10
postgresql_max_pred_locks_per_relation: -2 # (>= 10) negative values mean
# (max_pred_locks_per_transaction
# / -max_pred_locks_per_relation) - 1
postgresql_max_pred_locks_per_page: 2 # (>= 10) min 0
#------------------------------------------------------------------------------
# VERSION AND PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
# - Previous PostgreSQL Versions -
postgresql_array_nulls: on
postgresql_backslash_quote: "safe_encoding" # on, off, or safe_encoding
postgresql_escape_string_warning: on
postgresql_lo_compat_privileges: off
postgresql_operator_precedence_warning: off # (<= 13)
postgresql_quote_all_identifiers: off
postgresql_standard_conforming_strings: on
postgresql_synchronize_seqscans: on
postgresql_default_with_oids: off # (<= 11)
postgresql_sql_inheritance: on # (<= 9.6)
# - Other Platforms and Clients -
postgresql_transform_null_equals: off
#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------
# Terminate session on any error?
postgresql_exit_on_error: off
# Reinitialize after backend crash?
postgresql_restart_after_crash: on
# Retry or panic on failure to fsync data?
postgresql_data_sync_retry: off # (>= 11)
postgresql_recovery_init_sync_method: fsync #(>= 14) fsync, syncfs (Linux 5.8+)
#------------------------------------------------------------------------------
# CONFIG FILE INCLUDES
#------------------------------------------------------------------------------
# include files ending in '.conf' from
# directory 'conf.d'
postgresql_include_dir: "conf.d"
# include file only if it exists
postgresql_include_if_exists: false
# include file
postgresql_include: false
#------------------------------------------------------------------------------
# PGTUNE
#------------------------------------------------------------------------------
# Should we run pgtune and accept the changes it proposes?
postgresql_pgtune: no
# Total system memory in bytes, will attempt to detect if set to "no"
postgresql_pgtune_memory: no
# Database type, valid options are DW, OLTP, Web, Mixed, Desktop
postgresql_pgtune_type: "Mixed"
# Maximum number of expected connections, if "no", default based on db type
postgresql_pgtune_connections: no
#------------------------------------------------------------------------------
# INSTALL/REPO
#------------------------------------------------------------------------------
# Install repo, or rely on existing configuration (Satellite, Artifactory, etc.)
postgresql_install_repository: true
# APT settings
postgresql_apt_key_id: "ACCC4CF8"
postgresql_apt_key_url: "https://www.postgresql.org/media/keys/ACCC4CF8.asc"
postgresql_apt_repository: "deb http://apt.postgresql.org/pub/repos/apt/ {{ ansible_distribution_release }}-pgdg main {{ postgresql_version }}"
# Pin-Priority of PGDG repository
postgresql_apt_pin_priority: 500
# YUM repository locations
postgresql_yum_repository_url: "http://yum.postgresql.org"
postgresql_pgdg_repository_url: "https://download.postgresql.org/pub/repos/yum"
# YUM (RedHat, CentOS, etc.) baseurl/gpgkey
postgresql_yum_repository_baseurl: "{{ postgresql_yum_repository_url }}/{{ postgresql_version }}/{{ ansible_os_family | lower }}/rhel-{{ ansible_distribution_major_version }}-{{ ansible_architecture }}"
postgresql_yum_repository_gpgkey: "{{ postgresql_pgdg_repository_url }}/keys/PGDG-RPM-GPG-KEY-RHEL"
# DNF (Fedora) baseurl/gpgkey
postgresql_dnf_repository_baseurl: "{{ postgresql_yum_repository_url }}/{{ postgresql_version }}/fedora/fedora-{{ ansible_distribution_major_version }}-{{ ansible_architecture }}"
postgresql_dnf_repository_gpgkey: "{{ postgresql_yum_repository_gpgkey }}"
postgresql_apt_dependencies: ["python3-psycopg2", "locales"]
postgresql_hide_passwords: false