Thread: How to create database with default system locale is set to et_EE.UTF-8
In fresh Debian installation default system locale is set to et_EE.UTF-8 using
dpkg-reconfigure locales
Postgres is installed using
apt-get update
apt-get -t squeeze-backports install postgresql-9.1 postgresql-common postgresql-contrib
Trying to create database with et_EE.UTF-8 collation and character type returns error
---------------------------
pgAdmin III
---------------------------
An error has occurred:
ERROR: new collation (et_EE.UTF-8) is incompatible with the collation of the template database (en_US.UTF-8)
HINT: Use the same collation as in the template database, or use template0 as template.
How to create new database with et_EE.UTF-8 collation and character type ?
How to force Postgres installation to create template1 with et_EE.UTF-8 collation and character type ?
Andrus.
Re: How to create database with default system locale is set to et_EE.UTF-8
From
Adrian Klaver
Date:
On Wednesday, December 21, 2011 10:28:24 am Andrus wrote:
> In fresh Debian installation default system locale is set to et_EE.UTF-8
> using
>
> dpkg-reconfigure locales
>
> Postgres is installed using
>
> apt-get update
> apt-get -t squeeze-backports install postgresql-9.1 postgresql-common
> postgresql-contrib
>
> Trying to create database with et_EE.UTF-8 collation and character type
> returns error
>
> ---------------------------
> pgAdmin III
> ---------------------------
> An error has occurred:
>
> ERROR: new collation (et_EE.UTF-8) is incompatible with the collation of
> the template database (en_US.UTF-8) HINT: Use the same collation as in
> the template database, or use template0 as template.
^^^^^^^^^^^^^^^^^^
See below for more info.
>
> How to create new database with et_EE.UTF-8 collation and character type ?
http://www.postgresql.org/docs/9.1/interactive/multibyte.html
22.3.2. Setting the Character Set
Notice that the above commands specify copying the template0 database. When
copying any other database, the encoding and locale settings cannot be changed
from those of the source database, because that might result in corrupt data.
For more information see Section 21.3."
From Section 21.3:
"To create a database by copying template0, use:
CREATE DATABASE dbname TEMPLATE template0;
"
> How to force Postgres installation to create template1 with et_EE.UTF-8
> collation and character type ?
>
> Andrus.
--
Adrian Klaver
adrian.klaver@gmail.com
Adrian and Bèrto, Thank you very much for quick and excellent replies. Locale names are different in every Linux distro. Postgresql does not provide any way to retrieve them (ssh access is reqired to retireve them using locale -a) Thus suggection using hard coded locale names is not possible. How to force server to use et_EE.UTF-8 as default locale without hard coding it into application? How to force command CREATE DATABASE <yourdbname> TEMPLATE = template0 to use et_EE.UTF-8 locale by default ? Andrus.
Re: How to create database with default system locale is set to et_EE.UTF-8
From
Bèrto ëd Sèra
Date:
Hi!
How to force commandto use et_EE.UTF-8 locale by default ?
CREATE DATABASE <yourdbname> TEMPLATE = template0
http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html (see last comment), I haven't checked it myself as I usually have a mix of locales in my installs (often even in a single db) and never really used any "default", but it should still work.
Bèrto
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Re: How to create database with default system locale is set to et_EE.UTF-8
From
Adrian Klaver
Date:
On Thursday, December 22, 2011 8:29:11 am Andrus wrote: > Adrian and Bèrto, > > Thank you very much for quick and excellent replies. Locale names are > different in every Linux distro. > Postgresql does not provide any way to retrieve them (ssh access is reqired > to retireve them using locale -a) > > Thus suggection using hard coded locale names is not possible. > > How to force server to use et_EE.UTF-8 as default locale without hard > coding it into application? What application? > > How to force command > > CREATE DATABASE <yourdbname> TEMPLATE = template0 > > to use et_EE.UTF-8 locale by default ? Well you would use template0 as the TEMPLATE only if you wanted to CREATE a database with different collation than that in template1(the default template for the CREATE DATABASE command). So the question then is, why is the database cluster being created with a collation of en_US.UTF-8 when the locale is supposed to have been set to et_EE.UTF-8? First are you sure that dpkg-reconfigure locales is actually resetting the locale? Second when you connect to the cluster with psql what does \l show for encoding and collation? > > Andrus. -- Adrian Klaver adrian.klaver@gmail.com
Is it reasonable to use commands export LC_COLLATE='et_EE.UTF-8' export LC_CTYPE='et_EE.UTF-8' apt-get -t squeeze-backports install postgresql-9.1 postgresql-common postgresql-contrib Will this force et_EE.UTF-8 locale ? Andrus.
>http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html >(see last comment), I haven't checked it myself as I usually have a mix of >locales in my installs (often even in a single db) and never really used >any "default", but it should still work. using template1 requires exclusive access to cluster. I cannot force all users to log out while creating new db. So using template1 is not possible. Andrus.
Re: How to create database with default system locale is set to et_EE.UTF-8
From
Bèrto ëd Sèra
Date:
Is it reasonable to use commands
export LC_COLLATE='et_EE.UTF-8'
export LC_CTYPE='et_EE.UTF-8'
apt-get -t squeeze-backports install postgresql-9.1 postgresql-common postgresql-contrib
Hmmm no, not really. If your problem is that the system locale is wrong for your needs, you're going to have this thing popping up again and again (not just in postgres). I'd rather make sure the locale of my system is what it's expected to be, if I was you. Or do you have any particular reason to use a locale at system level, and another at postgresql level? It does look weird to me.
Besides, if I wanted to change a user locale in gentoo, I'd rather do
export LC_COLLATE="C"
I have no idea of how binary distros would do that, but I'd expect them to be able not to type the same value twice. So, in case this really is a requirement, you really want to check your distro for details.
Bèrto
-- ==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Re: How to create database with default system locale is set to et_EE.UTF-8
From
Bèrto ëd Sèra
Date:
Hi!
IMHO you really misunderstood the manual. The exclusive access is NOT to the entire cluster, but to the template. Which in turn means that if you ever use an existing database (not a template) to create another with a copy procedure you definitely need to shut down this single database.using template1 requires exclusive access to cluster.
I cannot force all users to log out while creating new db.
So using template1 is not possible.
I'm creating stuff based on template1 (and a couple of internally produced templates) ~once a month, and I never ever had to ask people to log out. The only such issue is if and when you create a database, you use it, have people logged in, and then decide "wouldn't it be wonderful to use it as a template?". In that case yes, you have to require them to log out while you copy it to, say, template9. But from then on you create based on template9, and your previous db still remains a normal database.
You do want to read about templates and make a few experiments, I should say.
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Bèrto
-- ==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
> What application?
My application.
>Well you would use template0 as the TEMPLATE only if you wanted to CREATE a
database with different collation than that in template1(the default
template for
>the CREATE DATABASE command). So the question then is, why is the database
>cluster being created with a collation of en_US.UTF-8 when the locale is
>supposed to have been set to et_EE.UTF-8?
>First are you sure that dpkg-reconfigure locales is actually resetting the
locale?
dpkg-reconfigure locales
Generating locales (this might take a while)...
en_US.UTF-8... done
et_EE.UTF-8... done
Generation complete.
*** update-locale: Warning: LANGUAGE ("en_US:en") is not compatible with
LANG (et_EE.UTF-8). Disabling it.
> Second when you connect to the cluster with psql what does \l show for
> encoding
> and collation?
psql postgres
psql (9.1.1)
Type "help" for help.
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
-----------+------------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
+
| | | | |
postgres=CTc/postgres
Andrus.
Re: How to create database with default system locale is set to et_EE.UTF-8
From
Adrian Klaver
Date:
On Thursday, December 22, 2011 9:45:16 am Andrus wrote:
>
> dpkg-reconfigure locales
> Generating locales (this might take a while)...
> en_US.UTF-8... done
> et_EE.UTF-8... done
> Generation complete.
> *** update-locale: Warning: LANGUAGE ("en_US:en") is not compatible with
> LANG (et_EE.UTF-8). Disabling it.
>
Actually the interesting part would be what locale and locale -a show after
the above:)?
>
> Andrus.
--
Adrian Klaver
adrian.klaver@gmail.com
> Actually the interesting part would be what locale locale LANG=et_EE.UTF-8 LC_CTYPE="et_EE.UTF-8" LC_NUMERIC="et_EE.UTF-8" LC_TIME="et_EE.UTF-8" LC_COLLATE="et_EE.UTF-8" LC_MONETARY="et_EE.UTF-8" LC_MESSAGES="et_EE.UTF-8" LC_PAPER="et_EE.UTF-8" LC_NAME="et_EE.UTF-8" LC_ADDRESS="et_EE.UTF-8" LC_TELEPHONE="et_EE.UTF-8" LC_MEASUREMENT="et_EE.UTF-8" LC_IDENTIFICATION="et_EE.UTF-8" LC_ALL= > and locale -a show after the above:)? locale -a C en_US.utf8 et_EE.utf8 POSIX Andrus.
Re: How to create database with default system locale is set to et_EE.UTF-8
From
Adrian Klaver
Date:
On Thursday, December 22, 2011 1:22:39 pm Andrus wrote: > > Actually the interesting part would be what locale > > locale > LANG=et_EE.UTF-8 > LC_CTYPE="et_EE.UTF-8" > LC_NUMERIC="et_EE.UTF-8" > LC_TIME="et_EE.UTF-8" > LC_COLLATE="et_EE.UTF-8" > LC_MONETARY="et_EE.UTF-8" > LC_MESSAGES="et_EE.UTF-8" > LC_PAPER="et_EE.UTF-8" > LC_NAME="et_EE.UTF-8" > LC_ADDRESS="et_EE.UTF-8" > LC_TELEPHONE="et_EE.UTF-8" > LC_MEASUREMENT="et_EE.UTF-8" > LC_IDENTIFICATION="et_EE.UTF-8" > LC_ALL= > > > and locale -a show after the above:)? > > locale -a > C > en_US.utf8 > et_EE.utf8 > POSIX Would seem to be one of two things: 1) The initdb is being done before the locale is changed. or 2) The installation is overriding the locale, though I find this one less possible than 1. > > Andrus. -- Adrian Klaver adrian.klaver@gmail.com
Re: How to create database with default system locale is set to et_EE.UTF-8
From
Peter Eisentraut
Date:
On tor, 2011-12-22 at 18:29 +0200, Andrus wrote: > How to force command > > CREATE DATABASE <yourdbname> TEMPLATE = template0 > > to use et_EE.UTF-8 locale by default ? If you don't want to re-initdb, you could just update the datctype and datcollate columns of pg_database for template0. If you want to re-initdb, check the pg_createcluster man page on Debian.
>Would seem to be one of two things: >1) The initdb is being done before the locale is changed. >or >2) The installation is overriding the locale, though I find this one less >possible than 1. Thank you. How to re-configure Postresql db cluster so that uses Debian default system locale? Andrus.
Re: How to create database with default system locale is set to et_EE.UTF-8
From
Adrian Klaver
Date:
On Friday, December 23, 2011 7:26:08 am Andrus wrote: > >Would seem to be one of two things: > >1) The initdb is being done before the locale is changed. > >or > >2) The installation is overriding the locale, though I find this one less > >possible than 1. > > Thank you. > How to re-configure Postresql db cluster so that uses Debian default system > locale? See Peters answer. > > Andrus. -- Adrian Klaver adrian.klaver@gmail.com
>If you don't want to re-initdb, you could just update the datctype and >datcollate columns of pg_database for template0. Thank you. where to find sql update statement which does this ? Is update pg_database set datctype ='et_EE.UTF-8', datcollate ='et_EE.UTF-8' best for this ? template0 is read-only, how to connect to and update it using pgAdmin ? Andrus.
Re: How to create database with default system locale is set to et_EE.UTF-8
From
Peter Eisentraut
Date:
On fre, 2011-12-23 at 17:32 +0200, Andrus wrote: > >If you don't want to re-initdb, you could just update the datctype and > >datcollate columns of pg_database for template0. > > Thank you. > where to find sql update statement which does this ? > Is > > update pg_database set datctype ='et_EE.UTF-8', datcollate ='et_EE.UTF-8' > > best for this ? add WHERE datname = 'template0' > template0 is read-only, how to connect to and update it using pgAdmin ? pg_database is shared between all databases, so you can connect to any database to do this.