Postgresql表空间简单使用

一、新建表空间
[postgres@db2 ~]$ mkdir -p /usr/local/pg9.3/data/pg_tblspc/tank  //注意Postgres权限

ghan=# create tablespace tank location '/usr/local/pg9.3/data/pg_tblspc/tank';
CREATE TABLESPACE
ghan=# \! ls -l
total 4
lrwxrwxrwx 1 postgres postgres   36 May 30 21:46 16388 -> /usr/local/pg9.3/data/pg_tblspc/tank
drwx------ 3 postgres postgres 4096 May 30 21:46 tank
ghan=# create table emp(id int,first_name text);
CREATE TABLE

二、新建表
ghan=# create table dept(id int,first_name text) tablespace tank;
CREATE TABLE
ghan=# select pg_relation_filepath('emp');
 pg_relation_filepath 
----------------------
 base/16384/16389
(1 row)


ghan=# select pg_relation_filepath('dept');
             pg_relation_filepath             
----------------------------------------------
 pg_tblspc/16388/PG_9.3_201306121/16384/16395
(1 row)


ghan=# select tablename,tablespace from pg_tables where tablename in ('emp','dept');
 tablename | tablespace 
-----------+------------
 emp       | 
 dept      | tank
(2 rows)

四、修改数据库默认表空间
ghan=# alter database ghan set default_tablespace='tank';
ALTER DATABASE
ghan=# \q
[postgres@rhel5-01 pg_tblspc]$ createuser tank
[postgres@rhel5-01 pg_tblspc]$ psql -d ghan
psql (9.3.2)
Type "help" for help.

五、修改用户默认表空间
ghan=# alter user tank set default_tablespace='tank';
ALTER ROLE


ghan=# alter user tank password 'tank';
ALTER ROLE
ghan=# \c ghan
You are now connected to database "ghan" as user "postgres".


ghan=# \c -  tank
You are now connected to database "ghan" as user "tank".
ghan=> create table tank (id int);
ERROR:  permission denied for tablespace tank

五、赋权用户使用表间
ghan=> \c - postgres
You are now connected to database "ghan" as user "postgres".

ghan=# grant postgres to tank;
GRANT ROLE
ghan=# \c -  tank
You are now connected to database "ghan" as user "tank".
ghan=> create table tank (id int);
CREATE TABLE
ghan=> select tablename,tablespace from pg_tables where tablename in ('tank');
 tablename | tablespace 
-----------+------------
 tank      | tank
(1 row)


ghan=>