DB Backup and Maintance

[Nachogor]

Hi guys!

Is there any documentation about best practices for shtpw database?

We would like to split our current year projects in our Tactic system, and have a Virtual Machine we can start only to check old projects with more of 2 years old, whenever we need to check something special about any past project. Does this sound ok or it's nonsense?

At some point we had an api reading some tasks even if they were part of retired projects.

Any advice will help us.

Thanks again!
Nacho

[listy]

The best practice would be to have single tactic server for single project.
This is because all sthpw/ tables are shared between all projects, and could not be easily backed-up and restored per-project.

When i need to backup db i use default pgsql tools

[Nachogor]

Uffff, that seems complicated due to the way we organize our work, but will consider it and analyze it.

Thanks!

[remkonoteboom]

Generally, places have kept all of the projects live together because database size is not really a factor. There are few tables that cause the size to balloon and you may wish to clean out periodically, namely, transaction_log, change_timestamp and ticket. I believe these often take up over 90% of the database space. Keeping everything live has advantages for reports and other diagnostics.

However, I understand that some way want to offline this. Every table that is needed in the sthpw database should be scoped with a "project_code" column, so you could export this if needed. I have indeed often questioned whether we should have put all the tables in each project (maybe this could be a enhancement in the future), but the reason for this was so that it would be easy to find a user's schedule even if they are working on multiple projects without thrashing every project in the system.

At any rate, you would need to export the following tables, scoped by the project code column: snapshot, file, note, status_log, pipeline, task
That's the ones I can think of offhand.

However, I am not sure this is worth it. I can understand if your tools are querying the entire table, but they should be scoped by project always or use appropriate filters. Also, if it is coming from an external API call that some technical director wrote in a script, it can be tricky to control because they can make an inadvertent huge query. How big of a problem is this situation?

[Nachogor]

Thanks Remko and Listy,

Will analyze this deeply and come back to you in a couple of days. THANKS!!!!

[listy]

Generally, places have kept all of the projects live together because database size is not really a factor.  There are few tables that cause the size to balloon and you may wish to clean out periodically, namely, transaction_log, change_timestamp and ticket.  I believe these often take up over 90% of the database space.  Keeping everything live has advantages for reports and other diagnostics.

I also noticed sthpw/sobject_log table. What is this for? Can it be cleaned up too?

[remkonoteboom]

It relates sobject to transactions in a way that can be searched quickly. You can clean this up if this historical data is not needed.