Error in DROP TABLESPACE - Mailing list pgsql-general

Hi,

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@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?

Thanks

Gabriele Fagnani