beginners postgres question

[Paul Makepeace]

On 10/8/07, graham <graham at theseamans.net> wrote:
> This is a postgres question more than a perl one (ergo, on-topic, I hope)
>
> I just started using postgres being only used to mysql.
>
> I used dia and tedia2sql to generate my original db design.
>
> The db now contains a huge amount of data.
>
> I now need to add some new tables to the design. Rather than altering
> the live db and risking getting out of sync with the documentation, I

Why not dump your live db to another instance, do your DDL mods on
there, and when you've got them all working replay the work on live?

> dumped the data with pg_dump, generated the new schema from tedia2sql,
> recreated the empty database with the new tables and.... WTF?
>
> 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.
>
> Googling shows sporadic complaints about this problem from the beginning
>   of the millenium. Searching CPAN gives me no modules which are either
> alternatives to pg_dump or which postprocess the files to fix it.
>
> But dumping/reloading data is a basic function for a database which is
> sworn by (not at) by many members of this list, so I'm missing something
> obvious[1]. What?
>
> Thanks
> Graham
>
> [1] I'm not including manually editing a huge dump file
>