Postgresql – Can’t restore PostgreSQL database backup

backuppostgresql

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?

Best Answer

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.

Related Topic