pg 表空间

  1. 云栖社区>
  2. 博客>
  3. 正文

pg 表空间

转身泪倾城 2016-05-13 10:18:03 浏览1845
展开阅读全文
一、说明 
   在数据库运维工作中,经常会有数据目录使用率较高需要调整的情况,通常会给数据库建立多个表空间, 
并分别位于不同的盘上,这时需要做的工作就是调整库中现有表和索引的表空间 

二、表空间用途 
  表空间就是一个简单的目录,其主要用途分两个: 
1.单独扩展表空间用,一旦磁盘或分区被耗尽,可以创建一个表空间到其他磁盘或分区上面。 
2.区分不同对象的存储位置,比如可将索引放入较快磁盘的表空间上,而将固定不变的数据放入较慢磁盘的表空间上。 

三、表空间共享 
    与Oracle数据库中的表空间被独占不同,PostgreSQL的表空间是可以被共享的。 
当创建了一个表空间后,这个表空间可以被多个数据库、表、索引等数据库对象使用。达到对象的分离与归类的目的。 
    在PostgreSQL中有两个系统自建表空间:pg_global和pg_default。 

前者是系统全局表空间,存储了关键的共享系统目录。后者是系统全局表空间,存储了关键的共享系统目录。 
后者是系统默认表空间,可通过set default tablespace=tablespacename来指定为其他表空间,在建立数据库、表、索引等数据库对象时, 
若不指定表空间参数,则系统自动将对象创建到默认表空间中。 


四、创建表空间: 
mkdir tbs_tina 
chmod 777 tbs_tina/ 
chown postgres:postgres tbs_tina/ 

create tablespace tbs_tina owner postgres location '/pgtina/tbs_tina'; 
create tablespace ind_tina owner postgres location '/pgtina/ind_tina'; 
create table t1(id int) tablespace tbs_tina; 
create index ind_t1 on t1(id) tablespace ind_tina;  ---可以将表和索引放在不同的表空间 


五、用户表空间权限: 
has_tablespace_privilege(user, tablespace, privilege) boolean 用户是否有访问表空间的权限 CREATE 
has_tablespace_privilege(tablespace, privilege) boolean   当前用户是否有访问表空间的权限 CREATE 

tina=# create user sqluser nosuperuser noreplication nocreatedb nocreaterole login encrypted password 'sqlpasswd'; --创建一个普通用户 
CREATE ROLE                                                            
tina=# select has_tablespace_privilege('sqluser','tbs_tina','create');  --sqluser没有tbs_tina表空间的权限 
has_tablespace_privilege 
-------------------------- 

(1 row) 
tina=# grant create on tablespace tbs_tina to sqluser;  ---授权给sqluser 
GRANT 
tina=# select has_tablespace_privilege('sqluser','tbs_tina','create'); --sqluser可以使用tbs_tina表空间了 
has_tablespace_privilege 
-------------------------- 

(1 row) 
tina=# select has_tablespace_privilege('tbs_tina','create'); ---当前用户postgres 拥有表空间tbs_tina的权限 
has_tablespace_privilege 
-------------------------- 

(1 row) 

六、表空间大小查询: 
pg_tablespace_size(oid) bigint 指定 OID 代表的表空间使用的磁盘空间 
pg_tablespace_size(name) bigint 指定名字的表空间使用的磁盘空间 

tina=# select oid,* from pg_tablespace; 
  oid  |  spcname   | spcowner |                  spcacl                  | spcoptions 
-------+------------+----------+------------------------------------------+------------ 
  1663 | pg_default |       10 |                                          | 
  1664 | pg_global  |       10 |                                          | 
16525 | tbs_tina   |       10 | {postgres=C/postgres,sqluser=C/postgres} | 
(3 rows) 


tina=# select pg_tablespace_size(16525)/1024 ||'KB'; ---表空间tbs_tina的oid为16525 
?column? 
---------- 
4KB 
(1 row) 
tina=# select pg_tablespace_size('tbs_tina')/1024||'KB';  ---也可以直接使用表空间名 
?column? 
---------- 
4KB 
(1 row) 
tina=# select pg_size_pretty(pg_tablespace_size('tbs_tina')); 
pg_size_pretty 
---------------- 
4096 bytes 
(1 row) 


七、表所在表空间查询 
PostgreSQL 提供类似" \ "命令很方便得到相关信息,命令如下: 
tina=# \d test1 
                                Table "public.test1" 
Column |         Type          |                     Modifiers                      
--------+-----------------------+---------------------------------------------------- 
id     | integer               | not null default nextval('test1_id_seq'::regclass) 
name   | character varying(10) | 

tina=# \d t1 
      Table "public.t1" 
Column |  Type   | Modifiers 
--------+---------+----------- 
id     | integer | 
Tablespace: "tbs_tina" 

备注:如果这个表的表空间为当前数据库的默认表空间,那么上面则不会显示 Tablespace 信息。 

               
7.1 查询数据库的默认表空间         
tina=# select datname,dattablespace from pg_database where datname='tina'; 
datname | dattablespace 
---------+--------------- 
tina    |          1663 
(1 row) 

tina=# select oid,spcname from pg_tablespace where oid=1663; 
oid  |  spcname   
------+------------ 
1663 | pg_default 
(1 row) 


7.2 查询在默认表空间的表和索引 
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',即可查找出位于当前数据库默认表空间的数据库表和索引。 

7.3 查询不在默认表空间的表和索引 
tina=> 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 >1664  order by a.relpages desc;  
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+---------- 
a       | r       |        0 | 0 bytes        |         16525 |    33979 
a       | r       |        0 | 0 bytes        |         16525 |    33979 
a       | r       |        0 | 0 bytes        |         16525 |    33979 


7.4 查询在某个表空间上的对象 
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='tbs_tina' order by a.relpages desc; 
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner 
---------+---------+----------+----------------+---------------+---------- 
t1      | r       |        0 | 0 bytes        |         16525 |       10 
(1 row) 

八、删除表空间 
tina=# drop tablespace ind_tina; 
ERROR:  tablespace "ind_tina" is not empty 

需要先清空表空间内的对象 
tina=# drop index ind_t1; 
DROP INDEX 
tina=# drop tablespace ind_tina; 
DROP TABLESPACE 

九、改变表空间所指向目录位置 
[root@oratest pgtina]# cp -R tbs_tina/  test_tina 

tina=# \db tbs_tina 
          List of tablespaces 
   Name   |  Owner   |     Location     
----------+----------+------------------ 
tbs_tina | postgres | /pgtina/tbs_tina 
(1 row) 

网上介绍的alter tablespace move 和修改pg_tablespace表的location位置都未验证成功

网友评论

登录后评论
0/500
评论
转身泪倾城
+ 关注