PostgreSQL table space creation, deletion

Tablespace: The physical space in which tables are stored is literally understood, including tables, indexes, sequences, etc. of the database.

Table spaces can be created on different partitions of the server, and the benefits are:

One, if the initialization cluster is already running out of partition, it is convenient to create table space on other partitions to achieve the purpose of capacity expansion.

Second, for frequently accessed data can be stored on higher performance, faster disk partitions, and infrequently used data stored on inexpensive slower disk partitions.

Grammar:

postgres=# \h Create Tablespace
Command:create tablespace
Description:define a new tablespace
Syntax:
CREATE tablespace Tablespace_name
[OWNER User_name]
Location ' directory '
[With (tablespace_option = value [, ...])]

The user must have access to the directory where the tablespace resides, so you need to create the appropriate directory under the corresponding partition and assign permissions to the table space before creating it.

[Email protected] ~]# Mkdir/usr/local/pgdata
[Email protected] ~]# chown postgres:postgres/usr/local/pgdata/

To create a table space example:

Postgres=Create'/usr/local/pgdata'; CREATE Tablespace

After creating the tablespace successfully, you can see a new directory under the DB cluster directory pg_tblspc a connection file 51276, pointing to/usr/local/pgdata

[Email protected] ~]# ll/mnt/syncdata/pgsql941/data/pg_tblspc/01  - from:51276 -/usr/local/pgdata

[[email protected] ~]# ll/usr/local/pgdata/4drwx2409630  from: pg_9.4_201409291

Create tables in this tablespace:

Postgres=createtableint) tablespace tbs_test; CREATE TABLE

A file corresponding to the test table will now be added to the table space directory:

[Email protected] ~]# ll/usr/local/pgdata/pg_9.4_201409291/13003/51277
-RW-------. 1 postgres postgres 0 02:15/usr/local/pgdata/pg_9.4_201409291/13003/51277

Where 51277 corresponds to the relfilenode,13003 of the test table is the OID of the database Postgres.

Postgres=#SelectOid,datname fromPg_databasewhereDatname= 'Postgres'; OID|Datname-------+---------- 13003 |Postgres (1row) Postgres=#SelectRelname,relfilenode fromPg_classwhereRelname='Test'; Relname|Relfilenode---------+-------------Test|       51277(1Row

To delete a table space:

postgres=# \h Drop Tablespace
Command:drop tablespace
Description:remove a tablespace
Syntax:
DROP tablespace [IF EXISTS] Name

You must delete all database objects under this tablespace before deleting the tablespace, or you cannot delete it.

Such as:

Postgresexists tbs_test; ERROR: Not  empty  

Delete the table test that you just created in this table space, and then delete the table space.

Postgres=droptableifexists  test; DROP TABLE Postgres = Drop if exists tbs_test; DROP Tablespace

PostgreSQL table space creation, deletion