571 lines
27 KiB
YAML
571 lines
27 KiB
YAML
#!/usr/bin/env patroni
|
|
#==============================================================#
|
|
# File : patroni.yml (tiny)
|
|
# Desc : Tiny Database Cluster Template (1-3C)
|
|
# 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 = 250 %}{% endif %}
|
|
{% set pg_max_prepared_transactions = pg_max_connections if 'citus' in pg_libs else 0 %}
|
|
{% set pg_max_locks_per_transaction = (2 * pg_max_connections)|int if 'citus' in pg_libs or 'timescaledb' in pg_libs else 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.25)|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 , 16 ])|max|int , 256])|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 + 4, 12])|max|int %}
|
|
{% set pg_max_parallel_workers = ([(node_cpu|int * 0.5)|round(0,'floor')|int, 1])|max|int %}
|
|
{% set pg_max_parallel_workers_per_gather = 0 %}
|
|
{% set pg_max_parallel_mt_workers = ([(node_cpu|int * 0.33)|round(0,'floor')|int, 1])|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 = 3 %}
|
|
{% 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 TINY config template (1-3 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, 200])|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 - output to stderr (captured by journald)
|
|
#--------------------------------------------------------------#
|
|
log:
|
|
level: INFO # NOTEST|DEBUG|INFO|WARNING|ERROR|CRITICAL
|
|
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: {{ patroni_watchdog_mode }}
|
|
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 >= 17 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
|
|
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 >= 17 %}
|
|
io_method: {{ pg_effective_io_method }} # io method: sync, worker, io_uring
|
|
io_workers: {{ pg_io_workers }} # number of io workers: {{ pg_io_workers }}
|
|
{% endif %}
|
|
temp_file_limit: {{ ([pg_size_twentieth, 200])|min }}GB # 1/20 of disk size, max 200GB
|
|
vacuum_cost_delay: 20ms # wait 20ms per 2000 cost
|
|
vacuum_cost_limit: 2000 # 2000 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
|
|
#------------------------------------------------------#
|
|
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 >= 17 %}
|
|
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.80 # 0.5 -> 0.8
|
|
{% if pgbackrest_enabled|bool %}
|
|
archive_mode: 'on'
|
|
archive_timeout: 300 # 5min archive timeout
|
|
archive_command: 'pgbackrest --stanza={{ pg_cluster }} archive-push %p'
|
|
{% endif %}
|
|
|
|
#------------------------------------------------------#
|
|
# 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 # default 4, 6 sync worker + 1~2 apply worker
|
|
max_sync_workers_per_subscription: 6 # default 2, 6 sync worker
|
|
|
|
#------------------------------------------------------#
|
|
# QUERY TUNING
|
|
#------------------------------------------------------#
|
|
# enable_partitionwise_join: on
|
|
{% 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 >= 17 %}
|
|
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: 200 # stat bucket 100 -> 200
|
|
|
|
#------------------------------------------------------#
|
|
# 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: 100 # log slow query (>100ms)
|
|
{% if pg_version|int >= 18 %}
|
|
{% 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
|
|
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: 2 # default autovacuum worker 3 -> 2
|
|
autovacuum_naptime: 1min # default autovacuum naptime 1min
|
|
# autovacuum_vacuum_scale_factor: 0.16 # 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: 10min # 10min timeout for idle in transaction
|
|
|
|
#------------------------------------------------------#
|
|
# 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: 2500 # default 2500 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 tiny template
|
|
|