problem with pg_dump and subsequent restoration.

From: "Morgan Kita" <mkita(at)verseon(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: problem with pg_dump and subsequent restoration.
Date: 2005-09-15 19:57:24
Message-ID: 08B420FF5BF7BC42A064212C2EB768801C10C6@neutron.verseon.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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 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.

Thanks,
Morgan

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2005-09-15 20:17:37 Re: problem with pg_dump and subsequent restoration.
Previous Message Walker, Jed S 2005-09-15 19:53:24 Is a table temporary