Re: problems transfering databases - Mailing list pgsql-general

I never transfered the file anywhere, it was all done under linux.  The
bottom line is pg_dump follwed by a psql import failed exactly like what was
described in this thread.  When I did a dump with "proper inserts" it worked
fine.  As I said, I don't know what was causing the problem, just that the CR
thing sounded plausible.  I only do dumps with "proper inserts" now since I
know that this problem can occur and that "proper inserts" is a workaround.
I'm just confirming the behavior that someone else reported.

--rob

Jeff Eckermann <jeckermann@verio.net> said:

> 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 -----
>   From: Rob Arnold
>   To: Jeff Eckermann ; Miroslav Koncar
>   Cc: pgsql-general@postgresql.org
>   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 -----
>     From: Jeff Eckermann
>     To: Miroslav Koncar
>     Cc: pgsql-general@postgresql.org
>     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 -----
>       From: Miroslav Koncar
>       To: Tom Lane
>       Cc: pgsql-general@postgresql.org
>       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 <miroslav.koncar@etk.ericsson.se> 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:miroslav.koncar@etk.ericsson.se
>
>