Unable To Drop Tablespace

2021年2月5日(金) 3:52 Pavan Pusuluri <pavan.pusuluri@gmail.com>:

Hi there

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

Regards

Ian Barwick

--
EnterpriseDB: https://www.enterprisedb.com

Thank you very much Ian. Will check it out.

Regards

On Thu, Feb 4, 2021, 5:43 PM Ian Lawrence Barwick <barwick@gmail.com> wrote:

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]/messages/by-id/CAEepm=0ULqYgM2aFeOnrx6YrtBg3xUdxALoyCG+XpssKqmezug@mail.gmail.com. You'd need
a directory listing to investigate that.

[1]: /messages/by-id/CAEepm=0ULqYgM2aFeOnrx6YrtBg3xUdxALoyCG+XpssKqmezug@mail.gmail.com