pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
>

view thread (3+ messages)  latest in thread

Message-ID: <1c66bda80704071429p68409036oa2f384a61d8d5811@mail.gmail.com>
Permalink:  ../1c66bda80704071429p68409036oa2f384a61d8d5811@mail.gmail.com/
Also on:    postgresql.org/message-id/1c66bda80704071429p68409036oa2f384a61d8d5811@mail.gmail.com

 · 

reply

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