From: adey <adey11@gmail.com>
To: Dan Harris <fbsd@drivefaster.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: Can't restore a pg_dump due to encoding errors
Date: Sun, 8 Apr 2007 09:29:39 +1200
Message-ID: <1c66bda80704071429p68409036oa2f384a61d8d5811@mail.gmail.com> (raw)
In-Reply-To: <46180885.4050504@drivefaster.net>
References: <46180885.4050504@drivefaster.net>
We had a similar conversion conflict some time back whe upgrading and found
using a search for anything not A-Z, 0-9, etc, was useful to find offensive
data and fix it.
On 4/8/07, Dan Harris <fbsd@drivefaster.net> wrote:
>
> Hello all:
>
> I'm in a bit of a bind here. Today is my maintenance window for upgrading
> from
> 8.0.3 to 8.2.3. I did a pg_dumpall overnight ( resulting in a 72GB file )
> and
> then a pg_dump on one of my small databases this morning to test
> pg_restore.
> When trying to reload, I'm getting the following error:
>
> psql:sac.bak:2793776: ERROR: invalid byte sequence for encoding "UTF8":
> 0x92
>
> A google search came up with this link:
> http://www.hostingforum.ca/280472-invalid-byte-sequence-encoding-utf8.html
>
> Which sounds very simliar to what's going on here.
>
> Again the dump was done on 8.0.3, which sounds like it was before the
> anti-sql
> injection patch that would refuse to import "invalid" characters. The
> database
> ( for now ) *should* only be storing English/Latin characters. Although,
> the
> old server had LOCALE='C' previously ( this was unnecessary so I'm trying
> to
> drop that today ). I don't know if this would matter, but that's the only
> thing
> that was different from a bog standard Postgres setup besides the obvious
> tuning
> changes. I have never changed any of the encoding settings on client or
> server
> from the defaults.
>
> So far, I tried editing the dump file and changing the client_encoding to
> 'UTF8'
> at the top, hoping this might make a difference but did not.
>
> The advice in that thread seems to be "fix the bad data". But I am not
> seeing
> how I can tell exactly what or where this bad data is. Luckily, this
> small dump
> file is manageable by opening in vim and pointing to the line 2793776 the
> error
> references, however the line referenced is just the "\." at the end of a
> huge
> COPY section. I don't know which line inside that secion is the problem
> and
> there's enough of them that it'd be pretty impractical for me to visually
> scan
> the file looking for something ( I'm not even quite sure what to look for
> at
> this point ). And, if that is the only solution and I run into a problem
> restoring the 72GB dump file, I'm going to be in some serious trouble,
> especially if I am not alerted to the problem until *after* it's finished
> looking at all the COPY statements!
>
> What say the sages?
>
> Thanks
>
> -Dan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: adey11@gmail.com, fbsd@drivefaster.net
Subject: Re: Can't restore a pg_dump due to encoding errors
In-Reply-To: <1c66bda80704071429p68409036oa2f384a61d8d5811@mail.gmail.com>
References: <46180885.4050504@drivefaster.net> <1c66bda80704071429p68409036oa2f384a61d8d5811@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox