Postgresql moving tablespace
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 location
1.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:
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
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 location
1.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:
- create a new tablespace in the desired new location
- go through all of the tables/indexes stored in the old tablespace, and do ALTER <object> TABLESPACE <new_tablespace> on each of them.
- drop the old tablespace
- More Reference : tablespace objects moving
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/dirstep 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);
Comments
Post a Comment