Thread: Dump 7.1.3->7.4.2
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).
What are the difference between the 7.1.3 and 7.4.2 formats (seems to be
related at least to newlines) ? Is it possible to modify the dump with a sed
script to make it usable by 7.4.2 ?
Thanks
cyril
"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. -Doug
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 ?)
cyril
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? -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "In a specialized industrial society, it would be a disaster to have kids running around loose." (Paul Graham)
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
cyril
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 > > cyril > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > Cyril, 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. -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
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
Quoting Cyril VELTER <cyril.velter@metadys.com>: > 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 > > Not so much cygwin but the fact that the newline characters are different maybe? -- Keith C. Perry, MS E.E. Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com