I very much doubt that the data wouldn't "dump
right". The dump will include any data that is in the table, including CRs
if they are in there.
In the current case, the CRs were not already in
there, because PostgreSQL would not have recognized that field as null ("\N") if
they were.
Any transfer of a file via a Windows machine is apt
to cause line endings to be silently changed, which would account for this
case. The real danger is where the rightmost field is a text type, because
the CRs would be silently imported into the new installation, where they could
cause real problems with any app that tries to use those fields, and a headache
to debug.
----- Original Message -----
Sent: Thursday, August 23, 2001 6:32
PM
Subject: Re: problems transfering
databases
I've had this problem before. The data is
stored correctly in PG, it just doesn't dump right (the CR thing I
guess. I didn't know the reason at the time). I think this was on
7.0.x. I worked around it by doing a pg_dump that dumped the table as
"proper inserts". That make the load MUCH slower, but it works around
the issue. Now that I know what the cause might have been, I guess I
better go look at my data . . .
--rob
----- Original Message -----
Sent: Thursday, August 23, 2001 10:59
AM
Subject: Re: problems transfering
databases
Looks like you already have the answer.
The error message is a dead giveaway. Some field, in this case having
a null value (represented as '\N') contains a carriage return character,
like: '\NCR'. PostgreSQL cannot make sense out of this combination, so
it returns an error. The CR contained in the error message causes the
output to return to the start of the line, outputting the rest of the
message from the beginning: see how the final " ' " overwrites the "p" in
"psql".
Edit out the CR characters, and the problem
will go away.
This is what Tom already said; I am expanding
on it.
----- Original Message -----
Sent: Thursday, August 23, 2001 3:18
AM
Subject: Re: [GENERAL] problems
transfering databases
Hello Tom,
thanks for the answer. This is the procedure, how we tried to transfer
the data from one machine to another. My collegue has used the
pg_dumpall command, in the following way:
pg_dumpall > backups/2001-06-04db.out
Since it is a simple test database, the 2001-06-04db.out file was
around 40kB, so he has sent it to me via email. I tried to load the
data from the file in two different ways:
psql -f 2001-08-21db.out cat 2001-06-04db.out | psql
but got the error like stated before. I've tried to do this on Solaris
and Linux (I've set postgreSQL up on my Linux box for testing purposes),
but the same error pops up.
Could it have something to do with the versions of the databases, and
how it was installed? The version I use is psql (PostgreSQL) 7.1.2
(the output of psql -V command), but I'm not sure what version did my
collegue use (I'll find that out, but I think it is 7.x.x).
Regards, Miroslav
Tom Lane wrote:
Miroslav Koncar
<[EMAIL PROTECTED]> writes: >
'sql:2001-08-21db.out:5: ERROR: copy: line 1, Bad abstime external
> representation '\N > psql:2001-08-21db.out:5: PQendcopy:
resetting connection
Given the weird line-wrapping of the message, I'm going to bet that
the problem is that newlines in the dump file have gotten converted
to DOS format (ie, \n became \r\n), and COPY is unhappy because it
sees the \r as part of the data. When it echoes the data it
didn't like, the presence of the \r messes up the format of the
error message.
Not sure *how* that happened in a Linux-to-Solaris transfer, though;
what tool did you use to transfer the dump file?
regards, tom lane --
Miroslav Koncar
Software Engineer
Ericsson Nikola Tesla
ETK/D/R
Tel: +385 1 365 3479
Fax: +385 1 365 3548
mailto:[EMAIL PROTECTED]
|