How to manage data directory in PostgreSql when it is 100% full ?

1-Postgresql database cluster is a directory known as base directory. It contains subdirectories and postgres files.


2-When we execute the initdb to initialize a new database cluster, a base directory will be created under the specified directory.


3-The path of the base directory is usually set to the environment variable PGDATA.


4- A database is a subdirectory under the base subdirectory, and each of the tables and indexes is one file stored under the subdirectory of the database to which it belongs.


5- As postgresql is filesystem based database ,when the database objects are created in the database ,unless ,otherwise designed to be stored under separate file system,will be stored under the data directory .


6- So eventually with volume of the data grows ,the data directory filesystem gets full.And we can't delete the files/subdirectories except the pg_log .


In this case how can we manage to keep data directory well below threshold ?

Here is the quick solution :


1- Locate the data directory for the database cluster


edb=# show data_directory ;

data_directory

--------------------

/opt/edb/as10/data

(1 row)


This directory is mounted in / file system in the example database .


2- Now check the oid of the database

edb=# SELECT datname, oid FROM pg_database ;

datname | oid

-----------+-------

template1 | 1

template0 | 15708

edb | 15710

postgres | 15709

db2 | 41038

db1 | 41037


3- If we go to the $PGDATA we will see these databases(oid) are created as sub directories under base directories


-bash-4.2$ echo $PGDATA

/opt/edb/as10/data

-bash-4.2$ cd $PGDATA

-bash-4.2$ cd base/

-bash-4.2$ ls -lrt

total 84

drwx------. 2 enterprisedb enterprisedb 8192 Feb 6 2020 1

drwx------. 2 enterprisedb enterprisedb 8192 Feb 6 2020 15708

drwx------. 2 enterprisedb enterprisedb 8192 Sep 9 18:06 15709

drwx------. 2 enterprisedb enterprisedb 8192 Sep 9 18:06 15710

drwx------ 2 enterprisedb enterprisedb 8192 Sep 9 18:06 41037

drwx------ 2 enterprisedb enterprisedb 8192 Sep 9 18:06 41038



6- Now if the data drectory is occupied 100% space .To reduce it and make space ,what we have to do ,we will create a new table space in a different mount point in different file system and move few big tables to the new one .

This will also release the space from the data directory where the tables were residing initially .


7- First check the usage of data directory

-bash-4.2$ df -h .

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/rhel-root 48G 37G 11G 78% /


8- Check the existing tablespace details of the database


edb=# select spcname ,pg_tablespace_location(oid) from pg_tablespace;

spcname | pg_tablespace_location

------------+------------------------

pg_default |

pg_global |

ts1 | /opt/edb/as10/data


9- Create a new directory for a separate table space in a different file system .

mkdir -p /u01/newloc

Now login to the database and create a new table space


edb=# create tablespace ts2 location '/u01/newloc' ;


10- Once we have a new tablespace created we will move one of our big table to the new tablespace .


edb=# alter table testtab set tablespace ts2;


11- Now once the table is moved .Now check the directory .The physical space has been released as well .



-bash-4.2$ df -h .

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/rhel-root 48G 36G 12G 75% /



In this way we can free data directory .

Note: For better performance the db should not be used during the tablespace movement .







158 views0 comments

Recent Posts

See All

This is the step by step documents to install and configure Enterprisedb Failover Manager (EFM) 4.2 in RHEL 8 #EDB #EFM #postgrsql 1- Prerequisite : - EDB v12 is installed in the primary and master s