Easiest way to fix postgres data partition full without any data loss ?

In this blog will be helpful to solve the problem of data partition once full how to solve the problem with less down time and what are steps want to follow for data partition full .

postgres data partition full ? we got this issue 2 time one is at the time of database restoration and second one is at normal transaction.

Step 1. Moving  archive 
if you get disk partition issue while restoring database means initially you have to move your archive logs to some other partition once you moved your archive means xlog (WAL LOG) will be archived automatically to archive partition once restarted your postgres server if instance is down , mostly instance will be down once data directory is full , the simple logic is “xlog is inside data”  following script will be helpful to move and compress your archive to some other partition

Step 2.  Postgres Log moving :

it is one of the maintenance activity in postgres , once you moved your logs means you will get some more free space at data partition simple logic is postgres logs is inside the data directory. following script will be helpful to move postgres logs to some other partition .

Script to move and compress the  postgres logs

Step 3. postgres data splitting: 

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

after above 2 steps you have to split your postgres data’s to different location following blogs will be helpful to splitting the data to some other disk partition

Moving PostgreSQL Tablespace Types

 Step 4.  VACUUM :

Postgres VACUUM FULL reclaims all free space released by VACUUM by removing dead rows physically but will not return physical space it is for future transaction not , but vacuum full return free physical space as well as remove dead rows but This method also requires extra disk space, since it writes a new copy of the table and doesn’t release the old copy until the operation is complete.

To solve this problem, we need additional storage like any other network drive or portable hard disk.
Please do not forget that VACUUM FULL requires an exclusive lock on the table so during this operation your table cannot be accessible.

To fix this issue you have to do VACUUM FULL one by one objects it may be schema , database , group of table , or single table it is depend on disk space you have to choose the objects one by one .

so better choose vacuum instead of vacuum full until disk adding. following script will be helpful to do vacuum for daily basis and weekly basis.

Thanks for reading our blogs if you like share this posts to linkedin, facebook, twitter, google+ …etc

For IT related Support please contact Our team

2ndnijam

Leave A Reply

Login with: