How can I tell what is in a Postgresql tablespace?

Question

I've created a new tablespace called indexes, and I'm trying to remove the old tablespace indexes_old, which used to contain some tables and indexes. When I try to drop the tablespace, I get:

=> drop tablespace indexes_old; ERROR:  tablespace "indexes_old" is not empty 

But when I try to see what's in there, it seems that no tables live in that tablespace:

=> select * from pg_tables where tablespace = 'indexes_old'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers ------------+-----------+------------+------------+------------+----------+------------- (0 rows)  => select * from pg_indexes where tablespace = 'indexes_old'; schemaname | tablename | indexname | tablespace | indexdef ------------+-----------+-----------+------------+---------- (0 rows) 

So what is in that tablespace that is preventing me from dropping it?

In case it matters, I've just migrated from Pg 8.4 to Pg 9.0 using the pg_upgrade tool.

The tablespaces look like this:

    Name     |  Owner   |    Location     | Access privileges | Description  -------------+----------+-----------------+-------------------+-------------  indexes     | nobody   | /data/pgindex90 |                   |   indexes_old | nobody   | /data/pgindex84 |                   |  

and the contents of /data/pgindex84 include all the old 8.4 indexes, plus this new 9.0 index that pg_upgrade automatically created

# sudo ls -al /data/pgindex84/PG_9.0_201008051/11874 total 8280 drwx------ 2 postgres postgres    4096 Feb  9 14:58 . drwx------ 3 postgres postgres    4096 Feb 11 09:28 .. -rw------- 1 postgres postgres   40960 Feb  9 14:58 10462602 -rw------- 1 postgres postgres   40960 Feb  9 14:58 10462604 -rw------- 1 postgres postgres 4644864 Feb  9 14:58 10462614 -rw------- 1 postgres postgres 3727360 Feb  9 14:58 10462616 

Answer 1

Check pg_class to see what is located where:

SELECT    c.relname,    t.spcname  FROM    pg_class c      JOIN pg_tablespace t ON c.reltablespace = t.oid  WHERE    t.spcname = 'indexes_old'; 

Answer 2

In PostgreSQL, a tablespace can be used by any PostgreSQL database. (As long as the requesting user has sufficient privileges, that is.) I think this query

SELECT spcname, spclocation FROM pg_tablespace; 

will show you the directory that index_old is using in the filesystem in PostgreSQL version through 9.1. Prowl around in there to see if something real is in your way. I'd be really cautious about trying to delete anything in there apart from using PostgreSQL's interface, though.

In 9.2+, try

select spcname, pg_tablespace_location(oid) from pg_tablespace;