pg_dump and pg_restore problem

Поиск
Список
Период
Сортировка
От Michele Petrazzo - Unipex
Тема pg_dump and pg_restore problem
Дата
Msg-id 49F7FCB3.9030109@unipex.it
обсуждение исходный текст
Ответы Re: pg_dump and pg_restore problem  (Alan Hodgson <ahodgson@simkin.ca>)
Список pgsql-general
Hi all,
I had a big problem that made me crazy... I want to simple backup some
tables from a my db and restore them to another.

My db:
table_two(
     id serial PRIMARY KEY,
     ...
)
table_one (
     id serial PRIMARY KEY,
     real_name text NOT NULL,
     username text NOT NULL,
     id_table_two integer REFERENCES table_two(id),
     ...
)
table_three (
     id serial PRIMARY KEY,
     id_user integer REFERENCES table_one (id) NOT NULL,
)...

For do this, I use pg_dump with two pass:
pg_dump --format=c --schema-only my_db > file_schema
pg_dump --format=c -a -t table_one -t table_two -t table_three > file_data

when I, into another host (same 8.3.7 version), execute the pg_restore I
receive a striking reply:

(drop db && create db && pg_restore -d my_db file_schema &&
pg_restore -d my_db file_data)

g_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1877; 0 16395 TABLE
DATA table_thee my_db
pg_restore: [archiver (db)] COPY failed: ERROR:  insert or update on
table "table_three" violates foreign key constraint
"table_thee_id_user_fkey"
DETAIL:  Key (id_user)=(644) is not present in table "table_one".
WARNING: errors ignored on restore: 1

On the 2^th host whit a "select id from table_one where id=644" I see
the data
there! And on table_three (of course?) there is no data...

Moving the pg_dump to the standard sql format, inside the sql file I see
that the table_three id_user=644 are the first of the exported records

The unique solution that I found it's that to export with pg_dump all my
tables except the table_three and, after, exporting only that and on the
other host and import that alone...

It's this a normal behavior, a "missing feature", or...?

Thanks,
Michele

В списке pgsql-general по дате отправления:

Предыдущее
От: Raymond O'Donnell
Дата:
Сообщение: Re: [Slony1-general] pgpass.conf for Windows Vista
Следующее
От: Jasen Betts
Дата:
Сообщение: Re: triggers and execute...