Репликация базы данных: PostgreSQL и MySQL на VPS

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

Зачем нужна репликация

Репликация решает сразу несколько задач, и важно понимать, что это не замена бэкапу, а дополнение к нему.

• Отказоустойчивость — упал мастер, переключаемся на реплику.
• Масштабирование чтения — SELECT-запросы уходят на реплики, мастер занят только записью.
• Бэкап без нагрузки — снимаем dump с реплики, не трогая боевой сервер.

Минимум для схемы — два VPS в одной или разных локациях. Разнести их по датацентрам (например UK, США и РФ у MAATRIX) полезно для защиты от отказа целой площадки.

Важно с самого начала различать два типа репликации. При асинхронной мастер подтверждает коммит, не дожидаясь реплики, — это быстро, но при внезапном отказе можно потерять последние транзакции, ещё не доехавшие до резерва. При синхронной коммит подтверждается только после записи на реплику: потерь нет, но каждая транзакция ждёт сетевой ответ, и задержка растёт. Для большинства проектов асинхронная репликация — разумный компромисс, а синхронную включают там, где потеря даже одной транзакции недопустима (платежи, финансовые операции).

PostgreSQL: подготовка мастера

На мастере включаем режим репликации и заводим отдельную роль с правом REPLICATION.

# postgresql.conf на мастере
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 512MB

sudo -u postgres psql -c "CREATE ROLE replica WITH REPLICATION LOGIN PASSWORD 'strongpass';"

Разрешаем подключение реплики в pg_hba.conf (замените IP на адрес реплики) и перезапускаем сервер:

echo 'host replication replica REPLICA_IP/32 scram-sha-256' | sudo tee -a /etc/postgresql/16/main/pg_hba.conf
sudo systemctl restart postgresql

PostgreSQL: запуск реплики

На втором сервере останавливаем PostgreSQL, очищаем каталог данных и снимаем базовую копию с мастера утилитой pg_basebackup — она сразу создаёт standby-конфигурацию.

sudo systemctl stop postgresql
sudo -u postgres rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres pg_basebackup -h MASTER_IP -U replica \
  -D /var/lib/postgresql/16/main -Fp -Xs -P -R

Флаг -R пропишет параметры подключения и создаст файл standby.signal. Запускаем реплику и проверяем, что она догоняет мастер:

sudo systemctl start postgresql
sudo -u postgres psql -c 'SELECT status, sent_lsn, replay_lsn FROM pg_stat_replication;'  # на мастере
sudo -u postgres psql -c 'SELECT pg_last_wal_replay_lsn();'  # на реплике

MySQL: master-replica

В MySQL/MariaDB настройка похожа по логике. На мастере включаем бинлог и уникальный server-id:

# my.cnf на мастере
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW

CREATE USER 'repl'@'%' IDENTIFIED BY 'strongpass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
SHOW MASTER STATUS;   -- запишите File и Position

На реплике задаём server-id = 2 и указываем координаты мастера:

CHANGE MASTER TO MASTER_HOST='MASTER_IP', MASTER_USER='repl',
  MASTER_PASSWORD='strongpass', MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;
START SLAVE;
SHOW SLAVE STATUS\G   -- Slave_IO_Running и Slave_SQL_Running должны быть Yes

Мониторинг отставания и переключение

Реплика может отставать под нагрузкой — за этим нужно следить. В PostgreSQL отставание видно как разница LSN, в MySQL — поле Seconds_Behind_Master.

# PostgreSQL: лаг в байтах на мастере
SELECT client_addr, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

При аварии мастера реплику повышают до основного узла. В PostgreSQL это делается одной командой:

sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main

• Одинаковый server-id в MySQL — репликация не стартует.
• Мало wal_keep_size — мастер удалит WAL, реплика отвалится.
• Реплику приняли за бэкап — ошибочный DELETE тут же уедет на неё.

Где разместить узлы

Для репликации важны стабильная сеть между узлами и предсказуемый диск. NVMe + AMD EPYC у MAATRIX ускоряют применение WAL и бинлога на реплике, а три локации (UK, США и РФ) позволяют разнести мастер и резерв по площадкам. Оплата картой РФ, СБП, криптой или токеном MAAT и ежедневные бэкапы дополняют схему надёжности, а root-доступ нужен для правки конфигов репликации.

Частые вопросы