Lucky me, I get to play DBA today!
I am migrating a Postgres 8.4 DB from one server to another (both RHEL6). I backed up the schema from the old server and restored it to the new server. Then I did a full database backup and restored it to the new server, but I had two tables that were skipped during the restore. In both cases I used the
format=c option with pg_dump and pg_restore.
I am now focusing on those two tables, and i need help! I do a backup on the old server:
pg_dump --format=c --file=mytable.sqlc -a --table public.mytable mydatabase
Now I go to the new server and attempt a restore:
pg_restore -d mydatabase -t mytable --format=c -a -c mytable.sqlc
I'm getting the following errors:
pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 2241; 0 23420338 TABLE DATA mytable mydatabase_script pg_restore: [archiver (db)] COPY failed for table "mytable": ERROR: invalid byte sequence for encoding "UTF8": 0xe05c62 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". CONTEXT: COPY mytable, line 231993 WARNING: errors ignored on restore: 1
The error seems to imply that I have an encoding difference between the old db server and the new one (even though I backed up/restored the schema). I did a:
select * from pg_database where datname = "mydatabase"
And it shows that both databases are UTF-8 encoded. What do I need to check next? Thanks.