On Tue, Apr 10, 2012 at 11:21 AM, Kiruba suthan
<kirubasuthan@gmail.com> wrote:
We are using Postgresql Database server to host DB for an analytical tool I am working on. Basically this DB has metrics about files. Row count of few tables are more than 18 million. But the content of the tables are basic data types like numbers, date & string. No binary data is stored. The size of the DB is around 1 GB when taken a full dump.
We query the DB using complex views to get reports and most of the result set of these queries are quite huge (row count in hundreds of thousand or in million).
The size of the DB Cluster Folder varies between 400GB to 600GB which is unreasonably huge for the actual data. It is eating up all disk-space in the server. When I create a fresh DB from the dump in a new server the size of the DB cluster folder is around 2.3 GB which is very reasonable to me.
Experts,
Could you help me how to clean up DB Cluster folder and reclaim disk space please? And please give me some insight into how data is organized in DB Cluster and what should I do to avoid this happening again?
Size of directories under DB Cluster Folder is mentioned below
[user@server DB_CLUSER_DATA]$ du -ksh *
407G base
316K global
49M pg_clog
4.0K pg_hba.conf
4.0K pg_ident.conf
120K pg_multixact
12K pg_notify
32K pg_stat_tmp
88K pg_subtrans
4.0K pg_tblspc
4.0K pg_twophase
4.0K PG_VERSION
129M pg_xlog
20K postgresql.conf
4.0K postmaster.opts
4.0K postmaster.pid
Perform VACUUM FULL on entire cluster to reclaim space. This operation will put Database Objects in Exclusive lock mode,so requesting you to do this activity in non-peak hours.
Once you completed above maintenance activity,then schedule manual VACUUM ANALYZE on every day once,so that dead rows space will be reused while inserting new records into a table.
--Raghu