PostgreSQL 表領域作成

表領域の作成

CREATE TABLESPACE tbspc_02 OWNER udonman  LOCATION '/var/lib/pgsql/pgdata/spc02';

myposdb=# CREATE TABLESPACE tbspc_02 OWNER udonman  LOCATION '/var/lib/pgsql/pgdata/spc02';
ERROR:  directory "/var/lib/pgsql/pgdata/spc02" does not exist
-- OSコマンドで事前にフォルダを作成する必要ありそうです。
-- mkdir /var/lib/pgsql/pgdata/spc02 別ターミナルで実施後以下再実行

myposdb=# CREATE TABLESPACE tbspc_02 OWNER udonman  LOCATION '/var/lib/pgsql/pgdata/spc02';
CREATE TABLESPACE
myposdb=#
myposdb=# select s.oid,s.spcname,u.usename table_space_owner,pg_tablespace_location(s.oid) as "Location"
myposdb-# from pg_catalog.pg_tablespace s
myposdb-# ,pg_catalog.pg_user u where s.spcowner = u.usesysid
myposdb-# order by spcname;
  oid  |  spcname   | table_space_owner |          Location
-------+------------+-------------------+-----------------------------
  1663 | pg_default | postgres          |
  1664 | pg_global  | postgres          |
 16567 | tbspc_01   | postgres          | /var/lib/pgsql/pgdata/spc01
 16671 | tbspc_02   | udonman           | /var/lib/pgsql/pgdata/spc02
(4 rows)


myposdb=#

表領域オーナ変更例

alter tablespace tbspc_01 owner to sooni;

-- 表領域のオーナ変更
myposdb=# alter tablespace tbspc_01 owner to sooni;
ALTER TABLESPACE
myposdb=#
myposdb=# select s.oid,s.spcname,u.usename table_space_owner,pg_tablespace_location(s.oid) as "Location"
myposdb-# from pg_catalog.pg_tablespace s
myposdb-# ,pg_catalog.pg_user u where s.spcowner = u.usesysid
myposdb-# order by spcname;
  oid  |  spcname   | table_space_owner |          Location
-------+------------+-------------------+-----------------------------
  1663 | pg_default | postgres          |
  1664 | pg_global  | postgres          |
 16567 | tbspc_01   | sooni             | /var/lib/pgsql/pgdata/spc01
 16671 | tbspc_02   | udonman           | /var/lib/pgsql/pgdata/spc02
(4 rows)


myposdb=#