Feature Request: Documentation on how to reset database

[joshkpeterson]

Is there a way to safely clear out the messaging data in the database?

I see in Heroku, under Add ons > Heroku Postgres > Settings, there is a "Reset Database" button. Scared to try that though, I suspect it will clear out things besides the messaging history.

My use case is:

• org uses Spoke for small campaigns of a few hundred people at a time
• took them over a year to fill up the database
• don't require storing data about campaigns long-term
• monthly cost to upgrade to heroku paid tier is considerable for this org

It would be great if we could just flush the messaging data and Move On, as it were. I was actually brought on to help set up a new fresh spoke instance, because a previous volunteer had apparently tried to flush the data but had bricked the old instance somehow.

So yeah, is there a way to do this easily + safely?

Thanks!

[ben-pr-p]

If you want to keep users and organizations, truncating all tables except user, organization, and user_organization should do it.

Truncating tables with foreign keys pointing at them will error because of foreign key contraints - to get around this, you can either truncate tables in the right order (truncating tables with foreign keys before tables that the foreign keys point to), or you can temporarily disable foreign key constraints by running SET session_replication_role TO 'replica' while you do the truncations

[Frydafly]

thanks so much, @ben-pr-p!

@joshkpeterson, did this work for you?

I'm curious if you tried it and happen to know the correct order in which to truncate. That would be helpful for our documentation!

[joshkpeterson]

@Frydafly I did end up trying this like 6 months ago, and it got the job done. Sorry I didn't give an update at the time, because I've forgotten what I did exactly.

Setting aside user, organization, and user_organization - I am not 100% sure but think I remember having some difficulty or reluctancy with truncating all of the remaining tables, but I don't remember why. Could have been that I kept getting foreign key constraints on a couple. My recollection is that one or two tables made up the vast majority of storage used and I was like, I'll wait and see if this half-way job causes any noticeable issues and it didn't.

This is from my notes:

// couldn't get set replica to work, instead when I ran into one with a foreign key I did 
> TRUNCATE TABLE interaction_step CASCADE;
// as long as it didn't look like it would delete one of the 3 tables above.

If I were to formulate this into a feature request, I think maybe having a button to delete all info for a given campaign might be useful? That way once the campaign is far gone, you can free up the space it's using. Not sure if "campaign" is the right division, but just trying to think of something more granular than [the entire database except user + organization].

[Frydafly]

Got it! Still helpful, thank you @joshkpeterson