From time to time it is a good idea to do some housekeeping in your home. The same is true for your iDempiere database. For some jobs you can use the Housekeeping facility for some jobs we can exchange hints and tricks here.

Housekeeping Window & Process

There is a housekeeping window in "System Admin" -> "General Rules" -> "Server". You can create database maintenance tasks. These tasks can be started by a process menu entry or on a regular basis by the process scheduler.

You can see what the Housekeeping process does with this records in the sourcecode. In short it does delete records that are defined by a WHERE-Clause. This may be e.g. temporary values that are older than one week. The deleted data can be saved to a history table or as XML files into a backup directory if you want.

If you use the "save in Historic" checkbox you have to create History tables. They have the same name like the original but with a prefix "HST_". I used the following SQL Statement to create them:

 CREATE TABLE HST_T_Aging AS SELECT * FROM T_Aging WHERE false;

(If you prefer you can create these tables on another PostgreSQL Tablespace to keep your fast and small production harddisk clean.)

If you use the scheduler you may see that the processes are not started the first time. You may restart the iDempiere server for that or you start the processes by hand. You can do that in the scheduler window or use the idempiereMonitor page (at http://yourserver:port/idempiereMonitor) and press "Run Now" there for single Processes or "Reload" for a whole reload of the server.

This wikipage may be a meeting point for community members to collect ideas what to clean up in the database.

Carlos Ruiz' Housekeeping

The first approach is from User:CarlosRuiz. He created IDEMPIERE-1509 to create a Housekeeping default configuration. His scripts cleans up some temporary tables (tables beginning with "T_"). Please see the migration script he provided and test it.

other ideas for housekeeping

My AD_Issue table has about 30000 entries (after implementing my system new and doing many mistakes while doing it). I do not think someone needs this longer than some weeks.

An process and button to create the HST_* historic table would be a nice feature to add.

ideas to measure what is going on

In the PostgreSQL Wiki I found Queries to see the tables in the database with the biggest storage size. You may want to see that to decide what to clean up.

You can use PgAdmin3 to see easy how many rows in your tables are. Keep an eye on this from time to time and report here when you find something interesting.

Testers

The Housekeeping is used (and tested) by User:CarlosRuiz and me (User:TBayen). Please add your name here!