Re: Questions about encoding between two databases

On Fri, 21 Aug 2009, Adrian Klaver wrote:

> On Thursday 20 August 2009 11:45:30 pm Archibald Zimonyi wrote:
>> Hello,
>>
>> I am sitting on version 7.4.x and am going to upgrade to version 8.3.x.
>> From all I can read I should have no problem with actual format of the
>> pgdump file (for actual dumping and restoring purposes) but I am
>> having problems with encoding (which I was fairly sure I would). I have
>> searched the web for solutions and one solution given (in one thread where
>> Tom Lane answered) was to set the correct encoding in the version 8.3.x
>> database.
>>
>> However, the default encoding in the version 8.3.x instance is
>> currently UTF8 and I am happy with that. The encoding for most of the
>> databases in the version 7.4.x was LATIN1. Is there any way I can ignore
>> the LATIN1 encoding and force the database to accept the UTF8 encoding of
>> the new version 8.3.x instance?
>>
>> I get the below message when I try the psql -f <file> <database> command.
>>
>> psql:aranzo20090812:30: ERROR:  encoding LATIN1 does not match server's
>> locale en_US.UTF-8
>> DETAIL:  The server's LC_CTYPE setting requires encoding UTF8.
>>
>> Any help would be appreciated.
>>
>> Archie
>
> To get the question out of the way, is there a reason you are not upgrading to
> latest version, 8.4?
>
Yes, I use Debian stable which which as far as I know only has 8.3.x as
its latest version. But it shouldn't really matter in this case as I would
most likely have the same problem with 8.4.x.

> Suggestion below is untested:
> Use pg_dump from 8.3.x to dump from 7.4 database.
>
The two version are located on two different machines, so probably not
possible.

> From here:
> http://www.postgresql.org/docs/8.3/interactive/app-pgdump.html
>
> "
> -E encoding
> --encoding=encoding
>
>    Create the dump in the specified character set encoding. By default, the
> dump is created in the database encoding. (Another way to get the same result
> is to set the PGCLIENTENCODING environment variable to the desired dump
> encoding.)  "
>
> Use the encoding switch to create the dump in UTF8.
>
I will look at this PGCLIENTENCODING variable to see if I can set that in
7.4.x but does anyone know the answer to it already? Would it work?

Will that also work with pg_dumpall?

Thanks for the response so far.

Archie