Re: BUG #15582: ALTER TABLE/INDEX ... SET TABLESPACE does not free disk 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