[Pgpool-general] Multiple access to the same DB

[Pablo Milano]

Hi Gabriele,

We are using such "high-availability" design in a production environment. We
run two application servers and two database servers. Each application
server has its own pgpool. Both pgpools point to the same pair of databases.

If both app-servers will operate concurrently, there are some important
things to take into account in this environment (based in our experience):

1) Both pgpools MUST have the same master and slave host definitions. If you
don't do this, you may run into a big problem if a mismatch is detected
between databases, because in this case each pgpool "degenerates" always its
master database.

2) You MUST set "replication_strict = true" on both pgpools, otherwise,
mismatch between databases will occur sooner or later, due to concurrency.

3) You SHOULD use "SELECT FOR UPDATE" sentences ONLY from inside a
transaction block (BEGIN ... COMMIT). Otherwise, deadlocks will probably
occur, due to concurrency too.

I hope this helps. 

> -----Original Message-----
> From: pgpool-general-bounces at pgfoundry.org 
> [mailto:pgpool-general-bounces at pgfoundry.org] On Behalf Of 
> Gabriele Turchi
> Sent: Martes, 07 de Febrero de 2006 07:56 a.m.
> To: pgpool-general at pgfoundry.org
> Subject: [Pgpool-general] Multiple access to the same DB
> 
> Hi.
> 
> I'm planning to use pgpool to enhance an existing application adding
> some sort of high-availability.
> 
> Now I have a producer process and a consumer one, and they 
> are using the
> DB as repository and syncronization point.
> 
> What is not so clear to me is: can I add a second consumer on a second
> server, talking to a _local_ pgpool based on the same database/tables?
> 
> More generic: multiple instances of pgpool can be used 
> against the same
> (couple of) database(s)?
> 
> [Host A] Producer      \
>                         v
>          Consumer A -> pgpool -> Postgres A (client_db)
>                             \   ^
>                              \ /
>                               X
>                              / \
>                             /   v
> [Host B] Consumer B -> pgpool -> Postgres B (client_db)
> 
> If the Host A fails, a new producer can be started on him, but two
> concurrently running consumers under normal conditions may greatly
> enhance the speed of the system.
> 
> 
> Thank you.
> Gabriele Turchi
> 
> P.S.: I'm sorry for my bad english.
> 
> 
> 
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general
>