4Core メモリ16GB のVM想定

# pkg install postgresqlXX-server

Disk追加はここらへん(パーティション追加して zfs を作成)を参照

# 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

ZFSがメモリを食いつぶさないよう、ARC（キャッシュ）を制限¹⁾

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

再起動

# shutdown -r now

確認

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

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

# 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"

PostgreSQL initdb オプション設定一覧

エンコーディング関連

• 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プロバイダ）を適用可能

-- 必要なときだけ日本語ソートを適用（※事前にICUサポートが必要）
SELECT name FROM users ORDER BY name COLLATE "ja-JP-x-icu";

これなら、基本は高速、必要な時だけ日本語順という「いいとこ取り」が可能²⁾

運用指針は以下の通りとした

# 初期化（ここで--data-checksums等が適用される）
service postgresql initdb

以下の様に設定と違う値が表示されるが、これはDefaultを表示しているだけの模様

selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB

var/db/postgres/data18/postgresql.conf の中身をチェックすると反映されている

# 起動
service postgresql start

# 設定値の確認（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)

/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からの接続が許可されます。³⁾

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

listen_addresses = '*'

root(postgres)ユーザのパスワードを変更します。⁴⁾

alter role postgres with password 'xxxxxxxxxxxxxxxxxxxx';

⁵⁾

# pkg install postgresqlXX-server

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からの接続が許可されます。⁶⁾

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

listen_addresses = '*'

# service postgresql start

root(postgres)ユーザのパスワードを変更します。⁷⁾

alter role postgres with password 'xxxxxxxxxxxxxxxxxxxx';

# 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の場合

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 ] ]

文字コードが標準と違う場合には

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となります。マニュアルなどにはハイフンなしで書かれていますがハイフンが必要です。⁹⁾

デフォルトの設定では、自動バキュームは有効で、関連するパラメータも適切に設定されています。 ¹⁰⁾¹¹⁾

Let's Postgres

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