Re: problem with pg_dump and subsequent restoration.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Morgan Kita" <mkita(at)verseon(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: problem with pg_dump and subsequent restoration.
Date: 2005-09-15 20:17:37
Message-ID: 16202.1126815457@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

"Morgan Kita" <mkita(at)verseon(dot)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. Ergo I 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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2005-09-15 20:20:45 Re: Is a table temporary
Previous Message Morgan Kita 2005-09-15 19:57:24 problem with pg_dump and subsequent restoration.