Обсуждение: Restore SQL_ASCII backup...

Hi all,

I'm trying to restore an old backup (7.1.4) into a new PG database (8.4). There are errors with some of the RI and
sequences,but those are easy to fix. 

The problem is that one table in the DB has TEXT fields in it and these seem to be sensitive to the DB encoding. The
defaultin the days of 7.1.4 was SQL_ASCII. The new default is UTF-8. pg_restore won't load the old table data into the
newtable. 

I tried creating a new DB with encoding SQL_ASCII and restoring the backup into that, but I get the error:

pg_restore: [archiver (db)] COPY failed: ERROR:  literal carriage return found in data
HINT:  Use "\r" to represent carriage return.
CONTEXT:  COPY encounters, line 9711: ""
WARNING: errors ignored on restore: 1
tarka@jetty:~$ psql -d triagedb
psql (8.4.1)
Type "help" for help.

The other problem is that the backups are in compressed format because I needed to save space on that system, so I
can'tjust edit the text of the data directly. 

What should I do at this point? Try to create an install of an old version of PG and restore into that?

Thanx in advance,

Brian

Le mercredi 2 décembre 2009 à 07:34:09, Brian Myers a écrit :
> Hi all,
>
> I'm trying to restore an old backup (7.1.4) into a new PG database (8.4).
>  There are errors with some of the RI and sequences, but those are easy to
>  fix.
>
> The problem is that one table in the DB has TEXT fields in it and these
>  seem to be sensitive to the DB encoding. The default in the days of 7.1.4
>  was SQL_ASCII. The new default is UTF-8. pg_restore won't load the old
>  table data into the new table.
>
> I tried creating a new DB with encoding SQL_ASCII and restoring the backup
>  into that, but I get the error:
>
> pg_restore: [archiver (db)] COPY failed: ERROR:  literal carriage return
>  found in data HINT:  Use "\r" to represent carriage return.
> CONTEXT:  COPY encounters, line 9711: ""
> WARNING: errors ignored on restore: 1
> tarka@jetty:~$ psql -d triagedb
> psql (8.4.1)
> Type "help" for help.
>
> The other problem is that the backups are in compressed format because I
>  needed to save space on that system, so I can't just edit the text of the
>  data directly.
>

You still can get it:

  pg_restore backup > backup.sql

will give you the SQL script.


--
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com