TACTIC Open Source
Purge DB before migrating from 4.5 to 4.8 - Printable Version

+- TACTIC Open Source (http://forum.southpawtech.com)
+-- Forum: TACTIC Open Source (http://forum.southpawtech.com/forumdisplay.php?fid=3)
+--- Forum: TACTIC Discussion (http://forum.southpawtech.com/forumdisplay.php?fid=4)
+--- Thread: Purge DB before migrating from 4.5 to 4.8 (/showthread.php?tid=226)



Purge DB before migrating from 4.5 to 4.8 - Nachogor - 07-06-2021

Hi guys!

It would be great to purge the DB when migrating from 4.5 to 4.8.

Understanding each project DB is easy, but what should we clean for sthpw DB?
It would be a problem if we run a query in DB/sthpw/Schema/tables/ which deletes all the entries containing each oldproject
Is there any other thing I should purge more than DB/sthpw/Schema/tables/ ?

Thanks again!!!
Nacho


RE: Purge DB before migrating from 4.5 to 4.8 - remkonoteboom - 07-06-2021

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.


RE: Purge DB before migrating from 4.5 to 4.8 - Nachogor - 07-07-2021

Thanks very much, checked the DB and this is very usefull. And cleaning all the old assets too.

This is great!