K8S环境下PostgreSQL仅10条数据却磁盘耗尽问题排查求助

Alright, let's dig into this tricky PostgreSQL issue you're facing—10GB filled in 5 days with only 10 records is definitely not normal, so let's break down the clues and fix this step by step.

First, Let's Analyze the Disk Usage Clues #

From your du output, two red flags jump out immediately:

• base/16384 is taking up 8.3G of space—this is almost certainly your users database (confirm with SELECT oid, datname FROM pg_database;). With only 10 records, this is way too large.
• pg_xlog (PostgreSQL 9.6's WAL directory) is at 961M, which is manageable but worth investigating since WAL files can pile up if something's misconfigured.

Troubleshooting Steps to Pinpoint the Root Cause #

1. Investigate the Oversized base/16384 Directory #

This is the biggest culprit. Let's drill down:

• SSH into your PostgreSQL pod and run du -h --max-depth=1 /var/lib/postgresql/data/base/16384 to see which files/folders are hogging space. It’s likely either a bloated table, a massive TOAST table (for large fields), or leftover temporary files.
• Run this query to find the largest objects in your database (note: this includes indexes and TOAST tables, which pg_relation_size doesn’t):

  SELECT 
    relname, 
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size
  FROM pg_stat_user_tables 
  ORDER BY pg_total_relation_size(relid) DESC;
  

• You mentioned an "abnormal record" in the users table. If that record has a huge field (like a large binary blob or unstructured text), it could drive TOAST table growth—but even one record shouldn’t hit 8G. More likely, repeated updates to that record are causing table bloat (dead tuples piling up because autovacuum isn’t cleaning them).

2. Check the pg_clog Error #

The error Could not write to file "pg_clog/0000" is a symptom of the disk being full, not the root cause. But we should confirm:

• Permissions on pg_clog are correct (run ls -ld /var/lib/postgresql/data/pg_clog to verify the postgres user owns it—official images should handle this by default).
• There’s no filesystem-level issue (like a read-only mount, which your K8s config doesn’t suggest).

3. Diagnose WAL (pg_xlog) Accumulation #

You have 61 WAL files (~960M total). Since you don’t use replication slots and all transactions are idle, these should be cleaned up after checkpoints. Let’s check:

• Run SELECT pg_last_checkpoint_time(); to see when the last checkpoint ran. If it’s been hours/days, that’s a problem—checkpoints should run automatically every 5 minutes (default) or when 32 WAL segments are filled.
• Check if archiving is enabled but failing: Run SELECT * FROM pg_stat_archiver;. If failed_count is non-zero, WAL files are piling up because PostgreSQL can’t archive them. If you don’t need archiving, ensure archive_mode = off in your postgresql.conf (it’s off by default, but double-check if your secret is overriding it).
• Verify wal_keep_segments: You have it commented out, so it uses the default of 32. That’s fine unless you had a disconnected replica, but you said no replication slots, so this shouldn’t be an issue.

4. Check Autovacuum Health #

Table bloat (dead tuples) is the most likely cause of the 8.3G base directory. Autovacuum should clean these up automatically—let’s confirm it’s working:

• Run SELECT * FROM pg_stat_activity WHERE query LIKE '%autovacuum%'; to see if autovacuum processes are running.
• Check dead tuple counts:

  SELECT 
    relname, 
    n_live_tup, 
    n_dead_tup,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
  FROM pg_stat_user_tables;
  

  If n_dead_tup is extremely high, autovacuum isn’t keeping up.
• Check if autovacuum is disabled for the users table:

  SELECT relname, reloptions FROM pg_class WHERE relname = 'users';
  

  Look for autovacuum_enabled=false in the reloptions column—if it’s there, that’s why dead tuples are piling up.

Step-by-Step Solutions #

1. Emergency Space Recovery #

First, free up space to get the database running normally:

• Trigger a manual checkpoint to clean up WAL files (run during low traffic, as it can block briefly):

  CHECKPOINT;
  

  After this, check pg_xlog—most old WAL files should be removed.
• Remove the abnormal record (if it’s the source of bloat):

  DELETE FROM users WHERE <your-abnormal-record-condition>;
  

• Run a manual VACUUM FULL to reclaim space (note: this locks the table, so do it during a maintenance window):

  VACUUM FULL users;
  

  VACUUM FULL rewrites the table to remove dead tuples, which will drastically reduce the size if bloat was the issue.

2. Fix the Root Cause #

• Enable Autovacuum if it’s disabled:
  If the users table has autovacuum_enabled=false, re-enable it with:

  ALTER TABLE users SET (autovacuum_enabled = true);
  

  Also check your postgresql.conf (or secret environment variables) to ensure autovacuum = on (default is on, but confirm no overrides).
• Fix WAL archiving (if needed):
  If you see failed archives in pg_stat_archiver, fix your archive command (e.g., ensure the archive destination is reachable). If you don’t need archiving, set archive_mode = off in postgresql.conf.
• Tune Autovacuum for your workload:
  If autovacuum is running but not keeping up, adjust parameters like autovacuum_vacuum_threshold (lower it to trigger more frequent vacuums) or autovacuum_vacuum_scale_factor in postgresql.conf.

3. Prevent Future Issues #

• Monitor disk usage: Set up alerts in your K8s cluster (e.g., Prometheus + Grafana) to notify you when disk usage hits 80%.
• Enable PostgreSQL logging: Turn on logging_collector = on and log_directory = 'pg_log' in postgresql.conf to capture autovacuum, checkpoint, and WAL activity—this will help diagnose issues faster next time.
• Regularly check for bloat: Schedule periodic queries to monitor table bloat and autovacuum health.
• Review your K8s config: Ensure your PVC has appropriate storage limits, and consider adding resource requests/limits for the PostgreSQL pod to prevent resource contention.

内容的提问来源于stack exchange，提问作者Sebastialonso