Re: BUG #15582: ALTER TABLE/INDEX ... SET TABLESPACE does not freedisk space

Hi,

On 2019-01-09 02:21:48 +0000, PG Bug reporting form wrote:
> I've added a tablespace recently to help deal with almost running out of
> disk space.
> 
> I then tried to use it with:
> 
> ALTER TABLE schema.table SET TABLESPACE hydro2_tmp;
> 
> This worked in so far as disk space was used on hydro2_tmp but nothing was
> ever freed in the default location.
> 
> I tried a VACUUM FULL but that did not help.
> 
> Then I tried moving the indexes belonging to the table but that also did not
> help.
> 
> Everywhere I've read and everyone I spoke to said that this is a move
> operation but it appears to be a copy so, unless I missed something, this
> appears to be a bug.
> 
> The two table spaces are on separate drives and postgres is from postgres'
> apt repository.

If you restart postgres, is the space freed?

I suspect the issue is that we don't properly close the old relation in
all backends that had it open, but it's hard to know for sure without
that.  If restarting isn't feasible, ensuring all backends older than
the move are ended, and issuing a CHECKPOINT; might also free the space
after a few seconds.

Greetings,

Andres Freund