467,923 Members | 1,289 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,923 developers. It's quick & easy.

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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #1
  • viewed: 1756
Share:
7 Replies
"Cyril VELTER" <cy**********@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2

From: "Doug McNaught" <do**@mcnaught.org>

"Cyril VELTER" <cy**********@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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #3
On Mon, May 31, 2004 at 07:57:01AM +0200, Cyril VELTER wrote:

From: "Doug McNaught" <do**@mcnaught.org>
"Cyril VELTER" <cy**********@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)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #4

From: "Alvaro Herrera" <al******@dcc.uchile.cl>
On Mon, May 31, 2004 at 07:57:01AM +0200, Cyril VELTER wrote:
From: "Doug McNaught" <do**@mcnaught.org>
"Cyril VELTER" <cy**********@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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #5
Quoting Cyril VELTER <cy**********@metadys.com>:

From: "Alvaro Herrera" <al******@dcc.uchile.cl>
On Mon, May 31, 2004 at 07:57:01AM +0200, Cyril VELTER wrote:
From: "Doug McNaught" <do**@mcnaught.org>
> "Cyril VELTER" <cy**********@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 ma*******@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #6
De : mailto:ne******@vcsn.com
Emission : 02/06/2004 15:59:31
Quoting Cyril VELTER <cy**********@metadys.com>:
From: "Alvaro Herrera" <al******@dcc.uchile.cl>
On Mon, May 31, 2004 at 07:57:01AM +0200, Cyril VELTER wrote:
> From: "Doug McNaught" <do**@mcnaught.org>
> > "Cyril VELTER" <cy**********@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #7
Quoting Cyril VELTER <cy**********@metadys.com>:
De : mailto:ne******@vcsn.com
Emission : 02/06/2004 15:59:31
Quoting Cyril VELTER <cy**********@metadys.com>:
From: "Alvaro Herrera" <al******@dcc.uchile.cl>
> On Mon, May 31, 2004 at 07:57:01AM +0200, Cyril VELTER wrote:
> > From: "Doug McNaught" <do**@mcnaught.org>
> > > "Cyril VELTER" <cy**********@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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Ravi Tallury | last post: by
3 posts views Thread by Chris Stiles | last post: by
2 posts views Thread by Gregory Stone | last post: by
7 posts views Thread by Matt | last post: by
9 posts views Thread by Microsoft News Server | last post: by
3 posts views Thread by Andrei Ivanov | last post: by
6 posts views Thread by EgyŁd Csaba | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.