Thread: BUG #15582: ALTER TABLE/INDEX ... SET TABLESPACE does not free diskspace

The following bug has been logged on the website:

Bug reference:      15582
Logged by:          Andrew P
Email address:      ap@zip.com.au
PostgreSQL version: 11.1
Operating system:   Linux/Debian stretch
Description:

Hi,

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.

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

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.

AP

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

Hi
If archiver can not save WAL - postgresql will not remove these WAL and pg_wal directory will grow unlimited. And i
thinkyour alter table set tablespace works ok and table was removed from original disk. But was fully copied to WAL and
suchWAL can not be deleted, therefore overall disk space usage is same. So you need repair you archive_command first.
 

regards, Sergei

On Thu, Jan 10, 2019 at 12:44:58PM +0300, Sergei Kornilov wrote:
> Hi
> If archiver can not save WAL - postgresql will not remove these WAL and pg_wal directory will grow unlimited. And i
thinkyour alter table set tablespace works ok and table was removed from original disk. But was fully copied to WAL and
suchWAL can not be deleted, therefore overall disk space usage is same. So you need repair you archive_command first.
 

Hi,

I just checked. There are only 16GB of WAL in pg_wal. I moved
5.5TB of data so I don't think that that is it.

There are also no errors when I'm not trying to shift an object
to a new tablespace and WAL backups are happening (recent files
in the pgbackrest backup dir).

AP

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. :(

AP

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