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

[Ramon de Carvalho Valle]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



On 06/07/2010 10:00 PM, Tatsuo Ishii wrote:
>> 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.
Would not it be a good behavior to degenerate the backends that return a
query error while the master do not?

I am concerned because I have an environment with seven backends in
load_balance_mode and it seems I can not be sure of the data integrity
of these backends in this mode.

- -Ramon


> 
>> 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
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkwO2uMACgkQGIS0iEuhp4NKNwCeNFbkyQ8z6j+DkOkDKQaPCX3n
6qgAoKEJUd4zZQ7jJvIX8EquM+CfNQv8
=aW9f
-----END PGP SIGNATURE-----