0

Current database takes almost all of SSD free space ~802GB. 800 GB of memory is taken by single table and it was never vacuumed since 2019. Recently i deleted old rows from the table and now i need to run VACUUM FULL to reclaim disk space back to OS. The problem is that there is no space left on SSD. I can connect external SSD (840 GB) but i don't know how to run VACUUM FULL and make it use external disk space.

postgesql 9.5 Ubuntu 16.04

Improve this question
1

1 Answer 1

Reset to default
1

You cannot do that with VACUUM (FULL), but you can use pg_dump to export the table to the second disk, drop the table and restore the dump.

If that is not feasible, for example because of foreign key constraints, you could

  1. create a tablespace on the second disk

  2. use ALTER TABLE to move the table to that tablespace

  3. run VACUUM (FULL) on the table

  4. move the table back with ALTER TABLE

  5. remove the tablespace

But this is more cumbersome.

Improve this answer
2
  • as far as i know, vacuum FULL using the same disk where table is stored. So i guess there is no point to change tablespace, cause both disks are only slightly bigger than the table. Also can't DROP table due to foreign key constraints.
    – Tamer Mukayev
    Aug 19, 2022 at 8:19
  • 4
    If you have no resources at all, life is difficult. Then you'll have to drop and re-create the foreign keys on top of dump/restore.
    – Laurenz Albe
    Aug 19, 2022 at 8:37

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.