Message-ID: <48B848B9.4040805@esiway.net>
Date: Fri, 29 Aug 2008 21:06:33 +0200
From: Marco Colombo <pgsql@esiway.net>
User-Agent: Thunderbird 2.0.0.14 (X11/20080501)
MIME-Version: 1.0
To: Phoenix Kiula <phoenix.kiula@gmail.com>
CC: Andrew Sullivan <ajs@commandprompt.com>, pgsql-general@postgresql.org
Subject: Re: Dumping/Restoring with constraints?
References: <e373d31e0808270619uba31c8eodc2c85662b85140a@mail.gmail.com>	
	<48B5BC2A.5070104@wildenhain.de>	
	<e373d31e0808271651l6c7b99f0q960aaa5247b96b67@mail.gmail.com>	
	<20080828174712.GB27283@commandprompt.com>	
	<e373d31e0808282253o13844c76odcd6f118fef4ff2e@mail.gmail.com>	
	<20080829133436.GG29463@commandprompt.com>
	<e373d31e0808290726x3109ef36s9234247a33a6e303@mail.gmail.com>
In-Reply-To: <e373d31e0808290726x3109ef36s9234247a33a6e303@mail.gmail.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Phoenix Kiula wrote:
> Thanks Andrew.
> 
> On the server (the DB to be dumped) everything is "UTF8".
> 
> On my home server (where I would like to mirror the DB), this is the output:
> 
> 
> =# \l
>             List of databases
>    Name    |      Owner      | Encoding
> -----------+-----------------+-----------
>  postgres  | postgres        | SQL_ASCII
>  pkiula    | pkiula_pkiula   | UTF8
>  template0 | postgres        | SQL_ASCII
>  template1 | postgres        | SQL_ASCII
> (4 rows)
> 
> 
> 
> This is a fresh install as you can see. The database into which I am
> importing ("pkiula") is in fact listed as UTF8! Is this not enough?
> 

You said you're getting these errors:
ERROR:  invalid byte sequence for encoding "UTF8": 0x80

those 0x80 bytes are inside the mydb.sql file, you may find it easier to 
  look for them there and identify the offending string(s). Try (on the 
linux machine):

zcat mydb.sql.gz | iconv -f utf8 > /dev/null

should tell you something like:

illegal input sequence at position xxx

BTW, 0x80 is usually found in windows encoding, such as windows-1250, 
where it stands for the EURO symbol:

echo -n "€" | iconv -t windows-1250 | hexdump -C
00000000  80                                                |.|
00000001


FYI, you *can* get non UTF-8 data from an UTF-8 database, if (and only 
if) your client encoding is something different (either because you 
explicitly set it so, or because of your client defaults).

Likewise, you can insert non UTF-8 data (such as your mydb.sql) into an 
UTF-8 database, provided you set your client encoding accordingly. 
PostgreSQL clients handle encoding conversions, but there's no way to 
guess (reliabily) the encoding of a text file.

OTOH, from a SQL_ASCII database you can get all sort of data, even mixed 
  encoding text (which you need to fix somehow). If your mydb.sql 
contains data from a SQL_ASCII database, you simply know nothing about 
the encoding.

I have seen SQL_ASCII databases containg data inserted from HTTP forms, 
both in UTF-8 and windows-1250 encoding. Displaying, dumping, restoring
that correctly is impossible, you need to fix it somehow before 
processing it as text.

.TM.