Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 69CB82E018E for ; Thu, 29 Nov 2007 17:37:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 04696-05 for ; Thu, 29 Nov 2007 17:37:08 -0400 (AST) Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.181]) by postgresql.org (Postfix) with ESMTP id CA73A2E0047 for ; Thu, 29 Nov 2007 17:37:08 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id m38so2677361waf for ; Thu, 29 Nov 2007 13:37:07 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; bh=eiM/wXGCbF1VtqqPrsnf/zF40Xtx20ue4ZyajQ5tlIw=; b=jT/IlDgICGkGB/2l2HefGxsJV9GBTOOGgsfx7gHd2O96/yPb5yirpXkn+3qJgLPu6a0hobb4az2roqB6v199EUSm615ZitsqFfxd6LhPnWr7VMj2qs7r9gbc6BpChjlcvakE52DjKjx4DFidso/9lJ/YTd4kt4tUZDWjh9mHd+w= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=dvwOQVqABZ5Y5Vuqzi0ogRnjRx4mmw2w8qcbSuleWoZbH0LhuyJf+vP32RSVwRhYZtXJxJEYThtKkDirKXjpKD8vdRy1DmEwhek6Jla6MdjFkQJTSF9cYmi8g4figtVitv0EIkhQIYpTEXoiExusgcjgtJbUt0LW8oKe1CUxp0I= Received: by 10.114.112.1 with SMTP id k1mr1186155wac.1196372226344; Thu, 29 Nov 2007 13:37:06 -0800 (PST) Received: by 10.114.199.9 with HTTP; Thu, 29 Nov 2007 13:37:06 -0800 (PST) Message-ID: <4544e0330711291337y4c875320r36414692972296a4@mail.gmail.com> Date: Thu, 29 Nov 2007 15:37:06 -0600 From: "Peter Koczan" To: "pgsql-admin@postgresql.org" Subject: Converting from SQL_ASCII to UTF8 MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/245 X-Sequence-Number: 27488 Hi all, I'd like to move my database encoding from SQL_ASCII to UTF8, mostly because "No encoding conversion will be done when the setting is SQL_ASCII. Thus, this setting is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding." (from http://www.postgresql.org/docs/current/static/multibyte.html) I saw a few threads on the list regarding this before, for instance this one (http://archives.postgresql.org/pgsql-admin/2004-01/msg00225.php) but there's a specific issue that I'm having that wasn't addressed. I have some UTF-8 data in my databases, and it's causing dump/restore to fail. Specifically, I'm seeing messages like: pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding "UTF8": 0xe14c65 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 applicants, line 282 Which happens even if I specify "-E UTF8" in the pg_dump command. Here's the weirder part. If I just update the encoding by hand in pg_database (as cautiously suggested by Tom Lane in the aforementioned thread), it works. I doubt this will work in the general case, and I'd like to at least offer this option for other people's databases. I also tried using GNU recode (version 3.6) as suggested in similar threads, but I got errors in both the plain and custom pg_dump formats. $ recode ascii..utf8 man.sql recode: man.sql failed: Invalid input in step `ANSI_X3.4-1968..UTF-8' $ recode ..utf8 man.sql recode: man.sql failed: Invalid input in step `CHAR..UTF-8' Any ideas? Peter