Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> wrote:
> One (ON DELETE CASCADE) FK constraint which was supposed to be
> there was found to be (mysteriously) absent.
Do you have old backups to see how long it has been gone?
> So we pg_dumped the schema on a known good 8.3.3 identical test
> database
That's an oxymoron. 8.3.3 has known bugs and security vulnerability
which have been fixed in maintenance releases which can be applied
without a dump and reload.
http://www.postgresql.org/support/versioning
The current 8.3 release is 8.3.12. For details of what's been
fixed, see this:
http://www.postgresql.org/docs/8.3/static/release.html
> and compared it against the suspicious schema on the problematic
> vessel. The diff (without options) alone produced ~ 7500 lines of
> output.
Were both databases at the same version number? Was the same
version of pg_dump used for both dumps? (Note: you can always dump
an older database with a newer version of pg_dump, but not vice
versa.)
> Both databases were created with the same procedure using dumps
> from 7.4.2.
The current version of 7.4 is 7.4.30!:
http://www.postgresql.org/docs/7.4/static/release.html
I'm not clear what you mean, though. Both databases are on 8.3.3?
> I must mention that the HW of the problematic vessel died some
> time around summer, and i had myself personally onboard, pg_dump
> the old DB, and restore it to the new box.
Did you get any errors when the dump was loaded? A damaged database
might have left orphaned rows which would have prevented creation of
the foreign key. Do you still have a dump file from that point?
> I am puzzled about the differences in the schema, if any one has
> any ideas of why this might be happening, would be great.
My first guess is that they were dumped by pg_dump executables from
different versions.
-Kevin