How do you handle Staging for sites with user data?

[mikey3times]

Background
My Wordpress update procedure has been:

1. Make a server backup and download to local drive.
2. Update plugins on live site.
3. Update Wordpress on live site.
4. Check live site (view pages, test contact forms, test user login, etc.)

Recently, I have been toying with the following procedure:

1. Make a server backup and download to local drive.
2. Use softaculous to create a staging copy to a password protected subdomain.
3. Update plugins then Wordpress on staging site.
4. Check staging site.
5. Update plugins then Wordpress on live site.
6. Check live site.

I've also experienced the white screen of death after updates in the past. Every time, my heart races and I scramble to google the solution and get everything back working in a couple of minutes.

Questions
How do you all handle Wordpress updates when you have course/download/ecommerce/user generated content?

I'm using a managed VPS so don't have the typical Managed Wordpress Hosting staging setups, but I don't think they are much different from Softaculous. I want to get into a more professional workflow where I test everything on staging and then push it live with Softaculous, but it isn't a smooth process because it copies the htaccess file with the password protection so I have to go in and delete that, plus some other minor fixes.

I am also afraid of losing user info if someone makes a change while I'm testing on staging. I know I can tell Softaculous to leave the database alone, but how do I know if the Wordpress updates involve database changes?

Backups are great, but if I don't realize I lost user info for a week or two (when the users complain), that means the database has a bunch of new info so I can't restore it later.

At the end of the day, I want to generate a SOP checklist so I can follow this every time Wordpress issues an update. Does anyone have a good SOP they can share that will make sure user data isn't lost and that the website doesn't get messed up during updates?

 

[Ryuzaki]

@mikey3times, this is not something I've done, and hopefully someone can chime in with more specific advice, but it's sounding like to me your workflow is fine, it just needs an additional step.

This would include comparing and merging the databases. That's a link to Stackoverflow that has a lot of discussion and tools available, including command line guidance.

Alternatively, I'm sure there's some way to export all the user data into some format like XML where you don't need to worry about the database structure. You'd update on your staging server, then export the users from the live site, push the staging version live, then import the user data.

I don't know. I'm thinking out loud. I don't deal with user data like that, and I'm thankful because of the complexity. I'm of the "screw it, we'll do it live" mentality, getting the heart racing, and just gambling that it's all going to work out right. Sometimes it doesn't but it's rare. Especially if you stay a couple versions behind everyone else in the Wordpress-o-sphere. If something happens then it's usually a plugin issue, which is why I keep them to a bare minimum.

Edit: Another post I just glanced at that may provide you some insight and tools.