flowchart LR
    A[Client 寫入] --> B[Primary 主庫(kù)]
    B --> C[生成 WAL 日志]
    C --> D[Replica 從庫(kù)]
    D --> E[重放 WAL 同步數(shù)據(jù)]

#!/bin/bash

PRIMARY_IP="192.168.1.10"
REPLICA_IP="192.168.1.11"
REPL_USER="repl_user"
REPL_PASS="YourStrongPassword"

set -e

echo "=== PostgreSQL 一主一從自動(dòng)化部署腳本 ==="
echo "1. 安裝并配置為主庫(kù) (Primary)"
echo "2. 安裝并配置為從庫(kù) (Replica)"
read -p "請(qǐng)選擇模式 (1/2): " MODE

sudo apt update
sudo apt install -y postgresql postgresql-contrib

PG_VER=$(psql --version | grep -oE '[0-9]+' | head -1)
DATA_DIR="/var/lib/postgresql/$PG_VER/main"
CONF_DIR="/etc/postgresql/$PG_VER/main"

if [ "$MODE" == "1" ]; then

    sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/ " $CONF_DIR/postgresql.conf

    sudo -u postgres psql -c "CREATE ROLE $REPL_USER WITH REPLICATION LOGIN ENCRYPTED PASSWORD '$REPL_PASS';"

    echo "host replication $REPL_USER $REPLICA_IP/32 md5" | sudo tee -a $CONF_DIR/pg_hba.conf

    sudo systemctl restart postgresql

elif [ "$MODE" == "2" ]; then

    sudo systemctl stop postgresql
    sudo rm -rf $DATA_DIR/*

    sudo -u postgres PGPASSWORD=$REPL_PASS pg_basebackup \
        -h $PRIMARY_IP \
        -D $DATA_DIR \
        -U $REPL_USER \
        -v -P -R \
        --slot=replica_slot_1 -C

    sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/ " $CONF_DIR/postgresql.conf

    sudo systemctl start postgresql

else
    echo "無(wú)效選項(xiàng)"
    exit 1
fi

sudo systemctl status postgresql --no-pager

set -e

PG_VER=$(psql --version | grep -oE '[0-9]+' | head -1)

/var/lib/postgresql/16/main

CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'xxx';

pg_basebackup -h 主庫(kù)IP -D 數(shù)據(jù)目錄 -U 用戶 -v -P -R --slot=replica_slot_1 -C

ufw status

SELECT * FROM pg_replication_slots;

wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

SELECT * FROM pg_stat_replication;

streaming

SELECT pg_is_in_recovery();

t