Thread: BUG #1050: cannot restore db at postgresql 7.4.1

The following bug has been logged online:

Bug reference:      1050
Logged by:          Taku YASUI

Email address:      tach@valinux.co.jp

PostgreSQL version: 7.4

Operating system:   Debian GNU/Linux

Description:        cannot restore db at postgresql 7.4.1

Details:

I tried to upgrade postgresql from 7.1.2(other host) to
7.4.1(localhost).  However an error occured and failed
restore.

I tried following steps.

   % pg_dump --version
   pg_dump (PostgreSQL) 7.4.1
   % pg_dump -b -Fc -h host -U user dbname -f dbname.dump
   % pg_restore -v -d dbname dbname.dump
   :
   pg_restore: restoring data for table "table_name"
   pg_restore: ERROR:  invalid input syntax for integer: "
   "
   CONTEXT:  COPY table_name, line 2, column column_name: "
   "
   pg_restore: [archiver (db)] error returned by PQendcopy
   pg_restore: *** aborted because of error

This error does not occur 7.3.4.  I found
a modification from 7.3 to 7.4 to cause this problem.
The release notes of 7.4 saids:

   COPY now can process files that use carriage-return or
carriage-return/line-feed end-of-line sequences. Literal carriage-returns
and line-feeds are no longer accepted in data values; use \r and \n instead.

When I tried to use '-d' option to run pg_dump,
pg_restore completed.  Therefore, I think this
modification causes the problem.

I think this is bug and pg_dump/pg_restore should
be fixed to keep with this modification.

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> The release notes of 7.4 saids:
>  COPY now can process files that use carriage-return or
>  carriage-return/line-feed end-of-line sequences. Literal carriage-returns
>  and line-feeds are no longer accepted in data values; use \r and \n instead.

> I think this is bug and pg_dump/pg_restore should
> be fixed to keep with this modification.

This was a deliberate change and will not be undone, even though it does
create compatibility issues with dumps from very old PG versions.
The best workaround I know of for dumping from such an old server is to
use the "dump as INSERT commands" option.

            regards, tom lane