public inbox for [email protected]
help / color / mirror / Atom feedError in DROP TABLESPACE
2+ messages / 2 participants
[nested] [flat]
* Error in DROP TABLESPACE
@ 2025-05-07 08:16 Fagnani Gabriele G (GDS I&TS) <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Fagnani Gabriele G (GDS I&TS) @ 2025-05-07 08:16 UTC (permalink / raw)
To: [email protected] <[email protected]>
INTERNAL
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Error in DROP TABLESPACE
@ 2025-05-07 08:27 Laurenz Albe <[email protected]>
parent: Fagnani Gabriele G (GDS I&TS) <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2025-05-07 08:27 UTC (permalink / raw)
To: Fagnani Gabriele G (GDS I&TS) <[email protected]>; [email protected] <[email protected]>
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@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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-05-07 08:27 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-05-07 08:16 Error in DROP TABLESPACE Fagnani Gabriele G (GDS I&TS) <[email protected]>
2025-05-07 08:27 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox