How to use PostgreSQL tablespace
This article mainly explains "how to use PostgreSQL tablespaces". The explanation in this article is simple and clear, easy to learn and understand. Please follow the ideas of Xiaobian slowly and deeply to study and learn "how to use PostgreSQL tablespaces" together!
1. Tablespace:
Tablespaces can be used to specify storage with different capabilities:
[root@jason1 ~]# mkdir -p /ssd/tbs_jason01
[root@jason1 ~]# chown -R postgres:postgres /ssd/tbs_jason01
[root@jason1 ~]# cd /ssd/tbs_jason01
postgres=# create tablespace tbs_jason01 LOCATION '/ssd/tbs_jason01';
CREATE TABLESPACE
#Each table space corresponds to a directory
[root@jason1 tbs_jason01]# ll
total 0
drwx------. 2 postgres postgres 6 Sep 24 21:22 PG_10_201707211
[root@jason1 tbs_jason01]#
2. Under the table space directory, each library corresponds to a directory
postgres=# create database jason01 tablespace tbs_jason01;
CREATE DATABASE
postgres=# select oid,datname from pg_database;
oid | datname
-------+-----------
13212 | postgres
16384 | test
1 | template1
13211 | template0
16385 | jason
16389 | jason01
(6 rows)
[root@jason1 PG_10_201707211]# ll
total 12
drwx------. 2 postgres postgres 8192 Sep 24 21:25 16389
In the database, each table corresponds to a file, stored in the form of 1G per file, and the maximum support for a single table is 32T.
postgres=# \c jason01
You are now connected to database "jason01" as user "postgres".
jason01=#
jason01=# create table jason01(
jason01(# id int,
jason01(# note text
jason01(# ) tablespace tbs_jason01;
CREATE TABLE
jason01=#
[root@jason1 16389]# pwd
/ssd/tbs_jason01/PG_10_201707211/16389
[root@jason1 16389]# ll -lrt
-rw-------. 1 postgres postgres 24576 Sep 24 21:25 13072_fsm
-rw-------. 1 postgres postgres 8192 Sep 24 21:25 13062_vm
-rw-------. 1 postgres postgres 24576 Sep 24 21:25 13062_fsm
-rw-------. 1 postgres postgres 136164 Sep 24 21:34 pg_internal.init
-rw-------. 1 postgres postgres 0 Sep 24 21:34 16408
-rw-------. 1 postgres postgres 8192 Sep 24 21:34 16413
-rw-------. 1 postgres postgres 0 Sep 24 21:34 16411
##View table storage path:
jason=# \c jason01
You are now connected to database "jason01" as user "postgres".
jason01=# select pg_relation_filepath('jason01');
pg_relation_filepath
---------------------------------------------
pg_tblspc/16388/PG_10_201707211/16389/16408
(1 row)
jason01=#
jason01=# insert into jason01 values(1,'dagagdgaaga');
INSERT 0 1
[root@jason1 16389]# ll 164*
-rw-------. 1 postgres postgres 8192 Sep 26 14:59 16408
-rw-------. 1 postgres postgres 0 Sep 24 21:34 16411
-rw-------. 1 postgres postgres 8192 Sep 24 21:34 16413
Thank you for reading, the above is "how to use PostgreSQL table space" content, after the study of this article, I believe we have a deeper understanding of how to use PostgreSQL table space this problem, the specific use of the situation also needs to be verified by practice. Here is, Xiaobian will push more articles related to knowledge points for everyone, welcome to pay attention!
