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