Обсуждение: Restore Data Encountered the ERROR: literal carriage return found in data Error

Hi All,

After a pg_dumpall, I'm trying to restore with psql -e template1 -f 21.bak &>/tmp/out21.bak .  I'm trying to migrate to
Postgres8.3.8. 


I'm getting lots errors like this one:
"psql:21.bak:340557: ERROR:  literal carriage return found in data
HINT:  Use "\r" to represent carriage return."
After doing some research, one person posted here :
http://forums.devshed.com/postgresql-help-21/restoring-data-from-pg-7-1-3-7-4-release-99865.htmland this person said 
"sed 's/^M/\\r/' alldump.sql > alldump2.sql
where ^M is entered by pressing Ctrl+V then Enter. "

I'm confused why "Ctrl+V"?  I thought that is a paste function key.


Mary

On 04/03/10 23:52, Wang, Mary Y wrote:
> Hi All,
>
> After a pg_dumpall, I'm trying to restore with psql -e template1 -f
> 21.bak&>/tmp/out21.bak .  I'm trying to migrate to Postgres 8.3.8.

Are you running pg_dumpall from version 8.3.8?

> I'm getting lots errors like this one: "psql:21.bak:340557: ERROR:
> literal carriage return found in data HINT:  Use "\r" to represent
> carriage return."

I'd only expect this if an old pg_dumpall was being used.

After doing some research, one person posted here :
> http://forums.devshed.com/postgresql-help-21/restoring-data-from-pg-7-1-3-7-4-release-99865.html
> and this person said "sed 's/^M/\\r/' alldump.sql>  alldump2.sql
> where ^M is entered by pressing Ctrl+V then Enter. "
>
> I'm confused why "Ctrl+V"?  I thought that is a paste function key.

That'll be a terminal or shell escape sequence of some sort. It will
convert the following keypress into a control-code that will get
displayed as "^M" (ctrl+M = ascii 13 = CR).

You could just do: sed 's/\r/\\r/' ... though

--
   Richard Huxton
   Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> On 04/03/10 23:52, Wang, Mary Y wrote:
>> I'm getting lots errors like this one: "psql:21.bak:340557: ERROR:
>> literal carriage return found in data HINT:  Use "\r" to represent
>> carriage return."

> I'd only expect this if an old pg_dumpall was being used.

Mary's dealing with a version so old that the behavior of COPY itself
was different.  One possible workaround is to use the --inserts option
of pg_dump.  That will mean slower dump and restore, though.

            regards, tom lane

Rich - Thanks for explanation for ctrl+M = ascii 13 = CR.    Yes. I've been using a very old version of Postgres and
tryingto migrate to Postgres 8.3.8.   
Wow! Thanks Tom!  Yes. It took a long time to restore but it was worth it.  Using -inserts option of pg_dump were able
torestore lots of data. Now, I just need to find out why some rows of some tables weren't able to be restored (I'm sure
theyhad some kind of errors).  Happy Friday! 

Mary


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, March 05, 2010 5:36 AM
To: Richard Huxton
Cc: Wang, Mary Y; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Restore Data Encountered the ERROR: literal carriage return found in data Error

Richard Huxton <dev@archonet.com> writes:
> On 04/03/10 23:52, Wang, Mary Y wrote:
>> I'm getting lots errors like this one: "psql:21.bak:340557: ERROR:
>> literal carriage return found in data HINT:  Use "\r" to represent
>> carriage return."

> I'd only expect this if an old pg_dumpall was being used.

Mary's dealing with a version so old that the behavior of COPY itself was different.  One possible workaround is to use
the--inserts option of pg_dump.  That will mean slower dump and restore, though. 

            regards, tom lane