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 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 idafter 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.
- Script for VACUUM and ANALYZE for postgres database
- How to schedule VACUUM and ANALYZE script for for postgres database ?
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
Login with Google