Обсуждение: dump from 9.0 to 8.3

Hello,
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 ?

I tried with pg_dump -i from the 8.3 server without good results.

--
Achilleas Mantzios

Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:

> 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 ?
>
> I tried with pg_dump -i from the 8.3 server without good results.

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.

-Kevin

Στις Monday 20 June 2011 18:46:33 ο/η Kevin Grittner έγραψε:
> Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
>
> > 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 ?
> >
> > I tried with pg_dump -i from the 8.3 server without good results.
>
> 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.
>

Thanx, unfortunately the dump is 310GB, and that rather forces me to go to the
"migration" direction.

Currently we run a central master system, (which i try to upgrade) and ~ 65 slaves in remote locations
(very few of them have TCP/IP connectivity).

I thought of migrating only the central system and leave the slaves at 8.3 but this possibility
seems rather weak.

> -Kevin
>



--
Achilleas Mantzios

On Tue, Jun 21, 2011 at 09:10:50AM +0300, Achilleas Mantzios wrote:
> Στις Monday 20 June 2011 18:46:33 ο/η Kevin Grittner έγραψε:
> > Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
> >
> > > 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 ?
> > >
> > > I tried with pg_dump -i from the 8.3 server without good results.
> >
> > 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.
> >
>
> Thanx, unfortunately the dump is 310GB, and that rather forces me to go to the
> "migration" direction.

I may get beaten up for this but... slony? :)

Afaik should be doable. Your tables just need primary keys.

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

Στις Tuesday 21 June 2011 09:47:22 ο/η CaT έγραψε:
> On Tue, Jun 21, 2011 at 09:10:50AM +0300, Achilleas Mantzios wrote:
> > Στις Monday 20 June 2011 18:46:33 ο/η Kevin Grittner έγραψε:
> > > Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
> > >
> > > > 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 ?
> > > >
> > > > I tried with pg_dump -i from the 8.3 server without good results.
> > >
> > > 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.
> > >
> >
> > Thanx, unfortunately the dump is 310GB, and that rather forces me to go to the
> > "migration" direction.
>
> I may get beaten up for this but... slony? :)
>
> Afaik should be doable. Your tables just need primary keys.
>
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
are supposed to build a new server for it, which contains a subset of the master 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 exactly the set
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 what to do next.
If we move the central master to 9.0, then chances are at the moment that we 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 upgrades
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 that might help.

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



--
Achilleas Mantzios

Στις Monday 20 June 2011 18:46:33 γράψατε:
> Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
>
> > 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 ?
> >
> > I tried with pg_dump -i from the 8.3 server without good results.
>
> 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.

For the moment, from the server running 9.0,

ALTER DATABASE dynacom SET bytea_output = 'escape';
pg_dump dynacom | sed 's/CREATE OR REPLACE/CREATE/g' | bzip2 | ssh 10.9.200.254 "bzcat | psql dynacom -f -"

seem to do the trick, till now at least, the restore is not yet complete.

>
> -Kevin
>



--
Achilleas Mantzios