Best practices for database migrations
abigail at abigail.be
Mon Nov 18 10:03:45 GMT 2013
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
- Do you have any downtime where you can do the migration  or must
the application be available all the time ? 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)? 
- Is this an inhouse migration ? Is it external, but you're behind
the wheels ? Or do you have to deliver a packaged solution that
performs the upgrade ?
- Can you roll back?  Or does that require restoring from backup? 
- Must your code be upgraded at the same time the database migration
happens , or can your code work with both the unmigrated and the
migrated database ?
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
 ..  Been there, done that, did not get a T-shirt.
More information about the london.pm