Миграция данных PostgreSQL: полное руководство по стратегиям, инструментам и предотвращению ошибок

Перенос базы данных PostgreSQL на новый сервер или обновление мажорной версии - задача, которая рано или поздно встает перед каждым администратором. Выбор неправильной стратегии приводит к длительным простоям сервиса, потере данных или несовместимости приложений с целевой средой. Это руководство содержит три проверенных метода миграции: pg_dump/pg_restore для небольших баз с допустимым окном обслуживания, pg_upgrade для быстрого обновления на месте и логическую репликацию для production-систем, требующих near-zero downtime.

Каждый метод разбирается на уровне конкретных команд, параметров и критериев применимости. Вы получите готовую последовательность действий для переноса данных между разными версиями PostgreSQL, решение проблем с кодировками и локалями, а также скрипты для автоматической проверки целостности после завершения миграции. Материал опирается на практический опыт и учитывает типичные ошибки: от забытых расширений до несовпадения контрольных сумм в больших таблицах.

Как выбрать стратегию миграции PostgreSQL: сравнение методов

Выбор метода миграции определяется тремя факторами: допустимое время простоя, объем базы данных и разница между исходной и целевой версиями PostgreSQL. Для базы размером до 50 ГБ с допустимым простоем в 30–60 минут оптимален pg_dump/pg_restore. Для обновления мажорной версии на том же сервере без переноса данных по сети используйте pg_upgrade - он завершает работу за время, сопоставимое с перезапуском службы. Если простой недопустим, единственный вариант - логическая репликация, которая синхронизирует изменения в реальном времени и позволяет переключить трафик с задержкой в несколько секунд.

Сравнительная таблица помогает быстро оценить компромиссы:

Для детального планирования миграции между разными СУБД, включая PostgreSQL, используйте пошаговый план с чек-листом рисков. Если вы переносите данные с SQL Server или Oracle, обратитесь к руководству по гетерогенной миграции с готовыми конфигурациями pgloader.

pg_dump/pg_restore: универсальный, но с простоем

pg_dump создает логическую копию базы данных в виде SQL-команд или бинарного архива. Формат custom (-Fc) дает максимальную гибкость: сжатие данных, возможность выборочного восстановления таблиц и параллельную загрузку. Процесс выглядит так:

pg_dump -h source_host -U postgres -Fc -j 4 -f /backup/mydb.dump mydb

Флаг -j 4 задействует 4 ядра для ускорения дампа. Восстановление на целевом сервере выполняется командой:

pg_restore -h target_host -U postgres -j 4 -d mydb /backup/mydb.dump

Параллельное восстановление (-j) кратно сокращает время загрузки на многодисковых системах. Ограничение метода - полная блокировка записи в базу на время создания дампа. Для базы объемом 500 ГБ это может занять несколько часов. Если такой простой недопустим, рассмотрите логическую репликацию.

pg_upgrade: быстрое обновление версии на месте

pg_upgrade выполняет обновление системных каталогов PostgreSQL без полного копирования данных. Вместо переноса содержимого таблиц он обновляет метаданные, указывая новой версии сервера на существующие файлы данных. Время выполнения зависит от количества таблиц и индексов, а не от объема данных - база в 2 ТБ обновляется за 10–15 минут.

Обязательные условия: исходный и целевой сервер должны быть установлены на одной машине, архитектуры CPU должны совпадать, все расширения в целевой версии должны быть совместимы. Перед запуском сделайте полный бэкап - pg_upgrade не создает резервную копию автоматически. Команда запуска:

/usr/lib/postgresql/16/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/15/main \
  --new-datadir=/var/lib/postgresql/16/main \
  --old-bindir=/usr/lib/postgresql/15/bin \
  --new-bindir=/usr/lib/postgresql/16/bin

После успешного обновления выполните ANALYZE для актуализации статистики планировщика запросов. Если pg_upgrade завершился с ошибкой несовместимости расширений, обновите их в старой версии до запуска процедуры.

Логическая репликация: миграция с near-zero downtime

Логическая репликация передает изменения на уровне строк (INSERT, UPDATE, DELETE) с исходного сервера на целевой в режиме реального времени. В отличие от потоковой репликации, она не требует одинаковых мажорных версий PostgreSQL и позволяет реплицировать отдельные таблицы, а не весь кластер. Настройка состоит из трех шагов:

1. Включите wal_level=logical в postgresql.conf источника и перезапустите сервер.
2. Создайте публикацию на источнике: CREATE PUBLICATION migration_pub FOR ALL TABLES;
3. Создайте подписку на целевом сервере: CREATE SUBSCRIPTION migration_sub CONNECTION 'host=source_host dbname=mydb' PUBLICATION migration_pub;

Начальная синхронизация копирует все существующие данные, затем реплика переходит в режим потоковой передачи изменений. Отставание репликации мониторится запросом:

SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots;

Когда отставание приближается к нулю, переключите приложение на новый сервер и удалите подписку командой DROP SUBSCRIPTION. Полный простой при таком подходе - 2–5 секунд на смену строки подключения. Для сложных сценариев с выборочной репликацией или миграцией между сильно различающимися версиями рассмотрите расширение pglogical.

Пошаговая миграция через pg_dump и pg_restore

Этот метод - самый востребованный для плановых миграций с запланированным окном обслуживания. Последовательность действий покрывает подготовку, перенос и пост-миграционную проверку.

Шаг 1: Подготовка целевого сервера. Установите PostgreSQL той же или более новой версии. Создайте базу данных и пользователей с теми же именами, что на источнике. Не назначайте права на этом этапе - pg_restore обработает их при восстановлении.

CREATE USER app_user WITH PASSWORD 'secure_password';
CREATE DATABASE mydb OWNER app_user;

Шаг 2: Создание дампа. Используйте custom-формат для сжатия и параллельного восстановления. Ключ --no-owner пропускает команды смены владельца, что важно при различии ролей на серверах:

pg_dump -h source_host -U postgres -Fc -j 4 --no-owner -f /backup/mydb.dump mydb

Шаг 3: Перенос файла. Скопируйте дамп на целевой сервер через rsync или scp. При объеме более 100 ГБ используйте rsync с флагом --progress для контроля передачи.

Шаг 4: Восстановление. Запустите pg_restore с параллельными потоками. Ключ --no-privileges предотвращает ошибки из-за отсутствия ролей:

pg_restore -h localhost -U postgres -j 4 --no-owner --no-privileges -d mydb /backup/mydb.dump

Шаг 5: Пост-миграционные действия. Обновите статистику планировщика и скорректируйте последовательности:

ANALYZE;
SELECT setval('sequence_name', (SELECT MAX(id) FROM table_name));

Обзор инструментов для разных сценариев миграции, включая pgloader и AWS DMS, собран в сравнительном материале по утилитам 2026 года.

Особенности миграции между разными версиями PostgreSQL

При переносе данных с PostgreSQL 13 на 16 используйте клиент pg_dump версии 16. Более новый клиент корректно обрабатывает системные каталоги старой версии и генерирует дамп, совместимый с целевым сервером. Обратная ситуация - дамп клиентом версии 13 для восстановления на версии 16 - допустима, но может пропустить новые возможности целевой версии.

Расширения требуют отдельного внимания. Если на источнике установлен PostGIS 3.2, а на целевом сервере доступен только PostGIS 3.4, восстановление пройдет успешно - расширение обновится автоматически. Но если расширение на источнике новее, чем на цели, восстановление завершится ошибкой. Проверьте список расширений до начала миграции:

SELECT extname, extversion FROM pg_extension;

Решение проблем с кодировками и локалями

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

\l

Вывод покажет столбцы Encoding, Collate и Ctype. Если целевой сервер не поддерживает нужную локаль, пересоздайте базу с явным указанием параметров:

CREATE DATABASE mydb OWNER app_user
  ENCODING 'UTF8'
  LC_COLLATE 'ru_RU.UTF-8'
  LC_CTYPE 'ru_RU.UTF-8'
  TEMPLATE template0;

Использование template0 обязательно при указании локали - template1 может содержать несовместимые настройки. Ключи --no-owner и --no-privileges в pg_restore предотвращают ошибки, связанные с отсутствием ролей на целевом сервере.

Минимизация времени простоя: инкрементальная миграция с репликацией

Для систем с требованием доступности 99.9% и выше окно обслуживания в несколько часов неприемлемо. Логическая репликация решает эту проблему через инкрементальную синхронизацию: начальный снапшот копируется однократно, затем все изменения передаются потоком. Приложение продолжает работать с исходной базой до момента переключения.

Пошаговая настройка:

1. На исходном сервере установите wal_level=logical и max_replication_slots=5 в postgresql.conf. Перезапустите PostgreSQL.
2. Создайте публикацию для всех таблиц: CREATE PUBLICATION prod_migration FOR ALL TABLES;
3. Скопируйте схему базы на целевой сервер: pg_dump -h source --schema-only -f schema.sql mydb && psql -h target -d mydb -f schema.sql
4. Создайте подписку на целевом сервере: CREATE SUBSCRIPTION prod_sub CONNECTION 'host=source dbname=mydb user=repl_user password=pass' PUBLICATION prod_migration;
5. Мониторьте отставание: SELECT slot_name, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag FROM pg_replication_slots;
6. Когда lag стабильно держится около нуля, запланируйте переключение. Остановите запись на источнике, дождитесь полной синхронизации, переключите приложение на целевой сервер.
7. Удалите подписку: DROP SUBSCRIPTION prod_sub;

Для крупных проектов с поэтапной миграцией нескольких компонентов изучите руководство по phased migration с примерами для PostgreSQL, Redis и Kafka.

Использование pglogical для сложных сценариев

Встроенная логическая репликация PostgreSQL имеет ограничения: она не реплицирует DDL-команды и large objects, а также требует, чтобы целевая версия была не ниже исходной. Расширение pglogical снимает эти ограничения. Оно поддерживает репликацию между разными мажорными версиями в обе стороны, фильтрацию на уровне строк и таблиц, а также каскадную репликацию.

Установка pglogical начинается с добавления расширения в shared_preload_libraries на обоих серверах. Затем создается провайдер на источнике:

SELECT pglogical.create_node(
  node_name := 'provider1',
  dsn := 'host=source dbname=mydb'
);
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

На целевом сервере создается подписчик:

SELECT pglogical.create_node(
  node_name := 'subscriber1',
  dsn := 'host=target dbname=mydb'
);
SELECT pglogical.create_subscription(
  subscription_name := 'migration_sub',
  provider_dsn := 'host=source dbname=mydb'
);

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

Проверка целостности данных после миграции

Верификация перенесенных данных - обязательный этап, который нельзя пропускать. Потеря даже 0.01% строк в финансовой системе или повреждение внешних ключей в каталоге товаров приводит к трудноуловимым ошибкам в работе приложений. Проверка выполняется на трех уровнях: структурном, количественном и содержательном.

Уровень 1: Сравнение структуры. Сравните схемы исходной и целевой баз, исключив системные таблицы:

pg_dump -h source --schema-only -f source_schema.sql mydb
pg_dump -h target --schema-only -f target_schema.sql mydb
diff source_schema.sql target_schema.sql

Различия в выводе diff укажут на пропущенные таблицы, индексы или ограничения.

Уровень 2: Сравнение количества строк. Выполните подсчет для всех пользовательских таблиц:

SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY schemaname, relname;

Запустите этот запрос на обоих серверах и сравните результаты. Расхождение в n_live_tup сигнализирует о неполной синхронизации.

Уровень 3: Сравнение контрольных сумм. Для критически важных таблиц вычислите MD5-хеш всех строк:

SELECT MD5(string_agg(row_hash, '' ORDER BY row_hash))
FROM (
  SELECT MD5(CAST((t.*) AS text)) AS row_hash
  FROM critical_table t
) sub;

Одинаковые хеши на источнике и цели гарантируют побитовую идентичность данных. Этот метод ресурсоемкий - для таблиц объемом более 100 ГБ выполняйте проверку выборочно или в нерабочее время.

Автоматизация проверки с помощью скриптов

Ручное сравнение десятков таблиц отнимает время и чревато пропусками. Bash-скрипт автоматизирует базовую проверку количества строк:

#!/bin/bash
TABLES=$(psql -h source -t -c "SELECT tablename FROM pg_tables WHERE schemaname='public'")
for table in $TABLES; do
  count_src=$(psql -h source -t -c "SELECT COUNT(*) FROM $table")
  count_tgt=$(psql -h target -t -c "SELECT COUNT(*) FROM $table")
  if [ "$count_src" != "$count_tgt" ]; then
    echo "MISMATCH: $table - source: $count_src, target: $count_tgt"
  fi
done

Для крупных инсталляций рассмотрите утилиту pg_comparator - она сравнивает таблицы напрямую через подключения к обеим базам и генерирует SQL-команды для исправления расхождений.

Типичные ошибки при миграции PostgreSQL и как их избежать

Многолетняя практика миграций выявила повторяющиеся ошибки, которые приводят к затяжным простоям и потере данных. Вот пять самых частых проблем и способы их предотвратить.

1. Отсутствие полного бэкапа перед миграцией. Даже при использовании pg_upgrade, который не модифицирует исходные данные, сбой на этапе обновления системных каталогов может сделать базу нечитаемой. Всегда создавайте резервную копию через pg_basebackup или pg_dumpall перед любыми операциями.

2. Забытые расширения. База данных использует PostGIS, pg_partman или pg_stat_statements, а на целевом сервере они не установлены. Восстановление падает с ошибкой. Решение: получите список расширений на источнике (SELECT extname FROM pg_extension;) и установите их на целевом сервере до начала миграции.

3. Игнорирование large objects. pg_dump по умолчанию включает large objects, но при использовании ключа --schema-only или выборочном дампе таблиц они могут быть пропущены. Если приложение хранит файлы в БД через lo_import, явно проверьте их наличие после миграции запросом SELECT COUNT(*) FROM pg_largeobject;.

4. Различия в мажорных версиях без тестирования. Переход с PostgreSQL 12 на 16 меняет поведение некоторых SQL-конструкций и удаляет устаревшие функции. Прогоните тестовый набор запросов приложения на staging-среде с целевой версией до начала миграции.

5. Непроверенные права доступа. После восстановления с ключами --no-owner и --no-privileges все объекты принадлежат пользователю, выполнившему pg_restore. Это ломает систему разрешений. Сравните вывод \dp на источнике и цели, при необходимости восстановите права вручную.

Планирование миграции: чек-лист для безопасного переноса

Структурированный план снижает вероятность пропуска критических шагов. Используйте этот чек-лист для любой миграции PostgreSQL, независимо от выбранного метода.

1. Инвентаризация базы данных. Зафиксируйте размер базы (SELECT pg_size_pretty(pg_database_size('mydb'));), список расширений, количество таблиц и наличие large objects. Эти данные определяют выбор стратегии.
2. Выбор стратегии и инструмента. Сопоставьте требования к downtime с возможностями методов из таблицы сравнения выше. Для критически важных систем выбирайте логическую репликацию.
3. Создание полного бэкапа. Выполните pg_basebackup или pg_dumpall. Проверьте целостность бэкапа тестовым восстановлением на отдельной машине.
4. Тестирование миграции на staging-среде. Воспроизведите миграцию на копии продакшен-базы. Замерьте время каждого этапа, выявите несовместимости расширений и проблемы с кодировками.
5. Согласование окна простоя. Если метод требует остановки записи, согласуйте точное время с владельцами сервиса. Подготовьте уведомления для пользователей.
6. Подготовка rollback-плана. Определите условия, при которых миграция признается неуспешной (например, расхождение количества строк более 0%), и опишите процедуру возврата к исходному серверу. Для стратегий миграции IT-систем с высокими рисками используйте фреймворк с оценкой рисков и планом отката.
7. Выполнение миграции. Следуйте пошаговой инструкции для выбранного метода. Фиксируйте время начала и завершения каждого этапа.
8. Пост-миграционная проверка. Выполните трехуровневую верификацию: структура, количество строк, контрольные суммы. Запустите ANALYZE и проверьте логи приложения на ошибки.

После успешной миграции сохраните старый сервер в неизменном состоянии минимум на 7 дней. Это даст возможность вернуться к исходной конфигурации, если в работе приложения обнаружатся скрытые проблемы, не выявленные тестированием. Для production-сред с высокими требованиями к доступности рассмотрите размещение целевой базы на облачной инфраструктуре - например, Timeweb Cloud предоставляет управляемые серверы баз данных с автоматическим резервным копированием и масштабированием ресурсов без остановки сервиса.