Best practices for database migrations

[Abigail]

On Mon, Nov 18, 2013 at 02:01:22AM +0000, Simon Wistow wrote:
> Is there a current favourite for doing database migrations (i.e having a 
> programatic way for a database to upgraded to a new version by having a 
> series of sql commands run on it)?
> 
> Some notes:
> 
> - We're not using DBIx::Class
> - Anything that relies on Moose is almost certainly out unless there's a 
>   really compelling reason [*]
> - That basically rules out DBIx::VersionedDDL
> - I actually prefer the ActiveRecord style of having the 'up' and 'down' 
>   actually in the same script 
> - I also kind of like being able to invoke a script rather than just 
>   have a plain sql file that gets executed although I've never actually 
>   needed that functionality so I'm not sure why
> - Actually something that was a direct port of ActiveRecord Migrations 
>   would be entirely palatable
> - Does anybody have any success stories and/or horror stories?
> - Is there now some completely different way of doing this stuff that I 
>   don't know about? [**]
> 
> [*] Although it appears from a quick look that Moose doesn't require a 
> substantial proportion of CPAN any more
> 
> [**] I fear that someone is going to suggestion expressing the 
> transformations as s-expressions or somethin



Over the past 15+ years, I've encountered many database migrations,
in many roles (sysadmin, DBA, consultant, developer). 

What you definitely don't want is "current best practise". You want
something that works for you, and your situation. If that aligns with
"current best practise" (whatever that is, how does one ever determine
that?), than that's a bonus, but a current best practise that actually
doesn't work for you is a disaster.


Questions one needs to answer even before designing a migration
strategy:
  
   -  Do you have any downtime where you can do the migration [1] or must
      the application be available all the time [2]? Or is there something
      in between (for instance, while you cannot shutdown everything, your
      environment may be N+1 or N+2 redundant, where you can shutdown one
      or more nodes at the time, and perform a migration)? [3]

   -  Is this an inhouse migration [4]? Is it external, but you're behind
      the wheels [5]? Or do you have to deliver a packaged solution that
      performs the upgrade [6]?

   -  Can you roll back? [7] Or does that require restoring from backup? [8]

   -  Must your code be upgraded at the same time the database migration
      happens [9], or can your code work with both the unmigrated and the
      migrated database [10]?


I would mistrust any solution offered by someone who doesn't know the
details of your environment/requirements -- and hence, I'm not going to
offer solution.



Abigail

[1] .. [10]  Been there, done that, did not get a T-shirt.