Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group