Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

The backup was created from a database with the UTF-8 encoding using pg_dump. The backup is in the tar format.

I then created a new database on another server running the same version of PostgreSQL (8.2.4) using this command:

createdb -E utf8 db1

When running pg_restore I get the following error:

pg_restore: [archiver (db)] Error from TOC entry 1667; 0 14758638 TABLE DATA table1 db1 
pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xc520

The original databse is no longer available.

How can I restore this data or find the byte sequence that is causing the problem?

share|improve this question
    
What arguments did you use to pg_restore? –  Paul Tomblin Jul 31 '09 at 7:37
    
what version of postgresql are you using ? can you redo the pg_dump or it is too late ? –  edomaur Jul 31 '09 at 7:47
add comment

4 Answers

Older versions of Postgres would allow invalid byte sequences to be entered into a database. There was a note about this and a suggsted fix in a recent releasenote:

Some users are having problems loading UTF-8 data into 8.1.X. This is because previous versions allowed invalid UTF-8 byte sequences to be entered into the database, and this release properly accepts only valid UTF-8 sequences. One way to correct a dumpfile is to run the command iconv -c -f UTF-8 -t UTF-8 -o cleanfile.sql dumpfile.sql. The -c option removes invalid character sequences. A diff of the two files will show the sequences that are invalid. iconv reads the entire input file into memory so it might be necessary to use split to break up the dump into multiple smaller files for processing.

If the database is not very large or complex it might be easier to locate the offending text in the original database and correct it before doing a new dump. A field that has user-entered input or contains data imported from other sources might be a culprit.

share|improve this answer
add comment
up vote 2 down vote accepted

I solved this problem with the following steps:

pg_restore -f db1.sql-v db1.tar

I then removed everything from the db1.sql file except for the table1 copy command. Then ran:

psql -d db1 < db1.sql

This then gave me the exact line number within the file where the error was occurring. I then opened the file and removed the problem character and re-ran the script.

share|improve this answer
add comment

This little Perl script may save you : Repairing Broken documents that mix UTF-8 and ISO-8859-1

Redirect the script output to a new file. All illegal characters should have been replaced with their correct UTF-8 incarnation. The script reads the input line by line, too, so it shouldn't need too much memory.

share|improve this answer
add comment

It's probably the same issue I had once, when migratting from a 7.4 to a 8.2 db. I used the instructions on this web article to solve the problem. This presuppose that you still have access to the original database. Otherwise, you can probably restore it in an older version of Postgresql and give a try with that procedure.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.