[Pgpool-general] Basics of replication with pgpool-II - how does it work? compared to Slony? to Postgres log shipping?

[Tatsuo Ishii]

> Hi.   We want to have redundancy for our database;
> is pgpool-II the right tool, with it's replication mode?
> 
> We want to have 2 database servers, and if one
> dies, our application just goes on using the other
> one, while we repair the broken one.
> 
> Can pgpool-II replicate writes such that if a write
> query is submitted, pgpool-II will write it to DB server
> A and DB server B, and will only return a success
> to the client if both writes succeeded?  otherwise
> it will return a failure, perform any necessary rollback
> if the write was replicated only to one server?

Yes, pgpool-II exactly works as you described. Plus, with write query
pgpool-II start a transaction if it's not already started by user.
The write order is first A then B (if A is the first one on
pgpool.conf). Commit order is B then A.

 If write query fails, pgpool-II aborts the transaction and start
a  degenration, which will be seen as connection shutdown from
PostgreSQL server by client. By reconnecting pgpool-II, client can use
the healthy PostgreSQL.

Please note that "fail" does not mean here a query error (for example,
violation of constraints). Rather "fail" means network error or
PostgreSQL server down or some such.

> If that's not how pgpool-II replication works, how does
> it work?  I've looked at the manual but I still don't
> understand.
> 
> I'm familiar with replication with Postgres 8 log shipping
> (warm standby),  Postgres 9 streaming replication
> (hot standby), and Slony (hot standby).  How does
> Pgpool-II replication work and how does it compare to
> the Postgres/Slony replication?

They are all log(physical or logical one) based asynchronous
replication systems. Pgpool-II is a query based synchronous
replication.  That's the big difference. In addition, those
replication systems do not have any automatic failover/load balancing
functionality while pgpool-II has.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp