Re: Best practices for moving UTF8 databases

On Tuesday 14 July 2009 11:36:57 Jasen Betts wrote:
> On 2009-07-13, Andres Freund <andres(at)anarazel(dot)de> wrote:
> > On Sunday 12 July 2009 13:19:50 Phoenix Kiula wrote:
> >> Hi. I *always* get an error moving my current fully utf-8 database
> >> data into a new DB.
> >>
> >> My server has the version 8.3 with a five year old DB. Everything, all
> >> collation, LC_LOCALE etc are all utf8.
> >>
> >> When I install a new Postgresql 8.4 on my home Mac OSX machine (after
> >> losing some hair) I set everything about a new database to be utf8. At
> >> least anything I could see in PgAdmin.
> >>
> >> But when I pull in the data dump from the server I always see that
> >> error from the utf8 mismatch and such.
> >>
> >> So, my question. What is a good way to make sure that error does NOT
> >> occur? I simply wish to replicate the server database on another PG
> >> installation. What should one do?
> >
> > What is the _exact_ error you get? During which statement?
> if you do an ascii dump and the dump starts out "SET CLIENT ENCODING
> 'UTF8'" or similar but you still get errors.
Do you mean that a dump from SQL_ASCII can yield non-utf8 data? right. But
According to the OP his 8.3 database is UTF8...
So there should not be invalid data in there.

Andres