PostgreSQL HA Setup Guide สำหรับ Production

sudo apt update && sudo apt upgrade -y
sudo apt install -y wget curl gnupg2 software-properties-common

# บน Primary (10.100.81.33)
sudo hostnamectl set-hostname pg-primary
echo "10.100.81.33 pg-primary" | sudo tee -a /etc/hosts
echo "10.100.81.34 pg-standby" | sudo tee -a /etc/hosts

# บน Standby (10.100.81.34)
sudo hostnamectl set-hostname pg-standby
echo "10.100.81.33 pg-primary" | sudo tee -a /etc/hosts
echo "10.100.81.34 pg-standby" | sudo tee -a /etc/hosts

sudo ufw allow 5432/tcp  # PostgreSQL
sudo ufw allow 2379/tcp  # etcd client
sudo ufw allow 2380/tcp  # etcd peer
sudo ufw allow 8008/tcp  # Patroni REST API
sudo ufw allow 5000/tcp  # HAProxy stats
sudo ufw allow 5433/tcp  # HAProxy PostgreSQL

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt update

sudo apt install -y postgresql-17 postgresql-contrib-17
sudo systemctl stop postgresql
sudo systemctl disable postgresql

sudo apt install -y etcd

# บน Primary (10.100.81.33)
sudo tee /etc/default/etcd << EOF
ETCD_NAME="etcd1"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_LISTEN_PEER_URLS="http://10.100.81.33:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.100.81.33:2379,http://127.0.0.1:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.100.81.33:2380"
ETCD_INITIAL_CLUSTER="etcd1=http://10.100.81.33:2380,etcd2=http://10.100.81.34:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster-token"
ETCD_ADVERTISE_CLIENT_URLS="http://10.100.81.33:2379"
EOF

# บน Standby (10.100.81.34)
sudo tee /etc/default/etcd << EOF
ETCD_NAME="etcd2"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_LISTEN_PEER_URLS="http://10.100.81.34:2380"
ETCD_LISTEN_CLIENT_URLS="http://10.100.81.34:2379,http://127.0.0.1:2379"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.100.81.34:2380"
ETCD_INITIAL_CLUSTER="etcd1=http://10.100.81.33:2380,etcd2=http://10.100.81.34:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster-token"
ETCD_ADVERTISE_CLIENT_URLS="http://10.100.81.34:2379"
EOF

sudo systemctl enable etcd
sudo systemctl start etcd
sudo systemctl status etcd

sudo apt install -y python3-pip python3-dev
sudo pip3 install patroni[etcd] psycopg2-binary

# บน Primary (10.100.81.33)
sudo tee /etc/patroni/patroni.yml << EOF
scope: postgres-cluster
namespace: /db/
name: pg-primary

restapi:
  listen: 10.100.81.33:8008
  connect_address: 10.100.81.33:8008

etcd:
  hosts: 10.100.81.33:2379,10.100.81.34:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 30
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        max_connections: 200
        shared_buffers: 4GB
        effective_cache_size: 12GB
        work_mem: 20MB
        maintenance_work_mem: 1GB
        checkpoint_completion_target: 0.9
        wal_buffers: 16MB
        default_statistics_target: 100
        random_page_cost: 1.1
        effective_io_concurrency: 200
        min_wal_size: 2GB
        max_wal_size: 8GB
        max_worker_processes: 6
        max_parallel_workers_per_gather: 3
        max_parallel_workers: 6
        max_parallel_maintenance_workers: 3
        hot_standby: on
        wal_level: replica
        max_wal_senders: 3
        max_replication_slots: 3
        wal_keep_segments: 100
        logging_collector: on
        log_destination: stderr
        log_directory: /var/log/postgresql
        log_filename: postgresql-%Y-%m-%d_%H%M%S.log
        log_rotation_age: 1d
        log_rotation_size: 100MB
        log_min_duration_statement: 1000
        log_line_prefix: '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
        log_lock_waits: on
        log_statement: ddl
        log_temp_files: 0
        
  initdb:
  - encoding: UTF8
  - data-checksums
  
  pg_hba:
  - host replication replicator 10.100.81.33/32 md5
  - host replication replicator 10.100.81.34/32 md5
  - host all all 0.0.0.0/0 md5
  - local all postgres peer
  - local all all peer
  
  users:
    admin:
      password: your_admin_password
      options:
        - createrole
        - createdb

postgresql:
  listen: 10.100.81.33:5432
  connect_address: 10.100.81.33:5432
  data_dir: /var/lib/postgresql/17/main
  bin_dir: /usr/lib/postgresql/17/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replicator_password
    superuser:
      username: postgres
      password: postgres_password
  parameters:
    unix_socket_directories: /var/run/postgresql

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

# บน Standby (10.100.81.34)
sudo tee /etc/patroni/patroni.yml << EOF
scope: postgres-cluster
namespace: /db/
name: pg-standby

restapi:
  listen: 10.100.81.34:8008
  connect_address: 10.100.81.34:8008

etcd:
  hosts: 10.100.81.33:2379,10.100.81.34:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 30
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        max_connections: 200
        shared_buffers: 4GB
        effective_cache_size: 12GB
        work_mem: 20MB
        maintenance_work_mem: 1GB
        checkpoint_completion_target: 0.9
        wal_buffers: 16MB
        default_statistics_target: 100
        random_page_cost: 1.1
        effective_io_concurrency: 200
        min_wal_size: 2GB
        max_wal_size: 8GB
        max_worker_processes: 6
        max_parallel_workers_per_gather: 3
        max_parallel_workers: 6
        max_parallel_maintenance_workers: 3
        hot_standby: on
        wal_level: replica
        max_wal_senders: 3
        max_replication_slots: 3
        wal_keep_segments: 100
        logging_collector: on
        log_destination: stderr
        log_directory: /var/log/postgresql
        log_filename: postgresql-%Y-%m-%d_%H%M%S.log
        log_rotation_age: 1d
        log_rotation_size: 100MB
        log_min_duration_statement: 1000
        log_line_prefix: '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
        log_lock_waits: on
        log_statement: ddl
        log_temp_files: 0

postgresql:
  listen: 10.100.81.34:5432
  connect_address: 10.100.81.34:5432
  data_dir: /var/lib/postgresql/17/main
  bin_dir: /usr/lib/postgresql/17/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replicator_password
    superuser:
      username: postgres
      password: postgres_password
  parameters:
    unix_socket_directories: /var/run/postgresql

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

sudo tee /etc/systemd/system/patroni.service << EOF
[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target

[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni/patroni.yml
KillMode=process
TimeoutSec=30
Restart=no

[Install]
WantedBy=multi-user.target
EOF

sudo mkdir -p /etc/patroni /var/log/postgresql
sudo chown -R postgres:postgres /etc/patroni /var/lib/postgresql /var/log/postgresql
sudo chmod 750 /var/lib/postgresql

sudo apt install -y haproxy

sudo tee /etc/haproxy/haproxy.cfg << EOF
global
    maxconn 100
    log 127.0.0.1:514 local0

defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind *:5000
    stats enable
    stats uri /

listen postgres
    bind *:5433
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-primary 10.100.81.33:5432 maxconn 100 check port 8008
    server pg-standby 10.100.81.34:5432 maxconn 100 check port 8008

listen postgres-replica
    bind *:5434
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-primary 10.100.81.33:5432 maxconn 100 check port 8008
    server pg-standby 10.100.81.34:5432 maxconn 100 check port 8008 backup
EOF

sudo apt install -y haproxy keepalived

sudo tee /etc/haproxy/haproxy.cfg << EOF
global
    maxconn 100
    log 127.0.0.1:514 local0

defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind *:5000
    stats enable
    stats uri /

listen postgres
    bind *:5433
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-primary 10.100.81.33:5432 maxconn 100 check port 8008
    server pg-standby 10.100.81.34:5432 maxconn 100 check port 8008

listen postgres-replica
    bind *:5434
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-primary 10.100.81.33:5432 maxconn 100 check port 8008
    server pg-standby 10.100.81.34:5432 maxconn 100 check port 8008 backup
EOF

# บน Primary (10.100.81.33)
sudo tee /etc/keepalived/keepalived.conf << EOF
vrrp_script chk_haproxy {
    script "/bin/kill -0 \`cat /var/run/haproxy.pid\`"
    interval 2
    weight 2
    fall 2
    rise 1
}

vrrp_instance VI_1 {
    state MASTER
    interface ens18    # เปลี่ยนตาม interface ของคุณ
    virtual_router_id 51
    priority 110
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass your_password_here
    }
    virtual_ipaddress {
        10.100.81.35/24    # Virtual IP สำหรับ HAProxy
    }
    track_script {
        chk_haproxy
    }
}
EOF

# บน Standby (10.100.81.34)
sudo tee /etc/keepalived/keepalived.conf << EOF
vrrp_script chk_haproxy {
    script "/bin/kill -0 \`cat /var/run/haproxy.pid\`"
    interval 2
    weight 2
    fall 2
    rise 1
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens18    # เปลี่ยนตาม interface ของคุณ
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass your_password_here
    }
    virtual_ipaddress {
        10.100.81.35/24    # Virtual IP เดียวกัน
    }
    track_script {
        chk_haproxy
    }
}
EOF

# เพิ่มใน HAProxy config บนทั้ง 2 เครื่อง
sudo tee -a /etc/haproxy/haproxy.cfg << EOF

# Bind to virtual IP
listen postgres-vip
    bind 10.100.81.35:5433
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server pg-primary 10.100.81.33:5432 maxconn 100 check port 8008
    server pg-standby 10.100.81.34:5432 maxconn 100 check port 8008
EOF

# Spring Boot Application Properties
spring.datasource.url=jdbc:postgresql://10.100.81.33:5432,10.100.81.34:5432/your_database?targetServerType=primary&loadBalanceHosts=true
spring.datasource.username=your_app_user
spring.datasource.password=your_app_password

# Connection Pool Settings
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.connection-timeout=5000
spring.datasource.hikari.validation-timeout=3000
spring.datasource.hikari.login-timeout=5

@Component
public class PostgreSQLHealthService {
    
    @Value("${spring.datasource.url}")
    private String primaryUrl;
    
    @Autowired
    private DataSource dataSource;
    
    @EventListener
    @Async
    public void handleConnectionFailure(ConnectionFailureEvent event) {
        // Custom logic to handle failover
        // อาจจะ refresh connection pool หรือ notify monitoring
    }
    
    @Scheduled(fixedDelay = 30000)
    public void checkDatabaseHealth() {
        try (Connection conn = dataSource.getConnection()) {
            try (PreparedStatement stmt = conn.prepareStatement("SELECT 1")) {
                stmt.executeQuery();
            }
        } catch (SQLException e) {
            log.warn("Database health check failed", e);
        }
    }
}

# บน Primary
sudo systemctl enable patroni
sudo systemctl start patroni
sudo systemctl status patroni

# รอ 30 วินาที แล้วเริ่ม Standby
# บน Standby
sudo systemctl enable patroni
sudo systemctl start patroni
sudo systemctl status patroni

# บน Primary
sudo systemctl enable patroni
sudo systemctl start patroni
sudo systemctl status patroni

# รอ 30 วินาที แล้วเริ่ม Standby
# บน Standby
sudo systemctl enable patroni
sudo systemctl start patroni
sudo systemctl status patroni

sudo systemctl enable haproxy
sudo systemctl restart haproxy
sudo systemctl status haproxy

# บนทั้ง 2 เครื่อง
sudo systemctl enable haproxy keepalived
sudo systemctl start haproxy
sudo systemctl start keepalived
sudo systemctl status haproxy keepalived

# ตรวจสอบ Virtual IP
ip addr show | grep 10.100.81.35

sudo -u postgres patronictl -c /etc/patroni/patroni.yml list

# Test Primary connection
psql -h 10.100.81.33 -p 5432 -U postgres -c "SELECT version();"

# Test via HAProxy
psql -h <haproxy_ip> -p 5433 -U postgres -c "SELECT version();"

# HAProxy endpoint (เปลี่ยน <haproxy_ip> เป็น IP ของเครื่อง HAProxy)
spring.datasource.url=jdbc:postgresql://<haproxy_ip>:5433/your_database
spring.datasource.username=your_app_user
spring.datasource.password=your_app_password

# Connection Pool Settings
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.leak-detection-threshold=60000

# Read-only replica (optional)
spring.datasource.replica.url=jdbc:postgresql://<haproxy_ip>:5434/your_database
spring.datasource.replica.username=your_app_user
spring.datasource.replica.password=your_app_password

# Virtual IP endpoint - จะ auto failover
spring.datasource.url=jdbc:postgresql://10.100.81.35:5433/your_database
spring.datasource.username=your_app_user
spring.datasource.password=your_app_password

# Connection Pool Settings (เหมือนกัน)
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.leak-detection-threshold=60000

# Multiple hosts with automatic failover
spring.datasource.url=jdbc:postgresql://10.100.81.33:5432,10.100.81.34:5432/your_database?targetServerType=primary&loadBalanceHosts=true&connectTimeout=5&socketTimeout=30&loginTimeout=5
spring.datasource.username=your_app_user
spring.datasource.password=your_app_password

# Shorter timeouts for faster failover detection
spring.datasource.hikari.minimum-idle=3
spring.datasource.hikari.maximum-pool-size=15
spring.datasource.hikari.connection-timeout=5000
spring.datasource.hikari.validation-timeout=3000
spring.datasource.hikari.leak-detection-threshold=30000
spring.datasource.hikari.max-lifetime=900000

# Health check settings
management.health.db.enabled=true
management.endpoint.health.show-details=always

# Retry configuration
spring.datasource.hikari.initialization-fail-timeout=-1
spring.datasource.continue-on-error=false

# Logging for troubleshooting
logging.level.com.zaxxer.hikari=DEBUG
logging.level.org.postgresql=DEBUG

-- เชื่อมต่อเป็น postgres user
CREATE DATABASE your_database;
CREATE USER your_app_user WITH PASSWORD 'your_app_password';
GRANT ALL PRIVILEGES ON DATABASE your_database TO your_app_user;

-- สำหรับ Spring Boot JPA
ALTER USER your_app_user CREATEDB;

# Check cluster status
sudo -u postgres patronictl -c /etc/patroni/patroni.yml list

# Manual failover
sudo -u postgres patronictl -c /etc/patroni/patroni.yml failover

# Restart PostgreSQL
sudo -u postgres patronictl -c /etc/patroni/patroni.yml restart postgres-cluster

# Check replication lag
sudo -u postgres psql -c "SELECT client_addr, state, sync_state, replay_lag FROM pg_stat_replication;"

# Daily backup script
#!/bin/bash
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
sudo -u postgres pg_dump -h localhost -p 5432 your_database | gzip > $BACKUP_DIR/backup_$DATE.sql.gz

# Keep only 7 days of backups
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +7 -delete

# Add to /etc/sysctl.conf
vm.swappiness = 1
vm.dirty_background_ratio = 3
vm.dirty_ratio = 10
kernel.shmmax = 8589934592
kernel.shmall = 2097152