Re: General Advice for avoiding concurrency during schema migrations

[Amador Alvarez <apradopg@xxxxxxxxx>]

Hi Ken,

With that level of dinamism of application servers where there is no way to keep consistency among them , as you say concurrency must be turned into a single thread to make sure schema migration will not be locked up by application threads .

Have you though about constraining connections with a connection pooler to handle incoming concurrency ?

Cheers,
A.A.

On Mon, Mar 24, 2014 at 10:59 AM, Ken Barber <ken@xxxxxx> wrote:

> Do you really need to allow web server connections to the database during a
> schema migration ? Why not locking them up either with pg_hba.cong or a
> firewal rule or symply shut it off temporarily ?

So this would be ideal if we could control the situation 100%, to be
clear our software is a shipped product
(http://github.com/puppetlabs/puppetdb), not something in-house so we
have to deal with cases that involve users not reading docs and doing
crazy stuff and still trying to preserve integrity :-).

The more I think on the issue, the more it is around dealing with two
potential scenarios:

* Someone has upgraded our software in two (or more) places and they
race to migrate.
* Someone has upgraded our software in only one place and other
application servers are not aware of that and continue to attempt to
work as before.

My current line of thought is around:

* Bumping our migration transaction isolation level to serializable.
For two migrations that are racing, I'd prefer that the loser backs
off, then realizes the migration has already happened and skips that
step.
* Perhaps locking tables across the board (or an a case-by-case,
depending on how hard-core we need it). While this might seem
unnecessary with serialization, it might fill some other gaps I have
fears about.
* Having the other (older & still running) application servers handle
a bump in the schema version properly by either stopping processing
and returning errors, or something else more clever. Without a
constant check on each transaction though, this could still allow some
failures through.

In most cases I'd prefer a fatal error rather than old application
servers to still continue processing and failing. There is also a lot
to be said about taking an app out of commission from a load balancer
perspective to avoid continued failed reception of messages an old app
server can't possibly process.

ken.