--//创建表空间所需目录
[postgres@postgres ~]$ mkdir tbs
[postgres@postgres ~]$ pwd
/home/postgres
[postgres@postgres ~]$ cd pg11
[postgres@postgres pg11]$ p
psql (11.1)
--//创建表空间语法
postgres=# \help create tablespace
Command: CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
[ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
LOCATION 'directory'
[ WITH ( tablespace_option = value [, ... ] ) ]
postgres=# CREATE TABLESPACE tbs location '/home/postgres/tbs';
CREATE TABLESPACE
postgres=#
postgres=# create table emp(id int) tablespace tbs;
CREATE TABLE
postgres=#
postgres=# insert into emp values (10);
INSERT 0 1
postgres=# \df *filepath*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+----------------------+------------------+---------------------+------
pg_catalog | pg_relation_filepath | text | regclass | func
(1 row)
postgres=# select pg_relation_filepath('emp');
pg_relation_filepath
---------------------------------------------
pg_tblspc/16387/PG_11_201809051/13237/16388
(1 row)
--//pg_tblspc/16387/PG_11_201809051/13237/16388
--//其中pg_tblspc是表空间的目录
[postgres@postgres pg11]$ cd pg_t
pg_tblspc/ pg_twophase/
[postgres@postgres pg11]$ cd pg_tblspc/
[postgres@postgres pg_tblspc]$ ls -ltr
total 0
lrwxrwxrwx 1 postgres postgres 18 Apr 1 10:25 16387 -> /home/postgres/tbs
--//16387代表表空间的oid
postgres=# select oid, spcname from pg_tablespace;
oid | spcname
-------+------------
1663 | pg_default
1664 | pg_global
16387 | tbs
(3 rows)
postgres=#
--//PG_11_201809051代表postgres的版本和
数据库版本
[postgres@postgres pg11]$ pg_controldata
pg_control version number: 1100
Catalog version number: 201809051
Database system identifier: 6779218108159293483
Database cluster state: in production
pg_control last modified: Wed 01 Apr 2020 10:28:43 AM EDT
Latest checkpoint location: 0/4015138
Latest checkpoint's REDO location: 0/4015100
Latest checkpoint's REDO WAL file: 000000010000000000000004
--//13237代表数据库的oid
postgres=# select oid, datname from pg_database;
oid | datname
-------+-----------
13237 | postgres
1 | template1
13236 | template0
(3 rows)
postgres=#
--//16388代表emp对象的oid
postgres=# \df *filenode*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+----------------------+------------------+---------------------+------
pg_catalog | pg_filenode_relation | regclass | oid, oid | func
pg_catalog | pg_relation_filenode | oid | regclass | func
(2 rows)
postgres=# select pg_relation_filenode('emp');
pg_relation_filenode
----------------------
16388
(1 row)
postgres=#
postgres=# select oid, relname from pg_class where relname = 'emp';
oid | relname
-------+---------
16388 | emp
(1 row)
postgres=#
postgres=# \db
List of tablespaces
Name | Owner | Location
------------+----------+--------------------
pg_default | postgres |
pg_global | postgres |
tbs | postgres | /home/postgres/tbs
(3 rows)
postgres=#
postgres=#