PostgreSQLのソート順がおかしいためロケール再設定とinitdbやり直し

またPostgreSQLのソート順でおかしくなったのでメモ。

普段使っているCentOS5ではロケールをja_JP.UTF-8に設定し、 その状態でPostgreSQLもインストール＆起動しているので気づかなかったのだが、 あるお客さんに収めるサーバをRHELで構築する際ロケールをen_US.UTF-8に設定してインストールしたところ日本語列のORDER BYの順番が明らかにおかしい。 どうやらまたロケールの問題がでてしまったらしい。

現状と調べてみた情報を列挙。

• initdbした時点でのロケールがずっと使われるため、postgresql.confのlc_で始まる設定値を変更してもダメ
• 現在のPostgreSQLのロケールはpg_controldata /var/lib/pgsql/dataで確認できる
• /etc/init.d/postgresql startを実行したときに/var/lib/pgsql/dataディレクトリがないと自動的にinitdbが動くが、その際のinitdbには--localeが指定されていないのでOSの環境を引き継いでいる
• initdbを再度実行するには/var/lib/pgsql/dataディレクトリを消してしまえばよい（つまりデータは使えなくなる、と。ダンプ必要）

幸い今回の環境ではen_US.UTF-8をja_JP.UTF-8に変更しても影響を受けるプログラムがない（と考えられる）ので、 OS全体のロケールを書き換え、PostgreSQLをinitdbからやり直すことにした。 （/etc/init.d/postgresqlを書き換えてinitdb時に--localeオプションを追加するという手もあるけど、今回はパス）

1. データのバックアップ

   pg_dumpコマンドを使用してダンプ

2. DBを停止

   # /etc/init.d/postgresql stop

3. OSのロケール変更

   # vim /etc/sysconfig/i18n

   en_US.UTF-8をja_JP.UTF-8に変更し保存。

4. OS再起動

   # shutdown -r now

   chkconfigでpostgresqlが自動起動するようになっている場合は再起動後PostgreSQLを停止。

5. initdbやり直し

   # cd /var/lib/pgsql
   # mv data data_old
   # mkdir data
   # chown postgres:postgres data
   # chmod 700 data
   # /etc/init.d/postgresql start
   

6. PostgreSQLのロケール確認

   # pg_controldata /var/lib/pgsql/data
   pg_control version number:            812
   Catalog version number:               200510211
   Database system identifier:           5316336277145481133
   Database cluster state:               in production
   pg_control last modified:             2009年03月23日 20時09分29秒
   Current log file ID:                  0
   Next log file segment:                1
   Latest checkpoint location:           0/38C484
   Prior checkpoint location:            0/33B194
   Latest checkpoint's REDO location:    0/38C484
   Latest checkpoint's UNDO location:    0/0
   Latest checkpoint's TimeLineID:       1
   Latest checkpoint's NextXID:          1607
   Latest checkpoint's NextOID:          24576
   Latest checkpoint's NextMultiXactId:  1
   Latest checkpoint's NextMultiOffset:  0
   Time of latest checkpoint:            2009年03月23日 20時09分29秒
   Maximum data alignment:               4
   Database block size:                  8192
   Blocks per segment of large relation: 131072
   Bytes per WAL segment:                16777216
   Maximum length of identifiers:        64
   Maximum columns in an index:          32
   Date/time type storage:               floating-point numbers
   Maximum length of locale name:        128
   LC_COLLATE:                           ja_JP.UTF-8
   LC_CTYPE:                             ja_JP.UTF-8
   

これで日本語列のソート順が意図したとおりになった。