From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-admin@postgresql.org
Subject: Re: FK disappeared in 8.3.3
Date: Mon, 22 Nov 2010 18:57:18 +0300
User-Agent: KMail/1.9.7
References: <201011221213.32035.achill@matrix.gatewaynet.com>
	<4CEA30A90200002500037CE1@gw.wicourts.gov>
In-Reply-To: <4CEA30A90200002500037CE1@gw.wicourts.gov>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201011221757.18325.achill@matrix.gatewaynet.com>

Kevin thanx,
I explicitly wrote that:
"One additional issue that is worsening our situation is that we dont have =
TCP/IP access to the vessel.=20
We only have term emulator (minicom) which dials up a remote mgetty=20
(which works only if the weather conditions are ok, and nothing else is bro=
ken: read if we are lucky)"
(which means that any upgrade ... aspirations are ... optimistic in the bes=
t case scenario.)
Now if what you asked is why didnt i upgrade the DB to whatever newest 8.3.=
* version existed
at the mid of July, the answer is that we have not done an upgrade plan yet=
, so yes we prefer to keep
the same version on all vessels, until we are capable (find resources/time)=
 to design a proper upgrade
plan (i repeat we do not have TCP/IP access to the DBs)
(imagine having to upgrade 61 installations for which you dont have ssh/scp=
 ;) sounds fun right? )

To your question about versions, yes both are 8.3.3, and the pg_dump used t=
o diff the schemas was from 8.3.3

=CE=A3=CF=84=CE=B9=CF=82 Monday 22 November 2010 16:58:17 =CE=BF/=CE=B7 Kev=
in Grittner =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
> =20
> > One (ON DELETE CASCADE) FK constraint which was supposed to be
> > there was found to be (mysteriously) absent.
> =20
> Do you have old backups to see how long it has been gone?
> =20
> > So we pg_dumped the schema on a known good 8.3.3 identical test
> > database
> =20
> 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.
> =20
> http://www.postgresql.org/support/versioning
> =20
> The current 8.3 release is 8.3.12.  For details of what's been
> fixed, see this:
> =20
> http://www.postgresql.org/docs/8.3/static/release.html
> =20
> > and compared it against the suspicious schema on the problematic
> > vessel.  The diff (without options) alone produced ~ 7500 lines of
> > output.
> =20
> 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.)
> =20
> > Both databases were created with the same procedure using dumps
> > from 7.4.2.
> =20
> The current version of 7.4 is 7.4.30!:
> =20
> http://www.postgresql.org/docs/7.4/static/release.html
> =20
> I'm not clear what you mean, though.  Both databases are on 8.3.3?
> =20
> > 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.
> =20
> 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?
> =20
> > I am puzzled about the differences in the schema, if any one has
> > any ideas  of why this might be happening, would be great.
> =20
> My first guess is that they were dumped by pg_dump executables from
> different versions.
> =20
> -Kevin
>=20


=2D-=20
Achilleas Mantzios