Руководство по настройке БД с репликацией ручная настройка#

Назначение#

Данное руководство описывает процесс настройки репликации PostgreSQL между основным сервером и репликами с использованием автоматизированного скрипта. Решение обеспечивает отказоустойчивость и распределение нагрузки без использования сложных оркестраторов.

Аудитория#

Руководство предназначено для системных администраторов и администраторов баз данных, которым требуется настроить репликацию PostgreSQL в средах, где развертывание полноценного кластера с Patroni нецелесообразно.

Архитектура решения#

Архитектура решения

Предварительные требования#

Аппаратные требования#

3 сервера БД с минимум 4 CPU, 4GB RAM, 50GB SSD каждый.
Сетевые требования: стабильная сеть с задержкой < 1ms между узлами.

Программные требования#

ОС: Debian 12.
PostgreSQL: версия 17.
Доступ: SSH доступ с правами sudo на всех серверах.
Сеть: открытые порты между узлами:
- 5432 (PostgreSQL).
- 22 (SSH).

Конфигурация#

Версия PostgreSQL: 17.
Пользователь для репликации: replicator.
Пароль для репликации: StrongPassword123!.
Пользователь PostgreSQL: postgres.
Пользователь БД: global.
Пароль пользователя БД: globalpass.

IP-адреса узлов:

Primary сервер: 192.168.184.151.
Replica 1: 192.168.184.152.
Replica 2: 192.168.184.153.

Подготовка серверов#

На всех серверах выполнить:#

# Обновление системы
sudo apt update && sudo apt upgrade -y

# Установка PostgreSQL 17
sudo apt install -y postgresql-17 postgresql-client-17

# Проверка статуса
sudo systemctl status postgresql@17-main

На Primary сервере (192.168.184.151):#

Убедитесь, что база данных уже создана:

# Проверка существующих баз данных
sudo -u postgres psql -c "\l"

# Если базы нет, создайте ее
sudo -u postgres psql -c "CREATE DATABASE replicatest OWNER global;"

Автоматизированный скрипт настройки репликации#

Создайте файл setup_replication.sh на Primary сервере:

#!/bin/bash

# Конфигурация
USERNAME="admin"
PRIMARY_IP="192.168.184.151"
REPLICA1_IP="192.168.184.152"
REPLICA2_IP="192.168.184.153"
REPLICA_USER="replicator"
REPLICA_PASSWORD="StrongPassword123!"
POSGRES_USER="postgres"
POSGRES_PASS="12345"
BD_USER="global"
BD_PASS="globalpass"
PG_VERSION="17"
DATA_DIR="/var/lib/postgresql/${PG_VERSION}/main"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

echo "=== Восстановление исходного PRIMARY сервера ==="

# 1. Останавливаем PostgreSQL на текущем primary
echo "Остановка PostgreSQL..."
sudo systemctl stop postgresql@${PG_VERSION}-main

# 2. Создаем бэкапы и удаляем файлы, указывающие на режим реплики
echo "Бэкап и удаление файлов реплики..."
sudo -u postgres mv "${DATA_DIR}/standby.signal" "${DATA_DIR}/standby.signal.${TIMESTAMP}.bak" 2>/dev/null || true
sudo -u postgres mv "${DATA_DIR}/recovery.signal" "${DATA_DIR}/recovery.signal.${TIMESTAMP}.bak" 2>/dev/null || true
sudo -u postgres mv "${DATA_DIR}/postgresql.auto.conf" "${DATA_DIR}/postgresql.auto.conf.${TIMESTAMP}.bak" 2>/dev/null || true

# 3. Создаем бэкап и восстанавливаем конфигурацию primary
echo "Бэкап и настройка postgresql.conf..."
sudo -u postgres mv "${DATA_DIR}/postgresql.conf" "${DATA_DIR}/postgresql.conf.${TIMESTAMP}.bak"

sudo -u postgres tee ${DATA_DIR}/postgresql.conf > /dev/null << EOF
listen_addresses = '*'
port = 5432
wal_level = replica
max_wal_senders = 20
max_replication_slots = 20
wal_keep_size = 1GB
hot_standby = on
synchronous_commit = remote_apply
synchronous_standby_names = 'ANY 1 (replica1, replica2)'
max_connections = 100
shared_buffers = 256MB
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
EOF

# 4. Создаем бэкап и настраиваем аутентификацию
echo "Бэкап и настройка pg_hba.conf..."
sudo -u postgres mv "${DATA_DIR}/pg_hba.conf" "${DATA_DIR}/pg_hba.conf.${TIMESTAMP}.bak"

sudo -u postgres tee ${DATA_DIR}/pg_hba.conf > /dev/null << EOF
# Local connections
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

# Replication connections
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

# Allow replication from replicas
host    replication     ${REPLICA_USER}      ${REPLICA1_IP}/32       md5
host    replication     ${REPLICA_USER}      ${REPLICA2_IP}/32       md5

# Allow connections from replicas for monitoring
host    all             all             ${REPLICA1_IP}/32       md5
host    all             all             ${REPLICA2_IP}/32       md5
EOF

# 5. Запускаем PostgreSQL и проверяем, что он в режиме primary
echo "Запуск PostgreSQL..."
sudo systemctl start postgresql@${PG_VERSION}-main

# Ждем запуска и проверяем режим
sleep 5
echo "Проверка режима сервера..."
if sudo -u postgres psql -t -c "SELECT pg_is_in_recovery();" | grep -q "t"; then
    echo "ОШИБКА: Сервер все еще в режиме реплики!"
    echo "Проверьте, что удалены все signal файлы и перезапустите скрипт"
    exit 1
fi

echo "Создание пользователя replicator..."
sudo -u postgres psql -c "DROP ROLE IF EXISTS ${REPLICA_USER};" 2>/dev/null || true
sudo -u postgres psql -c "CREATE ROLE ${REPLICA_USER} WITH REPLICATION LOGIN PASSWORD '${REPLICA_PASSWORD}';"

echo "Создание слотов репликации..."
sudo -u postgres psql -c "SELECT pg_drop_replication_slot('replica1');" 2>/dev/null || true
sudo -u postgres psql -c "SELECT pg_drop_replication_slot('replica2');" 2>/dev/null || true
sudo -u postgres psql -c "SELECT pg_create_physical_replication_slot('replica1');"
sudo -u postgres psql -c "SELECT pg_create_physical_replication_slot('replica2');"

# 6. Перезагружаем конфигурацию
echo "Перезагрузка конфигурации PostgreSQL..."
sudo -u postgres psql -c "SELECT pg_reload_conf();"

echo "=== Перенастройка РЕПЛИК ==="

# 7. Создаем единый скрипт для реплик
echo "Создание скрипта для настройки реплик..."

cat > /tmp/setup_replica.sh << 'SCRIPT'
#!/bin/bash

# Параметры передаются при запуске
PRIMARY_IP=$1
SLOT_NAME=$2
PG_VERSION=$3
REPLICA_USER=$4
REPLICA_PASSWORD=$5
DATA_DIR="/var/lib/postgresql/${PG_VERSION}/main"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

echo "Настройка реплики для слота: $SLOT_NAME"
echo "Подключение к primary: $PRIMARY_IP"

# Останавливаем PostgreSQL
sudo systemctl stop postgresql@${PG_VERSION}-main

# Создаем бэкап существующих данных
echo "Создание бэкапа существующих данных..."
sudo -u postgres mv "$DATA_DIR" "${DATA_DIR}_${TIMESTAMP}.bak" 2>/dev/null || true
echo "Бэкап создан: ${DATA_DIR}_${TIMESTAMP}.bak"

# Выполняем базовый бэкап
echo "Выполнение pg_basebackup..."
sudo -u postgres PGPASSWORD="$REPLICA_PASSWORD" pg_basebackup \
    -h "$PRIMARY_IP" \
    -U "$REPLICA_USER" \
    -D "$DATA_DIR" \
    -X stream \
    -S "$SLOT_NAME" \
    -P \
    -R

# Убеждаемся что права установлены правильно после basebackup
sudo -u postgres chmod 750 "$DATA_DIR"
sudo chown -R postgres:postgres "$DATA_DIR"

# Настраиваем реплику
echo "Настройка файлов реплики..."
sudo -u postgres touch "${DATA_DIR}/standby.signal"

# Создаем минимальный конфиг для реплики
sudo -u postgres tee "${DATA_DIR}/postgresql.auto.conf" > /dev/null << EOL
primary_conninfo = 'user=$REPLICA_USER password=$REPLICA_PASSWORD host=$PRIMARY_IP port=5432 application_name=$SLOT_NAME sslmode=prefer'
primary_slot_name = '$SLOT_NAME'
hot_standby = on
EOL

# Запускаем PostgreSQL и проверяем статус
echo "Запуск PostgreSQL..."
sudo systemctl start postgresql@${PG_VERSION}-main

sleep 5

if sudo systemctl is-active --quiet postgresql@${PG_VERSION}-main; then
    echo "Реплика $SLOT_NAME настроена успешно"

    # Проверяем, что реплика в режиме восстановления
    if sudo -u postgres psql -t -c "SELECT pg_is_in_recovery();" | grep -q "t"; then
        echo "Режим реплики подтвержден"
    else
        echo "ПРЕДУПРЕЖДЕНИЕ: сервер не в режиме реплики"
    fi
else
    echo "ОШИБКА: не удалось запустить PostgreSQL на реплике $SLOT_NAME"
    echo "Проверка статуса службы:"
    sudo systemctl status postgresql@${PG_VERSION}-main
    echo "Логи:"
    sudo journalctl -u postgresql@${PG_VERSION}-main -n 10 --no-pager
    exit 1
fi
SCRIPT

chmod +x /tmp/setup_replica.sh

# 8. Копируем и запускаем скрипт на репликах с разными параметрами
echo "Настройка replica1..."
scp /tmp/setup_replica.sh ${USERNAME}@${REPLICA1_IP}:/tmp/
ssh -t ${USERNAME}@${REPLICA1_IP} "chmod +x /tmp/setup_replica.sh && /tmp/setup_replica.sh ${PRIMARY_IP} replica1 ${PG_VERSION} ${REPLICA_USER} '${REPLICA_PASSWORD}'"

echo "Настройка replica2..."
scp /tmp/setup_replica.sh ${USERNAME}@${REPLICA2_IP}:/tmp/
ssh -t ${USERNAME}@${REPLICA2_IP} "chmod +x /tmp/setup_replica.sh && /tmp/setup_replica.sh ${PRIMARY_IP} replica2 ${PG_VERSION} ${REPLICA_USER} '${REPLICA_PASSWORD}'"

# 9. Очистка временных файлов
rm -f /tmp/setup_replica.sh

# 10. Проверка репликации
echo "=== Проверка репликации ==="
sleep 10

echo "Статус репликации на primary:"
sudo -u postgres psql -c "SELECT application_name, state, sync_state, sync_priority, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"

echo "Проверка подключений к репликам..."
echo "Replica1:"
ssh -t ${USERNAME}@${REPLICA1_IP} "sudo -u postgres psql -c 'SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();'"

echo "Replica2:"
ssh -t ${USERNAME}@${REPLICA2_IP} "sudo -u postgres psql -c 'SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();'"

# 11. Проверяем и настраиваем синхронную репликацию
echo "Проверка текущих настроек синхронной репликации:"
sudo -u postgres psql -c "SHOW synchronous_standby_names;"
sudo -u postgres psql -c "SHOW synchronous_commit;"

echo "Настройка синхронной репликации..."
sudo -u postgres psql -c "ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (replica1, replica2)';"
sudo -u postgres psql -c "SELECT pg_reload_conf();"

echo "Ожидание применения настроек синхронной репликации..."
sleep 5

echo "Финальная проверка статуса репликации:"
sudo -u postgres psql -c "SELECT application_name, state, sync_state, sync_priority, write_lag, flush_lag, replay_lag FROM pg_stat_replication;"

echo "=== Восстановление завершено ==="
echo "Primary: $PRIMARY_IP"
echo "Replica1: $REPLICA1_IP"
echo "Replica2: $REPLICA2_IP"
echo "Бэкапы созданы с меткой времени: $TIMESTAMP"

Запуск скрипта#

Сделайте скрипт исполняемым и запустите его:

chmod +x setup_replication.sh
./setup_replication.sh

Проверка работы репликации#

На Primary сервере:#

# Проверка статуса репликации
sudo -u postgres psql -c "
SELECT 
    application_name,
    client_addr,
    state,
    sync_state,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;"

# Проверка слотов репликации
sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

На репликах:#

# Проверка режима реплики
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

# Проверка статуса репликации
sudo -u postgres psql -c "SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;"

Ручное переключение при сбое Primary#

Процедура аварийного переключения:#

Определение новой Primary:

# На одной из реплик проверяем лаг репликации
sudo -u postgres psql -c "SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;"

Повышение реплики до Primary:

# Останавливаем PostgreSQL на выбранной реплике
sudo systemctl stop postgresql@17-main

# Превращаем реплику в primary
sudo -u postgres mv /var/lib/postgresql/17/main/standby.signal /var/lib/postgresql/17/main/standby.signal.bak

# Запускаем PostgreSQL
sudo systemctl start postgresql@17-main

# Проверяем, что сервер стал primary
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

Перенастройка остальных реплик:

# Останавливаем PostgreSQL на других репликах
sudo systemctl stop postgresql@17-main

# Удаляем данные и пересоздаем репликацию с нового primary
sudo -u postgres rm -rf /var/lib/postgresql/17/main/*
sudo -u postgres PGPASSWORD="StrongPassword123!" pg_basebackup \
    -h 192.168.184.152 \  # Новый primary
    -U replicator \
    -D /var/lib/postgresql/17/main \
    -X stream \
    -P \
    -R

# Запускаем PostgreSQL
sudo systemctl start postgresql@17-main

Мониторинг#

Ключевые метрики для мониторинга:#

-- На Primary
SELECT 
    application_name,
    state,
    sync_state,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

-- На репликах
SELECT 
    pg_is_in_recovery() as is_replica,
    pg_last_wal_receive_lsn() as receive_lsn,
    pg_last_wal_replay_lsn() as replay_lsn,
    pg_last_xact_replay_timestamp() as last_replay,
    now() - pg_last_xact_replay_timestamp() as replication_lag;

Создание скрипта мониторинга:#

#!/bin/bash
# monitor_replication.sh

PRIMARY_IP="192.168.184.151"
REPLICA1_IP="192.168.184.152" 
REPLICA2_IP="192.168.184.153"

echo "=== Статус репликации ==="

# Проверка Primary
echo "Primary ($PRIMARY_IP):"
sudo -u postgres psql -h $PRIMARY_IP -c "
SELECT 
    application_name,
    state,
    sync_state,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;" 2>/dev/null || echo "Недоступен"

# Проверка реплик
for replica in $REPLICA1_IP $REPLICA2_IP; do
    echo "Replica ($replica):"
    sudo -u postgres psql -h $replica -c "
SELECT 
    pg_is_in_recovery() as is_replica,
    now() - pg_last_xact_replay_timestamp() as replication_lag;" 2>/dev/null || echo "Недоступен"
done

Диагностика проблем#

Распространенные проблемы:#

Репликация не запускается:

# Проверка логов
sudo tail -f /var/log/postgresql/postgresql-17-main.log

# Проверка подключения между серверами
psql -h 192.168.184.151 -U replicator -c "SELECT 1;"

Большой лаг репликации:

# Проверка нагрузки на Primary
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'active';"

# Проверка дискового IO
iostat -x 1

Слот репликации не активен:

# Пересоздание слота репликации
sudo -u postgres psql -c "SELECT pg_drop_replication_slot('replica1');"
sudo -u postgres psql -c "SELECT pg_create_physical_replication_slot('replica1');"

Преимущества и ограничения#

Преимущества:#

Простота настройки и понимания.
Минимальные требования к ресурсам.
Прямой контроль над процессом репликации.

Ограничения:#

Ручное переключение базы данных.
Ручное управление фейловером.
Отсутствие автоматического восстановления.
Требуется ручной мониторинг.
Нет встроенной балансировки нагрузки.

Заключение#

Данная конфигурация обеспечивает надежную репликацию PostgreSQL для сред, где автоматизированные решения с Patroni избыточны. Регулярный мониторинг и своевременное реагирование на проблемы обеспечат стабильную работу репликации.

Для production-сред с высокими требованиями к доступности рекомендуется рассмотреть использование Patroni с автоматическим фейловером.

Руководство по настройке БД с репликацией ручная настройка

Содержание