Moving PostgreSQL Tablespace Types

postgresql tablespace moving is two types
they are:
1. online moving – with database down
2. offline moving – without database down

why moving tablespace ?
once default tablespace or non-default tablespace filesystem is full means you will get issue/error  at the time of new transaction in postgresql database
Error:- Cannot insert in that file id
For this purpose we are moving our tabelspace to some other filesystem location1.Online moving – with database down:
online method is too slow method to moving tablespace to other filesystem and no need to stop/shutdown the cluster but objects will lock at the time of moving tables/indexes

Steps for online moving tablespace:

1. create a new tablespace in the desired new location
2. go through all of the tables/indexes stored in the old tablespace, and do ALTER <object> TABLESPACE <new_tablespace>on each of them.
3. drop the old tablespace
4. More Reference : tablespace objects moving

2.Offline moving – without database down
Offline method is too fast for moving postgresql tablespace to other filesystem/partition but database mode should be shutdown/stop and data will be more consistency than online moving

Steps for offline moving tablespace:
  
step 1. Check the tablespace oid and name 

nijam=# SELECT oid,spcname FROM pg_tablespace WHERE spcname = 'tblspc';
  oid  | spcname 
-------+---------
 24580 | tblspc
(1 row)
(2)

step 2.Stop postgres Server

$ pg_ctl -D $PGDATA stop

step 3.Move or copy tablespace data directory from old one to new one

$ mv /old/dir /new/dir
or
$ cp -r /old/dir /new/dir
$ rm -rf /old/dir

step 4. Change tablespace’s link to new directoryIf you look in your $PGDATA directory, you will find a subdirectory called pg_tblspc.  This directory contains entries like this: 

$ cd $PGDATA/pg_tblspc
$ rm 24580
$ ln -s /new/dir 24580

step 5. Start postgres postgres server

$ pg_ctl -D $PGDATA start

Then, you can see the tablespace’s directory has changed.

nijam=# SELECT pg_tablespace_location(24580);