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 first started 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 -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. Ergo I had 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 slightest nor 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 some time. 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 there though 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.
pgsql-novice by date
|Next:||From: Tom Lane||Date: 2005-09-15 20:17:37|
|Subject: Re: problem with pg_dump and subsequent restoration. |
|Previous:||From: Walker, Jed S||Date: 2005-09-15 19:53:24|
|Subject: Is a table temporary|