====== PostgreSQL ======
===== zfsと組み合わせる =====

4Core メモリ16GB のVM想定
==== インストール ====
  # pkg install postgresqlXX-server

==== ZFSレイヤーの設定 ====

Disk追加は[[https://wiki.rookie-inc.com/os/freebsd/tips#%E3%83%91%E3%83%BC%E3%83%86%E3%82%A3%E3%82%B7%E3%83%A7%E3%83%B3%E8%BF%BD%E5%8A%A0%E3%81%97%E3%81%A6_zfs_%E3%82%92%E4%BD%9C%E6%88%90|ここらへん(パーティション追加して zfs を作成)]]を参照

<code>
# 1. PostgreSQL用のデータセット(親)作成（マウントはまだしない）
zfs create -p -o canmount=off zdata/var/db/postgres

# 2. バージョンごとのデータセットを作成（将来のアップグレードが楽になりそう）
# 例: PostgreSQL 18の場合
zfs create -o mountpoint=/var/db/postgres/data18 \
           -o recordsize=8k                      \
           -o primarycache=metadata              \
           zdata/var/db/postgres/data18

# 3. 所有者の変更
chown -R postgres:postgres /var/db/postgres/data18
chmod 700 /var/db/postgres/data18
</code>

==== OSレイヤーの設定 (/boot/loader.conf) ====


ZFSがメモリを食いつぶさないよう、ARC（キャッシュ）を制限((再起動後に反映))

<code>
# cat /boot/loader.conf
# ZFS ARCの上限を8GBに設定（残り8GBをOSとPGで分け合う）
vfs.zfs.arc.max="8G"
</code>

再起動

  # shutdown -r now

確認

<code>
# パラメータ名で確認
sysctl vfs.zfs.arc.max

# 実際に適用されている最大値を確認（前述と同じ）
sysctl kstat.zfs.misc.arcstats.c_max
</code>

==== PostgreSQL起動設定 (/usr/local/etc/rc.conf.d/postgresql) ====

<code>
# 1. ディレクトリ作成
mkdir -p /usr/local/etc/rc.conf.d
touch /usr/local/etc/rc.conf.d/postgresql

# 2. 設定の書き込み
sysrc -f /usr/local/etc/rc.conf.d/postgresql postgresql_enable="YES"
sysrc -f /usr/local/etc/rc.conf.d/postgresql postgresql_data="/var/db/postgres/data18"

## ログ出力先の指定 (postgresql_logdir)
## これは好みで
mkdir /var/log/postgresql
chown postgres /var/log/postgresql
sysrc -f /usr/local/etc/rc.conf.d/postgresql postgresql_logdir="/var/log/postgresql"


# 3. 初期化フラグ
sysrc -f /usr/local/etc/rc.conf.d/postgresql postgresql_initdb_flags="\
  --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"
</code>

PostgreSQL initdb オプション設定一覧

^ オプション ^ 役割・意味 ^ 設定の狙い・メリット ^
| ''----data-checksums'' | データチェックサムの有効化 | ディスク上の物理的なデータ破損を検知可能にする（必須級の安全策） |
| ''----encoding=UTF-8'' | 文字エンコーディングの設定 | データベース全体の文字コードをUTF-8に固定する |
| ''----no-locale'' | ロケール（言語・文化設定）の無効化 | OS依存のソート順などを排除し、パフォーマンス向上とOS更新時の不整合を防止する |
| ''----auth-local=peer'' | ローカル接続の認証方式 | OSユーザーとDBユーザーが一致すればパスワードなしで接続を許可する（安全かつ便利） |
| ''----auth-host=scram-sha-256'' | ネットワーク接続の認証方式 | パスワード認証に最も安全なSCRAM-SHA-256方式を強制する |
| ''----set password_encryption=scram-sha-256'' | パスワード保存形式の暗号化 | ユーザー作成時に保存されるパスワードハッシュをSCRAM形式に固定する |
| ''----set shared_buffers=4GB'' | 共有バッファ（メモリキャッシュ） | メモリ上にデータを保持する領域。16GBメモリに対し4GBを割り当て高速化を図る |
| ''----set work_mem=64MB'' | 作業用メモリ（クエリごと） | 複雑なソートや集計で各プロセスが使用するメモリ。多すぎるとOOMの原因になるため64MBに調整 |
| ''----set maintenance_work_mem=512MB'' | メンテナンス用メモリ | INDEX作成やVACUUM時に使用する。大きくすることで管理タスクが高速化する |
| ''----set max_connections=200'' | 最大同時接続数 | 同時にDBへ接続できる最大数。複数サービス同居を考慮しデフォルトより多めに設定 |
| ''----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の整合性を最優先とするため |

==== データベースの初期化と起動 ====


<code>
# 初期化（ここで--data-checksums等が適用される）
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
----------------
 4GB
(1 row)

 max_connections
-----------------
 200
(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';
  


-----

===== 普通の場合 ===== 
((少し古い))
==== インストール ====
  # pkg install postgresqlXX-server
  
==== /etc/rc.conf.d/postgresql ====

  postgresql_enable="YES"
  #postgresql_data="/var/db/postgres"

==== 初期化 ====

  
  # service postgresql initdb

  または
  # /usr/local/etc/rc.d/postgresql initdb

  または
  # mkdir /var/db/postgres
  # chown postgres:postgres /var/db/postgres
  # su - postgres
  $ initdb -D /var/db/postgres/data

==== クライアント認証 ====

/var/db/postgres/data/pg_hba.conf に設定します。
標準では自分自身からの接続のみを許可しています。

  # TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
  host    all             all             192.168.0.0/24          scram-sha-256

これで、192.168.0.xxxからの接続が許可されます。((scram-sha-256は暗号化パスワード接続))

ほかからアクセスする場合には、postgresql.confのlisten_addresses も変更が必要です。
  listen_addresses = '*'
==== 起動 ====

  # service postgresql start

==== password変更 ====
root(postgres)ユーザのパスワードを変更します。((パスワードは別紙参照))
  # sudo -u postgres psql
  postgres=# alter role postgres with password 'xxxxxxxxxxxxxxxxxxxx';
  
==== Role(ユーザ)の追加 ====

  # su - postgres
  $ createuser -d -U postgres -P ユーザー名
  Enter password for new role: 
  Enter it again: 
  Shall the new role be a superuser? (y/n) 

-d
データベースの作成を許可

-P
パスワード入力プロンプトを表示

または、psqlの中で

  CREATE ROLE role_name WITH LOGIN PASSWORD 'password'
===== ユーザの一覧 =====

  # psql postgres
  
  postgres=# \du
                               List of roles
   Role name | Superuser | Create role | Create DB | Connections | Member of 
  -----------+-----------+-------------+-----------+-------------+-----------
   dbuser1   | no        | no          | no        | no limit    | {}
   dbuser2   | no        | no          | no        | no limit    | {}
   postgres  | yes       | yes         | yes       | no limit    | {}
  (3 rows)
  postgres=# 

===== パスワード変更 =====

  # psql postgres
  
  postgres=# alter role postgres with password 'NewPassword';
  ALTER ROLE
  postgres=# 
===== データベースの作成 =====


  $ createdb -E UTF8 -O オーナー名 データベース名

私は、面倒なのでpgadminから作っています。;-)

SQLの場合
<code sql>
CREATE DATABASE [database_name]
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ CONNECTION LIMIT [=] connlimit ] ]
</code>
文字コードが標準と違う場合には
  TEMPLATE = template0
を指定する必要があります((必須です))

日本語DBの作成例
  CREATE DATABASE dbname WITH OWNER ownername 
    ENCODING UTF8 LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8'
    TEMPLATE template0
  ;

なお、localeはエンコーディング部分はUTF8ではなくてUTF-8となります。マニュアルなどにはハイフンなしで書かれていますがハイフンが必要です。((FreeBSDで確認したので、Linuxでは違うかもしれません。マニュアルにも環境依存と書かれていますし…))

===== AutoVacuum =====

デフォルトの設定では、自動バキュームは有効で、関連するパラメータも適切に設定されています。 ((9.0.x))((http://www.postgresql.jp/document/pg904doc/html/routine-vacuuming.html#AUTOVACUUM))

===== 参考 =====
[[http://lets.postgresql.jp/|Let's Postgres]]


http://everything-you-do-is-practice.blogspot.com/