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;

Answer 3

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

SELECT tablename from pg_tables WHERE tablespace = 'foo';

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

Answer 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.

Answer 6

I had this trouble recently, an I've needed add the host tag to dblink connection string to work. That is:

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 host=localhost',
                             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;

But the problem continued. I used the function to make sure that no objects in use were in the database server, and I forcibly removed the directory contents (rm -rf '[tablespace folder]/*')

Answer 7

Are we talking about the PgSQL Interface?

List schemas (tablespaces) like this:

\dn

List all the tables inside a schema (tablespace) like this:

\dn <table_space>.*

Use

\?

for more options