Dump 7.1.3->7.4.2

[Cyril VELTER]

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*******@postg resql.org

[Doug McNaught]

"Cyril VELTER" <cy**********@m etadys.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

[Cyril VELTER]

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

"Cyril VELTER" <cy**********@m etadys.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

[Alvaro Herrera]

On Mon, May 31, 2004 at 07:57:01AM +0200, Cyril VELTER wrote:

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

"Cyril VELTER" <cy**********@m etadys.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

[Cyril VELTER]

From: "Alvaro Herrera" <al******@dcc.u chile.cl>

On Mon, May 31, 2004 at 07:57:01AM +0200, Cyril VELTER wrote:

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

"Cyril VELTER" <cy**********@m etadys.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
010001002020100 00000000..."

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*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Keith C. Perry]

Quoting Cyril VELTER <cy**********@m etadys.com>:

From: "Alvaro Herrera" <al******@dcc.u chile.cl>

On Mon, May 31, 2004 at 07:57:01AM +0200, Cyril VELTER wrote:

From: "Doug McNaught" <do**@mcnaught. org>
> "Cyril VELTER" <cy**********@m etadys.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
010001002020100 00000000..."

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*******@postg resql.org so that your
message can get through to the mailing list cleanly

Cyril,

Maybe I'm misunderstandin g 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

[Cyril VELTER]

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

Quoting Cyril VELTER <cy**********@m etadys.com>:

From: "Alvaro Herrera" <al******@dcc.u chile.cl>

On Mon, May 31, 2004 at 07:57:01AM +0200, Cyril VELTER wrote:
> From: "Doug McNaught" <do**@mcnaught. org>
> > "Cyril VELTER" <cy**********@m etadys.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
010001002020100 00000000..."

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 misunderstandin g 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

[Keith C. Perry]

Quoting Cyril VELTER <cy**********@m etadys.com>:

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

Quoting Cyril VELTER <cy**********@m etadys.com>:

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

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 misunderstandin g 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