Thread: General Advice for avoiding concurrency during schema migrations
Hi there, I was just wondering if anyone has some general advice for how to ensure a schema migration for an application has exclusivity during its run. This is to avoid silly things like, if someone leaves an application server running during migration the migration should be able to lock somehow to avoid any changes while it is running. I've taken a look at LOCK TABLE, which can lock a table obviously (and I can obviously lock _all_ tables to fake a LOCK DATABASE), but I'm wondering if someone has some opinion around just raising the transaction isolation level to achieve similar goals? Any lessons learnt and best practices would be much appreciated :-). ken.
Hi Ken,
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 ?A.A.
On Fri, Mar 21, 2014 at 10:46 AM, Ken Barber <ken@bob.sh> wrote:
Hi there,
I was just wondering if anyone has some general advice for how to
ensure a schema migration for an application has exclusivity during
its run.
This is to avoid silly things like, if someone leaves an application
server running during migration the migration should be able to lock
somehow to avoid any changes while it is running.
I've taken a look at LOCK TABLE, which can lock a table obviously (and
I can obviously lock _all_ tables to fake a LOCK DATABASE), but I'm
wondering if someone has some opinion around just raising the
transaction isolation level to achieve similar goals?
Any lessons learnt and best practices would be much appreciated :-).
ken.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> 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.
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 .A.A.
On Mon, Mar 24, 2014 at 10:59 AM, Ken Barber <ken@bob.sh> wrote:
> Do you really need to allow web server connections to the database during aSo this would be ideal if we could control the situation 100%, to be
> schema migration ? Why not locking them up either with pg_hba.cong or a
> firewal rule or symply shut it off temporarily ?
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.