pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Benjamin Krajmalnik <kraj@illumen.com>
To: pgsql-admin@postgresql.org
Subject: Re: pg_dump/pg_restore problem
Date: Thu, 5 Oct 2006 20:49:00 -0600
Message-ID: <F4E6A2751A2823418A21D4A160B6898830D9@fletch.stackdump.local> (raw)

I finally found a workaround after much experimentatio, so here it is.
Hopefully, it will save someone else the headaches I went through:

1.  I created a target database with Latin1 encoding 
2.  pg_dumped using SQL_ASCII client encoding

The initial restore failed because for some reason one of the columns
was no longer large enough to hold the contents.
I increased the size of the columns in question, and proceeded to
truncate all of the tables which had  data to prevent errors when
building the indices, etc.

I reran the restore, and this time all of the records came through. 

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org 
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of 
> Benjamin Krajmalnik
> Sent: Thursday, October 05, 2006 12:03 PM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] pg_dump/pg_restore problem
> 
> I have a database which has UTF8 encoding enabled (why?  I am 
> really not sure why I did tihs other than the source of the 
> data is windows and I had some issues with characters > ascii 
> 128 being sent across from some of the Windows event logs).
> The problem which I am having is as follows:
>  
> The data is passed via the ODBC driver to a stored procedue, 
> and it made it successfully into the tables.
> I can create a pg_dump without any problem, but pg_restore is 
> giving the following error:
>  
> pg_restore: ERROR: invalid byte sequence for encoding "UTF8": 0x80
> 
> CONTEXT: COPY tblksalerts, line 22736
> 
> I have tried running pg_dump changing the encoding to Latin1 
> and Latin9.
> When creating the dunp, it is giving an error that there is 
> no equivalent in the character set.
> The problem is that, as it stands, pg_dump/pg_restore cannot 
> be used to easily backup/restore.
> In the past, I perfrmd singe table dumps and ran them so I 
> could identify which line was the problem, went back to the 
> database, deleted the offending line, and so forth, but this 
> is a very long process.
> 
> I was initially runnin 8.1.2.  I am now running 8.1.4.  I was 
> hoping that 8.1.4 would alleviate the problem (since some 
> encoding issues were addressed).
> 
> Any ideas how to easily identify the offending rows and 
> remove them easily?
> 
> I need to move the database to a new server with higher 
> performance, and this is currently a sticking point.
> 
> 
>  
> 
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 



view thread (3+ messages)  latest in thread

Message-ID: <F4E6A2751A2823418A21D4A160B6898830D9@fletch.stackdump.local>
Permalink:  ../F4E6A2751A2823418A21D4A160B6898830D9@fletch.stackdump.local/
Also on:    postgresql.org/message-id/F4E6A2751A2823418A21D4A160B6898830D9@fletch.stackdump.local

 · 

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: kraj@illumen.com
  Subject: Re: pg_dump/pg_restore problem
  In-Reply-To: <F4E6A2751A2823418A21D4A160B6898830D9@fletch.stackdump.local>
  References: <F4E6A2751A2823418A21D4A160B6898830D9@fletch.stackdump.local>

* 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