0

I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link (-k) option.

So basically I am having two data directories i.e. One is old data directory (v9.5) and the current one in running state (v12.4).

Yesterday I have dropped two tables of size 700GB and 300GB.

After connecting to postgres using psql utility I can see database size whose tables was dropped got decreased (with \l+ ) but what is making me worry is that only a few ~50GBs have been freed from storage partition.

I have run vacuumdb only on that database but no luck. I have checked if any deleted open file is there on OS level using lsof but there is none.

Note : Still I have not deleted old data directory (v9.5). I am not sure if deleting will impact my running postgres 12 as I have used 'link' option in pg_upgrade.

Looking for the solution.

2
  • If you drop the table, the files it used are gone. vacuumdb will only release space inside files of existing tables to it's no surprise it didn't change anything. I would highly suspect the way you measure things is somewhat incorrect.
    – user1822
    Nov 25, 2020 at 6:32
  • yes vacuuming will not do any good if tables are deleted. Vacuuming was done on database just to make ensure if something could be helpful. I can’t figure out what is happening here.
    – erTugRul
    Nov 25, 2020 at 7:18

1 Answer 1

0

You can use TRUNCATE first before dropping and that'll automatically reclaim the space for you. See more information in this StackOverflow: PostgreSQL drop table command is not reducing DB size

Though this shouldn't have to be your process normally with dropping tables, it might help clue you in on why the space isn't being reclaimed.

7
  • I have done that though. However, as I know dropping a table doesn't need to be vacuum the database. As soon as the table is dropped the size equivalent to the dropped table size is claimed. But not happening here.
    – erTugRul
    Nov 25, 2020 at 3:07
  • Have you tried truncating the same table first to see if the space is reclaimed?
    – J.D.
    Nov 25, 2020 at 3:09
  • No. I didn't truncate. Direct dropped it. I have been doing this (dropping) fine before upgrade on v9.5 but not able to do on it.
    – erTugRul
    Nov 25, 2020 at 3:12
  • Gotcha, I'm just curious if you truncated first would you see the space reclaimed or not (as this would be another clue into the issue)?
    – J.D.
    Nov 25, 2020 at 3:24
  • "You would need to execute a Vacuum command to reclaim the space after dropping the tables" that is plain wrong. You can't vacuum a table that is no longer there. Dropping the table will release the space occupied by the table. Truncating before dropping will also not change anything
    – user1822
    Nov 25, 2020 at 6:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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