Переезд базы данных PostgreSQL (отношение не существует)

Когда разворачивается дамп на новом сервере, возникают ошибки со внешними ключами (foreign key). Связанная таблица восстанавливается после зависимой. И база данных выдаёт ошибку что такой зависимости не существует и т.п.

pg_restore: [архиватор (БД)] could not execute query: ОШИБКА:  отношение "tablename" не существует

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

Создать дамп схемы
Разделить дамп схемы на непосредственно дамп создания таблиц, и дамп создания внешних ключей
Создать дамп данных

Создать дамп схемы

pg_dump --host 127.0.0.1 --port 5432 --username "user_name" --no-password  --format plain --schema-only --file "db_schema.sql" "db_name"

Разделить дамп схемы

В файле db_schema.sql найти место где после создания таблиц в "простом" (plain) дампе начинается место с созданием последовательностей для первичных ключей с автоинкрементом.

ALTER TABLE ONLY table_name ALTER COLUMN id SET DEFAULT nextval('table_name_id_seq'::regclass);

С этого момента все строки поместить в отдельный файл db_foreign_keys.sql, удалив их из db_schema.sql

Создать дамп данных

pg_dump --host 127.0.0.1 --port 5432 --username "user_name" --no-password  --format plain --data-only --inserts --file "db_data.sql" --table "public.table_1" --table "public.table_2" "db_name"

Восстановление

При восстановлении дампа я пошёл по следующему пути

Восстановить схему
Восстановить данные
Восстановить внешние ключи

psql -Uuser_name -d db_name < db_schema.sql
psql -Uuser_name -d db_name < db_data.sql
psql -Uuser_name -d db_name < db_foreign_keys.sql

Есть и другие более грамотные способы. Но этот тоже железно работает.

Сообщение по данной теме:

Harald Armin Massa wrote:
 > [snipped text]
> pg_dump --data-only -U user database
>
> and tried to reload the data. But it fails on foreign keys: depending tables
> are being dumped before the tables they depend on.
>
> I solved it by manually dumping the relevant tables and reloading them,
>
> Now I cannot find documentation
> - if pg_dump is supposed to produce a "ordered dump" so that not doing is a
> bug and I need to present a showcase
> - or if it is simply not implemented and an enhancement request;
> - or if it is even on a theoretical basis impossible to derive the correct
> order. [circular foreign keys came to my mind]

Hi

There are three possibilities to solve this
1) Use pg_dump (or pg_restore, if you are using custom-format dumps)
with the --disable-trigger option (check the man-page for the exact
syntax). This will disable all triggers, including those which check the
foreign-key contraints during the restore

2) If all your foreign keys are defined as "deferrable", you can
wrap the data-loading in a transaction, and do "set contraints all
deferred" before loading the data. This will defer the constraint-checks
until you issue commit.

3) If you have a schema and data-dump in seperate files, you could
manually split the schema dump into two files, one containing all
table definitions, the other containing the f-k definitions. You
can then first restore the schema, then your data, and finally your
foreign keys.

The ordering of the three options in terms of speed is 1 < 3 < 2, I
believe - but 2 and 3 give you additional security, because they check
the foreign keys during the import. 1) relies on the fact the the
dump doesn't containt foreign-key violations.

greetings, Florian Pflug

Original

Полезный ресурс по настройке PostgreSQL

PgTune — настройка производительности PostgreSQL для заданной аппаратной конфигурации (онлайн версия)

Переезд базы данных PostgreSQL (отношение не существует)