CODEWITHSUNDEEP
postgresqlstoragedigital-oceanvacuum
Edmond
Edmond

Reputation: 69

PostgreSQL: how to clean up full disk space after unsuccessful ALTER TABLE?

I have an instance of PostgreSQL on DigitalOcean. A part of the tables are stored in tablespaces on a separate machine (block volume).

Around 90% of the block volume were full when I tried altering some fields in Django. My command was unable to succeed due to the small free disk space available. As soon as the process was killed, I saw that my block volume is 100% full.

The question is, how to figure out what tables have consumed the 10% of space that had been available. I believe those are some system tables or temporary tables that Postgres created during the failed migration, and I can see the raw files created during the last day via ftp.

I need to do something to clean up the space to the previous level.

Upvotes: 1

Views: 8061

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656784

I tried altering some fields ... My command was unable to succeed

Postgres writes new row versions when updating anything. When the transaction succeeds (commits), those become visible and the old row versions are dead as soon as no older transaction is still running.

If your transaction fails (as it obviously did), the transaction is rolled back, and those new row versions themselves become dead tuples.

That's the nature of PostgreSQL's MVCC model. Use VACUUM to free up disk space occupied by dead tuples. If your failed operation mostly occupies data pages at the physical end of the file holding your table, plain VACUUM might be able to free up some or all of the that space. (Which would be fortunate, as VACUUM is less invasive and much cheaper than VACUUM FULL.) Else you need VACUUM FULL:

VACUUM FULL tablename;

Takes an exclusive lock, and also may take some time for big tables!

If autovacuum is running (as it should and is per default), chances are that some of the space is freed up after some time automatically.

VACUUM FULL rewrites the whole table, so it needs plenty of free space as "wiggle room". You obviously don't have enough, so read the chapter "Disk full" in this related answer:

TABLESPACE?

One more option. You mentioned:

tablespaces on a separate machine

If there is enough free space on a different TABLESPACE, you can (temporarily) change the tablespace for the table, which results in migrating the table to the different storage location. ALTER TABLE can do that:

This form changes the table's tablespace to the specified tablespace and moves the data file(s) associated with the table to the new tablespace. Indexes on the table, if any, are not moved; but they can be moved separately with additional SET TABLESPACE commands.

So:

ALTER TABLE name SET TABLESPACE new_tablespace;

The name of the default tablespace is pg_default by default (sic!). If in doubt, read the chapter "Tablespaces" in the manual.

pg_dump / restore

If all else fails, a dump-restore cycle can save you, as that implicitly removes all dead tuples and rewrites the table in pristine condition.

Upvotes: 4

Related Questions