Re: Error in DROP TABLESPACE

On Wed, 2025-05-07 at 08:16 +0000, Fagnani Gabriele G (GDS I&TS) wrote:
> INTERNAL

?

> I've run into an odd problem - I have what seems to be a "zombie" tablespace
> that PostgreSQL won't let me drop, but nothing inside it is active.
>  
> drop tablespace ts_idx_wb2;
> ERROR:  tablespace "ts_idx_wb2" is not empty
>  
> postgres=# \db+ ts_idx_wb2
>                                                List of tablespaces
>     Name    |  Owner   |             Location             |  Access privileges  | Options |  Size  | Description
> ------------+----------+----------------------------------+---------------------+---------+--------+-------------
> ts_idx_wb2 | postgres | /pgsql/postgres/tsdata1/ts_idx_wb2 | postgres=C/postgres+|         | 250 GB |
>             |          |                                  | mydatabase=C/postgres |         |        |
> (1 row)
>  
> Check on filesystem location.
> On the filesystem there are files related only to fileref 515555188:
>  
> postgres(at)myserver:/pgsql/postgres/tsdata1/ts_idx_wb2/PG_12_201909212/16419> ls -la
> total 261854080
> drwx------ 2 postgres postgres      69632 Apr 22 17:45 .
> drwx------ 3 postgres postgres       4096 Oct 31  2024 ..
> -rw------- 1 postgres postgres 1073741824 Oct 31  2024 515555188
> -rw------- 1 postgres postgres 1073741824 Oct 31  2024 515555188.1
> -rw------- 1 postgres postgres 1073741824 Oct 31  2024 515555188.10
> -rw------- 1 postgres postgres 1073741824 Oct 31  2024 515555188.100
> -rw------- 1 postgres postgres 1073741824 Oct 31  2024 515555188.101
> -rw------- 1 postgres postgres 1073741824 Oct 31  2024 515555188.102
> -rw------- 1 postgres postgres 1073741824 Oct 31  2024 515555188.103
> -rw------- 1 postgres postgres 1073741824 Oct 31  2024 515555188.104
> ...
> ...
> -rw------- 1 postgres postgres 1073741824 Oct 31  2024 515555188.97
> -rw------- 1 postgres postgres 1073741824 Oct 31  2024 515555188.98
> -rw------- 1 postgres postgres 1073741824 Oct 31  2024 515555188.99
>  
> SELECT pg_tablespace_databases((SELECT oid FROM pg_tablespace WHERE spcname = 'ts_idx_wb2'));
> pg_tablespace_databases
> -------------------------
>                    16419
>  
>  
> SELECT oid, datname FROM pg_database WHERE oid = 16419;
> oid  |  datname
> -------+-----------
> 16419 | mydatabase    <----------------
>  
>  
> Connected to database mydatabase:
>  
> select cl.relfilenode, nsp.nspname as schema_name, cl.relname, cl.relkind
> from pg_class cl
>   join pg_namespace nsp on cl.relnamespace = nsp.oid and cl.relfilenode=515555188 ;
> relfilenode | schema_name | relname | relkind
> -------------+-------------+---------+---------
> (0 rows)
>  
> To be sure I run the same query on the other databases with the same result (0 rows)
>  
> So there are no actual references to these files, yet the relationship
> persists sufficiently for the DROP TABLESPACE to error out.
> My guess is that the files belong to an index being moved on Oct 31 to ts_idx_wb2 tablespace, and during the move the
> postgres instance crashed due space shortage on wal filesystem, and this crash left this "zombie" files on the filesystem.
>  
> In order to drop the tablespace, is it safe to shut  that postmaster down and manually remove the contents of this
> directory?

Your diagnosis seems to be right, and I agree with your proposed remedy.

Yours,
Laurenz Albe