Re: Why can't I drop a tablespace?

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.

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.

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.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com