From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-admin@postgresql.org
Subject: Re: dump from 9.0 to 8.3
Date: Tue, 21 Jun 2011 10:59:15 +0300
User-Agent: KMail/1.9.7
References: <201106201841.36877.achill@matrix.gatewaynet.com>
	<201106210910.51035.achill@matrix.gatewaynet.com>
	<20110621064721.GH22320@zip.com.au>
In-Reply-To: <20110621064721.GH22320@zip.com.au>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201106211059.15309.achill@matrix.gatewaynet.com>

=CE=A3=CF=84=CE=B9=CF=82 Tuesday 21 June 2011 09:47:22 =CE=BF/=CE=B7 CaT =
=CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> On Tue, Jun 21, 2011 at 09:10:50AM +0300, Achilleas Mantzios wrote:
> > =CE=A3=CF=84=CE=B9=CF=82 Monday 20 June 2011 18:46:33 =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
> > > > is there any quick and dirty (or otherwise easy way) to have a
> > > > smooth procedure to load into a 8.3 server data dumped by an 9.0
> > > > server ?
> > > >=20
> > > > I tried with pg_dump -i from the 8.3 server without good results.
> > > =20
> > > Your best bet might be to dump using the 9.0 pg_dump and then see
> > > where the errors are on the load attempt, and edit the file as
> > > needed.
> > > =20
> >=20
> > Thanx, unfortunately the dump is 310GB, and that rather forces me to go=
 to the
> > "migration" direction.
>=20
> I may get beaten up for this but... slony? :)
>=20
> Afaik should be doable. Your tables just need primary keys.
>=20
I think slony is would be an overkill.

The issue is that we make new slaves on demand. Mgmt is buying a new vessel=
 and we=20
are supposed to build a new server for it, which contains a subset of the m=
aster DB.

(Once deployed), The whole system is based on a heavily modified version of=
 DBMirror
which does a lot of nice things, asynchronous, lazy, FK-orineted, row-level=
, fine grained
replication, which makes sure that at any point the vessel slave DB has exa=
ctly the set=20
of data needed in order for the DB to be consistent, nothing less than that=
 and nothing more.
This way we guarantee correct operation and minimal communication cost (via=
 SAT).
Once a new row enters a table, and this row is indeed needed for a vessel (=
or a set of vessels)
then it will cause a recursive replication of all parent rows in all parent=
 tables involved in FK
relationships to the said table.

So our problem is the initial build of the vessel DB. We are wondering of w=
hat to do next.
If we move the central master to 9.0, then chances are at the moment that w=
e must upgrade
the procedure and the reference "mother" vessel system we keep here at the =
office
to 9.0 as well.
But this increases the management burden in our side, since we will have 3 =
kinds of slaves
in the vessels : 7.4, 8.3 and 9.0.
I would love to have someone traveling to all vessels and performing the up=
grades
but this is not realistic. So we might adopt this 3-version scheme, even if=
 it means
that we will have to take care of every SQL we write to be at least runable=
 in 8.3 and 9.0
(as an example CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql; which already=
 has biten me in 8.3)

Thank all you for all your thoughts shared and please point out anything th=
at might help.

> --=20
>   "A search of his car uncovered pornography, a homemade sex aid, women's=
=20
>   stockings and a Jack Russell terrier."
>     - http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-1=
111118083480
>=20


=2D-=20
Achilleas Mantzios