title: How can I tell what is in a Postgresql tablespace?
body: <p>I've created a new tablespace called <code>indexes</code>, and I'm trying to remove the old tablespace <code>indexes_old</code>, which used to contain some tables and indexes.  When I try to drop the tablespace, I get:</p>
  
  <pre><code>=&gt; drop tablespace indexes_old;
  ERROR:  tablespace "indexes_old" is not empty
  </code></pre>
  
  <p>But when I try to see what's in there, it seems that no tables live in that tablespace:</p>
  
  <pre><code>=&gt; select * from pg_tables where tablespace = 'indexes_old';
  schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
  ------------+-----------+------------+------------+------------+----------+-------------
  (0 rows)
  
  =&gt; select * from pg_indexes where tablespace = 'indexes_old';
  schemaname | tablename | indexname | tablespace | indexdef
  ------------+-----------+-----------+------------+----------
  (0 rows)
  </code></pre>
  
  <p>So what is in that tablespace that is preventing me from dropping it?</p>
  
  <p>In case it matters, I've just migrated from Pg 8.4 to Pg 9.0 using the pg_upgrade tool.</p>
  
  <p>The tablespaces look like this:</p>
  
  <pre><code>    Name     |  Owner   |    Location     | Access privileges | Description 
  -------------+----------+-----------------+-------------------+-------------
   indexes     | nobody   | /data/pgindex90 |                   | 
   indexes_old | nobody   | /data/pgindex84 |                   | 
  </code></pre>
  
  <p>and the contents of /data/pgindex84 include all the old 8.4 indexes, plus this new 9.0 index that pg_upgrade automatically created</p>
  
  <pre><code># 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
  </code></pre>