Tablespace Management - PostgreSQL

 

Tablespace Management - PostgreSQL

-bash-4.2$ cd $PGDATA

-bash-4.2$ pwd

/u02/PostgreSQL/11/data

-bash-4.2$ cd ..

-bash-4.2$ pwd

/u02/PostgreSQL/11

-bash-4.2$ mkdir base2

-bash-4.2$ psql

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

Type "help" for help.

postgres=#

postgres=# create tablespace tblspc location '/u02/PostgreSQL/11/base2';

CREATE TABLESPACE

postgres=#

postgres=# \db

               List of tablespaces

    Name    |  Owner   |         Location

------------+----------+--------------------------

 pg_default | postgres |

 pg_global  | postgres |

 tblspc     | postgres | /u02/PostgreSQL/11/base2

(3 rows)

postgres=#

postgres=#

postgres=#  \dt

Did not find any relations.

postgres=#

postgres=#

postgres=#

postgres=# create table demo (id int) tablespace tblspc;

CREATE TABLE

postgres=#

postgres=#

postgres=#

postgres=# create table demo2 (id int);

CREATE TABLE

postgres=#

postgres=#

postgres=#

postgres=# \d demo

                Table "public.demo"

 Column |  Type   | Collation | Nullable | Default

--------+---------+-----------+----------+---------

 id     | integer |           |          |

Tablespace: "tblspc"

postgres=#

postgres=#

postgres=# \d demo2

               Table "public.demo2"

 Column |  Type   | Collation | Nullable | Default

--------+---------+-----------+----------+---------

 id     | integer |           |          |

postgres=#

postgres=#

postgres=#

postgres=# select oid,datname from pg_database;

  oid  |  datname

-------+-----------

 14007 | template0

 16400 | template1

 16402 | postgres

 16422 | samik

 16423 | prod

 16428 | edbstore

(6 rows)

postgres=#

postgres=# select oid,relname from pg_class where relname='demo2';

  oid  | relname

-------+---------

 16444 | demo2

(1 row)

postgres=# select oid,relname from pg_class where relname='demo';

  oid  | relname

-------+---------

 16441 | demo

(1 row)

postgres=#

postgres=#

-bash-4.2$ pwd

/u02/PostgreSQL/11/base2

-bash-4.2$ ls -lrt

total 4

drwx------ 3 postgres postgres 4096 Oct 23 21:12 PG_11_201809051

-bash-4.2$

-bash-4.2$

-bash-4.2$ cd PG_11_201809051

-bash-4.2$

-bash-4.2$ ls -lrt

total 4

drwx------ 2 postgres postgres 4096 Oct 23 21:12 16402

-bash-4.2$

-bash-4.2$ cd 16402

-bash-4.2$ ls -lrt

total 0

-rw------- 1 postgres postgres 0 Oct 23 21:12 16441

-bash-4.2$ psql

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

Type "help" for help.

postgres=#

postgres=# create tablespace tblspc location '/u02/PostgreSQL/11/base2';

CREATE TABLESPACE

postgres=#

postgres=# \db

               List of tablespaces

    Name    |  Owner   |         Location

------------+----------+--------------------------

 pg_default | postgres |

 pg_global  | postgres |

 tblspc     | postgres | /u02/PostgreSQL/11/base2

(3 rows)

postgres=#

postgres=#

postgres=#  \dt

Did not find any relations.

postgres=#

postgres=#

postgres=#

postgres=# create table demo (id int) tablespace tblspc;

CREATE TABLE

postgres=#

postgres=#

postgres=#

postgres=# create table demo2 (id int);

CREATE TABLE

postgres=#

postgres=#

postgres=#

postgres=# \d demo

                Table "public.demo"

 Column |  Type   | Collation | Nullable | Default

--------+---------+-----------+----------+---------

 id     | integer |           |          |

Tablespace: "tblspc"

postgres=#

postgres=#

postgres=# \d demo2

               Table "public.demo2"

 Column |  Type   | Collation | Nullable | Default

--------+---------+-----------+----------+---------

 id     | integer |           |          |

postgres=#

postgres=#

postgres=#

postgres=# select oid,datname from pg_database;

  oid  |  datname

-------+-----------

 14007 | template0

 16400 | template1

 16402 | postgres

 16422 | samik

 16423 | prod

 16428 | edbstore

(6 rows)

postgres=#

postgres=# select oid,relname from pg_class where relname='demo2';

  oid  | relname

-------+---------

 16444 | demo2

(1 row)

postgres=# select oid,relname from pg_class where relname='demo';

  oid  | relname

-------+---------

 16441 | demo

(1 row)

postgres=#

postgres=#

postgres=# q\

Invalid command \. Try \? for help.

postgres-#

postgres-#

postgres-#

postgres-# \q

-bash-4.2$

-bash-4.2$

-bash-4.2$ pwd

/u02/PostgreSQL/11/base2

-bash-4.2$ ls -lrt

total 4

drwx------ 3 postgres postgres 4096 Oct 23 21:12 PG_11_201809051

-bash-4.2$

-bash-4.2$

-bash-4.2$ cd PG_11_201809051

-bash-4.2$

-bash-4.2$ ls -lrt

total 4

drwx------ 2 postgres postgres 4096 Oct 23 21:12 16402

-bash-4.2$

-bash-4.2$ cd 16402

-bash-4.2$ ls -lrt

total 0

-rw------- 1 postgres postgres 0 Oct 23 21:12 16441

-bash-4.2$

-bash-4.2$

-bash-4.2$

-bash-4.2$

-bash-4.2$

-bash-4.2$

-bash-4.2$

-bash-4.2$

-bash-4.2$ psql

psql (10.9, server 11.7)

WARNING: psql major version 10, server major version 11.

         Some psql features might not work.

Type "help" for help.

postgres=#

postgres=#

postgres=# select oid,spcname from pg_tablespace;

  oid  |  spcname

-------+------------

  1663 | pg_default

  1664 | pg_global

 16440 | tblspc

(3 rows)

postgres=#

postgres=#

postgres=# \d demo

                Table "public.demo"

 Column |  Type   | Collation | Nullable | Default

--------+---------+-----------+----------+---------

 id     | integer |           |          |

Tablespace: "tblspc"

postgres=#

postgres=#

postgres=#  \d demo2

               Table "public.demo2"

 Column |  Type   | Collation | Nullable | Default

--------+---------+-----------+----------+---------

 id     | integer |           |          |

postgres=#

postgres=# alter table demo2 set tablespace tblspc;

ALTER TABLE

postgres=#

postgres=#

postgres=#

postgres=# \d demo2

               Table "public.demo2"

 Column |  Type   | Collation | Nullable | Default

--------+---------+-----------+----------+---------

 id     | integer |           |          |

Tablespace: "tblspc"

postgres=#

postgres=#

postgres=#

postgres=#  \l

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

 edbstore  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 prod      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 samik     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+

           |          |          |             |             | =c/postgres

(6 rows)

postgres=#

postgres=#

postgres=# create database testdb tablespace tblspc;

CREATE DATABASE

postgres=#

postgres=#

postgres=#

postgres=#

postgres=# select oid,datname from pg_database;

  oid  |  datname

-------+-----------

 14007 | template0

 16400 | template1

 16402 | postgres

 16422 | samik

 16423 | prod

 16428 | edbstore

 16448 | testdb

(7 rows)

postgres=#

postgres=#

postgres=#  create table demo3 (id int);

CREATE TABLE

postgres=#

postgres=#

postgres=#

postgres=# create index idx_demo3 on demo3 (id) tablespace tblspc;

CREATE INDEX

postgres=#

postgres=#

postgres=#

postgres=#

postgres=#

postgres=#

postgres=# select oid,relname from pg_class where relname='idx_demo3';

  oid  |  relname

-------+-----------

 16452 | idx_demo3

(1 row)

postgres=#

postgres=#