Postgresql - CREATE TABLE WITH TABLESPACE

Step 1 : Creating table with default tablespace

    postgres=# \db+
                                            List of tablespaces
        Name    |  Owner   |       Location       | Access privileges | Options |  Size   | Description 
    ------------+----------+----------------------+-------------------+---------+---------+-------------
    pg_default | postgres |                      |                   |         | 24 MB   | 
    pg_global  | postgres |                      |                   |         | 559 kB  | 
    tbs        | postgres | /var/lib/pgsql/tbspc |                   |         | 0 bytes | 
    (3 rows)
    
    postgres=# create table testtable(id int) tablespace tbs;

    CREATE TABLE
    postgres=# \db+
                                            List of tablespaces
        Name    |  Owner   |       Location       | Access privileges | Options |   Size   | Description 
    ------------+----------+----------------------+-------------------+---------+----------+-------------
    pg_default | postgres |                      |                   |         | 24 MB    | 
    pg_global  | postgres |                      |                   |         | 559 kB   | 
    tbs        | postgres | /var/lib/pgsql/tbspc |                   |         | 19 bytes | 
    (3 rows)

Step 2 : to know the exact location of database

    -bash-4.2$ cd tbspc
    -bash-4.2$ ll
    total 0
    drwx------. 3 postgres postgres 19 Dec 14 11:58 PG_13_202007201
    -bash-4.2$ cd PG_13_202007201/
    -bash-4.2$ ll
    total 0
    drwx------. 2 postgres postgres 19 Dec 14 11:58 14175
    -bash-4.2$ cd 14175/
    -bash-4.2$ ll
    total 0
    -rw-------. 1 postgres postgres 0 Dec 14 11:58 65541
    -bash-4.2$ pwd
    /var/lib/pgsql/tbspc/PG_13_202007201/14175
    
    The highlighted values is the identifier of the database. This is because one tablespace can be assigned to multiple databases.
    postgres=# select oid,* from pg_database;
    -[ RECORD 1 ]-+------------------------------------
    oid           | 14175
    oid           | 14175
    datname       | postgres
    datdba        | 10
    encoding      | 6
    datcollate    | en_US.UTF-8
    datctype      | en_US.UTF-8
    datistemplate | f
    datallowconn  | t
    datconnlimit  | -1
    datlastsysoid | 14174
    datfrozenxid  | 479
    datminmxid    | 1
    dattablespace | 1663
    datacl        |

Step 3 : to know the exact location of table

    -bash-4.2$ cd tbspc
    -bash-4.2$ ll
    total 0
    drwx------. 3 postgres postgres 19 Dec 14 11:58 PG_13_202007201
    -bash-4.2$ cd PG_13_202007201/
    -bash-4.2$ ll
    total 0
    drwx------. 2 postgres postgres 19 Dec 14 11:58 14175
    -bash-4.2$ cd 14175/
    -bash-4.2$ ll
    total 0
    -rw-------. 1 postgres postgres 0 Dec 14 11:58 65541
    -bash-4.2$ pwd
    /var/lib/pgsql/tbspc/PG_13_202007201/14175
    postgres=# select oid, relname from pg_class where relname='testtable';
    -[ RECORD 1 ]------
    oid     | 65541
    relname | testtable

Step 4 : set permanent tablespace

    postgres=# alter database demodb set tablespace tbs;

ALTER DATABASE
Step 5: rename tablespace

    postgres=# alter tablespace tbs rename to tbs1;
    ALTER TABLESPACE
    postgres=# \db+
                                            List of tablespaces
        Name    |  Owner   |       Location       | Access privileges | Options |  Size   | Description 
    ------------+----------+----------------------+-------------------+---------+---------+-------------
    pg_default | postgres |                      |                   |         | 24 MB   | 
    pg_global  | postgres |                      |                   |         | 559 kB  | 
    tbs1       | postgres | /var/lib/pgsql/tbspc |                   |         | 7977 kB |

Step 6 : Drop tablespace

    postgres=# drop tablespace tbs1;
    DROP TABLESPACE
    postgres=# \db+
                                    List of tablespaces
        Name    |  Owner   | Location | Access privileges | Options |  Size  | Description 
    ------------+----------+----------+-------------------+---------+--------+-------------
    pg_default | postgres |          |                   |         | 24 MB  | 
    pg_global  | postgres |          |                   |         | 559 kB |

Next Topic » (Postgresql - pg_default, pg_global tablespaces)