Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Postgresql tablespace moving scenario

Scenario:
If Postgresql data partition is  running out of disk space How to solve this issue

Ans:
Option 1:Create a new tablespace on a separate drive and move existing tables to it.

ALTER TABLE schema.table SET TABLESPACE tbs1;
Or using following steps you can migrating data from old tablespace to new tablespace

https://www.tutorialdba.com/2017/01/moving-database-objects-or-all-tables.html?m=1

You can also move all tables (or indexes) in one tablespace into another:

postgres=# alter table all in tablespace tbs3 set tablespace pg_default; 
ALTER TABLE
Note: Downtime is required for alter table moving. For “online” moving one can though use tools like pg_repack or pg_squeeze,Affected tables are locked while being relocated

Option 2:create a new tablespace and use for future tables:
PostgreSQL provides many options for setting the default location of newly created tables

You can default location based on the database using:

ALTER DATABASE dbname SET default_tablespace = tbs2; 
If you happened to have used mixed case when creating your database, you will need to quote the tablespace name e.g
CREATE DATABASE dbname3 OWNER nijam TABLESPACE tbs2;
based on the user creating the table
ALTER ROLE someuser SET default_tablespace = tbs1;
Temporarily for current session while you are creating a batch of tables using
SET default_tablespace = tbs2;
changing the default tablespace for the whole instance:
postgres=#  alter system set default_tablespace='tbs3';
ALTER SYSTEM
 
postgres=#  select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)
 
postgres=#  show default_tablespace ;
 default_tablespace 
--------------------
 tbs3
(1 row)

Option 3: using soft link you can move the tablespace location to other partition
https://www.tutorialdba.com/2017/01/postgresql-move-tablespace-using-soft.html?m=1


Comments

Popular posts from this blog

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

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform