Как реализовать репликацию базы данных и высокую доступность

О нас: Персональный сайт Тимофея Бугаевского и компании Зетка Интерактив

Руководства: Обширная коллекция технических руководств, написанных с точки зрения опытного разработчика. Каждая статья содержит подробные объяснения, практические примеры кода и паттерны для продакшена.

Базы данных: Администрирование баз данных, оптимизация и паттерны

Введение

Репликация базы данных обеспечивает долговечность и доступность данных за счёт поддержания копий на нескольких серверах. Для production-систем репликация не является опциональной — она необходима для аварийного восстановления, масштабирования чтения и выполнения обслуживания без простоя. В этом руководстве рассматриваются практические стратегии репликации для PostgreSQL и MySQL.

Концепции репликации

Типы репликации

Physical Replication: копирует точные байты базы данных. Быстро, но реплики должны быть на идентичных версиях PostgreSQL.

Logical Replication: копирует логические изменения (SQL-операции). Более гибко, допускает разные версии и выборочную репликацию.

Synchronous vs Asynchronous:

Synchronous: primary ждёт подтверждения от replica. Потери данных нет, но выше задержка.
Asynchronous: primary не ждёт. Лучшая производительность, но возможна потеря данных.

Потоковая репликация PostgreSQL (Streaming Replication)

Конфигурация primary-сервера

# postgresql.conf на primary
# Включить архивацию WAL
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
# Для synchronous replication (опционально)
synchronous_standby_names = 'replica1'

# pg_hba.conf — разрешить подключения для репликации
host replication replicator 192.168.1.0/24 md5

Создайте пользователя для репликации:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

Настройка replica-сервера

# Остановить PostgreSQL на replica
sudo systemctl stop postgresql
# Удалить существующие данные
rm -rf /var/lib/postgresql/14/main/*
# Скопировать с primary с помощью pg_basebackup
pg_basebackup -h primary.example.com -U replicator -D /var/lib/postgresql/14/main -P -R
# Флаг -R создаёт standby.signal и настраивает подключение

# postgresql.conf на replica
# Подключение к primary
primary_conninfo = 'host=primary.example.com port=5432 user=replicator password=secure_password'

# Запустить replica
sudo systemctl start postgresql

Проверка репликации

-- На primary: проверить статус репликации
SELECT client_addr, state, sent_lsn, write_lsn, replay_lsn
FROM pg_stat_replication;
-- На replica: проверить статус recovery
SELECT pg_is_in_recovery();
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

Логическая репликация PostgreSQL (Logical Replication)

Publisher (Source)

# postgresql.conf
wal_level = logical
max_replication_slots = 4
max_wal_senders = 4

-- Создать publication
CREATE PUBLICATION my_publication FOR TABLE users, orders;
-- Или для всех таблиц
CREATE PUBLICATION full_publication FOR ALL TABLES;

Subscriber (Target)

-- Создать subscription
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=publisher.example.com dbname=mydb user=replicator password=secure'
PUBLICATION my_publication;
-- Проверить статус
SELECT * FROM pg_stat_subscription;

Репликация MySQL

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

# my.cnf на primary
[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_do_db = mydb

-- Создать пользователя для репликации
CREATE USER 'replicator'@'%' IDENTIFIED BY 'secure_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;
-- Получить позицию binary log
SHOW MASTER STATUS;
-- Примечание: значения File и Position

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

# my.cnf на replica
[mysqld]
server-id = 2
relay_log = mysql-relay-bin
read_only = 1

-- Настроить репликацию
CHANGE MASTER TO
MASTER_HOST='primary.example.com',
MASTER_USER='replicator',
MASTER_PASSWORD='secure_password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
-- Запустить репликацию
START SLAVE;
-- Проверить статус
SHOW SLAVE STATUS\G

Стратегии failover

Ручной failover (PostgreSQL)

-- На replica: повысить до primary
SELECT pg_promote();
-- Или через командную строку
pg_ctl promote -D /var/lib/postgresql/14/main

Обновите строки подключения приложения, чтобы они указывали на новый primary.

Автоматический failover с Patroni

Patroni управляет высокой доступностью PostgreSQL с автоматическим failover.

# patroni.yml
scope: postgres-cluster
name: node1
restapi:
listen: 0.0.0.0:8008
connect_address: node1.example.com:8008
etcd:
hosts: etcd1:2379,etcd2:2379,etcd3:2379
bootstrap:
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
postgresql:
use_pg_rewind: true
parameters:
max_connections: 100
postgresql:
listen: 0.0.0.0:5432
connect_address: node1.example.com:5432
data_dir: /var/lib/postgresql/14/main
authentication:
replication:
username: replicator
password: secure_password
superuser:
username: postgres
password: postgres_password

HAProxy для маршрутизации подключений

# haproxy.cfg
frontend postgres_frontend
bind *:5432
default_backend postgres_backend
backend postgres_backend
option httpchk GET /master
http-check expect status 200
server node1 node1:5432 check port 8008
server node2 node2:5432 check port 8008
server node3 node3:5432 check port 8008

Масштабирование чтения с помощью реплик

Разделение чтения/записи на уровне приложения

// Пример для Laravel
// config/database.php
'pgsql' => [
'read' => [
'host' => [
'replica1.example.com',
'replica2.example.com',
],
],
'write' => [
'host' => 'primary.example.com',
],
'driver' => 'pgsql',
'database' => 'mydb',
// ...
],

// Принудительно использовать подключение для записи для критичных чтений
$user = DB::connection('pgsql')->select('SELECT * FROM users WHERE id = ?', [1]);

Пулинг подключений с read replicas

# PgBouncer с несколькими backends
[databases]
mydb = host=primary.example.com port=5432
mydb_ro = host=replica1.example.com,replica2.example.com port=5432

Мониторинг репликации

Задержка репликации PostgreSQL

-- На primary: проверить lag по каждой replica
SELECT client_addr,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / 1024 / 1024 AS lag_mb
FROM pg_stat_replication;
-- На replica: проверить lag
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds;

Задержка репликации MySQL

SHOW SLAVE STATUS\G
-- Ищите: Seconds_Behind_Master

Пороговые значения для оповещений

Warning: задержка > 10 секунд
Critical: задержка > 60 секунд
Emergency: replica отключена

Резервное копирование с использованием реплик

Делайте бэкапы с реплик, чтобы не влиять на primary:

# PostgreSQL — бэкап с replica
pg_dump -h replica.example.com -U postgres mydb > backup.sql
# Или physical backup
pg_basebackup -h replica.example.com -U replicator -D /backup -P

Лучшие практики

Используйте synchronous replication для критичных данных (как минимум одна replica).
Непрерывно мониторьте задержку репликации.
Регулярно тестируйте failover в non-production.
Размещайте реплики в разных availability zones.
Документируйте процедуры failover.
Автоматизируйте failover там, где это возможно (Patroni, orchestrator).
Используйте connection pooling для эффективного управления подключениями.

Заключение

Репликация базы данных обеспечивает основу для высокой доступности и аварийного восстановления. Начните с асинхронной потоковой репликации для масштабирования чтения, добавьте synchronous replication для критичных данных и внедрите автоматический failover для production-систем. Регулярное тестирование гарантирует, что ваши процедуры failover сработают, когда это потребуется.