Thread: initdb with lc-collate=C
Hey guys,
In PG 8.x, when I did an initdb with --lc-collate=c it was always effective in setting it server wide so it would apply to all databases. However, in 9.1.2, when I run initdb like so: /etc/init.d/postgresql-9.1 initdb --lc-collate=C, it doesn't seem to have any effect.
[root@dvrv5030 9.1]# psql -U postgres -Aqtc "select setting from pg_settings where name='lc_collate'"
en_US.UTF-8
Not sure why the difference in behavior. Our application depends on collation being set to C. I can create the individual database with that option, but why won't it stick setting it server-wide with initdb?
Thanks!
Scot Kreienkamp
Senior Systems Engineer
skreien@la-z-boy.com
On 12/12/2011 10:49 AM, Scot Kreienkamp wrote: > Hey guys, > > In PG 8.x, when I did an initdb with --lc-collate=c it was always > effective in setting it server wide so it would apply to all databases. > However, in 9.1.2, when I run initdb like so: /etc/init.d/postgresql-9.1 > initdb --lc-collate=C, it doesn't seem to have any effect. > > [root@dvrv5030 9.1]# psql -U postgres -Aqtc "select setting from > pg_settings where name='lc_collate'" > > en_US.UTF-8 > > Not sure why the difference in behavior. Our application depends on > collation being set to C. I can create the individual database with that > option, but why won't it stick setting it server-wide with initdb? Databases are created using template1 as the default template. Did the lc_collate get changed for template1? > > Thanks! > > Scot Kreienkamp > -- Adrian Klaver adrian.klaver@gmail.com
On 12/12/2011 12:15 PM, Adrian Klaver wrote: > On 12/12/2011 10:49 AM, Scot Kreienkamp wrote: >> Hey guys, >> >> In PG 8.x, when I did an initdb with --lc-collate=c it was always >> effective in setting it server wide so it would apply to all databases. >> However, in 9.1.2, when I run initdb like so: /etc/init.d/postgresql-9.1 >> initdb --lc-collate=C, it doesn't seem to have any effect. >> >> [root@dvrv5030 9.1]# psql -U postgres -Aqtc "select setting from >> pg_settings where name='lc_collate'" >> >> en_US.UTF-8 >> >> Not sure why the difference in behavior. Our application depends on >> collation being set to C. I can create the individual database with that >> option, but why won't it stick setting it server-wide with initdb? > > Databases are created using template1 as the default template. Did the > lc_collate get changed for template1? Wrong question:( Read the above too quickly and did not pick up you where getting the setting from pg_settings. Try again. Is there more than one database cluster on the machine and if so are you sure you did the initdb on the same cluster as the select..? > >> >> Thanks! >> >> Scot Kreienkamp >> > > > -- Adrian Klaver adrian.klaver@gmail.com
Nope.... no clusters. I never got past the initial install and configure. All I did was install, initdb, alter a few things in postgresql.conf (nothing relating to locale) and pg_hba.conf, startpostgres using the init script, and run the query to check the collation setting. Nothing more. Scot Kreienkamp Senior Systems Engineer skreien@la-z-boy.com -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com] Sent: Monday, December 12, 2011 3:26 PM To: Scot Kreienkamp Cc: Postgres General (pgsql-general@postgresql.org) Subject: Re: [GENERAL] initdb with lc-collate=C On 12/12/2011 12:15 PM, Adrian Klaver wrote: > On 12/12/2011 10:49 AM, Scot Kreienkamp wrote: >> Hey guys, >> >> In PG 8.x, when I did an initdb with --lc-collate=c it was always >> effective in setting it server wide so it would apply to all databases. >> However, in 9.1.2, when I run initdb like so: /etc/init.d/postgresql-9.1 >> initdb --lc-collate=C, it doesn't seem to have any effect. >> >> [root@dvrv5030 9.1]# psql -U postgres -Aqtc "select setting from >> pg_settings where name='lc_collate'" >> >> en_US.UTF-8 >> >> Not sure why the difference in behavior. Our application depends on >> collation being set to C. I can create the individual database with that >> option, but why won't it stick setting it server-wide with initdb? > > Databases are created using template1 as the default template. Did the > lc_collate get changed for template1? Wrong question:( Read the above too quickly and did not pick up you where getting the setting from pg_settings. Try again. Is there more than one database cluster on the machine and if so are you sure you did the initdb on the same cluster as the select..? > >> >> Thanks! >> >> Scot Kreienkamp >> > > > -- Adrian Klaver adrian.klaver@gmail.com This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidentialinformation which is exempt from disclosure under applicable laws. If you are not the intended recipient, pleasenote that you are strictly prohibited from disseminating or distributing this information (other than to the intendedrecipient) or copying this information. If you have received this communication in error, please notify us immediatelyby e-mail or by telephone at the above number. Thank you.
On 12/12/2011 12:37 PM, Scot Kreienkamp wrote: > Nope.... no clusters. I never got past the initial install and configure. > > All I did was install, initdb, alter a few things in postgresql.conf (nothing relating to locale) and pg_hba.conf, startpostgres using the init script, and run the query to check the collation setting. Nothing more. Did you happen to catch the initdb output message? > > Scot Kreienkamp > Senior Systems Engineer > skreien@la-z-boy.com > -- Adrian Klaver adrian.klaver@gmail.com
On 12/12/2011 12:37 PM, Scot Kreienkamp wrote: > Nope.... no clusters. I never got past the initial install and configure. > > All I did was install, initdb, alter a few things in postgresql.conf (nothing relating to locale) and pg_hba.conf, startpostgres using the init script, and run the query to check the collation setting. Nothing more. I just installed a 9.1 instance to test this. I did the initdb as you did and the lc_collate is set to C as per past behavior. Gets me back to thinking there is another cluster running on your machine. > > Scot Kreienkamp -- Adrian Klaver adrian.klaver@gmail.com
There wasn't any output from the initdb other than OK. [root@dvrv5030 9.1]# /etc/init.d/postgresql-9.1 initdb --lc-collate=C Initializing database: [ OK ] [root@dvrv5030 9.1]# /etc/init.d/postgresql-9.1 start Starting postgresql-9.1 service: [ OK ] Scot Kreienkamp Senior Systems Engineer skreien@la-z-boy.com -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com] Sent: Monday, December 12, 2011 3:56 PM To: Scot Kreienkamp Cc: Postgres General (pgsql-general@postgresql.org) Subject: Re: [GENERAL] initdb with lc-collate=C On 12/12/2011 12:37 PM, Scot Kreienkamp wrote: > Nope.... no clusters. I never got past the initial install and configure. > > All I did was install, initdb, alter a few things in postgresql.conf (nothing relating to locale) and pg_hba.conf, startpostgres using the init script, and run the query to check the collation setting. Nothing more. Did you happen to catch the initdb output message? > > Scot Kreienkamp > Senior Systems Engineer > skreien@la-z-boy.com > -- Adrian Klaver adrian.klaver@gmail.com This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidentialinformation which is exempt from disclosure under applicable laws. If you are not the intended recipient, pleasenote that you are strictly prohibited from disseminating or distributing this information (other than to the intendedrecipient) or copying this information. If you have received this communication in error, please notify us immediatelyby e-mail or by telephone at the above number. Thank you.
Scot Kreienkamp <SKreien@la-z-boy.com> writes:
> There wasn't any output from the initdb other than OK.
> [root@dvrv5030 9.1]# /etc/init.d/postgresql-9.1 initdb --lc-collate=C
> Initializing database: [ OK ]
Um. This isn't running initdb: this is running the package's initscript
and hoping it will pass the switch you supplied through to initdb.
If it doesn't, you'll get whatever locale is default in root's environment.
I gather from the package name that you're using Devrim's packages not
mine, so I don't know for sure what will happen here ... but I'm pretty
sure that that extra switch would *not* be honored in the Red Hat/Fedora
initscripts.
My suggestion for this would be to "su - postgres" and then run initdb
directly.
regards, tom lane
Thanks Tom. I had tried it with SU before I mailed the list and it didn't work. When I tried it again, I noticed that
Ityped a lower case C which it wouldn't accept. I tried it again with an upper case C and it worked. That's probably
whymy first attempt with SU failed and I didn't catch the error message before it rolled off the screen.
The initdb on the initscript used to honor the extra switches. That's how I've been doing it since 8.2. I'll run it
withSU from now on.
Thanks!
Scot Kreienkamp
Senior Systems Engineer
skreien@la-z-boy.com
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, December 13, 2011 12:00 AM
To: Scot Kreienkamp
Cc: Adrian Klaver; Postgres General (pgsql-general@postgresql.org)
Subject: Re: [GENERAL] initdb with lc-collate=C
Scot Kreienkamp <SKreien@la-z-boy.com> writes:
> There wasn't any output from the initdb other than OK.
> [root@dvrv5030 9.1]# /etc/init.d/postgresql-9.1 initdb --lc-collate=C
> Initializing database: [ OK ]
Um. This isn't running initdb: this is running the package's initscript
and hoping it will pass the switch you supplied through to initdb.
If it doesn't, you'll get whatever locale is default in root's environment.
I gather from the package name that you're using Devrim's packages not
mine, so I don't know for sure what will happen here ... but I'm pretty
sure that that extra switch would *not* be honored in the Red Hat/Fedora
initscripts.
My suggestion for this would be to "su - postgres" and then run initdb
directly.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
This message is intended only for the individual or entity to which it is addressed. It may contain privileged,
confidentialinformation which is exempt from disclosure under applicable laws. If you are not the intended recipient,
pleasenote that you are strictly prohibited from disseminating or distributing this information (other than to the
intendedrecipient) or copying this information. If you have received this communication in error, please notify us
immediatelyby e-mail or by telephone at the above number. Thank you.