X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 40F3B9DCAC3 for ; Sun, 11 Dec 2005 22:48:26 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 03711-07 for ; Sun, 11 Dec 2005 22:48:30 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from candle.pha.pa.us (candle.pha.pa.us [64.139.89.126]) by postgresql.org (Postfix) with ESMTP id C1C6D9DCAA9 for ; Sun, 11 Dec 2005 22:48:23 -0400 (AST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id jBC2mNB14188; Sun, 11 Dec 2005 21:48:23 -0500 (EST) From: Bruce Momjian Message-Id: <200512120248.jBC2mNB14188@candle.pha.pa.us> Subject: Re: 8.1: import of 8.0 dump fails with UTF-8 error In-Reply-To: To: Thomas Mueller Date: Sun, 11 Dec 2005 21:48:23 -0500 (EST) CC: pgsql-admin@postgresql.org X-Mailer: ELM [version 2.4ME+ PL121 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.006 required=5 tests=[AWL=0.006] X-Spam-Score: 0.006 X-Spam-Level: X-Archive-Number: 200512/87 X-Sequence-Number: 19807 Thomas Mueller wrote: > Hello, > > I did a pg_dumpall of all my Pg 8.0.3 databases, removed 8.0, installed > 8.1 and tried to import the dump. One table in one database failed with: > > ERROR: invalid UTF-8 byte sequence detected near byte 0x83 > CONTEXT: COPY pwd_name, line 22428, column name: "t.tonnement" > > So I exported that database with 8.0 as Inserts to a text file and tried > to fix it using iconv, but that fails as well: > > # iconv -f UTF-8 -t UTF-8 dump.sql > dump-fixed.sql > iconv: illegal input sequence at position 2588882 > > How can I fix the sql script to import it? > I have Debian Linux 3.1. We have updated the 8.1.0 release notes to mention a fix: 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. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073