beginners postgres question

[Ismail, Rafiq (IT)]

> -----Original Message-----
> From: london.pm-bounces at london.pm.org 
> [mailto:london.pm-bounces at london.pm.org] On Behalf Of graham
> Sent: 08 October 2007 11:25
> To: london.pm at london.pm.org
> Subject: beginners postgres question
...
> 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 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.
> 
....
> 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?
> 

When using postgres, there was a rather helpful postgres list, which I'd
go to when in doubt:

http://archives.postgresql.org/pgsql-admin/

I'd often dumped db's and restored them - for the purpose of pulling
test data - and never had any issues with referencial integrity.  What I
might suggest is dumping with the -i (dump as inserts statements) and
--disable-triggers flag - which is what I tended to do.  Assuming that
you've altered tables and the new schema is a superset of the old schema
+ alterations, I think that you should be able to repopulate this data
with specific inserts.  The dump ordering problem was probably resolved
by using the --disable-triggers option, which is supposed to ignore fk
constraints during the repopulation.  There might be a problem in that
this is supposed to do some magic with the internal pg_tables and I'd
imagine that if the internal oid's changed, then there might be an
issue.  

That said, to reach this state, you would need to start a afresh and
refresh the db to it's original state - and then dump again.

Another thing you might try doing is dump 'anything' with the disable
triggers option, have a look at the alterations made to disable ref.
integrity checks, and then, depending on how straight forward this is,
just duplicate this across 'all oid's' and then run your restore.  Once
done, re-enable across the board as well?

Just Thoughts,

R.
--------------------------------------------------------

NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error.