I have a postgres database running on my application, and the postgres partition is 100% full. Upon checking the files in pgdata directory is see below files occupying lot of space.

smsstoregt1 ins /postgres/pgdata/base/16384> du -skh * | grep -i M
589M    16400
244M    16405

Please can anyone suggest how to cleanup the space and if we can move the files 16400 and 16405 to another path or delete them, or if we can purge any data from postgres database from DB login.

Below is my postgres partition

/dev/mapper/mpath0p1  939M  900M     0 100% /postgres
  • If you have root on it, and it's ext3/4 then you can free up the "reserved for root" space by typing sudo tun2fs -m0 /dev/mapper/mpath0p1 Aug 30, 2017 at 15:48

3 Answers 3


You can create a new tablespace on a different disk and then move those tables to that new tablespace.

First create a new table space on a disk that has enough space:

create tablespace large_disk 
   location '/path/to/directory/with/more/space'
   owner your_postgres_user;

By making your_postgres_user the owner of that tablespace, the user automatically gets all privileges on that tablespace.

Then you need to find out to which tables those files belong to:

select 16400::regclass, 16405::regclass;

Then move the tables:

alter table public.table_one
    set tablespace large_disk;

alter table public.table_two
    set tablespace large_disk;

Those are your data files - you cannot delete them without destroying your DB. Instead use LVM to grow the partition. Noone can tell you what you can purge, its your database after all...

  • Thanks @Gaius for the feedback,i will look into the suggestion provided.The DB has been running since log but suddenly we see those datafiles growing into huge size,is there any way we can check the reason for the increase in these datafiles? Aug 29, 2017 at 11:45
  • When your DB is back online the \dt+ command in psql will show table sizes
    – Gaius
    Aug 29, 2017 at 12:55
  • Can you please let me know what might be the possible causes for the file size increase in the base directory and any possible way to avoid the space growing upto 100% Aug 31, 2017 at 11:34
  • There's only one possible cause - someone has inserted loads of data!
    – Gaius
    Aug 31, 2017 at 13:51

You can monitor the tablesize of all relations in the databases with the following script:

   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
    FROM pg_catalog.pg_statio_user_tables
    where relname in (select distinct a.relname
from pg_stat_user_indexes a,pg_index b,DBA_IND_COLUMNS c
where c.index_name = upper(a.indexrelname)
and   a.indexrelid = b.indexrelid
and idx_scan = 0 and schemaname = <your schema>)
     ORDER BY pg_total_relation_size(relid) DESC;

Please schedule this script using crontab on a daily basis and then analyze at which day/instance the table size grows drastically.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.