0

I'm trying to clean out some old tablespaces in my postgres instance, and I can't seem to figure out how to list everything in a specific tablespace.

SELECT * from pg_tables WHERE tablespace ='cold_storage' returns items from the current database in the tablespace, but short of iterating over every database and querying within that database for the tablespace contents, I haven't figured out how to do this.

pg_tables seems to be db-specific, whereas (I think) tablespaces are global for a postgres instance.

1

pg_tables seems to be db-specific, whereas (I think) tablespaces are global for a postgres instance.

That's true. But there is no way of knowing what's inside of a tablespace without connecting to the specific databases and checking their catalogs. Iterating is the only option. Think of it like a glorified virtual storage system.

You may consider running pg_dumpall --schema-only and trying to parse that output if you're interested.

3
  • If tablespaces were also db-specific, that'd make sense, but they're not. – Fake Name Apr 27 '18 at 6:12
  • @FakeName consider this, making and removing a tablespace requires you to be a database superuser. That means you can connect to every database anyway. I'm not saying what you want is impossible to do, only that no one has done it. noting keeps a Leger of what is in a tablespace in remote databases. – I Support The Boycott Apr 27 '18 at 6:19
  • That is extremely annoying. There is a global Leger of the size of all the table spaces (see pg_catalog.pg_tablespace_size(oid)). As it is, it just makes maintenance a pain. – Fake Name Apr 27 '18 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.