PostgreSQL

Clients
  |
PgBouncer (:6432)  -- connection pooling
  |
PostgreSQL (:5432) -- managed by Patroni
  |
etcd (:2379)       -- cluster state and leader election

export DB_PWD='<postgres-super-pw>'
export DB_REPLICA=$(openssl rand -base64 24)
export INIT_PASS=$(openssl rand -base64 24)

export CURRENT_NODE='node01'
export CURRENT_IP='192.0.2.111'

export IP_SQL01='192.0.2.111'
export IP_SQL02='192.0.2.112'
export IP_SQL03='192.0.2.211'

# LVM / filesystem
pvcreate /dev/sdb
vgcreate database /dev/sdb
lvcreate -l 100%FREE -n data database
mkfs.xfs -f -i size=512 /dev/mapper/database-data

# Persistent mounts
cat >> /etc/fstab <<EOF
# PostgreSQL data volume
/dev/mapper/database-data  /database          xfs  defaults  1 2
/database/patroni          /var/lib/patroni   none bind      0 0
EOF

mkdir -p /database/patroni /var/lib/patroni
mount -a

dnf install -y etcd patroni-etcd
firewall-cmd --permanent --add-port=2379/tcp
firewall-cmd --permanent --add-port=2380/tcp
firewall-cmd --reload

cat > /etc/etcd/etcd.conf <<EOF
[member]
ETCD_NAME="${CURRENT_NODE}"
ETCD_DATA_DIR="/var/lib/etcd"
ETCD_LISTEN_PEER_URLS="http://${CURRENT_IP}:2380"
ETCD_LISTEN_CLIENT_URLS="http://${CURRENT_IP}:2379"

[cluster]
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://${CURRENT_IP}:2380"
ETCD_INITIAL_CLUSTER="node1=http://${IP_SQL01}:2380,node2=http://${IP_SQL02}:2380,node3=http://${IP_SQL03}:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="${INIT_PASS}"
ETCD_ADVERTISE_CLIENT_URLS="http://${CURRENT_IP}:2379"
ETCD_ENABLE_V2="true"
EOF

systemctl daemon-reload
systemctl enable --now etcd

dnf install -y postgresql17 postgresql17-server patroni
firewall-cmd --permanent --add-service=postgresql
firewall-cmd --permanent --add-port=8008/tcp
firewall-cmd --reload

mkdir -p /var/lib/patroni/17/data
chown -R postgres:postgres /var/lib/patroni

cat > /etc/patroni/patroni.yml <<EOF
scope: patroni
namespace: /db/
name: ${CURRENT_NODE}

restapi:
  listen: ${CURRENT_IP}:8008
  connect_address: ${CURRENT_IP}:8008

etcd:
  hosts: ${IP_SQL01}:2379,${IP_SQL02}:2379,${IP_SQL03}:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
  initdb:
    - encoding: UTF8
    - data-checksums
  pg_hba:
    - host replication replicator 127.0.0.1/32 scram-sha-256
    - host replication replicator ${IP_SQL01}/24 scram-sha-256
    - host replication replicator ${IP_SQL02}/24 scram-sha-256
    - host replication replicator ${IP_SQL03}/24 scram-sha-256
    - host all all 0.0.0.0/0 scram-sha-256
  users:
    admin:
      password: ${DB_PWD}
      options: [createrole, createdb]

postgresql:
  listen: ${CURRENT_IP}:5432
  connect_address: ${CURRENT_IP}:5432
  bin_dir: /usr/pgsql-17/bin
  data_dir: /var/lib/patroni/17/data
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: ${DB_REPLICA}
    superuser:
      username: postgres
      password: ${DB_PWD}
  parameters:
    unix_socket_directories: '.'

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

cat > /etc/systemd/system/patroni.service <<EOF
[Unit]
Description=Patroni PostgreSQL 17 HA Manager
After=network-online.target
Wants=network-online.target

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

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl enable --now patroni

dnf install -y pgbouncer
firewall-cmd --permanent --add-port=6432/tcp
firewall-cmd --reload
mkdir -p /etc/pgbouncer

cat > /etc/pgbouncer/pgbouncer.ini <<EOF
[databases]
* = host=${CURRENT_IP} port=5432

[pgbouncer]
listen_addr          = ${CURRENT_IP}
listen_port          = 6432
logfile              = /var/log/pgbouncer/pgbouncer.log
pidfile              = /run/pgbouncer/pgbouncer.pid
auth_type            = hba
auth_hba_file        = /var/lib/patroni/17/data/pg_hba.conf
auth_file            = /etc/pgbouncer/.secrets
admin_users          = postgres
stats_users          = stats,postgres
pool_mode            = session
server_reset_query   = DISCARD ALL
max_client_conn      = 256
default_pool_size    = 194
ignore_startup_parameters = options,application_name,extra_float_digits
stats_period         = 1800
EOF

python /etc/pgbouncer/mkauth.py /etc/pgbouncer/.secrets \
  "host=${CURRENT_IP} port=5432 dbname=postgres user=postgres password=${DB_PWD}"
chown -R pgbouncer:pgbouncer /etc/pgbouncer

cat > /etc/systemd/system/pgbouncer.service <<'EOF'
[Unit]
Description=PgBouncer Connection Pooler
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
User=pgbouncer
Environment=BOUNCERCONF=/etc/pgbouncer/pgbouncer.ini
ExecStart=/usr/bin/pgbouncer ${BOUNCERCONF}
ExecReload=/bin/kill -HUP $MAINPID
TimeoutSec=300

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl enable --now pgbouncer

mkdir -p /var/lib/patroni/17/ssl && cd /var/lib/patroni/17/ssl

# CA
openssl genrsa -out ca-key.pem 4096
openssl req -new -x509 -days 3650 -key ca-key.pem -out ca.pem \
  -subj "/CN=PostgreSQL CA"

# Server certificate (SAN must include all node IPs)
openssl genrsa -out server-key.pem 4096
openssl req -new -key server-key.pem -out server-req.pem \
  -subj "/CN=$(hostname)"

cat > server-ext.cnf <<EOF
subjectAltName=IP:${CURRENT_IP},IP:127.0.0.1,DNS:$(hostname)
EOF

openssl x509 -req -days 3650 -in server-req.pem \
  -CA ca.pem -CAkey ca-key.pem -CAcreateserial \
  -out server-cert.pem -extfile server-ext.cnf

chown postgres:postgres /var/lib/patroni/17/ssl/*
chmod 600 /var/lib/patroni/17/ssl/*-key.pem

postgresql:
  parameters:
    ssl: "on"
    ssl_ca_file: /var/lib/patroni/17/ssl/ca.pem
    ssl_cert_file: /var/lib/patroni/17/ssl/server-cert.pem
    ssl_key_file: /var/lib/patroni/17/ssl/server-key.pem
    ssl_min_protocol_version: TLSv1.2

pg_hba:
  - hostssl replication replicator 127.0.0.1/32 scram-sha-256
  - hostssl replication replicator <subnet>/24 scram-sha-256
  - hostssl all all 0.0.0.0/0 scram-sha-256

psql "host=${CURRENT_IP} port=5432 dbname=postgres user=postgres sslmode=verify-full sslrootcert=/var/lib/patroni/17/ssl/ca.pem"

-- Check SSL status of current connection
SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();

postgresql:
  parameters:
    # Shared memory - 25% of total RAM
    shared_buffers: 4GB

    # Per-query work memory
    work_mem: 64MB

    # Maintenance operations (VACUUM, CREATE INDEX)
    maintenance_work_mem: 1GB

    # OS cache reliance
    effective_cache_size: 12GB

    # Background writer
    bgwriter_lru_maxpages: 100
    bgwriter_lru_multiplier: 2.0

postgresql:
  parameters:
    # WAL settings
    wal_level: replica           # 'logical' if using logical replication
    max_wal_size: 4GB
    min_wal_size: 1GB
    wal_buffers: 64MB
    checkpoint_timeout: 15min
    checkpoint_completion_target: 0.9

    # Write-ahead logging
    synchronous_commit: "on"     # 'off' for async (faster, risk of data loss)
    wal_compression: lz4

postgresql:
  parameters:
    max_connections: 200
    max_parallel_workers_per_gather: 4
    max_parallel_workers: 8
    max_worker_processes: 16
    max_parallel_maintenance_workers: 4

postgresql:
  parameters:
    autovacuum_max_workers: 4
    autovacuum_naptime: 30s
    autovacuum_vacuum_threshold: 50
    autovacuum_vacuum_scale_factor: 0.05
    autovacuum_analyze_threshold: 50
    autovacuum_analyze_scale_factor: 0.025
    autovacuum_vacuum_cost_delay: 2ms
    autovacuum_vacuum_cost_limit: 1000

postgresql:
  parameters:
    random_page_cost: 1.1       # Lower for SSD (default 4.0)
    effective_io_concurrency: 200  # Higher for SSD
    default_statistics_target: 200

postgresql:
  parameters:
    wal_level: replica
    archive_mode: "on"
    archive_command: 'rsync -a %p /archive/wal/%f'
    archive_timeout: 300     # Force switch after 5 minutes of inactivity

# Create archive directory
mkdir -p /archive/wal
chown postgres:postgres /archive/wal

pg_basebackup -U replicator -D /archive/base/$(date +%Y%m%d) \
  -Fp -Xs -P -R --checkpoint=fast

# Stop PostgreSQL, restore base backup to data directory
systemctl stop patroni

# Restore the base backup
rm -rf /var/lib/patroni/17/data/*
cp -a /archive/base/20260322/* /var/lib/patroni/17/data/

# Configure recovery target
cat >> /var/lib/patroni/17/data/postgresql.conf <<EOF
restore_command = 'cp /archive/wal/%f %p'
recovery_target_time = '2026-03-22 14:30:00+00'
recovery_target_action = 'promote'
EOF

touch /var/lib/patroni/17/data/recovery.signal
chown -R postgres:postgres /var/lib/patroni/17/data

systemctl start patroni

-- Check if recovery is complete
SELECT pg_is_in_recovery();  -- false = promoted to primary

-- Check the recovery timeline
SELECT timeline_id FROM pg_control_recovery();

# On the publisher (source), set in patroni.yml
postgresql:
  parameters:
    wal_level: logical
    max_replication_slots: 10
    max_wal_senders: 10

-- Replicate all tables in a schema
CREATE PUBLICATION my_pub FOR ALL TABLES;

-- Or specific tables
CREATE PUBLICATION my_pub FOR TABLE orders, customers;

-- With filtering
CREATE PUBLICATION my_pub FOR TABLE orders WHERE (status = 'active');

CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=<source_ip> port=5432 dbname=appdb user=replicator password=<password>'
  PUBLICATION my_pub;

-- On publisher: check replication slots
SELECT slot_name, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots;

-- On subscriber: check subscription status
SELECT subname, received_lsn, latest_end_lsn, latest_end_time
FROM pg_stat_subscription;

-- Check replication lag
SELECT slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag
FROM pg_replication_slots
WHERE slot_type = 'logical';

-- Pause replication
ALTER SUBSCRIPTION my_sub DISABLE;

-- Resume
ALTER SUBSCRIPTION my_sub ENABLE;

-- Drop
DROP SUBSCRIPTION my_sub;

CREATE ROLE app_user WITH LOGIN PASSWORD '<password>';
CREATE DATABASE appdb OWNER app_user;

pg_dump -Fc -U postgres -d appdb > appdb.dump
pg_restore -U postgres -d appdb_restored appdb.dump

GRANT CONNECT ON DATABASE appdb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO app_user;