22

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.

Hassan Baig
  • 2,079
  • 8
  • 31
  • 44

2 Answers2

19

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.

Craig Ringer
  • 57,821
  • 6
  • 162
  • 193
1

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!)

Vainstein K
  • 196
  • 3