Как восстановить одну базу данных из полного бэкапа PostgreSQL (pg_dumpall) и почему pg_restore выдает ошибки?

При работе с PostgreSQL, особенно в связке с системами 1С, администраторы часто сталкиваются с двумя задачами: необходимостью восстановить одну конкретную базу данных из резервной копии всего сервера (кластера) и непониманием ошибок, которые возникают в процессе восстановления. Рассмотрим обе ситуации и разберем по шагам, как правильно выполнять эти операции и интерпретировать сообщения системы.

Причина ошибок «does not exist» при восстановлении базы

Часто при восстановлении резервной копии, созданной утилитой pg_dump, в консоли появляются сообщения об ошибках, даже если восстановление происходит в чистую, только что созданную базу данных. Проанализируем типичный пример таких сообщений:

pg_restore: error: could not execute query: ERROR: index "_reference98_2" does not exist
Command was: DROP INDEX public._reference98_2;
pg_restore: error: could not execute query: ERROR: table "_inforgchngr2311" does not exist
Command was: DROP TABLE public._inforgchngr2311;

На первый взгляд, это выглядит как серьезная проблема. Однако выясним причину. Такие ошибки возникают, если резервная копия была создана с флагом --clean (или -c). Этот флаг добавляет в самое начало файла с бэкапом SQL-команды DROP ... для каждого объекта базы данных (таблицы, индекса, представления и т.д.).

Логика здесь простая: перед тем как создать объект, нужно убедиться, что объекта с таким же именем не существует. Команда DROP удаляет старый объект, а последующая команда CREATE создает его заново. Это гарантирует, что вы восстанавливаете базу в "чистом" виде, без остатков от предыдущей версии.

Когда вы восстанавливаете бэкап в абсолютно пустую базу, в ней еще нет никаких таблиц и индексов. Утилита pg_restore начинает выполнять команды из файла, натыкается на команду DROP TABLE ..., пытается удалить несуществующую таблицу, получает от сервера PostgreSQL ошибку "table does not exist" и выводит ее в консоль.

Это совершенно нормальное и безопасное поведение. Сразу после неудачной попытки удаления будет выполнена команда CREATE TABLE ..., которая успешно создаст нужный объект. Таким образом, эти сообщения можно считать "безвредными" и просто игнорировать, так как на конечный результат восстановления они не влияют.

Чтобы избежать путаницы и лишних сообщений, можно следовать простому алгоритму, который гарантирует чистое восстановление без ошибок:

1. Убедитесь, что у вас есть готовый файл резервной копии нужной базы.
2. В консоли управления PostgreSQL (например, через pgAdmin) полностью удалите базу данных, в которую планируется восстановление (если она существует).
3. Создайте новую, пустую базу данных с тем же именем и владельцем.
4. Запустите восстановление из вашего файла, но уже без опции --clean (или без галочки "Clean before restore" в графическом интерфейсе). Поскольку база пуста, очищать в ней нечего, и команды DROP не будут вызываться.

Этот подход обеспечивает наиболее предсказуемый и "чистый" процесс восстановления отдельной базы данных.

Как восстановить одну базу данных из резервной копии всего кластера (pg_dumpall)

Утилита pg_dumpall — мощный инструмент, который создает резервную копию всего кластера PostgreSQL: всех баз данных, а также глобальных объектов, таких как роли (пользователи) и табличные пространства. Это удобно для полного переноса сервера. Однако возникает проблема, когда из такого большого файла нужно восстановить только одну конкретную базу.

Важно понимать: стандартные утилиты PostgreSQL не предоставляют прямой команды для выборочного восстановления одной базы из дампа, созданного pg_dumpall. Файл бэкапа представляет собой единый SQL-скрипт, и утилита psql, используемая для восстановления, просто выполняет все команды из него последовательно.

Существует надежный и рекомендуемый обходной путь, который называют методом "песочницы" (sandbox). Разберем его по шагам.

1. Подготовка тестовой среды. Вам понадобится временный, тестовый сервер PostgreSQL. Это может быть другой физический сервер, виртуальная машина или даже Docker-контейнер. Главное, чтобы он был изолирован от вашего рабочего (production) сервера.
2. Полное восстановление в "песочницу". На этом временном сервере выполните полное восстановление из вашего файла pg_dumpall. Для этого используется утилита psql. psql -U postgres -f C:\\_BackUp\\AllServer\\allBackUp.sql

   Примечание: Этот процесс может занять много времени, и консоль может не отображать никакого прогресса. Чтобы убедиться, что процесс идет, можно использовать флаг --echo-all, который будет выводить каждую выполняемую SQL-команду. Для остановки при первой же серьезной ошибке используйте флаг -v ON_ERROR_STOP=1.

3. Создание бэкапа нужной базы. После того как весь кластер восстановится на временном сервере, подключитесь к нему и создайте резервную копию уже только той единственной базы, которая вам нужна. Для этого используйте стандартную утилиту pg_dump.
   Скопировать код

   
   pg_dump -U postgres -Fc -f C:\\_BackUp\\SingleDB\\my_needed_base.backup my_needed_base_name
   

   Здесь -Fc означает создание бэкапа в сжатом кастомном формате, который является предпочтительным для последующего восстановления через pg_restore.

4. Восстановление целевой базы на рабочем сервере. Теперь у вас есть файл резервной копии только одной базы. Перенесите его на рабочий сервер (или в место, доступное с него) и выполните восстановление в чистую, предварительно созданную базу данных с помощью pg_restore.
   Скопировать код

   
   pg_restore -U postgres -d my_needed_base_name C:\\_BackUp\\SingleDB\\my_needed_base.backup
   

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

Чего делать не стоит: Пытаться вручную редактировать огромный `.sql` файл от pg_dumpall, чтобы вырезать из него команды, относящиеся только к одной базе. Это крайне рискованно, так как легко пропустить важные зависимости, нарушить синтаксис или повредить данные, что приведет к неработоспособной базе после восстановления.

В качестве архитектурного решения для упрощения таких задач в будущем можно рассмотреть подход "один экземпляр PostgreSQL — одна важная база". Это позволяет изолировать базы друг от друга и упрощает процессы резервного копирования и восстановления для каждой из них.