18

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
1
  • You can reclaim some space if you can (temporarily) drop an index... truncate tables and then recreate it
    – joanolo
    Mar 18, 2017 at 13:54

2 Answers 2

Reset to default
13

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
3
  • Why does TRUNCATE have to write to wal, and what does that entry in WAL look like?
    – Evan Carroll
    Mar 19, 2017 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, 2017 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, 2017 at 5:02
0

A few more ideas on freeing up disk space in an emergency:

  • If possible, prevent new client connections from being made.
  • If possible, cancel non-critical running commands (SELECT, COPY IN, COPY OUT, CREATE INDEX, ...): not guaranteed to help, but may in some cases.
    1. Use Pg_Cancel_Backend(<PID>) if you already have a privileged session open, or just kill -s INT -- <PID> otherwise.
  • If possible, disconnect non-critical sessions, whether idle or active; again, not guaranteed to help, but may in some cases.
    1. Use Pg_Terminate_Backend(<PID>) if you already have a privileged session open, or just kill -s TERM -- <PID> otherwise.
  • If using pg_prewarm module:
    1. Run SET pg_prewarm.autoprewarm_interval = 0, if you already have a privileged session open.
    2. rm -f <PGDATA>/autoprewarm.blocks
  • If using pg_stat_statements module, and have a privileged session open:
    1. Run SELECT Pg_Stat_Statements_Reset().
  • If have a privileged session open:
    1. Run SELECT Pg_Stat_Reset().
  • If possible, drop indexes on user data which are big enough to matter, and can definitely be rebuilt from just the table rows; start with the largest.

(Just to be clear: these are all temporary measures, not long-term or even short-term solutions!)

Improve this answer

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.