Обсуждение: problem with pg_dump and subsequent restoration.

Hi,

Yesterday I tried to restore from a data dump of my db from about a week ago. I am running 8.0.1 and have been since I
firststarted use postgresql. 

As an example when I ran the dump I used: pg_dump -a my_db -f my_db_bakup.sql

When restoring the database I first restored the schema from a separate backup of just the schema that I ran as pg_dump
-smy_db -f my_db_schema.sql. 

Then I restored the data like: psql my_db < my_db_bakup.sql.

However, for a few of my major tables I failed on foreign key constraints to tables that had not yet been loaded. Ergo
Ihad a order dependancy issue. 

Now I admit the schema was slightly newer than the data backup... but none of my major tables have changed in the
slightestnor have their foreign key constraints.  
In fact the fk constraints the restore failed on were a few of my most fundamental ones that have been there for quite
sometime. So I am not sure whats happening but 
it looks like the dump did not place the COPY statements for my tables in the correct order. I know the statements are
therethough because entries in the refered table that were not 
there when the fk constraint was checked, show up in the DB at the end.

Suggestions? I have an earlier dump that worked before, but I am concerned that the newer dump has a problem.

Thanks,
Morgan

"Morgan Kita" <mkita@verseon.com> writes:
> As an example when I ran the dump I used: pg_dump -a my_db -f my_db_bakup.sql

> When restoring the database I first restored the schema from a separate backup of just the schema that I ran as
pg_dump-s my_db -f my_db_schema.sql. 

> Then I restored the data like: psql my_db < my_db_bakup.sql.

> However, for a few of my major tables I failed on foreign key constraints to tables that had not yet been loaded.
ErgoI had a order dependancy issue. 

It's simpler, more reliable, and generally faster to just use a normal
(schema + data) dump.  pg_dump will manage the foreign keys properly
when you do that, but it cannot promise anything about data-only dumps.

If you're really intent on using data-only dumps, you can use
--disable-triggers while restoring, but bear in mind that that
defeats the foreign key checks entirely ... you might end up with
inconsistent data and not know it.

            regards, tom lane