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

On Fri, Jan 11, 2019 at 10:03:46AM +1100, AP wrote:
> On Thu, Jan 10, 2019 at 07:22:00PM +1100, AP wrote:
> > > > 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
>
> So I restarted this ALTER to the new tablespace and let it complete. This time
> it freed disk space on the source (the above errors kept happening, though).
>
> The original 5.5TB, though, is still duplicated and now I've a DB bigger than
> the original storage can contain. :(

Thanks for your help on IRC.

Just to tidy up here: it was PEBCAK.

I left the old 10 db dir lying around after a linked pg_upgrade to 11. :( Realised
what was going on after Andres asked the magic question.

I now have 12TB free on the old tablespace, which makes sense.

Apologies for the hassle to one and all.

AP