pg_restore: [архиватор (db)] не смог выполнить запрос: ERROR: схема "public" уже существует

Я использую pg_dump / pg_restore для резервного копирования и восстановления базы данных PostgreSQL, но получаю некоторые сообщения об ошибках (и ненулевой статус завершения) от pg_restore. Я попробовал суперпростой базовый вариант (описанный ниже), но все равно получил эти ошибки:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR: схема "public" уже существует.
    Команда была: CREATE SCHEMA public;

Шаги для воспроизведения:

  1. Установите свежий, ванильный дистрибутив Ubuntu 14.04 (я'использую Vagrant с этой коробкой Vagrant).

  2. Установите PostgreSQL 9.3, настройте, чтобы разрешить локальные подключения от имени пользователя PostgreSQL "postgres" от любого пользователя Linux.

  3. Создайте тестовую базу данных. Я'просто делаю:

    vagrant@vagrant-ubuntu-trusty-64:~$ psql --username=postgres postgres
    psql (9.3.5)
    Введите "help" для получения справки.
    

    postgres=# create database mydb; СОЗДАТЬ БАЗУ ДАННЫХ postgres=# \q vagrant@vagrant-ubuntu-trusty-64:~$ psql --username=postgres mydb psql (9.3.5) Введите "help" для получения справки.

    mydb=# создать таблицу data(entry bigint); СОЗДАТЬ ТАБЛИЦУ mydb=# insert into data values(1); INSERT 0 1 mydb=# insert into data values(2); INSERT 0 1 mydb=# insert into data values(3); INSERT 0 1 mydb=# \q

  4. Создайте резервную копию базы данных следующим образом:

    PGPASSWORD="postgres" pg_dump --dbname=mydb --username=postgres --format=custom > pg_backup.dump
  5. Удалите несколько строк из таблицы данных в mydb, чтобы мы могли определить, успешно ли мы восстановили данные.

  6. Восстановите базу данных с помощью:

    PGPASSWORD="postgres" pg_restore --clean --create --dbname=postgres --username=postgres pg_backup.dump

Данные восстанавливаются, но команда pg_restore в шаге 6 завершается со статусом 1 и выдает следующий результат:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR: схема "public" уже существует.
    Команда была: CREATE SCHEMA public;

ПРЕДУПРЕЖДЕНИЕ: ошибки игнорируются при восстановлении: 1

Я не могу просто проигнорировать это, потому что я запускаю эту команду программно и должен использовать статус выхода, чтобы определить, не удалось ли восстановление или нет. Изначально я задался вопросом, не возникла ли эта проблема из-за того, что я поместил свою базу данных в public (схема по умолчанию). Я предположил, что public будет создана в результате опции --create в pg_restore до восстановления данных (которая, возможно, попытается создать и эту схему, поскольку именно в ней находится моя таблица), но когда я попробовал выполнить описанные выше шаги с моей таблицей в другой схеме, результаты были такими же, и сообщения об ошибках были идентичными.

Я делаю что-то не так? Почему я вижу эту ошибку?

Решение

Ошибка безвредна, но чтобы избавиться от нее, я думаю, вам нужно разбить это восстановление на две команды, как например:

dropdb -U postgres mydb && \
 pg_restore --create --dbname=postgres --username=postgres pg_backup.dump

Опция --clean в pg_restore выглядит не очень, но на самом деле вызывает нетривиальные проблемы.

Для версий до 9.1

Комбинация --create и --clean в опциях pg_restore раньше была ошибкой в старых версиях PG (до 9.1). Действительно, существует некоторое противоречие между (цитирую manpage 9.1):

--clean Очищать (удалять) объекты базы данных перед их воссозданием.

и

--create Создать базу данных перед восстановлением в ней.

Ведь какой смысл очищать совершенно новую базу данных?

Начиная с версии 9.2

Эта комбинация теперь принята, и в документации говорится следующее (цитирую manpage 9.3):

--clean Очистить (сбросить) объекты базы данных перед их воссозданием. (Это может вызвать несколько безобидных сообщений об ошибках, если какие-либо объекты отсутствуют в целевой базе данных).

--create Создать базу данных перед восстановлением в нее. Если также указано --clean, сбросьте и воссоздайте целевую базу данных перед подключением к ней.

Теперь наличие обоих параметров вместе приводит к такой последовательности действий при восстановлении:

DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...

Нет DROP для каждого отдельного объекта, только DROP DATABASE в начале. Если не использовать --create, то все будет наоборот.

В любом случае эта последовательность вызывает ошибку public схема уже существует, потому что создание mydb из template0 уже импортировало ее (что нормально, в этом смысл шаблонной базы данных).

Я не уверен, почему этот случай не обрабатывается автоматически pg_restore. Возможно, это вызовет нежелательные побочные эффекты, когда администратор решит настроить template0 и/или изменить назначение public, даже если мы не должны этого делать.

Комментарии (1)

В моем случае причина была в том, что я использовал pg_restore из postgresql-contrib версии 11.2 для восстановления дампа, сделанного pg_dump 9.6, на кластере PostgreSQL 9.6.

После того, как я понизил версию pg_restore до 9.6, эта ошибка `schema "public" уже существует" исчезла, и процесс восстановления работал как прежде.

Комментарии (1)