5 Replies Latest reply on Nov 29, 2007 9:16 AM by dawn

    how do you restore db's from live to a staging/qa environment?


      I'm curious as to how other people restore their live db to a staging/qa environment. I'm asking because there are a numebr of things that may be configured just for live that you do not want in staging/qa. One example is the jiveurl property. Another would be that you may want to turn all notifications off so people are not sent emails from non-live environments.



      Do you copy the whole thing, then run a couple scripts to change certain things? Do you just grab the tables of interest? Any other plans?



        • Re: how do you restore db's from live to a staging/qa environment?

          This seems like a really good question, but unfortunately we have no good answers yet.


          Very often we have people writing documents which they will want others on the team to review, but not for everyone in the company and certainly not for the open public. It's not actually an "approval" stage, which is somewhat supported. Instead, it's more like an "open for comments" stage to the rest of an insider group that can help polish the document before it becomes more widely visible.


          I'd love to have more support for group-level visibility control to help manage where content is in the real-world staging pipeline we need to manage. Hopefully not just for documents, either, but rather for all types of content.



          • Re: how do you restore db's from live to a staging/qa environment?

            We do regular dumps of the production data to our staging environments to test upgrades, customizations, etc.


            We take a dump of the database and jiveHome from production.  Stop the app server, and copy the db/jiveHome to the staging environment. 


            As soon as we restart the application server:

            • First, I disable email monitoring (and remove the email address just in case someone tries to re-enable it.) Alternatively, you can  leave it enabled, but change it to use a new email account.  Forgetting to change the email monitoring can be especially problematic - under the right circumstances, your staging server can intercept (and mangle / delete / destroy) notifications from production (I've seen it happen).

            • Second, I change the jiveURL.


            Those are the two critical changes that you need to make to avoid causing issues with your production environment.

              • Re: how do you restore db's from live to a staging/qa environment?

                Thanks Dawn. I was getting ready to put a very similar process in place. I know we have a few custom parameters that we'll need to change as well, but its good to hear that you were thinking along the same lines.

                • Re: how do you restore db's from live to a staging/qa environment?


                  Hi Dawn,



                    I'm having the same issue as Dan (in fact I'm on his team ).  More about our situation- we are trying to replicate working instances of a clearspace app in order to have identical community ID's/threads/comments between our dev/qa/staging and live environments.  Periodically, we will do refreshes back from live to sync it up. I've tried truncating and recreating the dev database with the live data, and comments are properly reflected in our dev clearspace instance as if all the data were present. SUCCESS! we thought.  We could create new comments as well on existing threads. But the last step in having a full working environment on dev, is to be able to create new threads, which we have not been able to do properly with our live refresh.



                     Taking your suggestion, in addition to copying the database, I've copied the jiveHome directory as well, from live to dev.  This didn't have any noticable effect. What exactly is happening is that I will hit a url to create a new thread, the page will hang, and eventually 404. I'll check the jiveCommunityProp table for the thread, which isn't there. But 8-12 minutes later, it will appear and the thread will create.  With a home-grown, non-copy of live, thread creation happens immediately. We've tried to narrow down what is causing this time lag, stepping through a java debugger, but I get so far into XFire code that I don't know what to make of it yet.



                    Any suggestions?









                    • Re: how do you restore db's from live to a staging/qa environment?



                      I haven't seen your specific issue before, and we do regular dumps of data from production to staging.  Here's the full procedure we use.  If this doesn't work, let us know and I'll see if I can find someone who can figure it out.


                      1. Prepare Staging System

                        1. Verify Staging is running same version of database, java, appserver, and system libraries.

                        2. Verify Staging has a current sync of production jiveHome and database data (see step below)

                      2. Obtain Dump of Production Data

                        1. Copy CSX jiveHome and mysqldump of the production database.

                        2. Backup existing jiveHome and database in staging

                        3. Load new jiveHome and database (from production) and replace current staging data

                      3. SCRUB DATA!

                        1. We don't want to send emails to production users, or pull email from production email dropbox. So, when copying production database back to testing for upgrade verification, run the following SQL to ensure that testing doesn't steal production email, etc.

                        2. jiveURL will change per test server name and CSX path.

                        3. if you DO NEED email addresses in the data, don't scrub them, but by default, we do.

                      UPDATE jiveProperty SET propValue='http://insert_your_staging_url_here' WHERE name='jiveURL';
                      UPDATE jiveProperty SET propValue='blah.bogus' WHERE name='mail.smtp.host';
                      UPDATE jiveProperty SET propValue='blah.bogus' WHERE name='checkmail.host';
                      UPDATE jiveProperty SET propValue='blah@bogus.com' WHERE name='checkmail.account';
                      UPDATE jiveProperty SET propValue='blah@bogus.com' WHERE name='checkmail.username';
                      UPDATE jiveProperty SET propValue='false' WHERE name='checkmail.deleteUnusedMail';
                      UPDATE jiveProperty SET propValue='false' WHERE name='checkmail.enabled';
                      UPDATE jiveProperty SET propValue='false' WHERE name='checkmail.password';
                      UPDATE jiveProperty SET propValue='false' WHERE name='moderation.emailalert.enabled';
                      UPDATE jiveProperty SET propValue='false' WHERE name='watches.emailNotifyEnabled';
                      UPDATE jiveUser SET email='foobar@bogus.com';


                      If upgrading, follow upgrade documentation: CS Upgrade Docs