main.yml

# file: postgresql/defaults/main.yml

# Basic settings
postgresql_version: 17
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 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"
  17: "3.5"

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 # (<= 16)

# 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)
postgresql_vacuum_buffer_usage_limit: "{{ '256kB' if postgresql_version is version_compare('16', '=') else '2MB' }}" # (>= 16) size of vacuum and analyze buffer access strategy ring;
                                                # 0 to disable vacuum buffer access strategy;
                                                # range 128kB to 16GB

# SLRU buffers (change requires restart)
postgresql_commit_timestamp_buffers: 0   # (>= 17) memory for pg_commit_ts
postgresql_multixact_offset_buffers: 16       # (>= 17) memory for pg_multixact/offsets
postgresql_multixact_member_buffers: 32       # (>= 17) memory for pg_multixact/members
postgresql_notify_buffers: 16       # (>= 17) memory for pg_notify
postgresql_serializable_buffers: 32       # (>= 17) memory for pg_serial
postgresql_subtransaction_buffers: 0       # (>= 17) memory for pg_subtrans (0 = auto)
postgresql_transaction_buffers: 0       # (>= 17) memory for pg_xact (0 = auto)

# - Disk -

# limits per-process temp file space in kB, or -1 for no limit (>= 9.2)
postgresql_temp_file_limit: -1

postgresql_max_notify_queue_pages: 1048576       # (>= 17)

# - 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_io_combine_limit: 128kB       # (>= 17) usually 1-32 blocks (depends on OS)
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 and <= 16) 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)

# - WAL Summarization -
postgresql_summarize_wal: off       # (>= 17) run WAL summarizer process?
postgresql_wal_summary_keep_time: 10d       # (>= 17) when to remove old summary files, 0 = never

#------------------------------------------------------------------------------
# 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_synchronized_standby_slots: "" # (>= 17) streaming replication standby server slot
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)
postgresql_sync_replication_slots: off       # (>= 17) enables slot synchronization on the physical standby from the primary

# - 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
postgresql_enable_group_by_reordering:     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_transaction_timeout:                   0           # (>= 17) 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'
postgresql_event_triggers: on       # (>= 17)

# - 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
postgresql_allow_alter_system:          on  # (>= 17)

#------------------------------------------------------------------------------
# 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_keyring: "/usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg"

# repsoitory base
postgresql_apt_repo_base: "https://apt.postgresql.org/pub/repos/apt"
postgresql_apt_repo_components: "main"

# (codename + -pgdg)
postgresql_apt_suite: "{{ ansible_facts['distribution_release'] | default('') }}-pgdg"

# Ligne de dépôt finale (utilise les variables ci-dessus)
postgresql_apt_repository: >-
  deb [signed-by={{ postgresql_apt_keyring }}]
  {{ postgresql_apt_repo_base }} {{ postgresql_apt_suite }} {{ postgresql_apt_repo_components }}

# Pin-Priority of PGDG repository
postgresql_apt_pin_enabled: true
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