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

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

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

Solution 3

In PG 10 and possibly a little earlier, this seems to have morphed to:

SELECT tablename from pg_tables WHERE tablespace = 'foo';

Solution 4

Unfortunately there is "global" view across all databases. However this can be done using the dblink extension together with the following function:

create or replace function show_tablespace_objects(p_tablespace text, p_user text, p_password text) 
  returns table (db_name text, schema_name text, object_name text, object_type text, tablespace_name text)
as
$func$
declare
  l_stmt text;
  l_con_name text := 'tbs_check_conn';
  l_con_string text;
  l_rec record;  
begin
  l_stmt := $query$SELECT current_database(), 
           n.nspname as schema_name, 
           c.relname as object_name,
           case c.relkind 
             when 'r' then 'table'
             when 'i' then 'index'
             when 't' then 'TOAST table'
             when 'm' then 'materialized view'
             when 'f' then 'foreign table'
             when 'p' then 'partitioned table'
             else c.relkind::text
           end as object_type,
           t.spcname as tablespace_name
    FROM pg_class c 
      JOIN pg_namespace n on n.oid = c.relnamespace
      JOIN pg_tablespace t ON c.reltablespace = t.oid$query$;

  if p_tablespace is not null then 
    l_stmt := l_stmt || format(' WHERE t.spcname=%L', p_tablespace);
  end if;

  for l_rec in (select * from pg_database where datallowconn) loop

     l_con_string := format('dbname=%L user=%L password=%L',
                             l_rec.datname, p_user, p_password);
     return query 
        select * 
        from dblink(l_con_string, l_stmt) 
             as t(db_name text, schema_name text, object_name text, object_type text, tablespace_name text);
  end loop;
end;
$func$
language plpgsql;

The function accepts a tablespace name and a username and password that is valid for all databases in the current server.

If the tablespace name is passed as null all objects that are not in the default tablespace are listed (that would be pg_global in a default installation without any additional tablespaces)

This can be used like this:

select *
from show_tablespace_objects('indexes_old', 'postgres', 'verysecretpassword');

Solution 5

A decade later, I had this problem, and one of the small comments above helped me find the solugion. select * from pg_tables where tablespace = 'my_tablespace'; only lists tables in the current database that use the tablespace. You have to cycle through each database you have trying that command to find one that uses that tablespace.

View more solutions

Comments

[Neoheurist]

I had this issue on Windows and it turned out to be that I had a database connection open.

[Andy Lester]

Also returns 0 rows. I'm beginning to think it's a problem with how pg_upgrade migrated me.

[Frank Heikens]

Then open the explorer (Windows) or command line to see what files are left in this tablespace. Use the oid's as input for your queries to make a match with PostgreSQL objects.

[Andy Lester]

I added the directory listing in the original question. One of the files is 10462602, which I assume is an OID? How can I tell where that's used?

[Frank Heikens]

Yes, that's an oid. There must be some database object (table, index, something else) using this number.

[araqnid]

it might be a relfilenode (column of pg_class) rather than oid if the table was rewritten at some point

[alfonx]

I am sorry, but I think you are wrong: tablespace and schema are two different things in postgresql.

[Alvin]

Postgres removed spclocation as of version 9.2

[Mike Sherrill 'Cat Recall']

@Alvin: Thanks. Updated the answer.

[Fake Name]

You seem to have some smart quotes in there.

[Fake Name]

Note that while this is handy, if misses some items. If you have a database in a tablespace, this query will not show them.

[Fake Name]

Additionally, if you're the local superuser, you can just leave the password field empty and it works.