Purge DB before migrating from 4.5 to 4.8

[remkonoteboom]

We just did a purge of an old database and happened to record the tables we cleaned up.

transaction_state
transaction_log
message
message_log
sobject_log
notification_log
notification_login
change_timestamp
ticket

In this case, we removed everything that was over 6 months old:

delete from ticket where now() - timestamp > '6 months';

You can adjust to whatever you wish.  Also on searching for what to purge, I did find an incredibly useful sql call for PostgreSQL:

Code:

select table_schema,
      table_name,
      (xpath('/row/cnt/text()', xml_count))[1]::text::int as row_count
from (
  select table_name, table_schema,
        query_to_xml(format('select count(*) as cnt from %I.%I', table_schema, table_name), false, true, '') as xml_count
  from information_schema.tables
  where table_schema = 'public'
) t;

It lists every table in the database and the number of rows in them.  This will allow you to see what is taking up so much disk space.