Thread: Unable To Drop Tablespace

On Fri, Feb 5, 2021 at 12:43 PM Ian Lawrence Barwick <barwick@gmail.com> wrote:
> 2021年2月5日(金) 3:52 Pavan Pusuluri <pavan.pusuluri@gmail.com>:
>> We are trying to drop a table space on RDS Postgres . We have removed the objects etc, but it still won't drop.
>>
>> I have checked and there's no reference anywhere to this tablespace but it complains it's not empty.
>>
>> I checked if it is a default for a database, revoked all privileges on the tablespace.
>>
>> We dropped the database but underpinning tablespace remained but when I query to see if any reference i get no hits.
>>
>> "Select c.relname,t.spcname from pg_class c JOIN pg_tablespace t ON c.reltablespace=t.oid where
t.spcname='mytablespace'
>>
>> I dont find any objects referencing. Kindly let me know if anything else needs to be checked?
>
>
> There's a handy function "pg_tablespace_databases()" to check which databases
> might still have objects in a database. There are a couple of useful queries demonstrating
> usage here:
>
>   https://pgpedia.info/p/pg_tablespace_databases.html

It's also possible for there to be stray files in there, in some crash
scenarios where PostgreSQL doesn't currently clean up relation files
that it ideally should.  The one with the widest window AFAIK is where
you crash after creating a table but before committing[1].  You'd need
a directory listing to investigate that.

[1] https://www.postgresql.org/message-id/flat/CAEepm%3D0ULqYgM2aFeOnrx6YrtBg3xUdxALoyCG%2BXpssKqmezug%40mail.gmail.com