Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2DBE02E2EF3 for ; Wed, 6 Feb 2008 22:29:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 21052-09 for ; Wed, 6 Feb 2008 22:29:05 -0400 (AST) Received: from mail.mailpen.net (mail.mailpen.net [209.90.228.14]) by postgresql.org (Postfix) with ESMTP id B384C2E2DFC for ; Wed, 6 Feb 2008 22:29:05 -0400 (AST) Received: from [192.168.42.79] (207.155.204.150.ptr.us.xo.net [207.155.204.150]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.mailpen.net (Postfix) with ESMTP id 98C6F2FE802 for ; Wed, 6 Feb 2008 18:29:03 -0800 (PST) Message-ID: <47AA6CEE.4010406@ultimeth.com> Date: Wed, 06 Feb 2008 18:29:02 -0800 From: "Dean Gibson (DB Administrator)" Organization: UltiMeth Systems User-Agent: Thunderbird 2.0.0.9 (Windows/20071031) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: 8.3.0, locales, and encodings Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200802/70 X-Sequence-Number: 28144 I got a small surprise when upgrading to 8.3.0 today (CentOS 4.4). When I went to restore my database, PSQL complained that my "CREATE DATABASE... ENCODING 'UTF8';" wasn't valid (the new phpBB 3.0 software enforces using UTF8). Sure enough, a bit of research showed that the supplied /etc/init.d/postgresql file had no encoding specified, which meant that it defaulted to my operating system's 'en_US.ISO-8859-1' encoding. A quick reading of the PostgreSQL documentation revealed that PostgreSQL can't really use an encoding different from that specified during INITDB (caveat below), and apparently that is now enforced in v8.3. OK, so I manually run INITDB w/ "-E UTF8", and now I can create/load one of my databases. Unfortunately, when I go to create another database (where the data is 'ISO-8859-1'), now IT won't create. A more detailed reading of the PostgreSQL documentation revealed that PostgreSQL can't use an encoding different from that specified during INITDB, UNLESS INITDB is run w/ "-locale POSIX". Ok, I rerun INITDB w/ "-locale POSIX -E UTF8", and now both databases create/load, apparently properly. I also did an INITDB on another database server, running INITDB w/ just "-locale POSIX" (apparently meaning that the "template1" encoding defaulted to "SQL_ASCII"), and successfully create/reload another database there (explicitly specifying an ENCODING there). Questions/suggestions: 1. The provided /etc/init.d/postgresql file seems to be part of this trap. Should it be changed to default the encoding to POSIX? 2. I want my installation to have the maximum flexibility. Should I be running INITDB w/ "-locale POSIX -E UTF8", or just "-locale POSIX" (implying "-E SQL_ASCII")? Or does it make a difference, since I always specify the desired database encoding when I "CREATE DATABASE... ENCODING '...';"? 3. One of my databases is ISO-8859-1, because that's the format of the data I get from the US gov't. However, should I instead use the 'UTF8' encoding for that database, and then use a client (PSQL) encoding of "LATIN1", and let PostgreSQL convert the data? Pros & cons? Performance is a concern, but disk space is not. I presume this ("admin") is the proper PostgreSQL list to post this to. If not, let me know. -- Dean Gibson