Create tablespace in PostgreSQL

What is tablespace?

Tablespace is file system to store databases and its objects. To create tablespace, user must have CREATE USER privilege. Before creating tablespace, we must first create a physical disk location to which to map the tablespace.

Why tablespaces?
Tablespaces makes it much easier for administrators to control how PostrgeSQL’s data table are stored in the file system, which is useful for tasks such as managing large tables and improving performance by distributing the load across different disk drives.

Create Tablespace:

1. If we want to create a new location storing PostgreSQL files on Linux server. First create directory.

mkdir /opt/pgdata

2. Change permission of pgdata directory to postgres by using chown command.

chown postgres.postgres /opt/pgdata.

3. NOw, create a PostgreSQL tablespace associated with above new directory.

CREATE TABLESPACE data_tablespace01 LOCATION '/opt/pgdata';

4. To cross check by querying pg_tablspace view.

postgres=# select * from pg_tablespace; spcname | spcowner | spcacl | spcoptions ------------+----------+--------+------------ pg_default | 10 | | pg_global | 10 | | dataspace | 10 | |

Related Posts

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: