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 .