Last 10 Minutes Before Database Disk Full: Emergency Response Protocol for PostgreSQL & MySQL

Minute 0: Declare Incident and Freeze High-Risk Changes

Designate an operator and a recorder. Freeze releases, DDL, batch jobs, and storage migrations. Record alert time, primary/replica roles, database versions, nodes, mount points, current business error rate, and actions taken.

On the host, use read-only commands to confirm time, filesystem, inodes, block devices, and mount relationships:

date --iso-8601=seconds
hostname -f
df -hT
df -ih
findmnt -o TARGET,SOURCE,FSTYPE,OPTIONS
lsblk -o NAME,TYPE,FSTYPE,SIZE,FSAVAIL,MOUNTPOINTS
# PostgreSQL: actual process, directory, and role
pid=$(pgrep -xo postgres)
ps -p "$pid" -o pid,lstart,cmd
sudo -u postgres psql -XAtqc "show data_directory; show hba_file; show config_file;"
sudo -u postgres psql -XAtqc "select pg_walfile_name(pg_current_wal_lsn()), pg_is_in_recovery();"
# MySQL: actual directory, binlog, and role
mysql --batch --skip-column-names -e "SELECT @@version, @@hostname, @@datadir, @@tmpdir, @@log_bin, @@log_bin_basename, @@read_only, @@super_read_only;"

Data block exhaustion and inode exhaustion are different failures. Databases commonly exhaust blocks with large files; massive temporary files or log small files may exhaust inodes first. The first six commands confirm which layer the alert hits; the next two groups identify PostgreSQL and MySQL actual directories and roles — do not guess from default paths. MySQL uses local controlled login; avoid putting passwords in command history or process lists. If database connection itself times out, preserve host evidence and infer role from existing monitoring; avoid repeated expensive queries under high load.

Minute 1: Calculate "How Long Can We Last?"

Percentages alone cannot rank risk. A 10 TB volume at 2% still has 200 GB; a 100 GB volume at 5% has only 5 GB. Need remaining bytes and growth rate. The script below samples the specified mount point every 10 seconds, calculates interval consumption speed and rough ETA; it does not scan directories, so overhead is low.

#!/usr/bin/env bash
set -euo pipefail
mountpoint=${1:?usage: $0 MOUNTPOINT [INTERVAL]}
interval=${2:-10}
read -r prev_used prev_avail < <(df -B1 --output=used,avail "$mountpoint" | awk 'NR==2')
while sleep "$interval"; do
  read -r used avail < <(df -B1 --output=used,avail "$mountpoint" | awk 'NR==2')
  delta=$((used-prev_used))
  if (( delta > 0 )); then
    rate=$((delta/interval))
    eta=$((avail/rate))
    printf '%(%F %T)T used=%d avail=%d growth_Bps=%d eta_seconds=%d
' -1 "$used" "$avail" "$rate" "$eta"
  else
    printf '%(%F %T)T used=%d avail=%d growth_Bps=%d eta_seconds=unknown
' -1 "$used" "$avail" "$((delta/interval))"
  fi
  prev_used=$used
done

Two samples cannot predict bursts; ETA is only for action priority. Combine with the last 5–15 minutes monitoring slope and business events. If remaining time is less than any storage expansion process, immediately throttle writes or remove the growth source instead of running a full du scan.

With Prometheus node_exporter, estimate seconds by dividing available bytes by the 10-minute growth rate. Adjust metric labels per environment.

node_filesystem_avail_bytes{instance="db01",mountpoint="/var/lib/postgresql"}
/
clamp_min(
  -deriv(node_filesystem_avail_bytes{instance="db01",mountpoint="/var/lib/postgresql"}[10m]),
  1
)

Also check if filesystem is read-only, I/O latency, and kernel errors. Disk failures causing write errors are not ordinary capacity events.

findmnt -no TARGET,SOURCE,FSTYPE,OPTIONS /var/lib/postgresql
iostat -xz 1 5
dmesg --ctime | tail -n 100 | grep -Ei 'I/O error|read-only|filesystem|ext4|xfs|nvme|blk'

If I/O errors, filesystem remounted read-only, or device timeouts appear, stop capacity-cleanup thinking; follow storage failure procedures to protect instance and replicas. Forced writes or filesystem repair may enlarge damage.

Minute 2: Identify Growth Category

Prioritize narrow, same-filesystem statistics.

sudo ionice -c3 nice -n 19 du -xhd1 /var/lib/postgresql 2>/dev/null | sort -h
sudo ionice -c3 nice -n 19 du -xhd1 /var/lib/mysql 2>/dev/null | sort -h
sudo find /var/lib/postgresql -xdev -type f -mmin -10 -size +100M \
  -printf '%T@ %s %p
' 2>/dev/null | sort -nr | head -n 30
dbpid=$(pgrep -xo postgres || pgrep -xo mysqld)
sudo lsof -nP -p "$dbpid" 2>/dev/null | awk '$4 ~ /[0-9]+[uw]/ {print}' | tail -n 100
sudo lsof -nP +L1 2>/dev/null | sort -k7,7nr | head -n 30

The first three commands find growth sources by directory and recently modified large files; the last three confirm which objects the process is writing. Three common growth categories: business data/indexes; transaction logs (PostgreSQL pg_wal, MySQL binlog/relay log); temporary files, database logs, exports, or backups. +L1 also finds deleted but still-open logs. Never manually delete files in the data directory you don't understand, and never truncate database data files via /proc/PID/fd.

If a plain text log was deleted but remains open, use the database's log rotation/reopen mechanism; do not restart the database just to close a log FD unless no safer path exists. PostgreSQL can run SELECT pg_rotate_logfile(); when logging collector is configured; the return value indicates success.

MySQL's FLUSH LOGS closes and reopens multiple logs and rotates binlog, potentially causing I/O and replication impact. If only error log is needed, use a narrower command per version and verify first; do not treat it as risk-free cleanup.

SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'log_output';
FLUSH ERROR LOGS;

FLUSH ERROR LOGS

support depends on MySQL version; confirm with current version's official manual or test environment before executing. If unsupported, the command errors; do not fall back to deleting the actively written file.

Minute 3: Determine If Transaction Logs Are "Pinned"

PostgreSQL: Why WAL Cannot Be Recycled

pg_wal

growth is not solved by deleting old segments. Common causes: archive failure, replication slots not consuming, replica lag, long-running backups, or high write volume. First examine archiver, replication slots, and streaming replication status.

SELECT now(), archived_count, failed_count, last_archived_wal,
       last_failed_wal, last_failed_time
FROM pg_stat_archiver;

SELECT slot_name, slot_type, active, restart_lsn,
       wal_status, safe_wal_size,
       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 NULLS LAST;

SELECT application_name, client_addr, state, sync_state,
       sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS byte_lag,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication
ORDER BY application_name;

The first two queries check archiver and replication slots; the third checks sender-side replica lag. Columns wal_status and safe_wal_size exist only in newer PostgreSQL versions; if they error, query available columns for that version — do not interpret a column error as slot absence.

If archiving fails, fix the archive target's capacity, permissions, network, or command first — do not disable archive_mode. Check database logs for archive command failed and test the target in shell without overwriting existing archives.

sudo -u postgres psql -XAtqc 'show archive_mode; show archive_command; show archive_timeout;'
journalctl -u postgresql --since '-15 min' --no-pager |
  grep -Ei 'archive|WAL|no space|could not write' | tail -n 100

Dropping a replication slot is an irreversible high-risk action: the consumer may never resume from the required LSN, requiring a fresh base backup. Before execution, confirm slot owner, consumer permanently offline, and have a usable backup and rebuild plan. First do impact assessment only.

SELECT slot_name, active, active_pid, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

-- Only after approval, consumer permanently offline, and recovery plan documented:
SELECT * FROM pg_replication_slots WHERE slot_name = 'retired_consumer_slot';
SELECT pg_drop_replication_slot('retired_consumer_slot');
CHECKPOINT;

The first three lines are for impact assessment; subsequent deletion applies only to confirmed abandoned logical slots, must ensure active=false, record plugin, database, and LSN, and avoid batch loops. CHECKPOINT may generate significant I/O and does not guarantee immediate WAL removal; only segments no longer required by archiver, slots, backups, and recovery can be recycled. Rollback is not "recreate same-named slot" because the original restart LSN is lost; rollback plan is to reinitialize subscription per consumer type or restore from a new snapshot.

MySQL: Binlog, Relay Log, and Large Transactions

First check binlog list, current file, expiration config, replication status. MySQL 8's replication status tables are more automation-friendly than legacy SHOW SLAVE STATUS, but fields vary by version.

SHOW BINARY LOGS;
SHOW MASTER STATUS;
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';
SHOW VARIABLES LIKE 'expire_logs_days';

-- Run on replica
SHOW REPLICA STATUS\G
SELECT CHANNEL_NAME, SERVICE_STATE, LAST_ERROR_NUMBER, LAST_ERROR_MESSAGE
FROM performance_schema.replication_applier_status_by_worker
WHERE LAST_ERROR_NUMBER <> 0;

The first four run on the primary to view binlog and expiration policy; the commented statements run on replicas to confirm receive/apply positions and errors. Older versions use SHOW SLAVE STATUS\G. Never delete mysql-bin.* files directly to free primary space; this breaks binlog index and replication/PITR. Must use SQL PURGE BINARY LOGS after confirming all replicas have read, and backup/PITR retention allows.

First record each replica's Source_Log_File / Master_Log_File or performance_schema receive position, identify the slowest replica. Then choose a time/file strictly earlier than all consumers' required positions. Example (not actual values):

PURGE BINARY LOGS TO 'mysql-bin.001234';
SHOW BINARY LOGS;

This command deletes binlogs before the target file; cannot be rolled back. Recovery relies only on existing backups or archived copies. If PURGE fails because disk is full, prioritize expansion or write throttling; do not manually unlink.

Check for uncommitted large transactions and temporary table growth. Long transactions create undo/history versions and may block cleanup.

SELECT trx_id, trx_mysql_thread_id, trx_started, trx_state,
       trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

SELECT PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST,
       PROCESSLIST_TIME, PROCESSLIST_STATE, PROCESSLIST_INFO
FROM performance_schema.threads
WHERE PROCESSLIST_COMMAND <> 'Sleep'
ORDER BY PROCESSLIST_TIME DESC
LIMIT 30;

Killing a transaction triggers rollback, which may generate more I/O, hold locks longer, and may not free space in the last few minutes. Only kill sessions that are clearly retryable, have a confirmed growth source, and have business owner approval. Use precise KILL QUERY <id> or KILL CONNECTION <id>; do not batch-kill all long transactions.

Minute 4: Throttle Writes or Expand First?

If ETA is less than expansion completion time, reduce write rate first. Prefer pausing replayable batch jobs, async consumers, reports, imports, and non-critical write APIs at the business entry point; retain read traffic and core transactions. Database-level forced read-only is a last resort because applications may retry failures into larger request storms.

In Kubernetes, first confirm target CronJob/Deployment and current replicas, then dry-run before pausing. Pausing a CronJob does not stop already-running Jobs.

kubectl -n batch get cronjob nightly-import -o wide
kubectl -n batch patch cronjob nightly-import --type=merge \
  -p '{"spec":{"suspend":true}}' --dry-run=server -o yaml
kubectl -n batch patch cronjob nightly-import --type=merge \
  -p '{"spec":{"suspend":true}}'
kubectl -n batch get jobs --sort-by=.metadata.creationTimestamp | tail
kubectl -n shop get deploy order-writer -o jsonpath='{.spec.replicas}{"
"}'
kubectl -n shop scale deploy/order-writer --replicas=0 --dry-run=server
kubectl -n shop scale deploy/order-writer --replicas=0
kubectl -n shop rollout status deploy/order-writer --timeout=2m

The first five commands pause new batch jobs but do not stop running Jobs; rollback by setting the same object's suspend back to false. The next four record and pause consumers; prefer scaling to 0 over deleting Deployment; rollback restores recorded replica count. Both recoveries must first confirm growth rate, message retention, and catch-up throughput to avoid refilling disk during catch-up.

Rollback restores recorded replica count and monitors queue lag, database TPS, and disk growth. If HPA manages the Deployment, direct scaling will be reverted by HPA; adjust HPA min/max or pause upstream producers via change process first.

Database-level read-only affects all write transactions. PostgreSQL can set default read-only for new sessions, but superusers, existing transactions, and certain operations may still write; the setting is not full isolation.

ALTER SYSTEM SET default_transaction_read_only = on;
SELECT pg_reload_conf();
SHOW default_transaction_read_only;

Save original SHOW value before execution and confirm if application retries. Rollback:

ALTER SYSTEM RESET default_transaction_read_only; SELECT pg_reload_conf();

. A safer write throttle for primaries is usually done at the entry or proxy layer with business-tiered controls.

MySQL's super_read_only blocks more privileged sessions, but replication thread semantics vary by version. Use only after confirming topology and failure handling plan.

SELECT @@global.read_only, @@global.super_read_only;
SET GLOBAL super_read_only = ON;
SELECT @@global.read_only, @@global.super_read_only;

Rollback restores recorded original values. Behavior of existing transactions after setting variables must be confirmed per version; application retries also need monitoring. Do not change replication node read-only status outside of a proper failover process.

Minutes 5–7: Obtain Safe Buffer

Online Filesystem Expansion

Expansion is usually safer than deleting database files, but must confirm hierarchy: cloud volume/physical disk, partition, LVM PV/LV, filesystem. Do read-only checks and snapshot/backup status confirmation first. Snapshots may have performance overhead and may not complete in time when disk is near full.

mountpoint=/var/lib/postgresql
source=$(findmnt -n -o SOURCE "$mountpoint")
fstype=$(findmnt -n -o FSTYPE "$mountpoint")
printf 'source=%s fstype=%s
' "$source" "$fstype"
lsblk -f
sudo pvs; sudo vgs; sudo lvs -a -o+devices,data_percent,metadata_percent

# Example: /dev/nvme1n1 expanded by cloud platform from 500G to 800G, PV uses whole disk
sudo blockdev --getsize64 /dev/nvme1n1
sudo pvresize --test /dev/nvme1n1
sudo pvresize /dev/nvme1n1
sudo lvextend --test -L +100G /dev/vg_db/lv_data
sudo lvextend -L +100G /dev/vg_db/lv_data
sudo lvs -o lv_name,lv_size,vg_name /dev/vg_db/lv_data

# Only run the command matching the actual filesystem type
case "$fstype" in
  xfs)  sudo xfs_growfs "$mountpoint" ;;
  ext4) sudo resize2fs "$source" ;;
  *)    echo "unsupported filesystem: $fstype" >&2; exit 1 ;;
esac

df -hT /var/lib/postgresql

This continuous flow first verifies mount, block device, and LVM, then assumes cloud volume already expanded on platform side and device capacity refreshed, finally expands per actual filesystem type. Device names and the added 100G must be validated via capacity planning; do not copy blindly. pvresize --test and lvextend --test are dry-runs; the case branch prevents using XFS commands on ext4 or vice versa. LVM metadata changes are usually not simply shrinkable; have storage snapshots or recoverable backups before execution.

Online expansion typically cannot be shrunk online; rollback strategy is not "shrink back to original size" but stop further operations, retain added capacity, and restore from backup/snapshot to a new volume. If any layer's size hasn't changed, stop and re-verify; do not trial-and-error on similar device names.

Move Non-Database Large Files Off Data Disk

If the same mount point contains confirmed migratable diagnostic packages, old exports, installation packages, or verified backup copies, move them to another filesystem with capacity. mv across filesystems is effectively copy-then-delete; large file copies within ten minutes may worsen I/O, and interruption leaves two copies.

First list candidates without touching PGDATA, InnoDB, WAL/binlog, socket, and lock files.

sudo find /var/lib/postgresql -xdev -type f \( -name '*.core' -o -name '*.tar' -o -name '*.sql.gz' \) \
  -size +500M -printf '%s %TY-%Tm-%Td %p
' 2>/dev/null |
  sort -nr | numfmt --field=1 --to=iec

# Below path must be replaced with a confirmed inactive export file
src=/var/lib/postgresql/exports/old-export.sql.gz
dst=/mnt/emergency-offload/
findmnt -T "$src"
findmnt -T "$dst"
sudo rsync -aHAX --info=progress2 "$src" "$dst"
sudo sha256sum "$src" "$dst/$(basename "$src")"
sudo rm -- "$src"
df -hT /var/lib/postgresql /mnt/emergency-offload

The first command group only lists candidates; the second group migrates one owner-confirmed inactive export file, using rsync to preserve source until verification completes. Source and target must be on different devices. The final delete is irreversible; execute only after checksum consistency, target persistence, and data owner confirmation. If target is network storage, also verify write completion and subsequent readability.

Release systemd Journal or Package Cache

Only valuable if they share the same full filesystem as the database. First check usage and retention requirements; audit logs must not be cleaned arbitrarily.

journalctl --disk-usage
sudo du -sh /var/cache/apt /var/cache/dnf 2>/dev/null
findmnt -T /var/log/journal
findmnt -T /var/lib/postgresql
sudo journalctl --rotate
sudo journalctl --vacuum-size=1G
journalctl --disk-usage
df -h /

The first four confirm usage and whether journal shares filesystem with database; only after confirming same disk and meeting audit retention, run the controlled vacuum commands — not direct journal file deletion. If the database is on a dedicated data disk, this frees zero space on the data disk; do not perform unrelated cleanup during an incident.

Minute 7: Identify Terminable Growth Transactions

Only terminate a session if evidence shows a retryable task is generating massive writes and stopping it has lower rollback cost than continuing to fill. Cancel query first; terminate connection only if necessary; database rollback itself needs WAL/undo and disk space.

PostgreSQL: view active write transactions and wait states. SQL text may contain sensitive values; limit output and access.

SELECT pid, usename, application_name, client_addr,
       xact_start, query_start, state, wait_event_type, wait_event,
       left(query, 300) AS query
FROM pg_stat_activity
WHERE datname = current_database()
AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST;

-- After confirming PID, business source, and retryability:
SELECT pg_cancel_backend(12345);
SELECT pid, state, wait_event_type, wait_event
FROM pg_stat_activity WHERE pid = 12345;

-- Only if approved and cancel ineffective:
SELECT pg_terminate_backend(12345);

The first read-only query locks the session; first use pg_cancel_backend(pid) to cancel current statement, confirm application can retry. Only if cancel fails and owner approves, use pg_terminate_backend(pid), which disconnects the session and rolls back the transaction. These operations cannot "restore the original transaction"; rollback is performed by the application per idempotent semantics. Must record transaction source and business batch to avoid duplicate data.

MySQL: first view threads and transactions, then choose KILL QUERY; do not batch kill by username.

SELECT p.ID, p.USER, p.HOST, p.DB, p.TIME, p.STATE,
       LEFT(p.INFO, 300) AS INFO, t.trx_started, t.trx_rows_modified
FROM information_schema.PROCESSLIST AS p
LEFT JOIN information_schema.innodb_trx AS t
ON t.trx_mysql_thread_id = p.ID
WHERE p.COMMAND <> 'Sleep'
ORDER BY p.TIME DESC;

-- After confirming thread, first cancel statement; only if needed disconnect entire session
KILL QUERY 12345;
-- Only when need to disconnect and rollback entire session:
KILL CONNECTION 12345;

Execute precise IDs only for confirmed, retryable threads; do not batch kill by username. Verify if disk growth stopped, locks released, and application not forming retry storm. If growth comes from normal core transactions, killing individual sessions is meaningless; apply rate limiting, expand, or switch write strategy.

Minute 8: Failover Is Not a Cleanup Button

Switching to a replica is only considered when the replica has sufficient space, replication lag is acceptable, log retention chain is intact, application connections can switch, and the failover process has been rehearsed. If growth is caused by business writes, the new primary will continue growing; if the old primary's WAL/binlog is pinned by slots or replicas, failover may complicate topology.

PostgreSQL pre-failover read-only checks: recovery status, receive and replay positions, time lag, and disk.

SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(),
       pg_last_xact_replay_timestamp(),
       now() - pg_last_xact_replay_timestamp() AS replay_delay;

df -hT /var/lib/postgresql
sudo -u postgres pg_controldata "$(sudo -u postgres psql -XAtqc 'show data_directory')" |
  grep -E 'Database cluster state|Latest checkpoint|REDO location'

MySQL replica first runs SHOW REPLICA STATUS\G to verify IO/SQL threads, Seconds_Behind_Source, positions, and errors, then runs

SELECT @@global.gtid_executed, @@global.read_only, @@global.super_read_only;

to record GTID and role. Seconds_Behind_Source=0 is not absolute proof of zero data loss; combine with GTID/position and semi-sync status.

A real promote/failover changes write authority, may cause split-brain and data loss, and must use existing orchestrators and fencing processes (Patroni, Orchestrator, cloud RDS, etc.). Do not give a single promote command in a generic article for manual copy-paste. Before execution, at minimum confirm old primary isolation, RPO, client routing, failback strategy, and backups; after execution, verify single primary, replication rebuild, business read/write, and old primary fencing.

Minute 9: Absolute "Quick Cleanup" Don'ts

Do not perform the following actions: rm -f pg_wal/*, rm mysql-bin.*, delete relay log, redo, undo, ibdata, table files;

Delete data directory files by mtime when file purpose is unknown;

Run truncate on database FDs, or empty actively used data/log files;

Force VACUUM FULL, OPTIMIZE TABLE, or rebuild large indexes when disk is full; these usually need extra temporary space and generate massive WAL/binlog;

Delete backups, archives, or snapshots still depended on by the recovery chain;

Reboot the whole machine hoping "cache clears and space appears"; restart may prevent instance startup due to inability to create PID, temporary, or recovery files;

Dual-primary switch without fencing;

Treat 100% filesystem usage as all writes already failed, continue stress testing or generate diagnostic packages.

PostgreSQL table bloat is not immediately returned to the filesystem by ordinary VACUUM; VACUUM FULL rewrites the table and takes an exclusive lock, requiring extra space — not a last-ten-minutes action. Read-only check of dead tuple trends:

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

MySQL OPTIMIZE TABLE for InnoDB may rebuild the table, similarly needing space and locks/metadata locks. First check table sizes; leave for post-incident planning.

SELECT table_schema, table_name,
       ROUND((data_length+index_length)/1024/1024/1024,2) AS total_gib,
       ROUND(data_free/1024/1024/1024,2) AS data_free_gib
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','performance_schema','information_schema','sys')
ORDER BY data_length+index_length DESC
LIMIT 20;

Minute 10: Verify Database Remains Consistent and Usable

After gaining space, do not immediately declare recovery. Observe continuously for 10–30 minutes: available space and growth rate, database write errors, transaction commit/rollback, replication lag, archiving, connections, business error rate, backup chain. First confirm filesystem is writable again and has a safety buffer.

df -hT /var/lib/postgresql /var/lib/mysql 2>/dev/null
df -ih /var/lib/postgresql /var/lib/mysql 2>/dev/null
findmnt -no TARGET,SOURCE,FSTYPE,OPTIONS /var/lib/postgresql 2>/dev/null
dmesg --ctime | tail -n 100
journalctl -u postgresql --since '-15 min' --no-pager |
  grep -Ei 'no space|PANIC|FATAL|could not (write|extend|fsync)|checkpoint|archive' | tail -n 200
sudo -u postgres psql -X -c 'select * from pg_stat_bgwriter;'

PostgreSQL lightweight health and write path verification. Do not create unapproved business tables in production; use existing health-check transactions or run an approved temporary table test in a dedicated ops schema with automatic rollback.

SELECT now(), pg_is_in_recovery(), current_database();
BEGIN;
CREATE TEMP TABLE disk_incident_probe(id integer);
INSERT INTO disk_incident_probe VALUES (1);
SELECT count(*) FROM disk_incident_probe;
ROLLBACK;

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;

Temporary tables may write to temp_tablespaces or the data directory, verifying basic write path; but do not execute when disk remains extremely tight. Subsequent queries verify that archiver and replication slot retention are decreasing. Newer PostgreSQL checkpoint metrics are gradually moving to pg_stat_checkpointer; query per version.

MySQL lightweight transaction verification using TEMPORARY TABLE to avoid polluting business schema persistent structures.

SELECT NOW(), @@read_only, @@super_read_only;
START TRANSACTION;
CREATE TEMPORARY TABLE disk_incident_probe(id INT) ENGINE=InnoDB;
INSERT INTO disk_incident_probe VALUES (1);
SELECT COUNT(*) FROM disk_incident_probe;
ROLLBACK;

Check error log, InnoDB status, replication, and binlog list.

mysql --batch --skip-column-names -e 'SHOW ENGINE INNODB STATUS\G' |
  grep -Ei -A3 -B3 'error|disk|file|log|checkpoint' | head -n 100
mysql -e 'SHOW BINARY LOGS; SHOW REPLICA STATUS\G'

SHOW REPLICA STATUS

returns empty on primary or when no replica configured — not a command failure. Monitor application-side write success rate and latency; confirm throttling/pauses are not immediately reverted by other automation.

A Ten-Minute Read-Only Collection Script

The script below does not clean, expand, or restart; it only collects host capacity, growth, directory first-level, I/O, deleted FDs, and service log summaries. Output directory must be on another filesystem with space; otherwise the diagnostic package itself may crush the disk.

#!/usr/bin/env bash
set -euo pipefail
mountpoint=${1:?usage: $0 MOUNTPOINT OUTPUT_DIR}
out=${2:?usage: $0 MOUNTPOINT OUTPUT_DIR}
mkdir -p "$out"
test "$(findmnt -n -o SOURCE "$mountpoint")" != "$(findmnt -n -o SOURCE "$out")" || {
  echo 'output directory must be on a different filesystem' >&2
  exit 1
}

date --iso-8601=seconds > "$out/time.txt"
df -hT > "$out/df-h.txt"
df -ih > "$out/df-i.txt"
findmnt -o TARGET,SOURCE,FSTYPE,OPTIONS > "$out/findmnt.txt"
lsblk -o NAME,TYPE,FSTYPE,SIZE,FSAVAIL,MOUNTPOINTS > "$out/lsblk.txt"
timeout 30 nice -n 19 ionice -c3 du -xhd1 "$mountpoint" > "$out/du-level1.txt" 2>&1 || true
iostat -xz 1 5 > "$out/iostat.txt" 2>&1 || true
sudo lsof -nP +L1 > "$out/deleted-open.txt" 2>&1 || true
journalctl --since '-15 min' -p warning --no-pager > "$out/journal-warning.txt" 2>&1 || true

for i in $(seq 1 30); do
  printf '%(%F %T)T ' -1 >> "$out/space-series.txt"
  df -B1 --output=used,avail,pcent "$mountpoint" | awk 'NR==2' >> "$out/space-series.txt"
  sleep 2
done

The script may still add load via du and lsof; timeout limits du. Under extreme I/O failure, skip them. Output may contain paths, process arguments, and logs; treat as sensitive data.

Post-Incident Fix: Prevent "Last Ten Minutes" Recurrence

Address root causes only after recovery. Capacity monitoring should not rely on a single static 90% threshold; alert on remaining bytes, growth rate ETA, inodes, thin pool data/metadata, WAL/binlog archive failures, replication slot retention, replica lag, temporary files, long transactions, and backup failures. Large volumes alert too late with percentages; small volumes may be too noisy.

PostgreSQL can monitor individual replication slot retained bytes and set appropriate max_slot_wal_keep_size (confirm version and acceptable failure behavior). Exceeding the limit may force consumer rebuild; it is not free protection.

max by (instance, slot_name) (
  pg_replication_slots_pg_wal_lsn_diff
) > 20 * 1024 * 1024 * 1024

# Archive failure increment
increase(pg_stat_archiver_failed_count[10m]) > 0

Metric names depend on postgres_exporter query configuration; first confirm existence in Prometheus. The second alerts on archive failure increment, avoiding permanent alert after cumulative failures exceed zero.

MySQL: monitor binlog total size, expiration policy, slowest replica position, and relay log growth. Periodically run

SELECT @@binlog_expire_logs_seconds, @@max_binlog_size, @@sync_binlog;

inside the database to verify policy, but avoid high-frequency re-querying. Expiration time must cover PITR, backup cycle, and longest replica outage.

Give batch jobs write budgets, batched commits, pause switches, and idempotent replay; limit single transaction size to avoid massive rollback on cancellation. Place data directory, transaction logs, temporary directory, and ordinary logs on independently alertable and expandable filesystems, reducing the chance of one log flood taking down the database.

Regularly rehearse online expansion and write-throttle rollback: verify cloud volume quotas, device refresh, LVM and filesystem commands; verify entry rate limiting, consumer pause, CronJob suspend; verify replica promote and old primary fencing; verify backups are truly restorable. Rehearsal docs must record each step's duration; otherwise "expandable" may be meaningless in a ten-minute window.

Finally, establish action priority: read-only confirmation and throttling first; rehearsed online expansion usually beats cleanup; only clean explicitly non-database files or use database-native commands to clean no-longer-needed logs; consider rollback amplification when terminating transactions; database failover requires full HA process; manual deletion of transaction logs and data files is never an emergency shortcut.

The ten minutes before disk fills tests change discipline. The correct answer is rarely a "magic command" but an evidence-driven chain: calculate ETA, identify growth type, freeze unnecessary writes, gain verifiable safe buffer, protect replication and recovery chains, then continuously verify. As long as operations remain auditable, gradual, and rollback-able, choices exist; once you manually delete recovery-critical data files, no amount of remaining space can restore consistency.