我怎么知道Postgresql表空间是什么？ [英] How can I tell what is in a Postgresql tablespace?

问题描述

我创建了一个名为 indexes 的新表空间，并且尝试删除旧的表空间 indexes_old ，其中曾经包含一些表和索引。当我尝试删除表空间时，我得到：

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?

如果很重要，我刚刚使用pg_upgrade从Pg 8.4迁移到Pg 9.0

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

表空间如下所示：

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

和/ data / pgindex84的内容包括所有旧的8.4索引，以及这个新的9.0索引， pg_upgrade自动创建

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

推荐答案

检查pg_class以查看其位置：

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

这篇关于我怎么知道Postgresql表空间是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！