Zenn
🐂

ミニPCでマルチサーバー構築 [PostgreSQLコンテナ]

2024/05/18に公開
Docker
PostgreSQL
docker-compose
memo
tech


ミニPCでマルチサーバー構築シリーズのPostgreSQLコンテナ構築の記録メモです。

シリーズ

  1. N100搭載ミニPCでマルチサーバーの構想と構築準備
  2. ミニPCでマルチサーバー構築 [OS等インストール]
  3. ミニPCでマルチサーバー構築 [Sambaコンテナ]
  4. ミニPCでマルチサーバー構築 [PostgreSQLコンテナ] この記事
  5. ミニPCでマルチサーバー構築 [Nginxコンテナ]
  6. ミニPCでマルチサーバー構築 [Immichコンテナ]
  7. ミニPCでマルチサーバー構築 [VaultwardenコンテナとTailscale]
  8. ミニPCでマルチサーバー構築 [AdGuard Homeコンテナ]
  9. ミニPCでマルチサーバー構築 [GUI環境整備]

簡単な要件

  • Port5432で通信する
  • スキーマ単位ロジカルレプリケーションを使用する
  • 暗号化はscram-sha-256を使用する
  • デフォルトエンコードにUTF8を使用する
  • ロケールにはCを使用する

コンテナ化

  • 公式のDocker imageが存在するためそれを使用する
    • pg_hba.confは環境変数経由で変更可能

Docker Compose周り作成

ディレクトリ構成

$ tree .
.
├── compose.yaml
└── pgsql
    ├── etc
    │   └── pgsql.conf
    └── secrets
        └── credential

compose.yaml

compose.yaml
services:
  pgsql:
    image: postgres:latest
    restart: unless-stopped  # Stop only once with err when initdb in order to use hash pass
    shm_size: 1gb
    ports:
      - "5432:5432"
    configs:
      - source: common-user
        target: /etc/passwd
      - source: common-group
        target: /etc/group
      - source: pgsql-config
        target: /etc/postgresql.conf
    secrets:
      - source: pgsql-secrets
        target: /run/secrets
    environment:
      - TZ=Asia/Tokyo
      - POSTGRES_PASSWORD_FILE=/run/secrets/credential
      - POSTGRES_HOST_AUTH_METHOD=scram-sha-256
      - "POSTGRES_INITDB_ARGS=-E UTF8 --no-locale -A scram-sha-256"
    volumes:
      - /srv/pgsql:/var/lib/postgresql/data
      - /var/log/pgsql:/var/log/pgsql
    networks:
      - pgnet
    command: postgres -c config_file=/etc/postgresql.conf

configs:
  common-user:
    file: /etc/passwd
  common-group:
    file: /etc/group
  pgsql-config:
    file: ./pgsql/etc/pgsql.conf

secrets:
  pgsql-secrets:
    file: ./pgsql/secrets

networks:
  pgnet:

設定

実際の設定ファイル

pgsql.conf

有効項目を抜粋

listen_addresses = '*'
port = 5432
max_connections = 30
shared_buffers = 256MB
work_mem = 8MB
maintenance_work_mem = 256MB
temp_buffers = 32MB
effective_io_concurrency = 200
random_page_cost = 1.1
default_statistics_target = 500
timezone = 'Asia/Tokyo'

wal_level = logical

log_destination = 'jsonlog'
logging_collector = on
log_directory = '/var/log/pgsql'
log_filename = 'postgresql_%Y%m%d_%H%M%S.log
log_line_prefix = '%t %u@%d(%p) '
log_rotation_age = 0
log_autovacuum_min_duration = 5min
log_min_duration_statement = 60000
log_timezone = 'Asia/Tokyo'

各設定値

  • listen_addresses: 外から接続受け付けるため'*'
  • max_connections: 自分しか使用しないため多めで30
  • shared_buffers: 大きくしすぎても無意味らしいのでまず256MB
  • work_mem: 接続毎に確保されるため8MB
  • maintenance_work_mem: 参考文献の値そのまま256MB
  • temp_buffers: 一時テーブル多用する予定のため32MB
  • effective_io_concurrency: SSDのため多めの値200
  • random_page_cost: SSDのため1に近い値1.1
  • default_statistics_target: そこそこの実行計画精度のため500
  • wal_level: ロジカルレプリケーションを使用するためlogical

ユーザー管理

スーパーユーザー名

  • 環境変数で設定可能
    • POSTGRES_USER : デフォルトはpostgres
    • POSTGRES_PASSWORD : 指定必須項目
      • POSTGRES_PASSWORD_FILE : ファイルで指定可能

パスワード設定

Docker Composeで扱う際にパスワードを平文で管理したくないため、暗号化されたハッシュ状態のパスワードを取り扱うようにする。

  • ハッシュ状態のパスワードで取り扱う方法
    1. 予めSandbox環境のPostgreSQLでユーザを作成する
    2. 以下SQLでハッシュ状態のパスワードを出力しファイルに保存
      select rolpassword from pg_catalog.pg_authid where rolname='username_to_create_a_hashpass';
    3. compose.yamlの環境変数POSTGRES_PASSWORD_FILEにそのファイルを指定する
    4. 以下SQLでその他のユーザも作成可能
      create role myuser login createdb createrole encrypted password '$hashpass';
  • ただし上記方法でコンテナ作成すると初期化スクリプトは動作しない
    • 初期化スクリプト動作前にエラーが発生し初回起動はコンテナ作成に失敗する
    • データベースクラスタは正しく作成されている
    • 初期化スクリプト実行時にDB接続する際、渡したパスワードを認証に使用する模様
      • ハッシュ化されているためログイン失敗 -> コンテナ作成失敗
    • 何も変更せず再度コンテナを立ち上げると正常に動作する

参考文献

Discussion