PostgreSQLとDockerの実践的な組み合わせ - 開発から本番まで

1
2

# 最小限の起動コマンド
docker run --name my-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres:18

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# compose.yaml
services:
  db:
    image: postgres:18
    container_name: dev-postgres
    environment:
      POSTGRES_USER: devuser
      POSTGRES_PASSWORD: devpassword
      POSTGRES_DB: myapp_development
      TZ: Asia/Tokyo
      POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=C"
    ports:
      - "5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U devuser -d myapp_development"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s

volumes:
  postgres-data:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# バックグラウンドで起動
docker compose up -d

# ログを確認
docker compose logs -f db

# 起動完了を待つ（healthcheck利用）
docker compose up -d --wait

# psqlで接続
docker compose exec db psql -U devuser -d myapp_development

# または外部からの接続
psql -h localhost -p 5432 -U devuser -d myapp_development

1
2
3
4
5

myapp_development=# SELECT version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 18.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-12) 14.2.0, 64-bit
(1 row)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

# compose.yaml
services:
  db:
    image: postgres:18
    container_name: dev-postgres
    environment:
      POSTGRES_USER: devuser
      POSTGRES_PASSWORD: devpassword
      POSTGRES_DB: myapp_development
      TZ: Asia/Tokyo
    ports:
      - "5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql
      - ./docker/db/init:/docker-entrypoint-initdb.d:ro

volumes:
  postgres-data:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

#!/usr/bin/env bash
set -e

# テスト用データベースを作成
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE DATABASE myapp_test;
    GRANT ALL PRIVILEGES ON DATABASE myapp_test TO $POSTGRES_USER;
EOSQL

echo "Additional database 'myapp_test' created successfully."

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# pg_dumpでバックアップ（SQL形式）
docker compose exec db pg_dump -U devuser -d myapp_development > backup.sql

# pg_dumpでバックアップ（カスタム形式 - 推奨）
docker compose exec db pg_dump -U devuser -Fc myapp_development > backup.dump

# リストア（SQL形式）
docker compose exec -T db psql -U devuser -d myapp_development < backup.sql

# リストア（カスタム形式）
docker compose exec db pg_restore -U devuser -d myapp_development --clean backup.dump

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# ボリュームのバックアップ（tarアーカイブ）
docker run --rm \
  -v postgres-data:/source:ro \
  -v $(pwd):/backup \
  alpine tar cvf /backup/postgres-backup.tar -C /source .

# ボリュームのリストア
docker run --rm \
  -v postgres-data:/target \
  -v $(pwd):/backup \
  alpine sh -c "cd /target && tar xvf /backup/postgres-backup.tar"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# ボリューム一覧
docker volume ls

# ボリュームの詳細確認
docker volume inspect postgres-data

# 使用していないボリュームの削除
docker volume prune

# 特定のボリュームを削除（データが消えるので注意）
docker volume rm postgres-data

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

-- docker/db/init/01-schema.sql

-- ユーザーテーブル
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 記事テーブル
CREATE TABLE IF NOT EXISTS articles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    published_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- インデックス
CREATE INDEX IF NOT EXISTS idx_articles_user_id ON articles(user_id);
CREATE INDEX IF NOT EXISTS idx_articles_status ON articles(status);
CREATE INDEX IF NOT EXISTS idx_articles_published_at ON articles(published_at);

-- 更新日時を自動更新するトリガー関数
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- トリガーの設定
CREATE TRIGGER users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER articles_updated_at
    BEFORE UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

-- docker/db/init/02-seed.sql

-- 開発用ユーザー
INSERT INTO users (email, name, password_hash) VALUES
    ('admin@example.com', '管理者', '$2b$10$dummyhashvalue1'),
    ('user1@example.com', 'テストユーザー1', '$2b$10$dummyhashvalue2'),
    ('user2@example.com', 'テストユーザー2', '$2b$10$dummyhashvalue3')
ON CONFLICT (email) DO NOTHING;

-- サンプル記事
INSERT INTO articles (user_id, title, content, status, published_at) VALUES
    (1, 'はじめての投稿', 'これは最初の記事です。', 'published', CURRENT_TIMESTAMP),
    (1, '下書き記事', '公開前の記事内容です。', 'draft', NULL),
    (2, 'ユーザー1の記事', '別のユーザーが投稿した記事です。', 'published', CURRENT_TIMESTAMP - INTERVAL '1 day')
ON CONFLICT DO NOTHING;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

#!/usr/bin/env bash
# docker/db/init/02-seed.sh
set -e

# 環境変数で制御（デフォルトはdevelopment）
APP_ENV=${APP_ENV:-development}

if [ "$APP_ENV" = "development" ] || [ "$APP_ENV" = "test" ]; then
    echo "Seeding database for $APP_ENV environment..."
    psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
        INSERT INTO users (email, name, password_hash) VALUES
            ('admin@example.com', '管理者', '\$2b\$10\$dummyhashvalue1')
        ON CONFLICT (email) DO NOTHING;
EOSQL
    echo "Seeding completed."
else
    echo "Skipping seed data for $APP_ENV environment."
fi

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

services:
  db:
    image: postgres:18
    environment:
      POSTGRES_USER: devuser
      POSTGRES_PASSWORD: devpassword
      POSTGRES_DB: myapp_development
      APP_ENV: development  # development, test, production
    volumes:
      - postgres-data:/var/lib/postgresql
      - ./docker/db/init:/docker-entrypoint-initdb.d:ro

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

# compose.yaml（Docker Secrets使用）
services:
  db:
    image: postgres:18
    environment:
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD_FILE: /run/secrets/db_password
      POSTGRES_DB: myapp_production
    secrets:
      - db_password

secrets:
  db_password:
    file: ./secrets/db_password.txt  # このファイルはGit管理外に

1
2
3
4
5
6

# compose.yaml
services:
  db:
    image: postgres:18
    env_file:
      - .env.production  # Git管理外

1
2
3
4

# .env.production
POSTGRES_USER=appuser
POSTGRES_PASSWORD=very_secure_random_password_here
POSTGRES_DB=myapp_production

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

services:
  app:
    build: .
    networks:
      - frontend
      - backend

  db:
    image: postgres:18
    # portsを指定しない = 外部からアクセス不可
    networks:
      - backend  # バックエンドネットワークのみ

networks:
  frontend:
  backend:
    internal: true  # 外部ルーティング不可

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

services:
  db:
    image: postgres:18
    deploy:
      resources:
        limits:
          cpus: '2.0'
          memory: 4G
        reservations:
          cpus: '0.5'
          memory: 1G
    # PostgreSQLの共有メモリ設定
    shm_size: 256mb

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

services:
  db:
    image: postgres:18
    command:
      - "postgres"
      - "-c" 
      - "shared_buffers=1GB"
      - "-c"
      - "effective_cache_size=3GB"
      - "-c"
      - "maintenance_work_mem=256MB"
      - "-c"
      - "work_mem=16MB"
      - "-c"
      - "max_connections=100"
      - "-c"
      - "random_page_cost=1.1"  # SSD使用時
      - "-c"
      - "log_statement=all"      # 本番では'ddl'または'none'推奨
      - "-c"
      - "log_min_duration_statement=1000"  # 1秒以上のクエリをログ

1
2
3
4
5
6
7

services:
  db:
    image: postgres:18
    volumes:
      - postgres-data:/var/lib/postgresql
      - ./config/postgresql.conf:/etc/postgresql/postgresql.conf:ro
    command: ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

services:
  db:
    image: postgres:18
    logging:
      driver: "json-file"
      options:
        max-size: "100m"
        max-file: "5"
    command:
      - "postgres"
      - "-c"
      - "logging_collector=on"
      - "-c"
      - "log_directory=/var/log/postgresql"
      - "-c"
      - "log_filename=postgresql-%Y-%m-%d.log"
      - "-c"
      - "log_rotation_age=1d"
      - "-c"
      - "log_rotation_size=100MB"
    volumes:
      - postgres-logs:/var/log/postgresql

volumes:
  postgres-logs:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# cluster.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: myapp-db
spec:
  instances: 3  # プライマリ1 + レプリカ2
  
  postgresql:
    parameters:
      shared_buffers: "256MB"
      effective_cache_size: "768MB"
  
  storage:
    size: 10Gi
    storageClass: standard
  
  bootstrap:
    initdb:
      database: myapp
      owner: myapp
  
  backup:
    barmanObjectStore:
      destinationPath: "s3://my-bucket/backups"
      s3Credentials:
        accessKeyId:
          name: s3-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: s3-creds
          key: SECRET_ACCESS_KEY

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# CloudNativePGのインストール
kubectl apply --server-side -f \
  https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/release-1.25/releases/cnpg-1.25.0.yaml

# クラスタの作成
kubectl apply -f cluster.yaml

# クラスタの状態確認
kubectl get clusters
kubectl get pods -l cnpg.io/cluster=myapp-db

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

project/
├── compose.yaml              # 開発環境用
├── compose.production.yaml   # 本番環境用（必要に応じて）
├── .env.example              # 環境変数のテンプレート
├── .env                      # 実際の環境変数（Git管理外）
├── docker/
│   └── db/
│       ├── init/
│       │   ├── 01-schema.sql     # スキーマ定義
│       │   ├── 02-seed.sql       # シードデータ
│       │   └── 03-extensions.sql # 拡張機能
│       └── config/
│           └── postgresql.conf   # カスタム設定（必要に応じて）
├── .gitignore
└── src/
    └── ...

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# 環境変数ファイル
.env
.env.production
.env.*.local

# シークレット
secrets/

# バックアップファイル
*.sql
*.dump
*.tar
backup/

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

.PHONY: up down logs db-shell db-reset db-backup db-restore

# 開発環境の起動
up:
	docker compose up -d --wait

# 開発環境の停止
down:
	docker compose down

# ログの表示
logs:
	docker compose logs -f db

# PostgreSQLに接続
db-shell:
	docker compose exec db psql -U devuser -d myapp_development

# データベースのリセット（注意: 全データ削除）
db-reset:
	docker compose down -v
	docker compose up -d --wait

# バックアップの作成
db-backup:
	@mkdir -p backup
	docker compose exec db pg_dump -U devuser -Fc myapp_development > backup/backup-$$(date +%Y%m%d-%H%M%S).dump
	@echo "Backup created: backup/backup-$$(date +%Y%m%d-%H%M%S).dump"

# 最新のバックアップからリストア
db-restore:
	@LATEST=$$(ls -t backup/*.dump 2>/dev/null | head -1); \
	if [ -z "$$LATEST" ]; then \
		echo "No backup found in backup/"; \
		exit 1; \
	fi; \
	echo "Restoring from $$LATEST..."; \
	docker compose exec -T db pg_restore -U devuser -d myapp_development --clean < $$LATEST