[Re] Re: Dump 7.1.3->7.4.2

De : mailto:netadmin(at)vcsn(dot)com
Emission : 02/06/2004 15:59:31

> Quoting Cyril VELTER <cyril(dot)velter(at)metadys(dot)com>:
> > From: "Alvaro Herrera" <alvherre(at)dcc(dot)uchile(dot)cl>
> > > On Mon, May 31, 2004 at 07:57:01AM +0200, Cyril VELTER wrote:
> > > > From: "Doug McNaught" <doug(at)mcnaught(dot)org>
> > > > > "Cyril VELTER" <cyril(dot)velter(at)metadys(dot)com> writes:
> > > > >
> > > > > > I'm trying to dump a database from a 7.1.3 server to a 7.4.2 one.
> > > > > > It doesn't works because of difference in COPY format (unless I
> > > > > > use -d which is VERY slow on a 16G database).
> > > > >
> > > > > Try using the 7.4.2 version of pg_dump to dump out the 7.1.3
> > > > > database. pg_dump is written to talk to multiple versions of the
> > > > > server, and it's generally recommended to use the same version of
> > > > > pg_dump as the server you're restoring into.
> > > >
> > > > That's what I already do, but the problem is in COPY TO format
> > > > (which is handled by the backend ? right ?)
> > >
> > > What backend? 7.4-pg_dump will generate 7.4-backend's compatible
> > > input, and the 7.1-backend does not interact at all --- save with
> > > 7.4-pg_dump, which will make itself understood easily ...
> > >
> > > Lots of people (including me) use this procedure to upgrade rather
> > > smoothly. What's your problem exactly?
> >
> > Thanks for your response,
> >
> > Perhaps I'm mistaken, but it seems that pg_dump (the 7.4 one) will send
> > a COPY to STDOUT command to the 7.1 backend and blindly copy the output to
> > the dump file.
> >
> > Here's the kind of errors I get :
> >
> > ERROR: literal carriage return found in data
> > HINT: Use "\r" to represent carriage return.
> > CONTEXT: COPY c244, line 221: "662188 1002 1002 2002-08-05
> > 12:15:12.20+00 2002-08-05 12:15:12.20+00 274 0000
> > 01000100202010000000000..."
> >
> > By looking more closely in the dump file, there is a CR (embedded in a
> > text field) which is not encoded and confuse the restore
> >
>
> Maybe I'm misunderstanding you but are you saying you already have the dump
> file? If not (of if you can connect to the 7.1.3 server with the 7.4.x dump
> program), you can, over tcp/ip, dump the data from 7.1.3 to a file and then
> reload that file into your 7.4.x cluster. I've upgraded 7.1.3 to 7.4 and
7.4.1
> this way after I found out that the 7.4 pg_dump had problem reading the 7.1.3
> file. This is way folks are saying its best to use the dump utils from your
> destination cluster (7.4.2 in your case) to move your data.
>

I've two postgres instances on two different machines (one is 7.1.3 under
cygwin the other 7.4.2 under linux). I use the 7.4 pg_dump binary under linux
to dump the 7.1 database either to a file to inspect it or piped to the 7.4
psql connected to the 7.4 database.

I hope this is more clear.

I've worked out a solution which I have only tested on some tables. I use sed
to replace embeded cr by the escaped form (\r). Will test this out with the
complete database.

Perhaps it's because of cygwin ?

Thanks,

Cyril