public inbox for [email protected]
help / color / mirror / Atom feedFrom: Laurenz Albe <[email protected]>
To: Fagnani Gabriele G (GDS I&TS) <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Error in DROP TABLESPACE
Date: Wed, 07 May 2025 10:27:47 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <PAXP191MB188589F40FA0F86D0DB9281DE288A@PAXP191MB1885.EURP191.PROD.OUTLOOK.COM>
References: <PAXP191MB188589F40FA0F86D0DB9281DE288A@PAXP191MB1885.EURP191.PROD.OUTLOOK.COM>
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
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Error in DROP TABLESPACE
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox