beginners postgres question

[Peter Corlett]

On Mon, Oct 08, 2007 at 11:25:08AM +0100, graham wrote:
[...]
> At this point I discover that pg_dump produces dump files which cannot be
> reloaded, since they take no account of dependencies. For example, I have
> a lot of join tables, which necessarily depend on foreign keys from other
> tables. But pg_dump produces output with these tables BEFORE the ones the
> keys come from.

They certainly should be reloadable. I certainly don't have that problem
with pg_dump.

If you look at a typical dump file, you'll see that it creates the tables
without constraints and then adds the constraints after all of the tables
have been loaded. There's not really another sane method: you could have
tables that are self-referential or mutually-referential and those couldn't
be dumped no matter which order they were emitted.

What is probably happening is that you're trying to load over the top of an
existing database and the existing constraints are getting in the way. Use
the -c option to pg_dump to emit code to drop the existing tables first.
There are many other useful options to pg_dump that allow you to hand-tune
the output for various purposes.

-- 
Improvable Tripe: http://tripe.cabal.org.uk/