Laurenz Albe wrote:
On Fri, 2021-07-09 at 20:04 +0100, Phil Endecott wrote:
=# create tablespace tempspace location "/db_temp";
=# alter table requests set tablespace tempspace;
That didn't work; I think disk space had actually reached zero:
PANIC: could not write to file "pg_wal/xlogtemp.19369": No space left on device
STATEMENT: alter table requests set tablespace tempspace;
So I shut down the database and resolved the problem in a more conventional
way by resizing the filesystem. It is now (apparently) functioning normally.
BUT: I am unable to drop the tablespace that I created:
=# drop tablespace tempspace;
ERROR: tablespace "tempspace" is not empty
On inspection /db_temp does contain a few GB of data that looks
consistent with my table "requests" that I had tried to move.
postgres:/db_temp/PG_11_201809051$ ls -l 17829/
total 2894972
-rw------- 1 postgres postgres 32137216 Jul 8 18:35 486095
-rw------- 1 postgres postgres 37240832 Jul 8 18:57 494286
-rw------- 1 postgres postgres 1073741824 Jul 8 19:02 502478
-rw------- 1 postgres postgres 1073741824 Jul 8 19:03 502478.1
-rw------- 1 postgres postgres 747577344 Jul 8 19:03 502478.2
I can't find what is using it:
These files don't get cleaned up after a crash, so they may well be
leftovers you can remove.
Thanks Laurenz. I was looking at the source for "alter table set
tablespace" yesterday trying to work out what is supposed to happen.
There is a comment at tablecmds.c line 3989: "Thanks to the magic of
MVCC, an error anywhere along the way rolls back the whole operation;
we don't have to do anything special to clean up." But I guess that
creating an entirely new file on a different filesystem is an
exception to that.
Use \dt+ and \di+ to determine if the table or any of its indexes
actually resides in the new tablespace. Don't forget the TOAST table.
"select distinct(reltablespace) from pg_class" gives only pg_global and
null. Is that including TOAST tables etc.?
If they are all still in the original tablespace as they should be
on account of the transactional guarantees, go ahead and manually
remove the files.
My plan is to wait for a couple of days to see if there are any other
opinions here, and then do that.
Thanks again,
Phil.
