From ccitt@thechillhacker.com Mon Dec 19 16:15:56 2011 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id EF8C616B0CAC for ; Mon, 19 Dec 2011 12:15:56 -0400 (AST) Received: from mail-qy0-f174.google.com ([209.85.216.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RcfsX-0003DP-Rd for pgsql-admin@postgresql.org; Mon, 19 Dec 2011 16:15:56 +0000 Received: by mail-qy0-f174.google.com with SMTP id z2so3161743qcq.19 for ; Mon, 19 Dec 2011 08:15:53 -0800 (PST) Received: by 10.229.135.140 with SMTP id n12mr5834307qct.44.1324311353474; Mon, 19 Dec 2011 08:15:53 -0800 (PST) Received: from Osiris (173-166-16-233-newengland.hfc.comcastbusiness.net. [173.166.16.233]) by mx.google.com with ESMTPS id ha3sm41001812qab.2.2011.12.19.08.15.51 (version=SSLv3 cipher=OTHER); Mon, 19 Dec 2011 08:15:52 -0800 (PST) From: "Scott Toland" To: Subject: PG export/import encoding issue Date: Mon, 19 Dec 2011 11:15:49 -0500 Message-ID: <030501ccbe69$79cc56e0$6d6504a0$@thechillhacker.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0306_01CCBE3F.91032020" X-Mailer: Microsoft Outlook 14.0 Thread-Index: Acy+aDlUh+PhG9VCRsWhsmiyMstwog== Content-Language: en-us X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201112/99 X-Sequence-Number: 38857 This is a multipart message in MIME format. ------=_NextPart_000_0306_01CCBE3F.91032020 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi guys I have a 8.4 install that I am moving to a new DB server running 9.0. This is all well and god for the most part, except when it comes to restoring the data in several of the tables. See the problem is the old schema was an ASCII neutral zone, and the new server has the schema set for UTF-8 for i18n compliance. Over the years, people have inserted data in a wide range of languages in many encodings, none of it tracked. This of course leads to the dreaded encoding errors on import that, ,with normal COPY mechanics, result in empty tables where there should be thousands of rows. Switching to INSERTS means I get to keep most of the table, and just lose the rows with encoding errors. Not great, but manageable - the real killer with this method is that an import takes hours, which we cannot allow. Is there a way to make pg_restore escape out chars it has problems with instead of failing? Alternatively, what is the best method to quickly and accurately import this database onto the new server? I have tried sql and custom pg_dump formats, with (not surprisingly) the custom format being the heads-and-shoulders winner in terms of performance Thanks a bunch guys Scott Toland ------=_NextPart_000_0306_01CCBE3F.91032020 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi = guys

 

I have a 8.4 install that I am moving to a new DB = server running 9.0. This is all well and god for the most part, except = when it comes to restoring the data in several of the tables. See the = problem is the old schema was an ASCII neutral zone, and the new server = has the schema set for UTF-8 for i18n compliance. Over the years, people = have inserted data in a wide range of languages in many encodings, none = of it tracked. This of course leads to the dreaded encoding errors on = import that, ,with normal COPY mechanics, result in empty tables where = there should be thousands of rows.

 

Switching to = INSERTS means I get to keep most of the table, and just lose the rows = with encoding errors. Not great, but manageable – the real killer = with this method is that an import takes hours, which we cannot = allow.

 

Is there a way to make pg_restore escape out chars it = has problems with instead of failing? Alternatively, what is the best = method to quickly and accurately import this database onto the new = server? I have tried sql and custom pg_dump formats, with (not = surprisingly) the custom format being the heads-and-shoulders winner in = terms of performance

 

Thanks a = bunch guys

 

Scott Toland

 

------=_NextPart_000_0306_01CCBE3F.91032020-- From jens.wilke@affinitas.de Tue Dec 20 17:06:22 2011 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id E2E9B1C3D21 for ; Tue, 20 Dec 2011 13:06:22 -0400 (AST) Received: from mail.affinitas.de ([91.102.12.84]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Rd38u-0003Gq-5w for pgsql-admin@postgresql.org; Tue, 20 Dec 2011 17:06:21 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.affinitas.de (Postfix) with ESMTP id B515FA40BE for ; Tue, 20 Dec 2011 18:06:07 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at mail.affinitas.de Received: from mail.affinitas.de ([127.0.0.1]) by localhost (mail.affinitas.de [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id HoqZPdhlRszx for ; Tue, 20 Dec 2011 18:06:07 +0100 (CET) Received: from affinitas180.localnet (unknown [91.102.12.2]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.affinitas.de (Postfix) with ESMTPSA for ; Tue, 20 Dec 2011 18:06:07 +0100 (CET) From: Jens Wilke Organization: Affinitas GmbH To: pgsql-admin@postgresql.org Subject: Re: PG export/import encoding issue Date: Tue, 20 Dec 2011 18:06:06 +0100 User-Agent: KMail/1.13.5 (Linux/2.6.32-5-amd64; KDE/4.4.5; x86_64; ; ) References: <030501ccbe69$79cc56e0$6d6504a0$@thechillhacker.com> In-Reply-To: <030501ccbe69$79cc56e0$6d6504a0$@thechillhacker.com> MIME-Version: 1.0 Content-Type: Text/Plain; charset="utf-8" Content-Transfer-Encoding: 7bit Message-Id: <201112201806.06766.jens.wilke@affinitas.de> X-Pg-Spam-Score: -4.5 (----) X-Archive-Number: 201112/103 X-Sequence-Number: 38861 On Monday 19 December 2011 17:15:49 Scott Toland wrote: > the problem is the old schema was > an ASCII neutral zone, and the new server has the schema set for UTF-8 You have to convert the Dump: http://blog.endpoint.com/2011/12/sanitizing-supposed-utf-8-data.html HTH, Jens