1

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 – Scott Marlowe Aug 30 '17 at 15:48
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;
0

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? – Nagesh Wali Aug 29 '17 at 11:45
  • When your DB is back online the \dt+ command in psql will show table sizes – Gaius Aug 29 '17 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% – Nagesh Wali Aug 31 '17 at 11:34
  • There's only one possible cause - someone has inserted loads of data! – Gaius Aug 31 '17 at 13:51
0

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

SELECT
   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, privacy policy and cookie policy

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