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
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';