14

I have a Django web application with postgresql 9.3.10 backend (sitting in a Linux OS). I ran into disk full error, such that even if I try to truncate a table, I get errors of the sort:

ERROR:  could not extend file "base/30137/33186048": No space left on device
HINT:  Check free disk space.

I can't easily add more disk space to the server, nor can I delete stuff on this VM. However there are several tables that are candidates for truncation, but seems I can't truncate them now either.

Can anyone give me advice on what I can do here? This is hitting my production server hard, and I'm a bit of an accidental DBA here, so totally stumped.

|improve this question
  • You can reclaim some space if you can (temporarily) drop an index... truncate tables and then recreate it – joanolo Mar 18 '17 at 13:54
9

Because PostgreSQL must write WAL before making any changes to tables, it needs free disk space in order to delete things and release more disk space.

If you let the disk fill up, you can't recover from within PostgreSQL. Even TRUNCATE still has to write to WAL.

So you must free some space on the volume, or expand the volume. If your PostgreSQL log files are in pg_log in the data directory, you can safely remove some of those and restart Pg.

Do not delete pg_xlog or pg_clog. These are not server error logs, they're critical parts of the database, the transaction log and commit log.

|improve this answer
  • Why does TRUNCATE have to write to wal, and what does that entry in WAL look like? – Evan Carroll Mar 19 '17 at 1:17
  • "truncate does not log the full data, only the fact that a truncate happened. In order to be able to roll it back, the underlying file is kept around until transaction commit." [source](postgresql.org/message-id/… So truncate's entry in wall is super small. mere bytes. If that was the entirety of the space load needed, you could probably get it by rm -rf /tmp/* or deleting your vimrc. – Evan Carroll Mar 19 '17 at 1:20
  • @EvanCarroll rm -rf /tmp/* ... that can delete things you might want too, such as application sockets etc. Better to be more selective. As for space needed, you're right, it's minimal - an 8k empty table + a few kb of WAL entries for the truncate, xid allocation, commit record, etc. – Craig Ringer Mar 19 '17 at 5:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.