From: | Amador Alvarez <apradopg(at)gmail(dot)com> |
---|---|
To: | Ken Barber <ken(at)bob(dot)sh> |
Cc: | Forums postgresql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: General Advice for avoiding concurrency during schema migrations |
Date: | 2014-03-24 22:16:45 |
Message-ID: | CA+vGRtjDoW5WPMUkx=iXEhCkD=_MJur6qiyGgvUjhvrXOinQmQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)bob(dot)sh> 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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Saffron | 2014-03-24 23:58:59 | Failure upgrading PG 9.2 to 9.3 |
Previous Message | john.tiger | 2014-03-24 22:16:40 | thanks core team for jsonb |