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

On Thu, Jan 10, 2019 at 02:58:21PM +1100, AP wrote:
> Hi,
> 
> On Wed, Jan 09, 2019 at 08:07:28AM -0800, Andres Freund wrote:
> > 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.
> > 
> > If you restart postgres, is the space freed?
> 
> No. :(
> 
> > 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.
> 
> I did a CHECKPOINT (it was fast) and it did not help. Then I did a restart
> and space is still used.

I tried it again (because I need to write data to the db ASAP) with other
tables that are actively being written to (the previous lot were "archived"
tables) and I noticed this in the logs:

ERROR: [082]: unable to push WAL segment '00000001000117BB00000001' asynchronously after 60 second(s)
2019-01-10 19:06:25.753 AEDT [21662] LOG:  archive command failed with exit code 82
2019-01-10 19:06:25.753 AEDT [21662] DETAIL:  The failed archive command was: pgbackrest --stanza=zonk archive-push
pg_wal/00000001000117BB00000001

Would this interfere and cause issues?

AP