Re: Next: Encoding Issue

I'm seeing this error when I try to create a database within TinyERP:

createdb: database creation failed: ERROR: encoding UTF8 does not match
server's locale en_US
DETAIL: The server's LC_CTYPE setting requires encoding LATIN1.

The application is designed for unicode (that's UTF8, isn't it?) and I
cannot pass the '-E' option to the createdb command within the application.
Please suggest approaches to resolve this conflict.

Thanks,

Rich

--
Richard B. Shepard, Ph.D. | Integrity Credibility
Applied Ecosystem Services, Inc. | Innovation
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

In the shell environment

export LC_ALL=C

This will often fix many locale ills.

>-----Original Message-----
>From: pdxpug-owner(at)postgresql(dot)org
>[mailto:pdxpug-owner(at)postgresql(dot)org] On Behalf Of Rich Shepard
>Sent: Monday, February 02, 2009 7:04 PM
>To: pdxpug(at)postgresql(dot)org
>Subject: [pdxpug] Next: Encoding Issue
>
> I'm seeing this error when I try to create a database
>within TinyERP:
>
>createdb: database creation failed: ERROR: encoding UTF8 does
>not match
>server's locale en_US
>DETAIL: The server's LC_CTYPE setting requires encoding LATIN1.
>
> The application is designed for unicode (that's UTF8, isn't
>it?) and I
>cannot pass the '-E' option to the createdb command within the
>application.
>Please suggest approaches to resolve this conflict.
>
>Thanks,
>
>Rich
>
>--
>Richard B. Shepard, Ph.D. | Integrity
> Credibility
>Applied Ecosystem Services, Inc. | Innovation
><http://www.appl-ecosys.com> Voice: 503-667-4517 Fax:
>503-667-8863
>
>--
>Sent via pdxpug mailing list (pdxpug(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pdxpug
>

Ewan, Michael wrote:
> In the shell environment
>
> export LC_ALL=C
>
> This will often fix many locale ills.

You will likely want to reinitialize your database cluster with `initdb
--locale=C --encoding=UTF-8 -D /where/ever/your/database/is`. Once the
locale for a cluster is set, it can not be changed (though alternate
compatible encodings can be specified per database) and it looks like
the Rich's was init'd with locale=en_US.LATIN1 which will not
accommodate a UTF8 encoding.

>> -----Original Message-----
>>
>> I'm seeing this error when I try to create a database
>> within TinyERP:
>>
>> createdb: database creation failed: ERROR: encoding UTF8 does
>> not match
>> server's locale en_US
>> DETAIL: The server's LC_CTYPE setting requires encoding LATIN1.
>>
>> The application is designed for unicode (that's UTF8, isn't
>> it?) and I
>> cannot pass the '-E' option to the createdb command within the
>> application.
>> Please suggest approaches to resolve this conflict.

On Mon, 2 Feb 2009, Ewan, Michael wrote:

> In the shell environment
>
> export LC_ALL=C
>
> This will often fix many locale ills.

Thanks for the suggestion, Michael, but it didn't help here. Perhaps I
should try changing the locale setting to en_US.utf8.

I've not encountered this issue before. When I listed existing databases
(including the three I dropped yesterday as legacies of applications that
didn't make the grade), I saw that two (created by sql-ledger and me are
encoded LATIN1, the three encoded by the former apps were US_ASCII, and the
four owned by postgres (including the templates) are encoded UTF8.

Rich

--
Richard B. Shepard, Ph.D. | Integrity Credibility
Applied Ecosystem Services, Inc. | Innovation
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Mon, 2 Feb 2009, Devin Ben-Hur wrote:

> You will likely want to reinitialize your database cluster with `initdb
> --locale=C --encoding=UTF-8 -D /where/ever/your/database/is`. Once the
> locale for a cluster is set, it can not be changed (though alternate
> compatible encodings can be specified per database) and it looks like the
> Rich's was init'd with locale=en_US.LATIN1 which will not accommodate a UTF8
> encoding.

Devin,

Since /var/lib/pgsql/data exists as a cluster, what happens to all
existing data if I remove or empty it so it can be recreated?

Thanks,

Rich

--
Richard B. Shepard, Ph.D. | Integrity Credibility
Applied Ecosystem Services, Inc. | Innovation
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Mon, 2 Feb 2009, Ewan, Michael wrote:

> In the shell environment
> export LC_ALL=C
> This will often fix many locale ills.

Michael,

I went one step further. In ~/.bash_profile I added:

export LC_ALL=
export LANG=en_US.utf8

then sourced the file. Now locale reports:

LANG=en_US.utf8
LC_CTYPE="en_US.utf8"
LC_NUMERIC="en_US.utf8"
LC_TIME="en_US.utf8"
LC_COLLATE=C
LC_MONETARY="en_US.utf8"
LC_MESSAGES="en_US.utf8"
LC_PAPER="en_US.utf8"
LC_NAME="en_US.utf8"
LC_ADDRESS="en_US.utf8"
LC_TELEPHONE="en_US.utf8"
LC_MEASUREMENT="en_US.utf8"
LC_IDENTIFICATION="en_US.utf8"
LC_ALL=

but this does not resolve the issue. I still see:

createdb: database creation failed: ERROR: encoding UTF8 does not match
server's locale en_US
DETAIL: The server's LC_CTYPE setting requires encoding LATIN1.

Setting LC_ALL=en_US.utf8 doesn't make any difference.

Any thoughts?

Rich

--
Richard B. Shepard, Ph.D. | Integrity Credibility
Applied Ecosystem Services, Inc. | Innovation
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Tue, Feb 3, 2009 at 7:09 AM, Rich Shepard <rshepard(at)appl-ecosys(dot)com> wrote:

> createdb: database creation failed: ERROR: encoding UTF8 does not match
> server's locale en_US
> DETAIL: The server's LC_CTYPE setting requires encoding LATIN1.
>
> Setting LC_ALL=en_US.utf8 doesn't make any difference.
>
> Any thoughts?

You have to re-initdb (as Devin indicated in an earlier message). See
similar issue:

http://www.nabble.com/Locale---Encoding-mismatch-td16384677.html

-selena

--
Selena Deckelmann
Open Source Bridge - http://www.opensourcebridge.org
PDXPUG - http://pugs.postgresql.org/pdx
Me - http://www.chesnok.com/daily

On Tue, 3 Feb 2009, Selena Deckelmann wrote:

> You have to re-initdb (as Devin indicated in an earlier message). See
> similar issue:

Selena,

I understand. What I don't yet understand is what happens to the existing
databases in the cluster if I remove /var/lib/pgsql/data so I can
re-initialize the cluster. I assume that existing encodings are unaffected,
and new ones will be in unicode/utf8 form. Back to the book.

Rich

--
Richard B. Shepard, Ph.D. | Integrity Credibility
Applied Ecosystem Services, Inc. | Innovation
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Tue, 3 Feb 2009, Selena Deckelmann wrote:

> You have to re-initdb (as Devin indicated in an earlier message).

If I tar /var/lib/pgsql/data/base/* before removing and recreating that
path, can I untar the base directories and return to a running postmaster?
Or, are there specific files to be removed that permit the new initdb to
work?

Thanks,

Rich

--
Richard B. Shepard, Ph.D. | Integrity Credibility
Applied Ecosystem Services, Inc. | Innovation
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Tue, 2009-02-03 at 08:39 -0800, Rich Shepard wrote:
> On Tue, 3 Feb 2009, Selena Deckelmann wrote:
>
> > You have to re-initdb (as Devin indicated in an earlier message).
>
> If I tar /var/lib/pgsql/data/base/* before removing and recreating that
> path, can I untar the base directories and return to a running postmaster?
> Or, are there specific files to be removed that permit the new initdb to
> work?

No.

You must use pg_dump and then restore the dump.

Joshua D. Drake

>
> Thanks,
>
> Rich
>
> --
> Richard B. Shepard, Ph.D. | Integrity Credibility
> Applied Ecosystem Services, Inc. | Innovation
> <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863
>
--
PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

On Tue, 3 Feb 2009, Joshua D. Drake wrote:

> You must use pg_dump and then restore the dump.

Joshua,

So I thought. Used pg_dumpall and reinitialized the cluster.

Thank you,

Rich

--
Richard B. Shepard, Ph.D. | Integrity Credibility
Applied Ecosystem Services, Inc. | Innovation
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863