postgres database disk issue

Question

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

• Scott Marlowe: 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

Answer 1

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;

• Evan Carroll: pg_repack may also be useful in this. It can move all tables in a schema, or specified tables into a tablespace. reorg.github.io/pg_repack

Answer 2

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...

• Nagesh Wali: 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?
• Gaius: When your DB is back online the \dt+ command in psql will show table sizes
• Nagesh Wali: 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%
• Gaius: There's only one possible cause - someone has inserted loads of data!

Answer 3

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.