PostgreSQL「no space left on device」の原因と対処

# ディスクと inode の空き
df -h
df -i

# どのディレクトリが増えているか（PGDATA 直下）
du -xhd1 "$PGDATA" | sort -h

# WAL/ログ/テンポラリの代表格
du -sh "$PGDATA/pg_wal" 2>/dev/null
du -sch "$PGDATA"/log/* 2>/dev/null | tail -1
find "$PGDATA" -type d -name "pgsql_tmp" -print -exec du -sh {} \;

# 最近巨大化したファイルの特定（24 時間以内に 100MB 超）
find "$PGDATA" -xdev -type f -size +100M -mtime -1 -ls

-- WAL とアーカイバの状況
SELECT * FROM pg_stat_archiver;
SELECT slot_name, plugin, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

-- 長時間トランザクション（VACUUM/不要WAL保持の元凶）
SELECT pid, usename, state, xact_start, now()-xact_start AS xact_age, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

-- データ/指数の肥大状況
SELECT relkind, schemaname||'.'||relname AS rel,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS table,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

-- 設定の重要どころ
SELECT name, setting, unit
FROM pg_settings
WHERE name IN ('data_directory','log_directory','temp_tablespaces',
               'wal_keep_size','max_wal_size','archive_mode','archive_command',
               'work_mem','maintenance_work_mem','log_temp_files');

# logrotate を手動起動（ディストリによりコマンドは異なる）
sudo logrotate -f /etc/logrotate.conf

# 非常用 1GB 予約ファイル（平常時に作成しておく）
sudo fallocate -l 1G /var/lib/postgresql/.reserve

# ディスクが詰まったら即削除
sudo rm -f /var/lib/postgresql/.reserve

-- 使っていないレプリケーションスロットを削除（要確認）
SELECT slot_name, active FROM pg_replication_slots WHERE active = false;
SELECT pg_drop_replication_slot('unused_slot_name');

-- wal_keep_size を縮小（再起動/リロードが必要な場合あり）
ALTER SYSTEM SET wal_keep_size = '256MB';  -- 例
SELECT pg_reload_conf();

-- アーカイブ失敗が続くならアーカイブ先の空き/到達性を回復
SELECT * FROM pg_stat_archiver;  -- failed_count, last_failed_wal を確認

-- 10MB 超のテンポラリ発生をログへ
ALTER SYSTEM SET log_temp_files = '10MB';
SELECT pg_reload_conf();

-- 一時ディレクトリ/テーブルスペースの使用量確認
du -sh "$PGDATA"/base/*/pgsql_tmp 2>/dev/null
-- 可能なら temp_tablespaces を別ディスクに
ALTER SYSTEM SET temp_tablespaces = 'ts_temp';  -- 事前に作成したテーブルスペース名

-- 全データベースを並列バキューム（要メンテ時間）
vacuumdb -j4 -d postgres -U postgres -v --all

-- 断片化がひどい索引を再構成
REINDEX TABLE CONCURRENTLY my_schema.my_table;

-- 代表的なサイズ可視化
SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 30;

-- ログの出力先を変更（ディレクトリは事前作成・権限調整）
ALTER SYSTEM SET log_directory = '/var/log/postgresql-db1';
SELECT pg_reload_conf();

-- WAL アーカイブ先も別ボリューム/オブジェクトストレージへ
ALTER SYSTEM SET archive_mode = 'on';
ALTER SYSTEM SET archive_command = 'rsync -a %p backuphost:/pgarchive/%f';

# 1) PostgreSQL 停止
sudo systemctl stop postgresql

# 2) 新マウントへ移動
sudo rsync -a --delete "$PGDATA/pg_wal/" /mnt/big/pg_wal/

# 3) 元を退避しシンボリックリンク作成
sudo mv "$PGDATA/pg_wal" "$PGDATA/pg_wal.bak"
sudo ln -s /mnt/big/pg_wal "$PGDATA/pg_wal"

# 4) パーミッション確認後、起動
sudo chown -h postgres:postgres "$PGDATA/pg_wal"
sudo systemctl start postgresql

#!/usr/bin/env bash
set -e
echo "== Disk blocks & inodes =="
df -h; echo; df -i
echo; echo "== Top dirs under PGDATA =="; du -xhd1 "$PGDATA" | sort -h
echo; echo "== pg_wal size =="; du -sh "$PGDATA/pg_wal" 2>/dev/null || true
echo; echo "== log dir size =="; du -sh "$PGDATA"/log 2>/dev/null || true
echo; echo "== temp dirs =="; find "$PGDATA" -type d -name pgsql_tmp -exec du -sh {} \; 2>/dev/null

WITH slots AS (
  SELECT slot_name, active, restart_lsn
  FROM pg_replication_slots
),
prog AS (
  SELECT pg_current_wal_lsn() AS cur
)
SELECT s.slot_name, s.active,
       pg_size_pretty(pg_wal_lsn_diff(p.cur, s.restart_lsn)) AS retained_from_slot
FROM slots s CROSS JOIN prog p
ORDER BY pg_wal_lsn_diff(p.cur, s.restart_lsn) DESC;

-- 10MB 超のテンポラリをログする（恒久化は postgresql.conf へ）
ALTER SYSTEM SET log_temp_files = '10MB';
SELECT pg_reload_conf();

-- 以降、ログ（csvlog）から "temporary file" を grep
grep "temporary file" /var/log/postgresql/*.csv | sort | tail -n 50