View source for HowTo: Clean old Billing PostgreSQL records

= About billing =
* Billing contains two ways in order to record all historic information:
:* Logfiles located in: '''dcip.pic.es:/var/lib/dcache/billing/'''
:* Database located in: '''dcip.pic.es:/var/lib/pgsql/'''
= Motivation =
* Billing database use to grow very fast.
* Sometimes, during a dCache upgrade (mostly on major release upgrades) a schema update is needed
:* How much greater is the database much longer will be the schema update.
* '''We need to decrease the database size by periodically deleting data'''
:* Or '''otherwise we can startup a database from the scratch''' by moving the old database to a historic instance and running the schema update on a fresh billing database
::Please refer to '''[[HowTo: Reset Billing PostgreSQL]]''' if you decide to startup a database from a scratch by keeping a backup of the old database.

= Deleting old information =
* We can reduce the size of the billing database by deleting records from the last ''X'' months.
* We should mostly delete information from the following tables:
:'''billinginfo'''
:'''doorinfo'''
:'''storageinfo'''
:'''hitinfo'''
:''costinfo'' (not available in our current dCache instances)
* Delete information of these tables as follows:
 delete from billinginfo where datestamp<current_date-interval '6 months';
 delete from doorinfo    where datestamp<current_date-interval '6 months';
 delete from storageinfo where datestamp<current_date-interval '6 months';
 delete from hitinfo     where datestamp<current_date-interval '6 months';
 delete from costinfo    where datestamp<current_date-interval '6 months';
:If needed, you can changes the number of months to be deleted from 6 to a different value.