Обсуждение: BUG #15382: Error create dictionary in pg_dump

The following bug has been logged on the website:

Bug reference:      15382
Logged by:          Андрей Ковальчук
Email address:      mr.kovalchuk.andrey@gmail.com
PostgreSQL version: 10.5
Operating system:   Ubuntu 16.04
Description:

Hello!

I need for dump my database and i used command:
/usr/lib/postgresql/10/bin/pg_dump" --host /tmp --port 50432 --username
postgres --schema-only --quote-all-identifiers --binary-upgrade
--format=custom  --file="pg_upgrade_dump_2678369.custom" 'dbname=apteka25'
>> "pg_upgrade_dump_2678369.log" 2>&1

Dump is creates, but have one trouble. Result of using pg_resote command:

pg_restore: создаётся TEXT SEARCH DICTIONARY "public.ispell_apteka25"
pg_restore: создаётся TEXT SEARCH DICTIONARY "public.ispell_russian"
pg_restore: создаётся TEXT SEARCH DICTIONARY "public.simple_apteka25"
pg_restore: создаётся TEXT SEARCH DICTIONARY "public.synonym_apteka25"
pg_restore: создаётся TEXT SEARCH DICTIONARY "public.thesaurus_apteka25"
pg_restore: [архиватор (БД)] Ошибка при обработке оглавления:
pg_restore: [архиватор (БД)] Ошибка из записи оглавления 2125; 3600 2618599
TEXT SEARCH DICTIONARY thesaurus_apteka25 postgres
pg_restore: [архиватор (БД)] could not execute query: ОШИБКА (Error):
словарь текстового поиска (text search dictionary) "ispell_apteka25" не
существует (not exists)
    Выполнялась команда (running command): CREATE TEXT SEARCH DICTIONARY
"public"."thesaurus_apteka25" (
    TEMPLATE = "pg_catalog"."thesaurus",
    dictfile = 'apteka25', dictionary = 'ispell_apteka25' );

If i change argument dictionary for this note to:  dictionary =
'public.ispell_apteka25' - it's now ok!
Chage dump file - not my way. How i can fix it? Or it's really bug? 

I find this problen also in 9.5v.

Thanks.

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> Dump is creates, but have one trouble. Result of using pg_resote command:

> pg_restore: [архиватор (БД)] could not execute query: ОШИБКА (Error):
> словарь текстового поиска (text search dictionary) "ispell_apteka25" не
> существует (not exists)
>     Выполнялась команда (running command): CREATE TEXT SEARCH DICTIONARY
> "public"."thesaurus_apteka25" (
>     TEMPLATE = "pg_catalog"."thesaurus",
>     dictfile = 'apteka25', dictionary = 'ispell_apteka25' );

Yeah, this is a consequence of the security changes we made awhile back
to run dump and restore under restrictive search_path settings.  You need
to explicitly schema-qualify that sub-dictionary name, since it isn't in
the pg_catalog schema.  Unfortunately, since dictionary parameters are
just strings without a lot of fixed semantics to them, pg_dump can't
really fix this for you --- it doesn't know that that parameter refers
to a database object, or which database object.

> If i change argument dictionary for this note to:  dictionary =
> 'public.ispell_apteka25' - it's now ok!

Yup, that's what you have to do.  Note that this is a good thing
anyway, independently of pg_dump, because your old definition could
have failed if the thesaurus dictionary were invoked by any
application running under a nondefault search_path.

            regards, tom lane

=?UTF-8?B?0JDQvdC00YDQtdC5INCa0L7QstCw0LvRjNGH0YPQug==?= <mr.kovalchuk.andrey@gmail.com> writes:
> Understood why. But I did not know what doing. Particularly, I need for
> upgrade my cluster to new version (9.5 to 10) and i used pg_upgrade
> command. Upgrade in turn used pg_dump, pg_restore combination and I can't
> affect for this.

I think what you need to do is fix the dictionary definition in the
source database, probably like this:

ALTER TEXT SEARCH DICTIONARY "public"."thesaurus_apteka25" (
  dictionary = 'public.ispell_apteka25'
);

            regards, tom lane