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

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Sequence

Postgresql maximum size

How to configure Replication Manager (repmgr) ?

PostgreSQL pgBadger