PostgreSQL「disk quota exceeded」の原因と対処

ERROR:  could not extend file "base/16384/2619": Disk quota exceeded
HINT:  Check free disk space.
DETAIL: Failed on block 123456 of relation base/16384/2619.
-- または
could not write to file "pg_wal/000000010000000A000000FF": Disk quota exceeded

-- PostgreSQL から主要パスを把握
SHOW data_directory;
SELECT spcname, pg_tablespace_location(oid) AS location FROM pg_tablespace;

-- OS からディスク/マウント確認
df -h
mount | egrep 'xfs|ext4|btrfs|zfs'

# クォータ対象と所有者を確認（postgres ユーザ前提）
id postgres
sudo -u postgres quota -s         # ユーザクォータ
sudo repquota -a                  # システム全体
sudo xfs_quota -x -c "report -h" /mountpoint  # XFS プロジェクト/ユーザ/グループ

-- データベース/テーブル/インデックスのサイズ上位
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database ORDER BY pg_database_size(datname) DESC;

SELECT schemaname||'.'||relname AS rel,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS tbl,
       pg_size_pretty(pg_indexes_size(relid)) AS idx
FROM pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 30;

-- テーブルスペース単位の使用量
SELECT spcname,
       pg_size_pretty(SUM(pg_total_relation_size(c.oid))) AS total
FROM pg_class c
JOIN pg_tablespace t ON c.reltablespace = t.oid OR (c.reltablespace=0 AND t.spcname='pg_default')
GROUP BY spcname ORDER BY SUM(pg_total_relation_size(c.oid)) DESC;

-- 実行中の長時間トランザクション
SELECT pid, usename, xact_start, now()-xact_start AS age, query
FROM pg_stat_activity WHERE xact_start IS NOT NULL
ORDER BY xact_start;

-- 一時ファイルログを有効化して発生源を掴む（要リロード）
ALTER SYSTEM SET log_temp_files = '10MB';
SELECT pg_reload_conf();
-- 以後、PostgreSQL ログの "temporary file" を確認

# ext4 のユーザ/グループクォータを編集（例：postgres を 500GB に）
sudo edquota -u postgres
# もしくは setquota（ソフト/ハードを MB 単位で設定）
sudo setquota -u postgres 0 524288000 0 0 /mountpoint

# XFS のプロジェクトクォータ（/etc/projects, /etc/projid を設定済みとする）
sudo xfs_quota -x -c 'report -h' /mountpoint
sudo xfs_quota -x -c 'limit -p bsoft=450g bhard=500g pgdata' /mountpoint

# 変更後、クォータの反映と確認
sudo quotaon -av
sudo repquota -a

# PostgreSQL ログの強制ローテート（ディストリによりパス調整）
sudo logrotate -f /etc/logrotate.conf

# 同一マウントに置いていた .dump/.gz/バックアップを外部へ退避
sudo du -sh /var/lib/postgresql/*.dump* 2>/dev/null
sudo mv /var/lib/postgresql/*.dump* /mnt/backup/

# 緊急用の「予約ファイル」を平時から用意 → 満杯時に削除して数 GB 確保
sudo fallocate -l 2G /var/lib/postgresql/.reserve
sudo rm -f /var/lib/postgresql/.reserve

-- 1) 長期トランザクションを解消（VACUUM/トランザクションID 再利用阻害の元）
-- 2) 使用されていないインデックスを洗い出し削除（pg_stat_user_indexes）
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan NULLS FIRST, relname;

-- 3) REINDEX/REINDEX CONCURRENTLY で断片化を縮小
REINDEX TABLE CONCURRENTLY my_schema.my_table;

-- 4) 大規模縮小は pg_repack やパーティション化再設計を検討
-- 5) 古いデータのアーカイブ/削除（業務要件に沿って）
DELETE FROM my_table WHERE created_at < now() - interval '365 days';

-- 別マウントにテーブルスペースを作成（空き/クォータ余裕のある場所）
CREATE TABLESPACE ts_large LOCATION '/mnt/large_pg/ts_large';

-- 重いテーブルとインデックスを移動（無停止で段階的に）
ALTER TABLE  my_schema.big_table  SET TABLESPACE ts_large;
ALTER INDEX  my_schema.big_table_idx SET TABLESPACE ts_large;

-- 新規オブジェクトのデフォルト先を切替（セッション単位/DB 単位で）
ALTER DATABASE mydb SET default_tablespace = 'ts_large';

-- ログ出力先の分離（別マウントへ）
ALTER SYSTEM SET log_directory = '/var/log/postgresql-db1';
SELECT pg_reload_conf();

-- 一時領域（temp_tablespaces）の分離
CREATE TABLESPACE ts_temp LOCATION '/mnt/fast_tmp/ts_temp';
ALTER SYSTEM SET temp_tablespaces = 'ts_temp';

-- WAL アーカイブ先も別ボリュームへ
ALTER SYSTEM SET archive_mode = 'on';
ALTER SYSTEM SET archive_command = 'rsync -a %p /mnt/pgarchive/%f';

#!/usr/bin/env bash
set -euo pipefail
echo "== Paths =="; sudo -u postgres psql -Atc "SHOW data_directory; SELECT spcname||':'||coalesce(pg_tablespace_location(oid),'[default]') FROM pg_tablespace;"
echo; echo "== Disk & Quota =="; df -h; df -i; sudo repquota -a || true
echo; echo "== XFS quota (if any) =="; mount | grep xfs && sudo xfs_quota -x -c "report -h" / || true
echo; echo "== Top dirs under PGDATA =="; PGDATA=$(sudo -u postgres psql -Atc "SHOW data_directory;"); du -xhd1 "$PGDATA" | sort -h

SELECT * FROM pg_stat_archiver;
SELECT slot_name, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;
-- 不要な非アクティブスロットは削除（要確認）
-- SELECT pg_drop_replication_slot('unused_slot');