Windows 11 Pro
WSL2 (Ubuntu 24.04 LTS)
Docker Desktop 4.44.3 (202357)
Docker Compose version v2.39.2
Docker Version 28.3.3-1
PostgreSQL 17

services:
  db:
    build:
      context: ./db
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
      - POSTGRES_DB=postgres
    volumes:
      - ./initdb:/docker-entrypoint-initdb.d
      - db-data:/var/lib/postgresql/data
      - ./postgresql.conf:/etc/postgresql/postgresql.conf
      - ./tests:/tests
      - ./logs:/var/lib/postgresql/data/log

volumes:
  db-data:

FROM postgres:17

RUN apt-get update && \
    apt-get install -y git make gcc libkrb5-dev postgresql-server-dev-17 curl

WORKDIR /tmp
RUN git clone https://github.com/EnterpriseDB/pldebugger

WORKDIR /tmp/pldebugger
# USE_PGXS=1 をつけること既存のPostgreSQLへの拡張インストールができる
RUN USE_PGXS=1 make 
RUN USE_PGXS=1 make install

# pg_proveのインストール
RUN curl -LO http://xrl.us/cpanm \
    && chmod +x cpanm \
    && ./cpanm TAP::Parser::SourceHandler::pgTAP

# pgTAPのインストール
WORKDIR /tmp
RUN git clone https://github.com/theory/pgtap.git

WORKDIR /tmp/pgtap
RUN make
RUN make install

CMD ["postgres", "-c", "config_file=/etc/postgresql/postgresql.conf"]

diff --git a/postgresql.conf b/postgresql.conf
index f50581b..1fe61c5 100644
--- a/postgresql.conf
+++ b/postgresql.conf
@@ -466,24 +466,24 @@ listen_addresses = '*'
 
 # - Where to Log -
 
-#log_destination = 'stderr'            # Valid values are combinations of
+log_destination = 'stderr'             # Valid values are combinations of
                                        # stderr, csvlog, jsonlog, syslog, and
                                        # eventlog, depending on platform.
                                        # csvlog and jsonlog require
                                        # logging_collector to be on.
 
 # This is used when logging to stderr:
-#logging_collector = off               # Enable capturing of stderr, jsonlog,
+logging_collector = on                 # Enable capturing of stderr, jsonlog,
                                        # and csvlog into log files. Required
                                        # to be on for csvlogs and jsonlogs.
                                        # (change requires restart)
 
 # These are only used if logging_collector is on:
-#log_directory = 'log'                 # directory where log files are written,
+log_directory = 'log'                  # directory where log files are written,
                                        # can be absolute or relative to PGDATA
-#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'       # log file name pattern,
+log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'        # log file name pattern,
                                                        # can include strftime() escapes
-#log_file_mode = 0600                  # creation mode for log files,
+log_file_mode = 0644                   # creation mode for log files,
                                        # begin with 0 to use octal notation
 #log_rotation_age = 1d                 # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
@@ -511,7 +511,7 @@ listen_addresses = '*'
 
 # - When to Log -
 
-#log_min_messages = warning            # values in order of decreasing detail:
+log_min_messages = info                        # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
@@ -525,7 +525,7 @@ listen_addresses = '*'
                                        #   fatal
                                        #   panic
 
-#log_min_error_statement = error       # values in order of decreasing detail:
+log_min_error_statement = error        # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
@@ -539,7 +539,7 @@ listen_addresses = '*'
                                        #   fatal
                                        #   panic (effectively off)
 
-#log_min_duration_statement = -1       # -1 is disabled, 0 logs all statements
+log_min_duration_statement = 0         # -1 is disabled, 0 logs all statements
                                        # and their durations, > 0 logs only
                                        # statements running at least this number
                                        # of milliseconds
@@ -550,7 +550,7 @@ listen_addresses = '*'
                                        # of milliseconds;
                                        # sample fraction is determined by log_statement_sample_rate
 
-#log_statement_sample_rate = 1.0       # fraction of logged statements exceeding
+log_statement_sample_rate = 1.0        # fraction of logged statements exceeding
                                        # log_min_duration_sample to be logged;
                                        # 1.0 logs all such statements, 0.0 never logs
 
@@ -576,12 +576,12 @@ listen_addresses = '*'
                                        # actions running at least this number
                                        # of milliseconds.
 #log_checkpoints = on
-#log_connections = off
-#log_disconnections = off
-#log_duration = off
+log_connections = on                   # log connections
+log_disconnections = on                        # log disconnections
+log_duration = on                      # log duration of completed statements
 #log_error_verbosity = default         # terse, default, or verbose messages
 #log_hostname = off
-#log_line_prefix = '%m [%p] '          # special values:
+log_line_prefix = '%m [%p] %u@%d %a '  # special values:
                                        #   %a = application name
                                        #   %u = user name
                                        #   %d = database name
@@ -614,7 +614,7 @@ listen_addresses = '*'
 #log_parameter_max_length_on_error = 0 # when logging an error, limit logged
                                        # bind-parameter values to N bytes;
                                        # -1 means print in full, 0 disables
-#log_statement = 'none'                        # none, ddl, mod, all
+log_statement = 'all'                  # none, ddl, mod, all
 #log_replication_commands = off
 #log_temp_files = -1                   # log temporary files equal or larger
                                        # than the specified size in kilobytes;
@@ -640,16 +640,17 @@ listen_addresses = '*'
 #track_io_timing = off
 #track_wal_io_timing = off
 #track_functions = none                        # none, pl, all
+track_functions = pl                   # Track PL/pgSQL function execution
 #stats_fetch_consistency = cache       # cache, none, snapshot
 
 
 # - Monitoring -
 
 #compute_query_id = auto
-#log_statement_stats = off
+log_statement_stats = on               # log statement execution statistics
 #log_parser_stats = off
 #log_planner_stats = off
-#log_executor_stats = off
+#log_executor_stats = off              # Cannot be enabled when log_statement_stats is on
 
 
 #------------------------------------------------------------------------------
@@ -691,7 +692,7 @@ listen_addresses = '*'
 
 # - Statement Behavior -
 
-#client_min_messages = notice          # values in order of decreasing detail:
+client_min_messages = notice           # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3

# コンテナを起動
docker-compose up -d

# ログファイルの確認
ls -la logs/

# リアルタイムでログを監視
tail -f logs/postgresql-$(date +%Y-%m-%d).log

# PostgreSQLに接続
docker compose exec db psql -U postgres -d postgres

# テーブル作成
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

# データ挿入
INSERT INTO users (name, email) VALUES 
('田中太郎', '[email protected]'),
('佐藤花子', '[email protected]');

# データ検索
SELECT * FROM users WHERE name LIKE '%田中%';

-- プロシージャの作成例
CREATE OR REPLACE PROCEDURE hello_world()
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE INFO 'Hello, World!';
END;  
$$;

-- プロシージャ実行
CALL hello_world();

-- エラーハンドリングテスト用プロシージャ
CREATE OR REPLACE PROCEDURE test_error_handling(
    IN p_test_type INTEGER,
    OUT p_result TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    RAISE NOTICE 'Starting error handling test with type: %', p_test_type;
    
    CASE p_test_type
        WHEN 1 THEN
            -- テーブル不存在エラー
            SELECT COUNT(*) FROM non_existent_table INTO p_result;
            
        WHEN 2 THEN
            -- 型変換エラー
            SELECT 'invalid_number'::INTEGER INTO p_result;
            
        WHEN 3 THEN
            -- カスタム例外
            RAISE EXCEPTION 'Custom error: Invalid input value %', p_test_type;
            
        WHEN 4 THEN
            -- ゼロ除算エラー
            SELECT (10 / 0)::TEXT INTO p_result;
            
        ELSE
            p_result := 'Success: No error occurred';
            RAISE NOTICE 'Test completed successfully';
    END CASE;
    
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Error caught in procedure: % %', SQLSTATE, SQLERRM;
        p_result := 'Error: ' || SQLERRM;
        -- エラーを再発生させる（ログに記録するため）
        RAISE;
END;
$$;

-- エラーハンドリングの動作確認
DO $$
DECLARE
    result_val TEXT;
BEGIN
    -- 正常ケース
    CALL test_error_handling(0, result_val);
    RAISE NOTICE 'Normal case result: %', result_val;
    
    -- エラーケース（テーブル不存在）
    BEGIN
        CALL test_error_handling(1, result_val);
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Caught error: %', SQLERRM;
    END;
    
    -- エラーケース（カスタム例外）
    BEGIN
        CALL test_error_handling(3, result_val);
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Caught custom error: %', SQLERRM;
    END;
END;
$$;

CALL test_error_handling(3, result_val);

# 開発用設定（postgresql.conf内）
log_statement = all
log_min_duration_statement = 0
log_line_prefix = '%m [%p] %u@%d %h '
log_connections = on
log_disconnections = on
log_duration = on
log_hostname = on
log_min_messages = info

# 本番用設定（100ms以上のクエリのみ）
log_statement = ddl
log_min_duration_statement = 100
log_line_prefix = '%m [%p] %u@%d '
log_connections = on
log_disconnections = on
log_checkpoints = on
log_lock_waits = on
log_min_messages = warning

# エラー調査用設定
log_statement = all
log_min_duration_statement = 0
log_min_error_statement = error
log_min_messages = info
log_error_verbosity = verbose
log_line_prefix = '%m [%p] %u@%d %h %e '
log_connections = on
log_disconnections = on

# ログローテーション設定（postgresql.conf内）
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_truncate_on_rotation = on

# よく実行されるクエリのパターンを確認
grep "statement:" logs/postgresql-*.log | \
  sed 's/.*statement: //' | \
  sort | uniq -c | sort -nr | head -10

# 実行時間が長いクエリを確認（例：1秒以上）
grep "duration:" logs/postgresql-*.log | \
  awk '$NF > 1000.0 {print}' | \
  sort -k7 -nr

# エラーログの確認
grep -i "error\|fatal\|panic" logs/postgresql-*.log