How do you list all items from all databases located in a specific tablespace in postgresql? 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.
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.