差分

このページの2つのバージョン間の差分を表示します。

--- serverapps:db:postgresql [2026/04/27 15:00] – [データベースの初期化と起動] hayashi
+++ serverapps:db:postgresql [2026/04/27 15:59] (現在) – [password変更] hayashi
@@ 行 21: / 行 21: @@
            zdata/var/db/postgres/data18
-# 3. チューニング
+# 3. 所有者の変更
-zfs set recordsize=8k zdata/var/db/postgres/data18
-zfs set primarycache=metadata zdata/var/db/postgres/data18
-# 4. 所有者の変更
 chown -R postgres:postgres /var/db/postgres/data18
 chmod 700 /var/db/postgres/data18
@@ 行 103: / 行 99: @@
 | ''----set autovacuum_max_workers=4'' | Autovacuumワーカー数 | 不要領域の掃除を並列で行う数。複数DBのメンテナンスを滞らせないよう4に増加 |
+エンコーディング関連
+  * encoding=UTF-8
+  * no-locale
+以下の観点でこう変更した
+  * OSアップデートによる「インデックス破損」
+    * FreeBSDやLinuxのマイナーアップデートで、OS側のロケール定義（glibcやlibcのソート順）がわずかに変更されることがあります。このとき、DB側のインデックスとOS側のルールに不整合が起き、**「検索にヒットしないデータが出る」「重複エラーで書き込めない」**といった、復旧が困難な破損を招くリスクがあります。
+    * PostgreSQL 17.1 のリリースノートでは、LC_CTYPE が C で LC_COLLATE が他のロケールという稀な構成で、問い合わせ結果が誤り、文字列インデックスが破損する可能性があったため、影響を受けるインデックスの再作成が必要と明記されています。
+    * SRA OSS の技術情報でも同じ趣旨が説明されており、その組み合わせでは文字列インデックスが破損しうるとされています
+  * パフォーマンスの劣化:
+    * ja_JP ロケールは文字列比較のたびに複雑な言語ルールを計算するため、C ロケールに比べてソート性能が数倍〜十数倍遅くなることがあります。
+**代替案**
+  * 読み仮名カラムを持つ
+    * name_kana カラムを作り、そこに平仮名を入れて、そのカラムでソート
+    * C ロケールでも、平仮名やカタカナの数値順は五十音順とほぼ一致するため、実用上の問題はないはず
+  * クエリ時にロケールを指定する (ICU規格)
+    * PostgreSQL 15以降では、DB全体を C ロケールにしても、特定のクエリや特定のカラムにだけ、**不変のロケール定義（ICUプロバイダ）**を適用可能
+<code SQL>
+-- 必要なときだけ日本語ソートを適用（※事前にICUサポートが必要）
+SELECT name FROM users ORDER BY name COLLATE "ja-JP-x-icu";
+</code>
+これなら、基本は高速、必要な時だけ日本語順という「いいとこ取り」が可能((2026-04-28: FreeBSD pkgでは'--without-icu'でビルドされているので注意))
+運用指針は以下の通りとした
+^ 項目 ^ 方針 ^ 理由 ^
+| OS更新時の運用 | 特になし (メンテナンスフリー) | C ロケール採用によりOS依存を排除したため |
+| インデックス管理 | 定期的な ANALYZE のみ | 破損リスクがないため REINDEX 運用は不要 |
+| ソート順の担保 | アプリケーション/設計で解決 | DBの整合性を最優先とするため |
 ==== データベースの初期化と起動 ====
@@ 行 109: / 行 137: @@
 <code>
 # 初期化（ここで--data-checksums等が適用される）
-su - postgres -c "/usr/local/bin/initdb -D /var/db/postgres/data18 \
-  --data-checksums \
-  --encoding=UTF-8 \
-  --no-locale \
-  --auth-local=peer \
-  --auth-host=scram-sha-256 \
-  --set password_encryption=scram-sha-256 \
-  --set shared_buffers=4GB \
-  --set work_mem=64MB \
-  --set maintenance_work_mem=512MB \
-  --set max_connections=200 \
-  --set autovacuum_max_workers=4"
-# ふつうはこちらだが、反映されなかった
 service postgresql initdb
+</code>
+以下の様に設定と違う値が表示されるが、これはDefaultを表示しているだけの模様
+  selecting default "max_connections" ... 100
+  selecting default "shared_buffers" ... 128MB
+var/db/postgres/data18/postgresql.conf の中身をチェックすると反映されている
+<code>
 # 起動
 service postgresql start
 </code>
+<code>
+# 設定値の確認（SQLで直接聞く）
+su - postgres -c "psql -c 'SHOW shared_buffers; SHOW max_connections; SHOW data_checksums;'"
+ shared_buffers
+----------------
+GB
+(1 row)
+ max_connections
+-----------------
+(1 row)
+ data_checksums
+----------------
+ on
+(1 row)
+</code>
+==== クライアント認証 ====
+/var/db/postgres/data/pg_hba.conf に設定します。
+標準では自分自身からの接続のみを許可しています。
+  # TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
+  # 1. ローカル（OS上のソート接続）は postgres ユーザーも含め Peer認証で許可
+  local   all             all                                     peer
+  # 2. 【重要】ネットワーク経由の postgres ユーザー（管理者）は一律拒否
+  host    all             postgres        0.0.0.0/0               reject
+  host    all             postgres        ::/0                    reject
+  # 3. ネットワーク経由の一般ユーザーは、特定のLAN内からのみ SCRAM認証で許可
+  host    all             all             192.168.0.0/24          scram-sha-256
+これで、管理者 postgres だけはネットワーク経由を拒否し、192.168.0.xxxからの接続が許可されます。((scram-sha-256は暗号化パスワード接続))
+ほかからアクセスする場合には、postgresql.confのlisten_addresses も変更が必要です。
+  listen_addresses = '*'
+==== 管理者 password変更 ====
+root(postgres)ユーザのパスワードを変更します。((パスワードは別紙参照))
+  alter role postgres with password 'xxxxxxxxxxxxxxxxxxxx';
 -----
@@ 行 157: / 行 226: @@
 ==== クライアント認証 ====
-/usr/local/pgsql/data/pg_hba.conf に設定します。
+/var/db/postgres/data/pg_hba.conf に設定します。
 標準では自分自身からの接続のみを許可しています。
@@ 行 172: / 行 241: @@
 ==== password変更 ====
-rootユーザのパスワードを変更します。((パスワードは別紙参照))
+root(postgres)ユーザのパスワードを変更します。((パスワードは別紙参照))
-  alter role postgres with password 'xxxxxxxxxxxxxxxxxxxx';
+  # sudo -u postgres psql
+  postgres=# alter role postgres with password 'xxxxxxxxxxxxxxxxxxxx';
 ==== Role(ユーザ)の追加 ====