[Re] Re: Dump 7.1.3->7.4.2 - Mailing list pgsql-general

De : mailto:netadmin@vcsn.com
Emission : 02/06/2004 15:59:31

> Quoting Cyril VELTER <cyril.velter@metadys.com>:
> > From: "Alvaro Herrera" <alvherre@dcc.uchile.cl>
> > > On Mon, May 31, 2004 at 07:57:01AM +0200, Cyril VELTER wrote:
> > > > From: "Doug McNaught" <doug@mcnaught.org>
> > > > > "Cyril VELTER" <cyril.velter@metadys.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