PostgreSQL High Availability với Patroni & etcd

Phần 2: Cài đặt & Cấu hình

xdev.asia

Mục tiêu

Sau bài học này, bạn sẽ:

• Hiểu sâu về từng section trong file patroni.yml
• Cấu hình bootstrap options
• Tuning PostgreSQL parameters cho HA
• Cấu hình authentication và security
• Sử dụng tags và constraints
• Tối ưu các timing parameters

1. Tổng quan về Patroni Configuration

1.1. Configuration layers

Patroni có nhiều layers cấu hình:

┌─────────────────────────────────────┐
│   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 (trong patroni.yml):

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

Dynamic configuration (trong DCS):

• PostgreSQL parameters
• Bootstrap settings
• TTL, loop_wait, retry_timeout
• Có thể update runtime: patronictl edit-config

2. Section: Scope và Namespace

2.1. Scope (Cluster name)

scope: postgres

Scope là tên unique của cluster trong DCS.

Ý nghĩa:

• Tất cả nodes trong cùng cluster phải có cùng scope
• DCS keys được prefix với scope: /service/postgres/...
• Cho phép nhiều clusters trên cùng etcd cluster

Best practices:

# Development
scope: postgres-dev
Staging
scope: postgres-staging
Production
scope: postgres-prod
Multi-tenant
scope: customer1-postgres
scope: customer2-postgres

2.2. Namespace

namespace: /service/

Namespace là prefix cho tất cả keys trong DCS.

Full DCS key structure:

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

Multiple clusters example:

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

3. Section: Node Information

3.1. Node name

name: node1

Requirements:

• Unique trong cluster
• Không đổi sau khi bootstrap
• Nên dùng hostname hoặc FQDN

Ví dụ naming conventions:

# 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

# Optional: Override hostname
host: 10.0.1.11

Patroni tự động detect hostname, nhưng có thể override nếu cần.

4. Section: REST API

4.1. Basic configuration

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

Parameters:

• listen: Interface và port để bind (0.0.0.0 = all interfaces)
• connect_address: Address mà các nodes khác dùng để connect

4.2. Authentication

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

Khi nào cần authentication?:

• REST API exposed ra internet
• Compliance requirements
• Multi-tenant environments

Sử dụng với curl:

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

4.3. SSL/TLS

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:

# 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:

# 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:

# Restart PostgreSQL
POST /restart

Reload configuration
POST /reload
Reinitialize
POST /reinitialize

5. Section: Bootstrap

5.1. DCS settings

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):

ttl: 30  # seconds

• Leader lock expiration time
• Nếu leader không renew trong TTL → lock expires
• Tradeoff:
  • TTL thấp (10s): Failover nhanh, nhưng risk false positives
  • TTL cao (60s): Ổn định hơn, nhưng downtime lâu hơn
• Recommended: 30 seconds

loop_wait:

loop_wait: 10  # seconds

• Interval giữa các health checks
• Leader renews lock mỗi loop_wait seconds
• Recommended: 10 seconds (1/3 của TTL)

retry_timeout:

retry_timeout: 10  # seconds

• Timeout cho DCS operations
• Nếu DCS không respond trong timeout → consider failed
• Recommended: 10 seconds

maximum_lag_on_failover:

maximum_lag_on_failover: 1048576  # bytes (1MB)

• Max replication lag để eligible for promotion
• Replica với lag > threshold sẽ không được chọn làm primary
• 0 = no limit (any replica can be promoted)
• Recommended: 1MB cho zero data loss preference

synchronous_mode:

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

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:

use_pg_rewind: true

• Enable automatic recovery với pg_rewind
• Faster recovery khi rejoining cluster
• Requires: wal_log_hints = on hoặc data checksums

use_slots:

use_slots: true

• Create replication slots tự động
• Prevent WAL deletion khi replica lag
• Recommended: true

5.3. initdb options

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

Lưu ý: initdb chỉ chạy khi bootstrap cluster lần đầu.

5.4. pg_hba configuration

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

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

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

post_bootstrap: Chạy sau khi bootstrap cluster (chỉ trên primary) post_init: Chạy sau khi initialize database

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

#!/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

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 để PostgreSQL listen connect_address: Address để replication connections proxy_address: Virtual IP (HAProxy, pgBouncer)

6.2. Data and binary directories

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

Lưu ý:

• data_dir: Nơi lưu database files
• bin_dir: Nơi chứa PostgreSQL binaries (psql, pg_ctl, etc.)
• config_dir: Nếu config files ở nơi khác với data_dir

6.3. Authentication

postgresql:
authentication:
replication:
username: replicator
password: replicator_password

superuser:
  username: postgres
  password: postgres_password

rewind:
  username: rewind_user
  password: rewind_password

replication: User cho streaming replication superuser: Patroni dùng để manage PostgreSQL rewind: User cho pg_rewind (optional, có thể dùng superuser)

Security best practice: Store passwords in environment variables hoặc secrets manager.

6.4. Runtime parameters

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:

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

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 với entries từ bootstrap.pg_hba.

6.6. Callback scripts

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:

#!/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

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

Include custom configuration file.

Example (custom.conf):

# Custom settings
statement_timeout = 30000
idle_in_transaction_session_timeout = 60000

6.8. Remove data directory on failover

postgresql:
remove_data_directory_on_rewind_failure: true
remove_data_directory_on_diverged_timelines: true

Cẩn thận: Xóa data directory nếu recovery fail.

7. Section: Tags

7.1. Failover tags

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

nofailover:

nofailover: false  # Node có thể become primary
nofailover: true   # Node KHÔNG BAO GIỜ become primary

Use case: Replica chỉ dùng cho reporting, analytics.

noloadbalance:

noloadbalance: false  # Node có thể serve read queries
noloadbalance: true   # Node KHÔNG serve read queries

Use case: Node đang maintenance hoặc có issue.

clonefrom:

clonefrom: false  # Node có thể làm source cho basebackup
clonefrom: true   # Node ưu tiên làm source

Use case: Designated backup node.

nosync:

nosync: false  # Node có thể become synchronous standby
nosync: true   # Node KHÔNG become synchronous standby

Use case: Async replica ở datacenter khác.

7.2. Custom tags

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

Use cases:

• Monitoring và labeling
• Custom failover logic
• Geographic routing
• Multi-tenant identification

7.3. Priority tag

tags:
nofailover: false

Higher number = higher priority for promotion
failover_priority: 100

Example cluster:

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

8. Section: Watchdog

8.1. Basic watchdog configuration

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

Modes:

• off: Disable watchdog
• automatic: Use if available
• required: Fail if watchdog không available

8.2. Hardware watchdog

Check watchdog availability:

ls -l /dev/watchdog

crw------- 1 root root 10, 130 ... /dev/watchdog

Load watchdog module:

# 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:

# 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 nhưng PostgreSQL vẫn chạy
• Network issue: Patroni loses DCS but node alive
• Watchdog reboots node → Prevent zombie primary

Flow:

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

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

synchronous_mode: Enable sync replication synchronous_mode_strict: Primary refuses writes nếu không có sync standby synchronous_node_count: Số sync standbys (≥1)

9.2. Synchronous mode variants

Async (default):

synchronous_mode: false

• Fast writes
• Risk data loss nếu primary fails

Synchronous:

synchronous_mode: true
synchronous_mode_strict: false

• Wait for 1 standby confirmation
• Degrade to async nếu không có standbys

Strict synchronous:

synchronous_mode: true
synchronous_mode_strict: true

• REFUSE writes nếu không có sync standby
• Zero data loss guarantee
• Risk availability impact

9.3. Multiple synchronous standbys

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

PostgreSQL 18 supports:

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

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

# /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:

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

11. Tổng kết

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 và node roles

✅ Sync replication: Balance between durability và availability

Best Practices Checklist

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

Chuẩn bị cho Bài 9

Bài 9 sẽ bootstrap cluster lần đầu với configuration đã chuẩn bị:

• Start Patroni trên 3 nodes
• Verify cluster formation
• Test basic operations
• Troubleshoot common issues