How to create tablespace in PostgreSQL with examples

In this article, we will see How to create tablespace in PostgreSQL with examples and other actions can be performed related to PostgreSQL tablespace.

What is PostgreSQL tablespace?

A tablespace is a location on the disk where PostgreSQL stores data files containing database objects e.g., tables, and indexes. PostgreSQL uses a tablespace to map a logical name to a physical location on disk.

PostgreSQL comes with two default tablespaces:
pg_default tablespace stores user data.
pg_global tablespace stores global data.

Syntax to Create PostgreSQL Tablespace:

CREATE TABLESPACE tablespace_name
    [ OWNER owner_name]
    LOCATION 'directory' 

Note: The name cannot begin with pg_, as such names are reserved for system tablespaces.

PostgreSQL Create Tablespace examples

Create PostgreSQL tablespace on Linux environment:

1. Create directory in Linux file system by using sudo privileges.

mkdir /var/data/

2. Change owner of the above directory.

chown postgres:postgres /var/data/

3. Now, create PostgreSQL tablespace.

CREATE TABLESPACE tblprod101
OWNER postgres
LOCATION '/var/data/';

Create PostgreSQL tablespace on Windows environment:

create tablespace tblspctest
location 'C:\data1';

Find list of tablespaces in PostgreSQL server

We can get list of tablespaces by using meta command \db or sql statement.

1. Find the tablespaces using meta command \db

\db

2. Find the tablespaces using SQL statements:

 select * from pg_tablespace;

3. Find the tablespaces and their sizes.

\db+

Alter tablespace in PostgreSQL

Using alter tablespace command, we can rename, change the owner, or set parameters for a tablespace.

Change owner of PostgreSQL tablespace:

alter tablespace tblspcdvd
owner to r2schools;

Rename PostgreSQL tablespace:

alter tablespace tblspcdvd
rename to tblspcdev;

So in this article, we have seen how to create PostgreSQL tablespace with examples.