在数据库运维工作中，经常会有数据目录使用率较高需要调整的情况，通常会给数据库建立多个表空间，并分别位于不同的盘上，这时需要做的工作就是调整库中现有表和索引的表空间，下面简单总结下这块维护工作的内容，以下都是基于 PostgreSQL 9.0.1 做的测试。

查询表的表空间

PostgreSQL 提供类似” “命令很方便得到相关信息，命令如下：

1
2
3
4
5
6
7
8
9
10
11

skytf=> /d test_2  
 Table "skytf.test_2"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer |  
obj_id | integer | not null  
name | character varying(64) |  
Indexes:  
 "idx_hash_name" hash (name)  
 "idx_test_2" btree (id, obj_id)  
Tablespace: "tbs_skytf_idx"

备注：如果这个表的表空间为当前数据库的默认表空间，那么上面则不会显示 Tablespace 信息，相反，则会显示这张有的表空间，例如上面的表 test_2 的表空间为 tbs_skytf_idx，而表空间 “tbs_skytf_idx” 不是数据库 skytf 的默认表空间， 那么如何查询数据库的默认表空间呢，可以通过以下命令查询。

查询数据库的默认表空间

1
2
3
4
5
6
7
8
9
10
11

skytf=> select datname,dattablespace from pg_database where datname='skytf';  
datname | dattablespace  
---------+---------------  
skytf | 14203070  
(1 row)

skytf=> select oid,spcname from pg_tablespace where oid=14203070;  
 oid | spcname  
----------+-----------  
14203070 | tbs_skytf  
(1 row)

备注：通过以上查出数据库 skytf 的默认表空间为 tbs_skytf。

批量查询表和索引的表空间

查询表和索引所在的表空间

1
2
3
4
5

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), tb.spcname  
 from pg_class a, pg_tablespace tb  
where a.reltablespace = tb.oid  
 and a.relkind in ('r', 'i')  
order by a.relpages desc;

备注：上面只取了部分结果，这个查询能够查询表和索引所处的表空间，但是有一点需要注意，这个查询仅显示表空间不是数据库默认表空间的数据库对像，而我们通常需要查出位于数据库默认表空间的对像，显然上面的查询不是我们想要的，接下来看另一个查询。

查询位于默认数据库表空间的对像

1
2
3
4
5

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner  
from pg_class a  
where a.relkind in ('r', 'i')  
and reltablespace='0'  
order by a.relpages desc;

备注：这个查询加入限制条件 reltablespace=’0’，即可查找出位于当前数据库默认表空间的数据库表和索引。 通常这才是我们想要的结果，接下来可以把部分表转移到其它表空间上去，转移的方法可以用 “ALTER TABLE move tablespace “或者重建索引移表空间等方法，这里不详细介绍。

查询在某个表空间上的对像

1
2
3
4
5
6

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner  
from pg_class a, pg_tablespace tb  
where a.relkind in ('r', 'i')  
and a.reltablespace=tb.oid  
and tb.spcname='tablespace_name'  
order by a.relpages desc;

关于 reltablespace

手册上对于 pgclass 视图的 reltablespace 字段解释

The tablespace in which this relation is stored. If zero, the database is default tablespace is
implied. (Not meaningful if the relation has no on-disk file.)