Как безопасно перенести базу данных PostgreSQL: полное руководство по логической репликации и другим методам

Введение

Перенос базы данных PostgreSQL — задача непростая, особенно для больших проектов. Часто это один из самых крупных и ответственных процессов для разработчиков и администраторов. Основные сценарии переноса включают:

• обновление до новой версии PostgreSQL;

• перенос базы на другой сервер или хостинг;

• миграция с минимальным временем простоя.

В зависимости от размера базы и ограничений инфраструктуры есть три основных подхода.

1. Перенос с помощью pg_dump и pg_restore

pg_dump позволяет создать дамп всей базы, включая схемы, таблицы и специальные объекты. Для небольших баз (50–150 ГБ) это часто самый простой вариант.

Пример использования:

pg_dump -Fc $SOURCE_DB_URI > dump_file.dump
pg_restore --no-acl --no-owner -d $TARGET_DB_URI dump_file.dump

Плюсы:

• Надёжно и просто;

• Полный дамп базы, включая схему и данные.

Минусы:

• При больших базах (сотни ГБ и выше) процесс может занять часы;

• Требуется время на восстановление и минимизация простоя.

2. Использование WAL (Write-Ahead Logging)

Если у вас настроено резервное копирование на основе WAL, например через pgBackRest, WAL-G или WAL-E, можно выполнить масштабную миграцию:

1. Создаётся полная резервная копия базы;

2. Настраивается потоковая передача WAL на новый сервер;

3. После завершения первичной синхронизации можно переключить приложение на новую базу с минимальным простоем.

Плюсы:

• Подходит для терабайтных баз;

• Минимизирует простой.

Минусы:

• Требует доступа к WAL (не поддерживается, например, в Amazon RDS).

3. Логическая миграция PostgreSQL

Логическая репликация позволяет переносить данные на новый сервер без доступа к WAL.

• Принцип работы: текущая база (publisher) передаёт изменения новой базе (subscriber);

• Репликация распространяется на данные таблиц, но не переносит схему, индексы и последовательности;

• С помощью дополнительных шагов можно выполнить полную миграцию.

Основные шаги логической миграции

Шаг 1: Перенос схемы

Сначала необходимо создать на новом сервере структуру базы:

pg_dump -Fc -s $SOURCE_DB_URI | pg_restore --no-acl --no-owner -d $TARGET_DB_URI

• При активной разработке изменений схемы: синхронизируйте изменения и на подписчике.

Шаг 2: Настройка издателя (старый сервер)

1. Включите логическую репликацию:

ALTER SYSTEM SET wal_level = logical;

2. Настройте параметры слотов репликации:

max_replication_slots
max_wal_senders
max_logical_replication_workers
max_worker_processes
max_sync_workers_per_subscription

3. Убедитесь, что сеть разрешает подключения с нового сервера.

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

CREATE ROLE elizabeth WITH REPLICATION LOGIN PASSWORD 'my_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO elizabeth;

5. Определите таблицы без первичных ключей:

select tab.table_schema, tab.table_name
from information_schema.tables tab
left join information_schema.table_constraints tco
  on tab.table_schema = tco.table_schema
  and tab.table_name = tco.table_name
  and tco.constraint_type = 'PRIMARY KEY'
where tab.table_type = 'BASE TABLE'
  and tab.table_schema not in ('pg_catalog', 'information_schema')
  and tco.constraint_name is null
order by table_schema, table_name;

• Для таких таблиц используйте уникальный индекс или REPLICA IDENTITY FULL:

ALTER TABLE tablename REPLICA IDENTITY USING INDEX idx_unique_index;
-- или
ALTER TABLE tablename REPLICA IDENTITY FULL;

6. Создайте публикацию всех таблиц:

CREATE PUBLICATION bridge_migration FOR ALL TABLES;
SELECT * FROM pg_publication_tables;

Шаг 3: Настройка подписчика (новый сервер)

Создаём подписку на публикацию:

CREATE SUBSCRIPTION bridge_migration
CONNECTION 'host={host} port=5432 dbname={database} user={login} password={password}'
PUBLICATION bridge_migration;

• Для больших баз можно ограничить число одновременно синхронизируемых таблиц через max_sync_workers_per_subscription.

Шаг 4: Мониторинг первичной загрузки

Проверяем прогресс через:

SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_subscription_rel;

• Состояния таблиц:

  • i — инициализация

  • d — копирование данных

  • f — копирование завершено

  • s — синхронизация выполнена

  • r — обычная репликация

Шаг 5: Тестирование и переключение

1. Остановите запись на исходной базе;

2. Проверьте данные на новом сервере;

3. Переключите приложение на новую базу.

Шаг 6: Синхронизация последовательностей

Логическая репликация не переносит последовательности. Используйте команды setval:

SELECT
  'SELECT setval(' || quote_literal(quote_ident(n.nspname) || '.' || quote_ident(c.relname)) || ', ' || s.last_value || ');'
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_sequences s ON s.schemaname = n.nspname AND s.sequencename = c.relname
WHERE c.relkind = 'S';

• Выполните результат на новом сервере, чтобы синхронизировать все последовательности.

Заключение

Логическая репликация — безопасный и эффективный способ миграции PostgreSQL, особенно при крупных базах и ограничениях по доступу к WAL.

• Данные остаются согласованными, если схема подписчика идентична схеме издателя;

• Репликация однонаправленная, без конфликтующих записей на подписчике;

• Можно минимизировать простой при переходе на новый сервер.