pg_restore при сбое таблицы из-за hstore

Фон:

Я использую PostgreSQL 9.3.5 на Ubuntu 14.04.

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

Ошибка:

При попытке восстановления получаю:

$ pg_restore -a --dbname=a193 -Fc --host=localhost --port=5434 --username=postgres -W --table=foo ~/tmp/a193.dump
Password: 
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4600; 0 26146 TABLE DATA foo u2su8s81ul0a52
pg_restore: [archiver (db)] COPY failed for table "foo": ERROR:  type "hstore" does not exist
LINE 6:     h_old hstore;

Расширение точно есть.

=> \dx
                                        List of installed extensions
+--------------------+---------+------------+--------------------------------------------------------------+
|        Name        | Version |   Schema   |                         Description                          |
+--------------------+---------+------------+--------------------------------------------------------------+
| dblink             | 1.1     | public     | connect to other PostgreSQL databases from within a database |
| hstore             | 1.2     | public     | data type for storing sets of (key, value) pairs             |
| isn                | 1.0     | public     | data types for international product numbering standards     |
| pg_stat_statements | 1.1     | public     | track execution statistics of all SQL statements executed    |
| pgcrypto           | 1.0     | public     | cryptographic functions                                      |
| plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language                                 |
| plpythonu          | 1.0     | pg_catalog | PL/PythonU untrusted procedural language                     |
| postgres_fdw       | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers           |
| uuid-ossp          | 1.0     | public     | generate universally unique identifiers (UUIDs)              |
+--------------------+---------+------------+--------------------------------------------------------------+
(9 rows)

И я могу использовать его в запросе (как пользователь postgres — та же роль, которую я использую выше для восстановления):

=> select current_user;
+--------------+
| current_user |
+--------------+
| postgres     |
+--------------+
(1 row)

=> \du
                                 List of roles
+----------------+------------------------------------------------+-----------+
|   Role name    |                   Attributes                   | Member of |
+----------------+------------------------------------------------+-----------+
| postgres       | Superuser, Create role, Create DB, Replication | {}        |
| u2su8s81ul0a52 |                                                | {}        |
+----------------+------------------------------------------------+-----------+

=> select 'a=>1'::hstore;
+----------+
|  hstore  |
+----------+
| "a"=>"1" |
+----------+
(1 row)

Вопросы:

  1. Почему я получаю эту ошибку, когда в базе данных установлено это расширение?
  2. Как я могу обойти эту проблему, кроме сброса триггера? Отбрасывание триггера — не самое худшее в мире, но кажется, что это должно быть возможно, и в производственной базе данных я хотел бы иметь возможность видеть контрольный след того, что кто-то восстановил данные и т. д.

postgresql pg-restore postgresql-9.3
person David S    schedule 28.01.2015    source источник
comment
Единственное, что я могу думать, это то, что hstore не находится в search_path для пользователя postgres, а для того, кого вы входите в систему, чтобы проверить. Попробуйте восстановить данные в файл, а не напрямую в БД, и посмотрите на SQL, если хотите проверить.   -  person Richard Huxton    schedule 28.01.2015
comment
@RichardHuxton Я обновил свой OP, чтобы включить информацию о том, под каким пользователем я вошел в систему, когда я делаю выбор как (то есть postgres).   -  person David S    schedule 28.01.2015
comment
Что ж, либо вы изменили приглашение по умолчанию, либо лишили postgres прав суперпользователя, потому что это неправильное приглашение для суперпользователя.   -  person Richard Huxton    schedule 28.01.2015
comment
Я изменил приглашение по умолчанию в .psqlrc   -  person David S    schedule 29.01.2015
comment
В какой схеме находится таблица, которую вы пытаетесь восстановить? pg_dump изменяет search_path в начале дампа с помощью SET search_pach=.... но не заботится о типах, которые используются из таблиц (ошибка pg_dump?). Если ваш дамп представляет собой обычный текст, посмотрите его в текстовом редакторе.   -  person Tom-db    schedule 29.01.2015
comment
Я столкнулся с точно такой же проблемой (stackoverflow.com /questions/28218855/). Похоже, это происходит только тогда, когда установлен флаг -t/--table.   -  person JacobEvelyn    schedule 29.01.2015
comment
@Томмазо. Я не уверен, как это может быть проблемой search_path (но, возможно, это так). Как вы можете видеть выше, hstore установлен как общедоступный. И я подтвердил, что общественность находится на пути поиска. Резервное копирование не выполняется при выполнении функции триггера аудита (которая использует hstore). Этот триггер находится в схеме аудита, которая всегда находится на пути поиска. Дамп был создан с опцией -Fc, поэтому он не текстовый. И я не думаю, что возможно восстановить одну таблицу из дампа простого текста.   -  person David S    schedule 29.01.2015
comment
@ Дэвид, мой вопрос был в том, в какой схеме находится таблица, а не триггер или хранилище. Рад, что вы нашли решение   -  person Tom-db    schedule 30.01.2015

Ответы (2)


arrow_upward
0
arrow_downward

Похоже, это ошибка либо в pg_dump, либо в pg_restore. По предложению Ричарда Хакстона выше я восстановил файл.

pg_restore --data-only --table=foo -f ~/tmp/foo.sql ~/tmp/a193.dump

Когда я посмотрел на содержимое, я обнаружил, что он делает следующее вверху:

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = myschema, pg_catalog;

Запуск этой строки изнутри psql с \i по-прежнему не работает, но редактирование последней строки для включения общедоступной схемы (где установлен hstore) работает.

SET search_path = myschema, pg_catalog, public;

Затем я могу запустить psql изнутри с помощью \i и импортировать потерянные данные.

person David S    schedule 29.01.2015
comment
Я не знаю точно, но похоже, что это не ошибка, а очень большая ошибка в том, как работает search_path. См.: postgresonline.com/article_pfriendly/279.html. - person UlfR; 06.05.2019

arrow_upward
0
arrow_downward

У меня была такая же проблема с двумя функциями (checksum и is_valid) и таблицей (master_values), определенной публично. Здесь checksum звонил is_valid, а master_values имел чек-ограничение:

"master_values_master_id_check" CHECK(is_valid(master_id))"

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

При попытке восстановить дамп я получил это во время восстановления:

pg_restore: [archiver (db)] COPY failed for table "master_values": ERROR:  function checksum(integer) does not exist

Странно то, что после восстановления обе функции и таблица есть и работают как положено. Единственное, чего не хватает, так это данных в master_values, которые не были восстановлены.

Это было решено путем указания search_path для is_valid:

ALTER FUNCTION is_valid SET search_path = public;

Для получения дополнительной информации см.:

person UlfR    schedule 06.05.2019