Re: Stored procedure version control

On Wed, Jun 29, 2016 at 1:46 PM, Neil Anderson <neil(at)postgrescompare(dot)com> wrote:
> On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote:
>>
>> Elsewhere, somebody was asking how people implemented version control
>> for stored procedures on (MS) SQL Server.
>>
>> The consensus was that this is probably best managed by using scripts or
>> command files to generate stored procedures etc., but does anybody have
>> any comment on that from the POV of PostgreSQL?
>>
>
> I can't comment from the POV of those who represent Postgres, but I used to
> work for a company who specialised in change management for database
> products, SQL Server and Oracle in particular. There are at least two
> approaches. The migrations approach and the state based approach.
>
> For migrations you create up and down scripts/code fragments to move the
> database through versions over time, committing them to a source control
> system as you go. Usually the database will contain some tables to keep
> track of the current live version.
>
> With the state based approach you just store the DDL for each object in the
> source control system. You can see how an object changes over time by just
> inspecting one file. You can automate the scripting process or use one of
> the diffing tools that supports comparing to DDL directly.
>
> State based handles merge conflicts better than migrations. Migrations
> handles data changes better than state based. Migrations also is better if
> you are deploying to multiple production databases that may all be on
> different versions.
>
> If your database contains a lot of logic or you have a large distributed
> team you are more likely to have merge issues and so state based is probably
> the better choice. Smaller team, less logic and a production environment
> where you need to be able to update from any version reliably? Migrations is
> a good choice.
>
> Additionally you don't have to stick with one or the other. In the early
> days while you have little data to worry about you might use the static
> approach and then switch to migrations. You just pick a baseline to start
> from and carry on from there.

This is an excellent summary. I personally think the 'migrations'
based approach (as you describe it) is a better approach for large
teams and complex environments. A good migration script will redeploy
functions and views from source without having to be instructed to do
so by development. State migrations are good for simple cases,
particularly when the level of database expertise on the team is low.
A lot of times teams doing this tend to not even bother checking
database scripts into SCM, a huge long term mistake IMO.

It's not really necessary to create version down scripts. In five
years of managing complex database environments we've never had to
roll a version back and likely never will; in the event of a disaster
it's probably better to restore from backup anyways.

merlin