Re: Linux x Windows LOCALE/ENCODING compatibility

2010/11/1 Carlos Henrique Reimer <carlos(dot)reimer(at)opendb(dot)com(dot)br>
>
> Hi,
>
> I currently have my PostgreSQL server running in a windows box and now we're migrating it to a Linux operational system.
>
> Current windows configuration:
> pg_controldata shows the LC_COLLATE and LC_CTYPE are Portuguese_Brasil.1252
> psql \l command shows we have databases with encoding WIN1252 and UTF8
>
> New Linux box:
> Which --locale and --encoding options should I use for the INITDB command to have a compatible locale and encoding cluster?
> Which --encoding option should I use in the CREATEDB command to have a compatible encoding database?

from your description I assume you have 8.3 on Windows,
but you did not tell us which PostgreSQL version you are planning to
deploy on Linux. I'd recommend 8.4.

main difference is that on Windows, UTF-8 encoding can be used with
any locale. on Unix, not.

generally, it is safe to do ``initdb --locale=pt_BR.UTF-8'' and create
fresh databases with default settings.

then, use Linux pg_dump and psql to clone Windows databases. like this:
pg_dump -h windowsbox -p 5432 -U postgres DBNAME | psql DBNAME

UTF-8 is compatible with all client encodings.

you will get compatible bahaviour for your existing apps with
SET client_encoding TO 'whatever'; /* this can be entered as pre-SQL
on every client connection */

see http://www.postgresql.org/docs/8.4/static/multibyte.html,
22.2.3. Automatic Character Set Conversion Between Server and Client

HTH

--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/