olap.yml

#!/usr/bin/env patroni
#==============================================================#
# File      :   patroni.yml (olap)
# Desc      :   Analysis Database Cluster Template (4-128C)
# Time      :   {{ '%Y-%m-%d %H:%M' |strftime }}
# Host      :   {{ pg_instance }} @ {{ inventory_hostname }}:{{ patroni_port }}
# Path      :   /pg/conf/{{ pg_instance }}.yml <- /etc/patroni/patroni.yml
# Note      :   ANSIBLE MANAGED, DO NOT CHANGE!
# Docs      :   https://patroni.readthedocs.io/en/latest/SETTINGS.html
# License   :   Apache-2.0 @ https://pigsty.io/docs/about/license/
# Copyright :   2018-2026  Ruohang Feng / Vonng (rh@vonng.com)
#==============================================================#
{% macro abort(error) %}{{ None['[ERROR] ' ~ error][0] }}{% endmacro %}
{% if pg_max_conn != 'auto' and pg_max_conn|int >= 20 %}{% set pg_max_connections = pg_max_conn|int %}{% else %}{% set pg_max_connections = 500 %}{% endif %}
{% set pg_max_prepared_transactions = pg_max_connections if 'citus' in pg_libs else 0 %}
{% set pg_max_locks_per_transaction = (4 * pg_max_connections)|int if 'citus' in pg_libs or 'timescaledb' in pg_libs else 2 * pg_max_connections %}
{% set pg_shared_buffers = (node_mem_mb|int * pg_shared_buffer_ratio|float) | round(0, 'ceil') | int %}
{% set pg_maintenance_mem = (pg_shared_buffers|int * 0.50)|round(0, 'ceil')|int %}
{% set pg_effective_cache_size = node_mem_mb|int - pg_shared_buffers|int  %}
{% set pg_workmem =  ([ ([ (pg_shared_buffers / pg_max_connections)|round(0,'floor')|int , 64 ])|max|int , 8192])|min|int %}
{% set pg_size_twentieth =  ([ ([(node_fs_bytes|int / 21474836480 )|round(0, 'ceil')|int , 1]) | max , 100 ]) | min  %}
{% set pg_max_worker_processes = ([node_cpu|int + 12, 20])|max|int %}
{% set pg_max_parallel_workers = ([(node_cpu|int * 0.8)|round(0,'ceil')|int, 2])|max|int %}
{% set pg_max_parallel_workers_per_gather = ([(node_cpu|int * 0.5)|round(0,'floor')|int, 2])|max|int %}
{% set pg_max_parallel_mt_workers = ([(node_cpu|int * 0.33)|round(0,'ceil')|int, 2])|max|int %}
{% set pg_allow_list = ( [admin_ip] + pg_cluster_members|default([]) + groups["infra"]|default([]) )|sort|unique %}
{% set pg_listen_address = pg_listen | replace('${ip}', inventory_hostname)  | replace('${lo}', '127.0.0.1') %}
{% if pg_vip_enabled|bool and not (pg_vip_address|string).startswith('127.0.0') and '/' in (pg_vip_address|string) %}
{% set pg_listen_address = pg_listen_address | replace('${vip}', (pg_vip_address|string).split('/')[0]) %}
{% endif %}
{% set pg_effective_io_method = pg_io_method if pg_io_method|default('auto') != 'auto' else ('io_uring' if (os_package|default('rpm') == 'deb' or os_version|default(9)|int >= 10) else 'worker') %}
{% set pg_io_workers = ([[(node_cpu|int * 0.5)|round(0,'ceil')|int, 4]|max, 32])|min|int %}
{% set pg_rto_mode = pg_rto if pg_rto in pg_rto_plan else 'norm' %}
{% set rto = pg_rto_plan[pg_rto_mode] %}
{% set pg_ttl = rto[0] %}{% set pg_loop_wait = rto[1] %}{% set pg_retry_timeout = rto[2] %}{% set pg_primary_start_timeout = rto[3] %}{% set pg_safety_margin = rto[4] %}

#==============================================================#
# Summary for patroni OLAP config template (4-128 cpu cores)
#==============================================================#
# {{ inventory_hostname }}:{{ pg_port }} | [{{ pg_role }}] {% if pg_role == 'primary' %}{% if pg_upstream is defined %}STANDBY LEADER @ {{ pg_instance }}{% else %}CLUSTER LEADER @ {{ pg_instance }}{% endif %}{% else %}{% if pg_upstream is defined %}REPLICATE UPSTREAM {{ pg_upstream }} -> {{ pg_instance }} {% else %}REPLICATE PRIMARY {{ pg_primary_ins }} -> {{ pg_instance }}{% endif %}{% endif %}

# Node: {{ node_cpu }}C{{ node_mem_gb }}G , node_tune = {{ node_tune }}
# Memory: {{ node_mem_mb }}MB , Disk : {{ [ (node_fs_bytes|int / 1073741824)|round(0, 'ceil')|int , 1 ] | max }}GB {{ pg_storage_type|default('SSD') }} @ {{ pg_fs_main }}
# ETCD: {% for ip in groups['etcd']|default([])|sort %}{{ ip }}:{{ etcd_port }} {% endfor %}

#--------------------------------------------------------------#
# pgconf, maxconn, workmem   = {{ pg_conf }} , {{ pg_max_connections }} , {{ pg_workmem }}MB
# buffer, cache, maintain    = {{ pg_shared_buffers }}MB, {{ pg_effective_cache_size }}MB,  {{ pg_maintenance_mem }}MB
# prepared xact, locks/xact  = {{ pg_max_prepared_transactions }} , {{ pg_max_locks_per_transaction }}
# wal min,max,slot,temp = {{ ([pg_size_twentieth, 200])|min }}GB, {{ ([pg_size_twentieth * 4, 2000])|min }}GB, {{ ([pg_size_twentieth * 6, 3000])|min }}GB, {{ ([pg_size_twentieth * 4, 2000])|min }}GB
# proc, worker, mtw, gather  = {{ pg_max_worker_processes }}, {{ pg_max_parallel_workers }}, {{ pg_max_parallel_mt_workers }}, {{ pg_max_parallel_workers_per_gather }}
# libraries = {{ pg_libs }}
#==============================================================#

---
#--------------------------------------------------------------#
# identity
#--------------------------------------------------------------#
# namespace, instance, cluster/shard scope
namespace: {{ pg_namespace }}/
name: {{ pg_instance }}
{% if pg_mode == 'citus' %}
scope: {{ pg_shard }}   # citus shard cluster: {{ pg_cluster }}

#--------------------------------------------------------------#
# citus
#--------------------------------------------------------------#
citus:
  group: {{ pg_group }}
  database: {{ pg_primary_db }}
{% else %}
scope: {{ pg_cluster }}   # common pgsql cluster
{% endif %}

#--------------------------------------------------------------#
# log
#--------------------------------------------------------------#
log:
  level: INFO                           #  NOTEST|DEBUG|INFO|WARNING|ERROR|CRITICAL
  dir: {{ patroni_log_dir }}            #  patroni log dir
  file_size: 33554432                   #  32MB log triggers log rotation
  file_num: 4                           #  keep at most 4 x 32MB = 128MB log
  dateformat: '%Y-%m-%d %H:%M:%S %z'    #  IMPORTANT: discard milli timestamp
  format: '%(asctime)s %(levelname)s: %(message)s'

#--------------------------------------------------------------#
# etcd
#--------------------------------------------------------------#
etcd3:
  hosts: '{% for ip in groups['etcd']|default([])|sort %}{% if not loop.first %},{% endif %}{{ ip }}:{{ etcd_port }}{% endfor %}'
  cacert: /pg/cert/ca.crt
  cert:   /pg/cert/server.crt
  key:    /pg/cert/server.key
  protocol: https
  username: '{{ pg_shard if pg_mode == "citus" else pg_cluster }}'
  password: '{{ pg_etcd_password | default(pg_shard if pg_mode == "citus" else pg_cluster, true) }}'

#--------------------------------------------------------------#
# ctl
#--------------------------------------------------------------#
ctl:
{% if patroni_ssl_enabled|bool %}
  insecure: false
  cacert:   /pg/cert/ca.crt
  certfile: /pg/cert/server.crt
  keyfile:  /pg/cert/server.key
{% else %}                                      # patroni_ssl disabled
  insecure: true
{% endif %}                                     # end of patroni_ssl_enabled

#--------------------------------------------------------------#
# api
#--------------------------------------------------------------#
# how to expose patroni service
restapi:
{% if patroni_ssl_enabled|bool %}
  cacert:   /pg/cert/ca.crt
  certfile: /pg/cert/server.crt
  keyfile:  /pg/cert/server.key
  verify_client: none            # none|optional|required
{% endif %}
  listen: {{ inventory_hostname }}:{{ patroni_port }}
  connect_address: {{ inventory_hostname }}:{{ patroni_port }}
  # unsafe api can only be accessed from meta nodes with auth
  authentication:
    username: '{{ patroni_username }}'
    password: '{{ patroni_password }}'
  allowlist: [ {{ pg_allow_list|join(', ') }} ]

#--------------------------------------------------------------#
# tags
#--------------------------------------------------------------#
tags:
  nofailover: false
  clonefrom: true
  noloadbalance: false
  nosync: false
{% if pg_upstream is defined %}
  replicatefrom: {{ pg_upstream }}    # clone from another replica rather than primary
{% endif %}
  version:  '{{ pg_version }}'
  spec: '{{ node_cpu }}C.{{ node_mem_gb }}G.{{ [ (node_fs_bytes|int / 1073741824)|round(0, 'ceil')|int , 1 ] | max }}G'
  conf: '{{ pg_conf }}'

#--------------------------------------------------------------#
# watchdog
#--------------------------------------------------------------#
# available mode: off|automatic|required
watchdog:
  mode: '{{ 'off' if patroni_watchdog_mode|string|lower in ['off', 'false'] else patroni_watchdog_mode|string|lower }}'
  device: /dev/watchdog
  safety_margin: {{ pg_safety_margin }}

#--------------------------------------------------------------#
# bootstrap
#--------------------------------------------------------------#
bootstrap:

  #------------------------------------------------------------#
  # bootstrap method
  #------------------------------------------------------------#
  method: initdb
  # add custom bootstrap method here

  # default bootstrap method: initdb
  initdb:
{% if pg_checksum != '' and pg_checksum|bool %}
    - data-checksums
{% elif pg_version|int >= 18 and not pg_checksum|bool %}
    - no-data-checksums
{% endif %}
{% if pg_version|default(18)|int >= 17 and pg_encoding|default('UTF8') in ['UTF8','UTF-8'] and pg_locale|default('C')|upper in ['C', 'C.UTF-8'] %}
    # use postgres built-in C or C.UTF-8 locale since PG 17 by default
    - encoding: {{ pg_encoding }}
    - locale-provider: builtin
    - builtin-locale: {{ pg_locale }}
{% else %}
{% if pg_encoding != '' %}
    - encoding: {{ pg_encoding }}
{% endif %}
{% if pg_locale != '' %}
    - locale: {{ pg_locale }}
{% endif %}
{% endif %}
{% if pg_lc_collate != '' %}
    - lc-collate: {{ pg_lc_collate }}
{% endif %}
{% if pg_lc_ctype != '' %}
    - lc-ctype: {{ pg_lc_ctype }}
{% endif %}
{% if pg_mode == 'oracle' %}
    - compatibility_mode: ora
{% endif %}

  #------------------------------------------------------------#
  # customization
  #------------------------------------------------------------#
  # post_init: /pg/bin/pg-init

  #------------------------------------------------------------#
  # bootstrap config
  #------------------------------------------------------------#
  # this section will be written to /{{ pg_namespace }}/{{ pg_cluster }}/config
  # it will NOT take any effect after cluster bootstrap
  dcs:

{% if pg_role == 'primary' and pg_upstream is defined %}
    #----------------------------------------------------------#
    # standby cluster definition
    #----------------------------------------------------------#
    standby_cluster:
      host: {{ pg_upstream }}
      port: {{ pg_port }}
{% if pg_delay is defined and pg_delay != '' and pg_delay != 0 %}
      recovery_min_apply_delay: {{ pg_delay }}
{% endif %}
      # primary_slot_name: patroni     # must be created manually on upstream server, if specified
      create_replica_methods:
        - basebackup
{% endif %}

    #----------------------------------------------------------#
    # important parameters
    #----------------------------------------------------------#
    # constraint: ttl >= loop_wait + retry_timeout * 2

    # the TTL to acquire the leader lock (in seconds). Think of it as the length of time before initiation of the automatic failover process. Default value: 30
    # config this according to your network condition to avoid false-positive failover
    ttl: {{ pg_ttl }}

    # the number of seconds the loop will sleep. Default value: 10
    # this is patroni check loop interval
    loop_wait: {{ pg_loop_wait }}

    # timeout for DCS and PostgreSQL operation retries (in seconds). DCS or network issues shorter than this will not cause Patroni to demote the leader. Default value: 10
    retry_timeout: {{ pg_retry_timeout }}

    # the amount of time a primary is allowed to recover from failures before failover is triggered (in seconds)
    # Max RTO: 2 loop wait + primary_start_timeout
    primary_start_timeout: {{ pg_primary_start_timeout }}

    # important: candidate will not be promoted if replication lag is higher than this
    # maximum RPO: 1MB
    maximum_lag_on_failover: {{ pg_rpo }}

    # the maximum bytes a synchronous follower may lag before it is considered as an unhealthy candidate and swapped by healthy asynchronous follower
    maximum_lag_on_syncnode: -1

    # The number of seconds Patroni is allowed to wait when stopping Postgres and effective only when synchronous_mode is enabled
    primary_stop_timeout: 30

    # turns on synchronous replication mode. In this mode a replica will be chosen as synchronous and only the latest leader and synchronous replica are able to participate in leader election
    # if crit template is used or RPO set to 0,1 , sync mode will be enabled
    synchronous_mode: {% if pg_rpo|int == 0 %}true{% else %}false{% endif %}


    # prevents disabling synchronous replication if no synchronous replicas are available, blocking all client writes to the primary
    synchronous_mode_strict: false
    # synchronous_node_count: 1  # edit-config only, when using quorum commit

    # Postgres may continue to run as a primary if it can access all known members of the cluster via Patroni REST API.
    failsafe_mode: true

    # postgres cluster metadata
    pg_version: {{ pg_version }}
    pg_cluster: {{ pg_cluster }}
    pg_shard:   {{ pg_shard|default(pg_cluster) }}
    pg_group:   {{ pg_group|default(0) }}

    #----------------------------------------------------------#
    # postgres parameters
    #----------------------------------------------------------#
    postgresql:
      use_slots: true
      use_pg_rewind: true
      remove_data_directory_on_rewind_failure: true

      parameters:
        #------------------------------------------------------#
        # IMPORTANT PARAMETERS
        #------------------------------------------------------#
        max_connections: {{ pg_max_connections }}
        superuser_reserved_connections: 10
        max_locks_per_transaction: {{ pg_max_locks_per_transaction }}
        max_prepared_transactions: {{ pg_max_prepared_transactions }}
        track_commit_timestamp: 'on'                # enabled xact timestamp
        wal_level: logical                          # force logical level
        wal_log_hints: 'on'                         # wal log hints to support rewind
{% if pg_version|int >= 15 and pg_mode != 'ivory' %}
        wal_compression: lz4                        # PG15+: lz4 full-page image compression
{% endif %}
        max_worker_processes: {{ pg_max_worker_processes + 8 }}
        max_wal_senders: 50                         # 10 -> 50
        max_replication_slots: 50                   # 10 -> 50

        #------------------------------------------------------#
        # AUTHENTICATION
        #------------------------------------------------------#
        password_encryption: {{ pg_pwd_enc|default('scram-sha-256') }}
        ssl: 'on'                               # enable server ssl
        ssl_cert_file: '/pg/cert/server.crt'    # public cert path
        ssl_key_file: '/pg/cert/server.key'     # private key path
        ssl_ca_file: '/pg/cert/ca.crt'          # ca file path

        #------------------------------------------------------#
        # RESOURCE USAGE (except WAL)
        #------------------------------------------------------#
        # memory: shared_buffers and maintenance_work_mem will be dynamically set
        shared_buffers: {{ pg_shared_buffers }}MB
        maintenance_work_mem: {{ pg_maintenance_mem }}MB
        work_mem: {{ pg_workmem }}MB
        max_parallel_workers: {{ pg_max_parallel_workers }}
        max_parallel_maintenance_workers: {{ pg_max_parallel_mt_workers }}
        max_parallel_workers_per_gather: {{ pg_max_parallel_workers_per_gather|int }}
{% if pg_version|int >= 13 %}
        hash_mem_multiplier: 8.0                # use 8x work mem in hash-based operations
{% endif %}
        huge_pages: try                         # try huge pages
{% if pg_version|int >= 18 %}
        io_method: {{ pg_effective_io_method }}            # io method: sync, worker, io_uring
{% if pg_version|int == 18 %}
        io_workers: {{ pg_io_workers }}                    # number of io workers: {{ pg_io_workers }}
{% endif %}
{% endif %}
        temp_file_limit: {{ ([pg_size_twentieth * 4, 2000])|min }}GB     # 1/5 of disk size, max 2000GB
        vacuum_cost_delay: 10ms                 # wait 10ms per 10000 cost
        vacuum_cost_limit: 10000                # max cost each round
        bgwriter_delay: 10ms                    # check dirty page every 10ms
        bgwriter_lru_maxpages: 800              # 100 -> 800
        bgwriter_lru_multiplier: 5.0            # 2.0 -> 5.0  more cushion buffer

        #------------------------------------------------------#
        # WAL
        #------------------------------------------------------#
{% if pgbackrest_enabled|bool %}
        archive_mode: 'on'
        archive_timeout: 300                    # 5min archive timeout
        archive_command: 'pgbackrest --stanza={{ pg_cluster }} archive-push %p'
{% endif %}
        min_wal_size: {{ ([pg_size_twentieth, 200])|min }}GB     # 1/20 disk size, max 200GB
        max_wal_size: {{ ([pg_size_twentieth * 4, 2000])|min }}GB     # 2/10 disk size, max 2000GB
        max_slot_wal_keep_size: {{ ([pg_size_twentieth * 6, 3000])|min }}GB     # 3/10 disk size, max 3000GB
{% if pg_version|int >= 18 %}
        idle_replication_slot_timeout: 7d       # drop idle replication slot after 7 days
{% endif %}
        wal_buffers: 16MB                       # max to 16MB
        wal_writer_delay: 20ms                  # wait period
        wal_writer_flush_after: 1MB             # max allowed data loss
        commit_delay: 20                        # 200ms -> 20ms, increase speed
        commit_siblings: 10                     # 5 -> 10
        checkpoint_timeout: 15min               # checkpoint 5min -> 15min
        checkpoint_completion_target: 0.95      # 0.9 -> 0.95

        #------------------------------------------------------#
        # REPLICATION
        #------------------------------------------------------#
        # synchronous_standby_names: ''
{% if pg_version|int >= 17 %}
        sync_replication_slots: on
{% endif %}
        max_standby_archive_delay: 10min        # max delay before canceling queries when reading WAL from archive;
        max_standby_streaming_delay: 3min       # max delay before canceling queries when reading streaming WAL;
        wal_receiver_status_interval: 1s        # send replies at least this often
        hot_standby_feedback: 'on'              # send info from standby to prevent query conflicts
        wal_receiver_timeout: 60s               # time that receiver waits for
        max_logical_replication_workers: 8      # 4 -> 8, 6 sync worker + 1~2 apply worker
        max_sync_workers_per_subscription: 6    # 2 -> 6, 6 sync worker

        #------------------------------------------------------#
        # QUERY TUNING
        #------------------------------------------------------#
        enable_partitionwise_join: 'on'         # enable for olap system
        enable_partitionwise_aggregate: 'on'    # enable for olap system
{% if pg_storage_type == 'SSD' %}
        random_page_cost: 1.1                   # 4 for HDD, 1.1 for SSD
        effective_io_concurrency: 200           # 200 for SSD
{% if pg_version|int >= 13 %}
        maintenance_io_concurrency: 100        # 100 for SSD maintenance
{% endif %}
{% endif %}
        effective_cache_size: {{ pg_effective_cache_size }}MB  # max mem - shared buffer
        default_statistics_target: 1000         # stat bucket 100 -> 1000

        #------------------------------------------------------#
        # REPORTING AND LOGGING
        #------------------------------------------------------#
{% if pg_mode != 'oracle' and pg_mode != 'polar' %}
        log_destination: csvlog                 # use standard csv log
{% endif %}
        logging_collector: 'on'                 # enable csvlog
        log_directory: {{ pg_log_dir }}         # postgres log directory
        log_timezone: 'UTC'                     # always use UTC timezone for logging
        log_filename: 'postgresql-%a.log'       # rotate on weekly basis
        log_file_mode: '0640'                   # allow group log read
        log_rotation_age: '1d'                  # rotate log everyday
        log_rotation_size: '0'                  # do not rotate by size
        log_truncate_on_rotation: 'on'          # truncate on rotate
        log_checkpoints: 'on'                   # log checkpoint info
        log_lock_waits: 'on'                    # log lock wait info
        log_temp_files: 1024                    # log temp file > 1MB
        log_replication_commands: 'on'          # log replication info
        log_statement: ddl                      # log ddl change
        log_min_duration_statement: 1000         # log slow query (>1s)
{% if pg_version|int >= 18 %}
        log_connections: 'authorization'        # log successful connections
{% if pg_mode|default('pgsql') != 'ivory' %}
        file_copy_method: clone                 # clone for instant db clone on CoW fs
{% endif %}
{% endif %}

        #------------------------------------------------------#
        # STATISTICS
        #------------------------------------------------------#
        track_io_timing: 'on'                   # collect io statistics
{% if pg_version|int >= 18 %}
        track_cost_delay_timing: 'on'           # track vacuum/analyze cost delay timing
{% endif %}
        track_functions: all                    # track all functions (none|pl|all)
        track_activity_query_size: 8192         # max query length in pg_stat_activity

        #------------------------------------------------------#
        # AUTOVACUUM
        #------------------------------------------------------#
        log_autovacuum_min_duration: 1s         # log autovacuum activity take more than 1s
        autovacuum_max_workers: 3               # default autovacuum worker 3
        autovacuum_naptime: 1min                # default autovacuum naptime 1min
        autovacuum_vacuum_threshold: 1000       # 50 -> 1000, more relaxed for batch load scenarios
        autovacuum_analyze_threshold: 500       # 50 -> 500, more relaxed for batch load scenarios
        autovacuum_vacuum_scale_factor: 0.08    # fraction of table size before vacuum   20% -> 8%
        autovacuum_analyze_scale_factor: 0.04   # fraction of table size before analyze  10% -> 4%
        autovacuum_vacuum_cost_delay: -1        # default vacuum cost delay: same as vacuum_cost_delay
        autovacuum_vacuum_cost_limit: -1        # default vacuum cost limit: same as vacuum_cost_limit
        autovacuum_freeze_max_age: 1000000000   # age > 1e9 triggers force vacuum

        #------------------------------------------------------#
        # CLIENT
        #------------------------------------------------------#
        deadlock_timeout: 50ms                  # 50ms for deadlock
        idle_in_transaction_session_timeout: 0  # disable idle in xact timeout in analysis database

        #------------------------------------------------------#
        # CUSTOMIZED OPTIONS
        #------------------------------------------------------#
        # extensions
        shared_preload_libraries: '{{ pg_libs | default("pg_stat_statements, auto_explain") }}'

        # auto_explain
        auto_explain.log_min_duration: 1s       # auto explain query slower than 1s
        auto_explain.log_analyze: 'on'          # explain analyze
        auto_explain.log_verbose: 'on'          # explain verbose
        auto_explain.log_timing: 'on'           # explain timing
        auto_explain.log_nested_statements: true

        # pg_stat_statements
        pg_stat_statements.max: 10000           # 5000 -> 10000 queries
        pg_stat_statements.track: all           # track all statements (all|top|none)
        pg_stat_statements.track_utility: 'off' # do not track query other than CRUD
        pg_stat_statements.track_planning: 'off' # do not track planning metrics

        # pg_wait_sampling
        pg_wait_sampling.profile_pid: 'false'   # do not track profile pid
        pg_wait_sampling.profile_queries: 'all' # track all queries
        pg_wait_sampling.history_size: 100000   # keep 100K event in buffer

{% if 'timescaledb' in pg_libs | default("pg_stat_statements, auto_explain") %}
        # timescaledb
        timescaledb.telemetry_level: 'off'      # disable timescale telemetry
        timescaledb.max_background_workers: {{ pg_max_worker_processes }}
{% endif %}

{% if 'pgsodium' in pg_libs | default("pg_stat_statements, auto_explain") %}
        # pgsodium
        pgsodium.getkey_script: /pg/bin/pgsodium_getkey
        pgsodium.enable_event_trigger: off
{% endif %}

{% if 'pg_duckdb' in pg_libs | default("pg_stat_statements, auto_explain") %}
        # pg_duckdb / pg_mooncake
        duckdb.allow_community_extensions: true
{% endif %}

{% if pg_mode|default('pgsql') == 'polar' %}
        # polardb
        polar_resource_manager.enable_resource_manager: 'off'
{% endif %}

        # pg_cron
{% if pg_parameters is defined and pg_parameters is mapping and 'cron.database_name' in pg_parameters %}
        cron.database_name: {{ pg_parameters['cron.database_name'] }}
{% else %}
        cron.database_name: {{ pg_primary_db|default('postgres') }}
{% endif %}



#--------------------------------------------------------------#
# postgres
#--------------------------------------------------------------#
postgresql:

  #------------------------------------------------------------#
  # how to connect to postgres
  #------------------------------------------------------------#
  bin_dir: {{ pg_bin_dir }}
  data_dir: {{ pg_data }}
  config_dir: {{ pg_data }}
  pgpass: {{ pg_dbsu_home }}/.pgpass
  listen: {{ pg_listen_address }}:{{ pg_port }}
  connect_address: {{ inventory_hostname }}:{{ pg_port }}
  use_unix_socket: true # default: /var/run/postgresql, /tmp
  parameters:
    unix_socket_directories: '/var/run/postgresql, /tmp'

  #------------------------------------------------------------#
  # who to connect to postgres
  #------------------------------------------------------------#
  authentication:
    superuser:
      username: {{ pg_dbsu }}
{% if pg_dbsu_password is defined and pg_dbsu_password != '' %}
      password: {{ pg_dbsu_password }}
{% endif %}
    replication:
      username: {{ pg_replication_username }}
      password: '{{ pg_replication_password }}'
    rewind:
      username: {{ pg_replication_username }}
      password: '{{ pg_replication_password }}'

  #------------------------------------------------------------#
  # how to react to database operations
  #------------------------------------------------------------#
  # event callback script log: /pg/log/patroni/callback.log
  callbacks:
    on_start: /pg/bin/pg-failover-callback
    on_stop: /pg/bin/pg-failover-callback
    on_reload: /pg/bin/pg-failover-callback
    on_restart: /pg/bin/pg-failover-callback
    on_role_change: /pg/bin/pg-failover-callback

  # rewind policy: data checksum should be enabled before using rewind
  use_pg_rewind: true
  remove_data_directory_on_rewind_failure: true
  remove_data_directory_on_diverged_timelines: false

  #------------------------------------------------------------#
  # how to create replica
  #------------------------------------------------------------#
  # create replica method: default pg_basebackup
  create_replica_methods:
    - basebackup
{% if pgbackrest_enabled|bool and pgbackrest_method != 'local' %}
    - pgbackrest
{% endif %}
  basebackup:
    - max-rate: '1000M'
    - checkpoint: fast
    - verbose
    - progress
{% if pgbackrest_enabled|bool %}
  pgbackrest:
    command: /usr/bin/pgbackrest --stanza={{ pg_cluster }} --delta restore
    keep_data: true
    no_params: true
    no_leader: true
{% endif %}

  #------------------------------------------------------------#
  # ad hoc parameters (overwrite with default)
  #------------------------------------------------------------#
  # parameters:

  #------------------------------------------------------------#
  # host based authentication, overwrite default pg_hba.conf
  #------------------------------------------------------------#
  # pg_hba:
  #   - local  all          postgres            ident
  #   - local  all          all                 {{ pg_pwd_enc }}
  #   - host   all          all      0.0.0.0/0  {{ pg_pwd_enc }}
  #   - local  replication  postgres            ident
  #   - local  replication  all                 {{ pg_pwd_enc }}
  #   - host   replication  all      0.0.0.0/0  {{ pg_pwd_enc }}

... # end of patroni olap template