beginners postgres question
paulm at paulm.com
Mon Oct 8 15:06:01 BST 2007
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. What?
>  I'm not including manually editing a huge dump file
More information about the london.pm