how to determine initdb locale (after the event)?

From: John Sidney-Woollett <johnsw(at)wardbrook(dot)com>
To: pgsql <pgsql-general(at)postgresql(dot)org>
Subject: how to determine initdb locale (after the event)?
Date: 2005-01-13 12:50:08
Message-ID: 41E66E80.9050403@wardbrook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I need to create a new database v7.4.6 on a new server that will act as
a copy of our current live 7.4.1 database. Ultimately replicated using
slony (we hope)...

I have installed 7.4.6 on the new server - no problems so far.

Now I'm trying to figure out what locale options were passed to initdb
for our current live server when it was created. (And whether they are
in fact correct for our needs - see below?!)

pg_controldata gives the following output:

pg_control version number: 72
Catalog version number: 200310211
Database cluster state: in production
pg_control last modified: Thu 13 Jan 2005 12:26:47 GMT
Current log file ID: 18
Next log file segment: 133
Latest checkpoint location: 12/84411BBC
Prior checkpoint location: 12/843E0D78
Latest checkpoint's REDO location: 12/84411BBC
Latest checkpoint's UNDO location: 0/0
Latest checkpoint's StartUpID: 167
Latest checkpoint's NextXID: 392845890
Latest checkpoint's NextOID: 400628
Time of latest checkpoint: Thu 13 Jan 2005 12:26:45 GMT
Database block size: 8192
Blocks per segment of large relation: 131072
Maximum length of identifiers: 64
Maximum number of function arguments: 32
Date/time type storage: floating-point numbers
Maximum length of locale name: 128
LC_COLLATE: C
LC_CTYPE: C

and locale -a offers the following (on both servers):

locale -a
C
en_AU
en_AU.utf8
en_BW
en_BW.utf8
en_CA
en_CA.utf8
en_DK
en_DK.utf8
en_GB
en_GB.iso885915
en_GB.utf8
en_HK
en_HK.utf8
en_IE
en_IE(at)euro
en_IE.utf8
en_IE(dot)utf8(at)euro
en_IN
en_NZ
en_NZ.utf8
en_PH
en_PH.utf8
en_SG
en_SG.utf8
en_US
en_US.iso885915
en_US.utf8
en_ZA
en_ZA.utf8
en_ZW
en_ZW.utf8
POSIX

Listing the databases (on our live server) gives the following output:

Name | Owner | Encoding
---------------+----------+----------
db_live | postgres | UNICODE
template0 | postgres | UNICODE
template1 | postgres | UNICODE

Basically our database serves a java web application where we want to
store data from several european languages (mixed in the same tables).
We're happy to accept "english" sorting rules...

Question 1) Should we try to determine what locale setting was used for
our live database and use that for the new server, or should we be using
the following for the new db (because we're in the UK):

initdb --locale en_GB.utf8

Question 2) Or should be go for the C locale regardless of the current
locale setting on the live server (since we happy with "english" sorting
and indexing)? And does that work with UNICODE encoding?

Question 3) If the new database locale is different from the current
live one, as we going to get problems when dumping and restoring between
the two databases?

Question 4) Because we want to make use of slony - could differences in
the locale cause problems down the line - if we use a different locale
for initdb for the new db?

Sorry if I'm asking dumb questions, but this whole locale thing and its
implications has me totally baffled. I need to get this right because I
can't afford to trash our db or data.

Thanks for any help anyone can give.

John Sidney-Woollett

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2005-01-13 12:50:38 Re: vacuum vs open transactions
Previous Message Együd Csaba 2005-01-13 12:33:16 pgsqlODBC problems