Thread Details
  • State Not Answered
  • Replies 3 replies
  • Subscribers 356 subscribers
  • Views 836 views
  • Users 0 members are here
Forum Actions
Related

Sugar Upgrade - DB (and file) cleaning

Frédéric Rinaldi

Hello,

I was wondering about your method to backup a Sugar instance before running an upgrade.

The way we work

  • an upgrade for one of our customer may requires multiple run (because of an error during the upgrade process but also because multiple step are required if the customer is upgrading from an old version)
  • this first step is really a technical step to identify the issues, solve them, upgrade the code or feature with new up to date method etc.
  • then we run test without and then with customer
  • after approval, we re-run the upgrade process with the current live instance

For the first steps, the full database (and even files with the upload folder) are not required

  • because it can be huge files
  • because the bigger the files are, the more complicated copy / transfer process are, even more when you got an issue and need to reload the instance etc

So for the first step of the upgrade we are working with a light dump (by removing several records / tables) and removing the upload content (sometime only emptying every file to 0k when the instance is an old one and we want to validate the process that will reorganize the upload folder).

For this cleaning, we are using multiple tips / tricks provided here (thanks  ,  ,   etc.).

The most consuming tables that are not really necessary for the technical validation step

  • job_queue
  • fts_queue
  • activities
  • audit
  • emails_text
  • pmse
  • etc.

Are you proceeding the same way ? any tricks are welcome.

Fred

Parents
  • 0

    We proceed similarly.
    I have three instances: DEV, TST, PRD

    Before an upgrade we:

    • may copy the database from PRD to TST/DEV minus emails, audit tables and some large tables we have for custom modules.
    • may copy the application from PRD to TST/DEV minus uploads & config files.


    Generally I keep the code up to date on all three as changes go: DEV->TST->PRD and I don't care as much if the data on DEV/TST is fictitious, it's good enough for testing. So we don't often do copies. But when we do, we avoid loading large tables and inbound_email to make sure we don't accidentally read inbound email from PRD mailboxes into DEV/TST.
    (We have a redirect on the DEV/TST email servers, set up by our sysadmins, that send any and all outbound email from those instances to an internal address so we cannot accidentally email customers from DEV/TST even if we wanted to.)

    In copying the DB we skip copying content for:

    • *_audit
    • emails
    • email_text
    • emails_beans
    • emails_email_addr_rel
    • inbound_email
    • a couple of other large custom tables

    I test the upgrade on DEV, taking detailed notes, sort out any issues... again detailed notes.

    I repeat my steps, and any fixes I have documented during DEV upgrade in TST and add to my upgrade notes if needed
    My testing team does a thorough round of testing from both their Admin and non Admin accounts (impersonating different Roles if they have special rules), in particular they check any code customizations.

    While DEV/TST are expendable and I can rebuild them any time, PRD of course is not.

    To go to Production:

    We have regular backups of the application server performed by sysadmin.

    Code is on git. And I also do regular backups of the file system (tar) that I keep on my local machine, backed up by TimeMachine on my Mac.
    The PRD database has a Master-Slave setup.

    When we upgrade PRD:

    • we set the instance to Maintenance mode
    • sysadmin or I un-mount the upload directory
    • I tar the sugar directory and download a copy on my machine (without the upload of course)
    • I follow my detailed steps to upgrade PRD and perform a silent upgrade (I've never used the wizard)
    • My testing team does a round of sanity checks
    • I fix any issues we may find on the way, occasionally it happens (usually just file permissions).
    • when everyone is happy with the upgrade I re-mount the upload directory
    • bring users back online (turn maintenance mode off)
    • after a day or so the DBAs will restart the replication to the Slave

    Caveat: When I go to 13 I need to figure out a way to move my Upload files to the proper directories offline, I don't want to hold up PRD for the duration of the move which I expect will take some time given that Upload is enormous and even stat fails every time inbound email processes an attachment.

    If all hell broke loose during an upgrade I would:

    • Restore the code from backup, with config in maintenance mode
    • DBA would Make the slave DB the Master
    • run a bunch of tests to make sure we're back to where we were
    • mount uploads
    • bring the users back on line
    • after a day or so DBA would Make the Old Master the Slave

    I have to say (knock on wood) I've never had to restore an instance in 12 years of doing upgrades.

    FrancescaS

Reply
  • 0

    We proceed similarly.
    I have three instances: DEV, TST, PRD

    Before an upgrade we:

    • may copy the database from PRD to TST/DEV minus emails, audit tables and some large tables we have for custom modules.
    • may copy the application from PRD to TST/DEV minus uploads & config files.


    Generally I keep the code up to date on all three as changes go: DEV->TST->PRD and I don't care as much if the data on DEV/TST is fictitious, it's good enough for testing. So we don't often do copies. But when we do, we avoid loading large tables and inbound_email to make sure we don't accidentally read inbound email from PRD mailboxes into DEV/TST.
    (We have a redirect on the DEV/TST email servers, set up by our sysadmins, that send any and all outbound email from those instances to an internal address so we cannot accidentally email customers from DEV/TST even if we wanted to.)

    In copying the DB we skip copying content for:

    • *_audit
    • emails
    • email_text
    • emails_beans
    • emails_email_addr_rel
    • inbound_email
    • a couple of other large custom tables

    I test the upgrade on DEV, taking detailed notes, sort out any issues... again detailed notes.

    I repeat my steps, and any fixes I have documented during DEV upgrade in TST and add to my upgrade notes if needed
    My testing team does a thorough round of testing from both their Admin and non Admin accounts (impersonating different Roles if they have special rules), in particular they check any code customizations.

    While DEV/TST are expendable and I can rebuild them any time, PRD of course is not.

    To go to Production:

    We have regular backups of the application server performed by sysadmin.

    Code is on git. And I also do regular backups of the file system (tar) that I keep on my local machine, backed up by TimeMachine on my Mac.
    The PRD database has a Master-Slave setup.

    When we upgrade PRD:

    • we set the instance to Maintenance mode
    • sysadmin or I un-mount the upload directory
    • I tar the sugar directory and download a copy on my machine (without the upload of course)
    • I follow my detailed steps to upgrade PRD and perform a silent upgrade (I've never used the wizard)
    • My testing team does a round of sanity checks
    • I fix any issues we may find on the way, occasionally it happens (usually just file permissions).
    • when everyone is happy with the upgrade I re-mount the upload directory
    • bring users back online (turn maintenance mode off)
    • after a day or so the DBAs will restart the replication to the Slave

    Caveat: When I go to 13 I need to figure out a way to move my Upload files to the proper directories offline, I don't want to hold up PRD for the duration of the move which I expect will take some time given that Upload is enormous and even stat fails every time inbound email processes an attachment.

    If all hell broke loose during an upgrade I would:

    • Restore the code from backup, with config in maintenance mode
    • DBA would Make the slave DB the Master
    • run a bunch of tests to make sure we're back to where we were
    • mount uploads
    • bring the users back on line
    • after a day or so DBA would Make the Old Master the Slave

    I have to say (knock on wood) I've never had to restore an instance in 12 years of doing upgrades.

    FrancescaS

Children
  • 0 in reply to Francesca Shiekh

    Solid answer  !

    For your specific challenge, how about running a separate upgrade even days before the real one, to a copy of the mounted file system to "transform" the file structure in advance, and then complete only the remainder with the real upgrade with some rsync/scp magic sprinkled on top?

    I have not tested it, but in past upgrades, I did decompose it and complete pieces separately myself either before or after the fact, whatever made more sense for the specific timing challenge and backward compatibility problem

    --

    Enrico Simonetti

    Sugar veteran (from 2007)

    www.naonis.tech


    Feel free to reach out for consulting regarding:

    • API Integration and Automation Services
    • Sugar Architecture
    • Sugar Performance Optimisation
    • Sugar Consulting, Best Practices and Technical Training
    • AWS and Sugar Technical Help
    • CTO-as-a-service
    • Solutions-as-a-service
    • and more!

    All active SugarCRM certifications

    Actively working remotely with customers based in APAC and in the United States