Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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:
  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)
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
$ 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);


Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

Ora2PG - Oracle/MySQL to Postgres DB migration Version 20.0

PostgreSQL Introduction