[Pgpool-general] Sync Problems with two pgpool nodes

Fri Aug 14 13:16:00 UTC 2009

In this case the pgpool nodes are not balancing.. they are serving
different psql clients.
So, we have 2 servers, each server runs a psql client and a dedicated
pgpool in the same server.
 The 2 pgpool instances, share the same 2 backends...

The idea, is to provide high availability to each independent server,
sharing the same database.

In our test we saw each pgpool replicating queries over the two
databases. But in some queries, no special queries, it doesn't
replicate over second backend (always is the second backend which is
unsync). In a more detailed (capturing tcp traffic)  revision we saw
that when happens this kind of errors pgpool only opens connections to
one backend.

So, i don't know really if this has any relation with the two pgpool
instances or is just a bug. The curious thing is that happens always
with the second backend.

Thanks,
Agustin

2009/8/13, pgpool-general-request at pgfoundry.org
<pgpool-general-request at pgfoundry.org>:
> Send Pgpool-general mailing list submissions to
> 	pgpool-general at pgfoundry.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
> 	http://pgfoundry.org/mailman/listinfo/pgpool-general
> or, via email, send a message with subject or body 'help' to
> 	pgpool-general-request at pgfoundry.org
>
> You can reach the person managing the list at
> 	pgpool-general-owner at pgfoundry.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of Pgpool-general digest..."
>
>
> Today's Topics:
>
>    1. Re: Order of execution of queries by pgpool (Jaume Sabater)
>    2. Sync Problem with two pgpool nodes (Agustin Almonte Ferrada)
>    3. Re: sync between pgpool (Tatsuo Ishii)
>    4. Re: Sync Problem with two pgpool nodes (Jaume Sabater)
>    5. Re: What is the performance penalty of using pgpool (Tatsuo Ishii)
>    6. Re: Order of execution of queries by pgpool (Tatsuo Ishii)
>    7. Re: sync between pgpool (Tatsuo Ishii)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Wed, 12 Aug 2009 21:42:37 +0200
> From: Jaume Sabater <jsabater at gmail.com>
> Subject: Re: [Pgpool-general] Order of execution of queries by pgpool
> To: Daniel.Crespo at l-3com.com
> Cc: pgpool-general at pgfoundry.org
> Message-ID:
> 	<3786f7bb0908121242q5e37fd2am44ce31baf364bb40 at mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> On Wed, Aug 12, 2009 at 8:15 PM, <Daniel.Crespo at l-3com.com> wrote:
>
>
>> So, if there are 3 backends (0, 1, and 2), the order will be:
>> 1. Execute in the last backend (backend 2)
>> 2. Wait for execution.
>> 3. If okay, execute (paralelly) in the rest of backends (in this case in
>> backend 0 and 1)
>> 4. If okay, return okay to client.
>
> I think it's first in the master, then in all slaves, but you are
> making me doubt right now... Tatsuo will know.
>
> --
> Jaume Sabater
> http://linuxsilo.net/
>
> "Ubi sapientas ibi libertas"
>
>
> ------------------------------
>
> Message: 2
> Date: Wed, 12 Aug 2009 18:02:04 -0400
> From: Agustin Almonte Ferrada <aalmonte at antica.cl>
> Subject: [Pgpool-general] Sync Problem with two pgpool nodes
> To: pgpool-general at pgfoundry.org
> Message-ID: <907533FD-E026-49E5-8AE4-AE8DFE4CC774 at antica.cl>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes
>
>
> Hi i'm using pgpool latest version and i'm having synchronization
> problems
>
>     My scenario is:
>     4 servers, two for database and two acting as pgpool clients. So
>     service is balanced over two servers which acts over the two
> databases
>     in a redundancy configuration.
>     The problem seems to be the two instances trying to alter same
>     registers.
>     any experience about this kind of configuration? i'd really
> appreciate
>     any help.
>
>
>     Thanks in advance.
>     Agust?n Almonte F.
>
> ------------------------------
>
> Message: 3
> Date: Thu, 13 Aug 2009 11:28:23 +0900 (JST)
> From: Tatsuo Ishii <ishii at sraoss.co.jp>
> Subject: Re: [Pgpool-general] sync between pgpool
> To: moch_isnaini at yahoo.com
> Cc: pgpool-general at pgfoundry.org
> Message-ID: <20090813.112823.119859665.t-ishii at sraoss.co.jp>
> Content-Type: Text/Plain; charset=us-ascii
>
> pgpool is a synchronous replication system. If you need asyncronous
> rep system, you should go to Slony-I or some such.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
>
>> I have successed create log every update statement through pgpool.
>> Did you have plan to create communication between pgpool in a group ?.
>> My target is creating an asyncronouse replication.
>> Would you give me suggestion ?.
>>
>> Thanks.
>>
>> Muhammad Isnaini.
>
>
>
> ------------------------------
>
> Message: 4
> Date: Thu, 13 Aug 2009 09:29:41 +0200
> From: Jaume Sabater <jsabater at gmail.com>
> Subject: Re: [Pgpool-general] Sync Problem with two pgpool nodes
> To: Agustin Almonte Ferrada <aalmonte at antica.cl>
> Cc: pgpool-general at pgfoundry.org
> Message-ID:
> 	<3786f7bb0908130029g45171c44mb11f9fe34a898188 at mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> On Thu, Aug 13, 2009 at 12:02 AM, Agustin Almonte
> Ferrada<aalmonte at antica.cl> wrote:
>
>> Hi i'm using pgpool latest version and i'm having synchronization problems
>>
>> ? My scenario is:
>> ? 4 servers, two for database and two acting as pgpool clients. So
>> ? service is balanced over two servers which acts over the two databases
>> ? in a redundancy configuration.
>> ? The problem seems to be the two instances trying to alter same
>> ? registers.
>> ? any experience about this kind of configuration? i'd really appreciate
>> ? any help.
>
> You can't have two pgpool-II instances accessing the same two nodes.
> Only one can be active at a given time.
>
> Use Heartbeat to provide HA, or a similar mechanism, and share an IP
> address Whether you put pgpool-II on two separated machines or in the
> same ones hosting the PostgreSQL servers, that won't make a difference
> but in performance (IMHO, worse to have them in two separated
> machines).
>
> --
> Jaume Sabater
> http://linuxsilo.net/
>
> "Ubi sapientas ibi libertas"
>
>
> ------------------------------
>
> Message: 5
> Date: Thu, 13 Aug 2009 17:39:33 +0900 (JST)
> From: Tatsuo Ishii <ishii at sraoss.co.jp>
> Subject: Re: [Pgpool-general] What is the performance penalty of using
> 	pgpool
> To: jsabater at gmail.com
> Cc: pgpool-general at pgfoundry.org
> Message-ID: <20090813.173933.35045736.t-ishii at sraoss.co.jp>
> Content-Type: Text/Plain; charset=us-ascii
>
>> What would you understand as one session? One client only?
>
> Yes.
>
>> In case of
>> web server farms, that would never be the case, would it?
>
> Correct. The typical use case for what I mentioned would be a batch
> job in the midnight.
>
>> > Also note that if you have the third node, time taken will be 100%,
>> > not 200%. This is because pgpool issue INSERT concurrently for the
>> > second and the third node.
>>
>> That answers the question in my just sent mail for the previous thread :)
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
>
>
> ------------------------------
>
> Message: 6
> Date: Thu, 13 Aug 2009 17:39:40 +0900 (JST)
> From: Tatsuo Ishii <ishii at sraoss.co.jp>
> Subject: Re: [Pgpool-general] Order of execution of queries by pgpool
> To: jsabater at gmail.com
> Cc: Daniel.Crespo at l-3com.com, pgpool-general at pgfoundry.org
> Message-ID: <20090813.173940.129785348.t-ishii at sraoss.co.jp>
> Content-Type: Text/Plain; charset=us-ascii
>
>> > So, if there are 3 backends (0, 1, and 2), the order will be:
>> > 1. Execute in the last backend (backend 2)
>> > 2. Wait for execution.
>> > 3. If okay, execute (paralelly) in the rest of backends (in this case in
>> > backend 0 and 1)
>> > 4. If okay, return okay to client.
>>
>> I think it's first in the master, then in all slaves, but you are
>> making me doubt right now... Tatsuo will know.
>
> If the command is not commit/abort etc:
>
> 1. Wait for execution in backend 0
> 2. If okay, execute (paralelly) in the rest of backends (in this case in
> backend 1 and 2)
>
> If the command is not commit/abort etc:
>
> 1. Execute (paralelly) in the rest of backends (in this case in backend 1
> and 2)
> 2. If okay, execute in backend 0
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
>
>
> ------------------------------
>
> Message: 7
> Date: Thu, 13 Aug 2009 17:45:09 +0900 (JST)
> From: Tatsuo Ishii <ishii at sraoss.co.jp>
> Subject: Re: [Pgpool-general] sync between pgpool
> To: moch_isnaini at yahoo.com
> Cc: pgpool-general at pgfoundry.org
> Message-ID: <20090813.174509.105167773.t-ishii at sraoss.co.jp>
> Content-Type: Text/Plain; charset=us-ascii
>
>> I mean that I will develop pgpool have async mode with some modification.
>> Currently I success create a log file on every update statement.
>> Next plan is create a protocol for communication.
>>
>> Did you have any suggestion ?.
>
> Interesting.
>
> - You will need to "serialize" statements in the log file so that they
>   will create correct data. I believe Slony-I already does this.
>
> - How do you handle "extended protocol case"(prepared statement)? You
>   need to gather parameter information for a update statement in an
>   extended protocol.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
>
>
> ------------------------------
>
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general
>
>
> End of Pgpool-general Digest, Vol 57, Issue 12
> **********************************************
>

-- 
Enviado desde mi dispositivo móvil

Agustín Almonte