CloudTada | Infrastructure & DevOps Insights

Detailed Patroni Configuration

Objectives

After this lesson, you will:

Understand each section in the patroni.yml file in depth
Configure bootstrap options
Tune PostgreSQL parameters for HA
Configure authentication and security
Use tags and constraints
Optimize timing parameters

1. Overview of Patroni Configuration

1.1. Configuration layers

Patroni has multiple configuration layers:

TEXT

┌─────────────────────────────────────┐
│   1. Command line arguments         │ (Highest priority)
│      patroni --config-file=...      │
├─────────────────────────────────────┤
│   2. Environment variables          │
│      PATRONI_SCOPE=postgres         │
├─────────────────────────────────────┤
│   3. Configuration file             │
│      /etc/patroni/patroni.yml       │
├─────────────────────────────────────┤
│   4. DCS (Dynamic configuration)    │
│      Stored in etcd                 │
└─────────────────────────────────────┘
        ↓
   Merged configuration

Priority order: Command line > Environment > Config file > DCS

1.2. Static vs Dynamic configuration

Static configuration (in patroni.yml):

Node-specific settings (name, addresses)
etcd connection info
Data directory, bin directory
Restart required to apply changes

Dynamic configuration (in DCS):

PostgreSQL parameters
Bootstrap settings
TTL, loop_wait, retry_timeout
Can be updated at runtime: patronictl edit-config

2. Section: Scope and Namespace

2.1. Scope (Cluster name)

TEXT

scope: postgres

Scope is the unique name of the cluster in DCS.

Meaning:

All nodes in the same cluster must have the same scope
DCS keys are prefixed with scope: /service/postgres/...
Allows multiple clusters on the same etcd cluster

Best practices:

TEXT

# Development
scope: postgres-dev

# Staging
scope: postgres-staging

# Production
scope: postgres-prod

# Multi-tenant
scope: customer1-postgres
scope: customer2-postgres

2.2. Namespace

TEXT

namespace: /service/

Namespace is the prefix for all keys in DCS.

Full DCS key structure:

TEXT

/service/postgres/leader
/service/postgres/members/node1
/service/postgres/config

Multiple clusters example:

TEXT

/service/postgres-prod/leader
/service/postgres-staging/leader
/application/myapp-db/leader

3. Section: Node Information

3.1. Node name

TEXT

name: node1

Requirements:

Unique in cluster
Does not change after bootstrap
Should use hostname or FQDN

Example naming conventions:

TEXT

# Simple
name: node1
name: node2
name: node3

# With datacenter
name: dc1-node1
name: dc2-node1

# With role hint (not recommended)
name: pg-primary-01   # ❌ Role changes
name: pg-db-01        # ✅ Better

3.2. Host information

TEXT

# Optional: Override hostname
host: 10.0.1.11

Patroni automatically detects hostname, but can be overridden if needed.

4. Section: REST API

4.1. Basic configuration

TEXT

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.1.11:8008

Parameters:

listen: Interface and port to bind (0.0.0.0 = all interfaces)
connect_address: Address that other nodes use to connect

4.2. Authentication

TEXT

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.1.11:8008
  authentication:
    username: admin
    password: secret_password

When is authentication needed?:

REST API exposed to internet
Compliance requirements
Multi-tenant environments

Use with curl:

TEXT

curl -u admin:secret_password http://10.0.1.11:8008/

4.3. SSL/TLS

TEXT

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.1.11:8008
  certfile: /etc/patroni/ssl/server.crt
  keyfile: /etc/patroni/ssl/server.key
  cafile: /etc/patroni/ssl/ca.crt
  verify_client: required  # none, optional, required

Generate self-signed certificates:

TEXT

# CA
openssl genrsa -out ca.key 4096
openssl req -new -x509 -days 3650 -key ca.key -out ca.crt

# Server certificate
openssl genrsa -out server.key 4096
openssl req -new -key server.key -out server.csr
openssl x509 -req -days 365 -in server.csr -CA ca.crt -CAkey ca.key -set_serial 01 -out server.crt

4.4. REST API endpoints

Health check endpoints:

TEXT

# General health
GET /health
# Returns: 200 if healthy, 503 if not

# Primary check
GET /primary
GET /master  # deprecated
# Returns: 200 if primary, 503 if not

# Replica check
GET /replica
# Returns: 200 if replica, 503 if primary or unhealthy

# Read-only check (replica or sync standby)
GET /read-only
# Returns: 200 if can serve reads

# Synchronous standby check
GET /synchronous
GET /sync
# Returns: 200 if synchronous standby

Management endpoints:

TEXT

# Restart PostgreSQL
POST /restart

# Reload configuration
POST /reload

# Reinitialize
POST /reinitialize

5. Section: Bootstrap

5.1. DCS settings

TEXT

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    maximum_lag_on_syncnode: 1048576
    synchronous_mode: false
    synchronous_mode_strict: false

TTL (Time To Live):

TEXT

ttl: 30  # seconds

Leader lock expiration time
If leader does not renew within TTL → lock expires
Tradeoff:
- Low TTL (10s): Fast failover, but risk of false positives
- High TTL (60s): More stable, but longer downtime
Recommended: 30 seconds

loop_wait:

TEXT

loop_wait: 10  # seconds

Interval between health checks
Leader renews lock every loop_wait seconds
Recommended: 10 seconds (1/3 of TTL)

retry_timeout:

TEXT

retry_timeout: 10  # seconds

Timeout for DCS operations
If DCS does not respond within timeout → consider failed
Recommended: 10 seconds

maximum_lag_on_failover:

TEXT

maximum_lag_on_failover: 1048576  # bytes (1MB)

Max replication lag to be eligible for promotion
Replica with lag > threshold will not be chosen as primary
0 = no limit (any replica can be promoted)
Recommended: 1MB for zero data loss preference

synchronous_mode:

TEXT

synchronous_mode: false
synchronous_mode_strict: false

false: Asynchronous replication (default)
true: Enable synchronous replication
synchronous_mode_strict: Strict sync mode (no writes if no sync standby)

5.2. PostgreSQL bootstrap parameters

TEXT

bootstrap:
  dcs:
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        # Replication
        wal_level: replica
        hot_standby: "on"
        wal_keep_size: "1GB"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
        
        # Archiving (optional)
        archive_mode: "on"
        archive_timeout: 300
        archive_command: "cp %p /var/lib/postgresql/18/archive/%f"
        
        # Performance
        shared_buffers: "2GB"
        effective_cache_size: "6GB"
        maintenance_work_mem: "512MB"
        checkpoint_completion_target: 0.9
        wal_buffers: "16MB"
        default_statistics_target: 100
        random_page_cost: 1.1
        effective_io_concurrency: 200
        work_mem: "16MB"
        min_wal_size: "1GB"
        max_wal_size: "4GB"
        max_worker_processes: 4
        max_parallel_workers_per_gather: 2
        max_parallel_workers: 4
        max_parallel_maintenance_workers: 2

use_pg_rewind:

TEXT

use_pg_rewind: true

Enable automatic recovery with pg_rewind
Faster recovery when rejoining cluster
Requires: wal_log_hints = on or data checksums

use_slots:

TEXT

use_slots: true

Create replication slots automatically
Prevent WAL deletion when replica lags
Recommended: true

5.3. initdb options

TEXT

bootstrap:
  initdb:
    - encoding: UTF8
    - locale: en_US.UTF-8
    - data-checksums
    - auth-host: scram-sha-256
    - auth-local: peer

Common options:

encoding: Character encoding (UTF8 recommended)
locale: System locale
data-checksums: Enable page checksums (detect corruption)
auth-host: Default authentication method for host connections
auth-local: Default authentication method for local connections

Note: initdb only runs when bootstrapping cluster for the first time.

5.4. pg_hba configuration

TEXT

bootstrap:
  pg_hba:
    # Local connections
    - local all all peer
    - local all all md5
    
    # Localhost
    - host all all 127.0.0.1/32 scram-sha-256
    - host all all ::1/128 scram-sha-256
    
    # Replication connections
    - host replication replicator 10.0.1.11/32 scram-sha-256
    - host replication replicator 10.0.1.12/32 scram-sha-256
    - host replication replicator 10.0.1.13/32 scram-sha-256
    
    # Application connections
    - host all all 10.0.1.0/24 scram-sha-256
    
    # Allow from specific app servers
    - host myapp myapp_user 10.0.2.0/24 scram-sha-256

Best practices:

✅ Use scram-sha-256 (most secure)
✅ Specific IP addresses/subnets
✅ Separate users for different purposes
❌ Avoid trust method
❌ Avoid 0.0.0.0/0 unless necessary

5.5. Bootstrap users

TEXT

bootstrap:
  users:
    admin:
      password: admin_password_here
      options:
        - createrole
        - createdb
    
    myapp:
      password: myapp_password_here
      options:
        - login
    
    monitoring:
      password: monitoring_password_here
      options:
        - login

User types:

admin: Administrative tasks
application: Application database user
monitoring: Prometheus exporter, etc.
replication: Already handled by Patroni

5.6. Post-bootstrap scripts

TEXT

bootstrap:
  post_bootstrap: /etc/patroni/scripts/post_bootstrap.sh
  post_init: /etc/patroni/scripts/post_init.sh

post_bootstrap: Runs after bootstrapping cluster (only on primary) post_init: Runs after initializing database

Example script (/etc/patroni/scripts/post_bootstrap.sh):

TEXT

#!/bin/bash
# Create extensions
psql -U postgres -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
psql -U postgres -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"

# Create databases
psql -U postgres -c "CREATE DATABASE myapp;"

# Grant permissions
psql -U postgres -d myapp -c "GRANT ALL ON SCHEMA public TO myapp;"

6. Section: PostgreSQL

6.1. Connection settings

TEXT

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.1.11:5432
  proxy_address: 10.0.1.100:5432  # Optional: VIP address

listen: Interface for PostgreSQL to listen connect_address: Address for replication connections proxy_address: Virtual IP (HAProxy, pgBouncer)

6.2. Data and binary directories

TEXT

postgresql:
  data_dir: /var/lib/postgresql/18/data
  bin_dir: /usr/lib/postgresql/18/bin
  config_dir: /etc/postgresql/18/main  # Optional
  pgpass: /var/lib/postgresql/.pgpass  # Optional

Notes:

data_dir: Where database files are stored
bin_dir: Where PostgreSQL binaries are located (psql, pg_ctl, etc.)
config_dir: If config files are in a different location from data_dir

6.3. Authentication

TEXT

postgresql:
  authentication:
    replication:
      username: replicator
      password: replicator_password
    
    superuser:
      username: postgres
      password: postgres_password
    
    rewind:
      username: rewind_user
      password: rewind_password

replication: User for streaming replication superuser: Patroni uses to manage PostgreSQL rewind: User for pg_rewind (optional, can use superuser)

Security best practice: Store passwords in environment variables or secrets manager.

6.4. Runtime parameters

TEXT

postgresql:
  parameters:
    # Connection
    max_connections: 200
    superuser_reserved_connections: 3
    
    # Memory
    shared_buffers: "4GB"              # 25% of RAM
    effective_cache_size: "12GB"       # 50-75% of RAM
    maintenance_work_mem: "1GB"
    work_mem: "20MB"
    
    # WAL
    wal_buffers: "16MB"
    min_wal_size: "2GB"
    max_wal_size: "8GB"
    wal_compression: "on"
    
    # Checkpoints
    checkpoint_timeout: "15min"
    checkpoint_completion_target: 0.9
    
    # Query planning
    default_statistics_target: 100
    random_page_cost: 1.1              # SSD
    effective_io_concurrency: 200      # SSD
    
    # Parallel query
    max_worker_processes: 8
    max_parallel_workers_per_gather: 4
    max_parallel_workers: 8
    max_parallel_maintenance_workers: 4
    
    # Logging
    log_line_prefix: "%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h "
    log_checkpoints: "on"
    log_connections: "on"
    log_disconnections: "on"
    log_lock_waits: "on"
    log_temp_files: 0
    log_autovacuum_min_duration: 0
    
    # Auto-vacuum
    autovacuum: "on"
    autovacuum_max_workers: 3
    autovacuum_naptime: "10s"
    
    # Locale
    lc_messages: "en_US.UTF-8"
    lc_monetary: "en_US.UTF-8"
    lc_numeric: "en_US.UTF-8"
    lc_time: "en_US.UTF-8"
    
    # Extensions
    shared_preload_libraries: "pg_stat_statements"

Memory sizing guide:

TEXT

Total RAM: 16GB

shared_buffers: 4GB (25%)
effective_cache_size: 12GB (75%)
maintenance_work_mem: 1GB
work_mem: 20MB × max_connections = 4GB max

6.5. Additional pg_hba entries

TEXT

postgresql:
  pg_hba:
    # Additional entries beyond bootstrap
    - hostssl all all 10.0.3.0/24 scram-sha-256
    - host replication replicator 10.0.4.0/24 scram-sha-256

Merge with entries from bootstrap.pg_hba.

6.6. Callback scripts

TEXT

postgresql:
  callbacks:
    on_reload: /etc/patroni/scripts/on_reload.sh
    on_restart: /etc/patroni/scripts/on_restart.sh
    on_role_change: /etc/patroni/scripts/on_role_change.sh
    on_start: /etc/patroni/scripts/on_start.sh
    on_stop: /etc/patroni/scripts/on_stop.sh

on_role_change example:

TEXT

#!/bin/bash
# /etc/patroni/scripts/on_role_change.sh

ROLE=$1  # 'master' or 'replica'
CLUSTER=$2
LEADER=$3

if [ "$ROLE" = "master" ]; then
    echo "$(date): Promoted to PRIMARY" >> /var/log/patroni/role_changes.log
    
    # Update HAProxy
    curl -X POST http://haproxy:9999/update
    
    # Send notification
    curl -X POST https://slack.webhook.url \
      -d "{\"text\": \"PostgreSQL node promoted to PRIMARY\"}"
else
    echo "$(date): Demoted to REPLICA" >> /var/log/patroni/role_changes.log
fi

6.7. Custom configuration files

TEXT

postgresql:
  custom_conf: /etc/postgresql/18/main/custom.conf

Include custom configuration file.

Example (custom.conf):

TEXT

# Custom settings
statement_timeout = 30000
idle_in_transaction_session_timeout = 60000

6.8. Remove data directory on failover

TEXT

postgresql:
  remove_data_directory_on_rewind_failure: true
  remove_data_directory_on_diverged_timelines: true

Be careful: Deletes data directory if recovery fails.

7.1. Failover tags

TEXT

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

nofailover:

TEXT

nofailover: false  # Node can become primary
nofailover: true   # Node will NEVER become primary

Use case: Replica only used for reporting, analytics.

noloadbalance:

TEXT

noloadbalance: false  # Node can serve read queries
noloadbalance: true   # Node does NOT serve read queries

Use case: Node under maintenance or has issues.

clonefrom:

TEXT

clonefrom: false  # Node can be source for basebackup
clonefrom: true   # Node prioritized as source

Use case: Designated backup node.

nosync:

TEXT

nosync: false  # Node can become synchronous standby
nosync: true   # Node does NOT become synchronous standby

Use case: Async replica in different datacenter.

7.2. Custom tags

TEXT

tags:
  datacenter: dc1
  environment: production
  application: myapp
  version: v1.0.0
  rack: rack1
  zone: us-east-1a

Use cases:

Monitoring and labeling
Custom failover logic
Geographic routing
Multi-tenant identification

7.3. Priority tag

TEXT

tags:
  nofailover: false
  # Higher number = higher priority for promotion
  failover_priority: 100

Example cluster:

TEXT

node1: failover_priority: 100  ← Preferred primary
node2: failover_priority: 50
node3: failover_priority: 10   ← Last resort

8. Section: Watchdog

8.1. Basic watchdog configuration

TEXT

watchdog:
  mode: required     # off, automatic, required
  device: /dev/watchdog
  safety_margin: 5

Modes:

off: Disable watchdog
automatic: Use if available
required: Fail if watchdog not available

8.2. Hardware watchdog

Check watchdog availability:

TEXT

ls -l /dev/watchdog
# crw------- 1 root root 10, 130 ... /dev/watchdog

Load watchdog module:

TEXT

# Load softdog module
sudo modprobe softdog

# Make persistent
echo "softdog" | sudo tee -a /etc/modules

# Verify
lsmod | grep dog
# softdog   ...

Grant access to postgres user:

TEXT

# Create udev rule
sudo tee /etc/udev/rules.d/60-watchdog.rules << EOF
KERNEL=="watchdog", OWNER="postgres", GROUP="postgres", MODE="0660"
EOF

# Reload udev
sudo udevadm control --reload-rules
sudo udevadm trigger

8.3. Why use watchdog?

Split-brain prevention:

Patroni hangs but PostgreSQL still running
Network issue: Patroni loses DCS but node alive
Watchdog reboots node → Prevent zombie primary

Flow:

TEXT

1. Patroni healthy → Kicks watchdog every 10s
2. Patroni hangs/loses DCS → Stops kicking
3. Watchdog timeout (safety_margin) → Reboot node
4. Node reboots → No zombie primary

9. Section: Synchronous Replication

9.1. Enable synchronous mode

TEXT

bootstrap:
  dcs:
    synchronous_mode: true
    synchronous_mode_strict: false
    synchronous_node_count: 1

synchronous_mode: Enable sync replication synchronous_mode_strict: Primary refuses writes if no sync standby synchronous_node_count: Number of sync standbys (≥1)

9.2. Synchronous mode variants

Async (default):

TEXT

synchronous_mode: false

Fast writes
Risk data loss if primary fails

Synchronous:

TEXT

synchronous_mode: true
synchronous_mode_strict: false

Wait for 1 standby confirmation
Degrade to async if no standbys

Strict synchronous:

TEXT

synchronous_mode: true
synchronous_mode_strict: true

REFUSE writes if no sync standby
Zero data loss guarantee
Risk availability impact

9.3. Multiple synchronous standbys

TEXT

bootstrap:
  dcs:
    synchronous_mode: true
    synchronous_node_count: 2  # Wait for 2 standbys

PostgreSQL 18 supports:

TEXT

synchronous_standby_names = 'FIRST 2 (node2, node3, node4)'
-- or
synchronous_standby_names = 'ANY 2 (node2, node3, node4)'

10. Complete Configuration Example

10.1. Production-grade patroni.yml

TEXT

scope: postgres-prod
namespace: /service/
name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 10.0.1.11:8008
  authentication:
    username: admin
    password: ${PATRONI_RESTAPI_PASSWORD}
  certfile: /etc/patroni/ssl/server.crt
  keyfile: /etc/patroni/ssl/server.key

etcd:
  hosts: 10.0.1.11:2379,10.0.1.12:2379,10.0.1.13:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    maximum_lag_on_syncnode: 1048576
    synchronous_mode: true
    synchronous_mode_strict: false
    
    postgresql:
      use_pg_rewind: true
      use_slots: true
      
      parameters:
        # Replication
        wal_level: replica
        hot_standby: "on"
        wal_keep_size: "2GB"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_log_hints: "on"
        
        # Performance
        max_connections: 200
        shared_buffers: "4GB"
        effective_cache_size: "12GB"
        maintenance_work_mem: "1GB"
        work_mem: "20MB"
        wal_buffers: "16MB"
        checkpoint_completion_target: 0.9
        
        # Logging
        logging_collector: "on"
        log_directory: "log"
        log_filename: "postgresql-%Y-%m-%d_%H%M%S.log"
        log_line_prefix: "%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h "
        log_checkpoints: "on"
        log_connections: "on"
        log_disconnections: "on"
        log_min_duration_statement: 1000
        
        # Extensions
        shared_preload_libraries: "pg_stat_statements"
  
  initdb:
    - encoding: UTF8
    - locale: en_US.UTF-8
    - data-checksums
  
  pg_hba:
    - local all all peer
    - host replication replicator 10.0.1.11/32 scram-sha-256
    - host replication replicator 10.0.1.12/32 scram-sha-256
    - host replication replicator 10.0.1.13/32 scram-sha-256
    - host all all 10.0.1.0/24 scram-sha-256
    - hostssl all all 0.0.0.0/0 scram-sha-256
  
  users:
    admin:
      password: ${ADMIN_PASSWORD}
      options:
        - createrole
        - createdb

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 10.0.1.11:5432
  data_dir: /var/lib/postgresql/18/data
  bin_dir: /usr/lib/postgresql/18/bin
  
  authentication:
    replication:
      username: replicator
      password: ${REPLICATION_PASSWORD}
    superuser:
      username: postgres
      password: ${POSTGRES_PASSWORD}
  
  parameters:
    unix_socket_directories: "/var/run/postgresql"
  
  callbacks:
    on_role_change: /etc/patroni/scripts/on_role_change.sh
    on_start: /etc/patroni/scripts/on_start.sh
    on_stop: /etc/patroni/scripts/on_stop.sh

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false
  datacenter: dc1
  environment: production
  failover_priority: 100

watchdog:
  mode: automatic
  device: /dev/watchdog
  safety_margin: 5

10.2. Environment variables

TEXT

# /etc/patroni/patroni.env
export PATRONI_RESTAPI_PASSWORD="secure_api_password"
export ADMIN_PASSWORD="secure_admin_password"
export REPLICATION_PASSWORD="secure_replication_password"
export POSTGRES_PASSWORD="secure_postgres_password"

Load in systemd:

TEXT

[Service]
EnvironmentFile=/etc/patroni/patroni.env

11. Summary

Key Takeaways

✅ Configuration layers: Command line > Env > Config file > DCS

✅ Static config: Node-specific, requires restart

✅ Dynamic config: Cluster-wide, update via patronictl edit-config

✅ Bootstrap: One-time initialization settings

✅ Tags: Control failover behavior and node roles

✅ Sync replication: Balance between durability and availability

Best Practices Checklist

Use environment variables for passwords
Enable use_pg_rewind with wal_log_hints: on
Set appropriate ttl, loop_wait, retry_timeout
Configure maximum_lag_on_failover for zero data loss
Use data-checksums in initdb
Set up callback scripts for notifications
Configure watchdog for split-brain prevention
Use scram-sha-256 authentication
Document custom tags and their meanings
Regular backup of configuration files

Preparation for Lesson 9

Lesson 9 will bootstrap the cluster for the first time with the prepared configuration:

Start Patroni on 3 nodes
Verify cluster formation
Test basic operations
Troubleshoot common issues

Course

PostgreSQL High Availability A-Z

Share this article

You might also like