a month ago
My Postgres volume (postgres-volume-25QK) shows 83% capacity used (~20.75GB of 25GB), but when I query the database directly:
SELECT pg_size_pretty(pg_database_size('railway')) = 2745 MB
SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir() = 992 MB
Total accounted for: ~3.7GB
Unexplained gap: ~17GB
Can you help identify what is consuming the remaining ~17GB on the volume? I want to understand if this is backup snapshots, system files, or something else before considering an upgrade.
Plan: Hobby
Project: heroic-fulfillment
Add'l info: Update: Attempted fstrim -v /var/lib/postgresql/data from the Railway Postgres console but got FITRIM ioctl failed: Operation not permitted — so the fix needs to be applied at the infrastructure level by Railway.
2 Replies
a month ago
This thread has been opened as a bounty so the community can help solve it.
Status changed to Open Railway • 27 days ago
a month ago
Run these inside the Postgres service shell:
df -hT /var/lib/postgresql/data
du -xhd1 /var/lib/postgresql/data | sort -h
du -xhd1 /var/lib/postgresql/data/* 2>/dev/null | sort -h | tail -50
and show the results
19 days ago
Ran the three commands inside the Postgres service shell as requested:
df -hT /var/lib/postgresql/data
Filesystem Type Size Used Avail Use% Mounted on
/dev/zd27168 ext4 4.6G 3.8G 788M 83% /var/lib/postgresql/data
du -xhd1 /var/lib/postgresql/data | sort -h
16K /var/lib/postgresql/data/lost+found
40K /var/lib/postgresql/data/certs
3.8G /var/lib/postgresql/data
3.8G /var/lib/postgresql/data/pgdata
du -xhd1 /var/lib/postgresql/data/* 2>/dev/null | sort -h | tail -50
4.0K pgdata/pg_commit_ts
4.0K pgdata/pg_dynshmem
4.0K pgdata/pg_notify
4.0K pgdata/pg_replslot
4.0K pgdata/pg_serial
4.0K pgdata/pg_snapshots
4.0K pgdata/pg_stat
4.0K pgdata/pg_tblspc
4.0K pgdata/pg_twophase
12K pgdata/pg_xact
16K lost+found
16K pgdata/pg_logical
28K pgdata/pg_multixact
28K pgdata/pg_subtrans
40K certs
556K pgdata/global
14M pgdata/pg_stat_tmp
1.1G pgdata/pg_wal
2.8G pgdata/base
3.8G pgdata
This actually clarifies the original question rather than confirming a 17GB mystery: df shows the underlying filesystem itself is only 4.6G total, not 25G. So the "83% used" / "20.75GB of 25GB" figure I quoted originally was apparently reading the plan's allocated/billable volume size (25GB) somewhere in the dashboard, while the actual mounted filesystem Postgres is writing to is 4.6GB. du confirms this directly — the whole pgdata directory is 3.8G (base 2.8G of real table data, pg_wal 1.1G, everything else negligible), which fully accounts for the "Used" column. There's no unexplained gap once measured against the real 4.6G filesystem.
So the actual situation: I have a real, small database (2.8GB) sitting on a 4.6GB filesystem with only 788MB free, not a 25GB volume with 17GB unaccounted for. Given fstrim is blocked at the container level (confirmed earlier — FITRIM ioctl failed: Operation not permitted), I think what I actually need is for the underlying volume to be resized larger at the infrastructure level, since there's nothing further I can reclaim or diagnose from inside the container. Is a volume resize something that can be done on this Hobby plan, or does that require an upgrade tier? Want to fix this before it fills completely and starts failing writes.