Using PostgreSQL 9.3. I had a long-running query updating around 8M rows. Something obviously went wrong. 2 days later I got a low disk space alert. I stopped the query and this is what I saw.
root@server:/var/lib/postgresql/9.3/main# du -BM * | sort -n
1M global
1M pg_notify
1M pg_serial
1M pg_snapshots
1M pg_stat
1M pg_stat_tmp
1M pg_tblspc
1M pg_twophase
1M PG_VERSION
1M pg_xlog/archive_status
1M mydb.opts
1M mydb.pid
3M pg_subtrans
7M base/1
7M base/12030
7M base/12035
11M base/22029472
21M pg_clog
72M pg_multixact/offsets
315M pg_log
444M pg_multixact/members
516M pg_multixact
625M pg_xlog
3493M base/pgsql_tmp
61851M base/22053373
65372M base
Note this: 61851M base/22053373
Since my actual data is in tablespaces stored in different volumes, I presume this is some temporary transaction stuff that has piled up.
There are some online posts about similar problems, but no canonical solution that I found. In general, the advice is "run VACUUM FULL" and sometimes the accumulated cruft goes away. That's what I'm doing now, but it's taking its time and I'm afraid I might fill up the remaining disk space (3GB) and bring everything crashing down.
Anyone had experience with this? What is stored here? Is there a safe way to quickly free up this space? Or at least move it elsewhere (I have plenty of space on my tablespace disks).