[pgpool-general: 2937] Re: pool members are reporting different backend states

Tatsuo Ishii ishii at postgresql.org
Fri Jun 20 17:10:06 JST 2014


> Postgres can stop. So does the Linux, or the hardware. Network connection
> can be lost. You can blame me, but this is the real life.
> This is why we use more than one of them.
>
> If Pg stops, still I'm sure that backend status must reflect this in all
> the pool members!
> Even if a single pool cannot reach the backend, it should notify all others
> not to use that backend, or say good bye to your consistent database!

Are you sure that all or one of pgpoo-II are up and running while one
of PostgreSQL starts or goes down?

If all of pgpool-II are down, there's no way for pgpool-II to know the
PostgreSQL status changed.

> So finally: is there any status information what's shared among pool
> members? What is it, if not the backend database status?

They are in pgpool-II's memory. When pgpool-II shuts down, it is
written to the status file.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Attila
> 
> 
> 2014-06-20 9:47 GMT+02:00 Tatsuo Ishii <ishii at postgresql.org>:
> 
>> Attila,
>>
>> If you restart pgpool-II and the backend status which pgpool-II
>> recognizes is different from actual PostgreSQL's staus, then it is
>> likely that you stop or restart PostgreSQL between pgpool-II stops and
>> restarts. In another other word, you directly changed backend situation
>> without let pgpool-II notices it. i.e. your fault.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
>> > Yep, "service pgpool2 force-reload" has corrected this, but I am not
>> happy
>> > at all.
>> >
>> > I thought that the pool status is consistent among the members. If pgpool
>> > does the replication, it is a disaster to have different backend sets in
>> > the pool members!
>> > No way to keep databases consistent... One pool will write just one
>> > backend, the other will write both.
>> >
>> > Attila
>> >
>> >
>> > 2014-06-19 12:08 GMT+02:00 Yugo Nagata <nagata at sraoss.co.jp>:
>> >
>> >> Hi,
>> >>
>> >> On Thu, 19 Jun 2014 11:05:32 +0200
>> >> Attila Heidrich <attila.heidrich at gmail.com> wrote:
>> >>
>> >> > Dear Guys,
>> >> >
>> >> > How on earth it is possible, that the pool members reports different
>> >> > backend status? It will cause problems very soon I guess.
>> >>
>> >> It is possible if pgpool reads old backend status info when this starts
>> up.
>> >> pgpool will discard the old backend info when starts with -D option.
>> >>
>> >> However, it would be better pgpools can detect the status mismatch
>> >> between them in some way.
>> >>
>> >> >
>> >> >     Node: 1
>> >> >     Host: 10.100.1.11
>> >> >     Port: 5433
>> >> >     Weight: 0.500000
>> >> >     Status: Up, in pool (1)
>> >> >     WD status: Standby (2)
>> >> >
>> >> >     Node: 2
>> >> >     Host: 10.100.1.10
>> >> >     Port: 5433
>> >> >     Weight: 0.500000
>> >> >     Status: Up, in pool (1)
>> >> >
>> >> > ---
>> >> >
>> >> >     Node: 1
>> >> >     Host: 10.100.1.11
>> >> >     Port: 5433
>> >> >     Weight: 0.500000
>> >> >     Status: Up, detached from pool (3)
>> >> >     WD status: Standby (2)
>> >> >
>> >> >     Node: 2
>> >> >     Host: 10.100.1.10
>> >> >     Port: 5433
>> >> >     Weight: 0.500000
>> >> >     Status: Up, in pool (1)
>> >> >     WD status: Standby (2)
>> >> >
>> >> > ---
>> >> >
>> >> >     Node: 1
>> >> >     Host: 10.100.1.11
>> >> >     Port: 5433
>> >> >     Weight: 0.500000
>> >> >     Status: Up, in pool (1)
>> >> >     WD status: Standby (2)
>> >> >
>> >> >     Node: 2
>> >> >     Host: 10.100.1.10
>> >> >     Port: 5433
>> >> >     Weight: 0.500000
>> >> >     Status: Up, in pool (1)
>> >> >     WD status: Active (3)
>> >> >
>> >> > ---
>> >> >
>> >> >     Node: 1
>> >> >     Host: 10.100.1.11
>> >> >     Port: 5433
>> >> >     Weight: 0.500000
>> >> >     Status: Up, in pool (1)
>> >> >     WD status: Standby (2)
>> >> >
>> >> >     Node: 2
>> >> >     Host: 10.100.1.10
>> >> >     Port: 5433
>> >> >     Weight: 0.500000
>> >> >     Status: Up, in pool (1)
>> >> >     WD status: Standby (2)
>> >> >
>> >> > ---
>> >> >
>> >> >     Node: 1
>> >> >     Host: 10.100.1.11
>> >> >     Port: 5433
>> >> >     Weight: 0.500000
>> >> >     Status: Up, detached from pool (3)
>> >> >     WD status: Standby (2)
>> >> >
>> >> >     Node: 2
>> >> >     Host: 10.100.1.10
>> >> >     Port: 5433
>> >> >     Weight: 0.500000
>> >> >     Status: Up, in pool (1)
>> >> >     WD status: Active (3)
>> >> >
>> >> > I have got five pool members, two of them are the backends themselves.
>> >> >
>> >> > Every pool member has the other 4 as other_pgpool, and
>> >> > heartbeat_destination, and the same two backend servers, here is the
>> >> > relevant part of the config for all pool members:
>> >> >
>> >> >
>> >> > nor-openfire-1:
>> >> >     backend_hostname1 = '10.100.1.11'
>> >> >     backend_port1 = 5433
>> >> >     backend_weight1 = 1
>> >> >     backend_data_directory1 = '/var/lib/postgresql/9.3'
>> >> >     backend_flag1 = 'ALLOW_TO_FAILOVER'
>> >> >     backend_hostname2 = '10.100.1.10'
>> >> >     backend_port2 = 5433
>> >> >     backend_weight2 = 1
>> >> >     backend_data_directory2 = '/var/lib/postgresql/9.3'
>> >> >     backend_flag2 = 'ALLOW_TO_FAILOVER'
>> >> >     fail_over_on_backend_error = on
>> >> >     heartbeat_destination0 = 'nor-postgres-2'
>> >> >     heartbeat_destination_port0 = 9694
>> >> >     other_pgpool_hostname0 = 'nor-postgres-2'
>> >> >     other_pgpool_port0 = 5432
>> >> >     heartbeat_destination1 = 'nor-jboss-2'
>> >> >     heartbeat_destination_port1 = 9694
>> >> >     other_pgpool_hostname1 = 'nor-jboss-2'
>> >> >     other_pgpool_port1 = 5432
>> >> >     heartbeat_destination2 = 'nor-postgres-1'
>> >> >     heartbeat_destination_port2 = 9694
>> >> >     other_pgpool_hostname2 = 'nor-postgres-1'
>> >> >     other_pgpool_port2 = 5432
>> >> >     heartbeat_destination3 = 'nor-jboss-1'
>> >> >     heartbeat_destination_port3 = 9694
>> >> >     other_pgpool_hostname3 = 'nor-jboss-1'
>> >> >     other_pgpool_port3 = 5432
>> >> > nor-postgres-1:
>> >> >     backend_hostname1 = '10.100.1.11'
>> >> >     backend_port1 = 5433
>> >> >     backend_weight1 = 1
>> >> >     backend_data_directory1 = '/var/lib/postgresql/9.3'
>> >> >     backend_flag1 = 'ALLOW_TO_FAILOVER'
>> >> >     backend_hostname2 = '10.100.1.10'
>> >> >     backend_port2 = 5433
>> >> >     backend_weight2 = 1
>> >> >     backend_data_directory2 = '/var/lib/postgresql/9.3'
>> >> >     backend_flag2 = 'ALLOW_TO_FAILOVER'
>> >> >     fail_over_on_backend_error = on
>> >> >     heartbeat_destination0 = 'nor-postgres-2'
>> >> >     heartbeat_destination_port0 = 9694
>> >> >     other_pgpool_hostname0 = 'nor-postgres-2'
>> >> >     other_pgpool_port0 = 5432
>> >> >     heartbeat_destination1 = 'nor-jboss-2'
>> >> >     heartbeat_destination_port1 = 9694
>> >> >     other_pgpool_hostname1 = 'nor-jboss-2'
>> >> >     other_pgpool_port1 = 5432
>> >> >     heartbeat_destination2 = 'nor-openfire-1'
>> >> >     heartbeat_destination_port2 = 9694
>> >> >     other_pgpool_hostname2 = 'nor-openfire-1'
>> >> >     other_pgpool_port2 = 5432
>> >> >     heartbeat_destination3 = 'nor-jboss-1'
>> >> >     heartbeat_destination_port3 = 9694
>> >> >     other_pgpool_hostname3 = 'nor-jboss-1'
>> >> >     other_pgpool_port3 = 5432
>> >> > nor-postgres-2:
>> >> >     backend_hostname1 = '10.100.1.11'
>> >> >     backend_port1 = 5433
>> >> >     backend_weight1 = 1
>> >> >     backend_data_directory1 = '/var/lib/postgresql/9.3'
>> >> >     backend_flag1 = 'ALLOW_TO_FAILOVER'
>> >> >     backend_hostname2 = '10.100.1.10'
>> >> >     backend_port2 = 5433
>> >> >     backend_weight2 = 1
>> >> >     backend_data_directory2 = '/var/lib/postgresql/9.3'
>> >> >     backend_flag2 = 'ALLOW_TO_FAILOVER'
>> >> >     fail_over_on_backend_error = on
>> >> >     heartbeat_destination0 = 'nor-jboss-2'
>> >> >     heartbeat_destination_port0 = 9694
>> >> >     other_pgpool_hostname0 = 'nor-jboss-2'
>> >> >     other_pgpool_port0 = 5432
>> >> >     heartbeat_destination1 = 'nor-openfire-1'
>> >> >     heartbeat_destination_port1 = 9694
>> >> >     other_pgpool_hostname1 = 'nor-openfire-1'
>> >> >     other_pgpool_port1 = 5432
>> >> >     heartbeat_destination2 = 'nor-postgres-1'
>> >> >     heartbeat_destination_port2 = 9694
>> >> >     other_pgpool_hostname2 = 'nor-postgres-1'
>> >> >     other_pgpool_port2 = 5432
>> >> >     heartbeat_destination3 = 'nor-jboss-1'
>> >> >     heartbeat_destination_port3 = 9694
>> >> >     other_pgpool_hostname3 = 'nor-jboss-1'
>> >> >     other_pgpool_port3 = 5432
>> >> > nor-jboss-1:
>> >> >     backend_hostname1 = '10.100.1.11'
>> >> >     backend_port1 = 5433
>> >> >     backend_weight1 = 1
>> >> >     backend_data_directory1 = '/var/lib/postgresql/9.3'
>> >> >     backend_flag1 = 'ALLOW_TO_FAILOVER'
>> >> >     backend_hostname2 = '10.100.1.10'
>> >> >     backend_port2 = 5433
>> >> >     backend_weight2 = 1
>> >> >     backend_data_directory2 = '/var/lib/postgresql/9.3'
>> >> >     backend_flag2 = 'ALLOW_TO_FAILOVER'
>> >> >     fail_over_on_backend_error = on
>> >> >     heartbeat_destination0 = 'nor-postgres-2'
>> >> >     heartbeat_destination_port0 = 9694
>> >> >     other_pgpool_hostname0 = 'nor-postgres-2'
>> >> >     other_pgpool_port0 = 5432
>> >> >     heartbeat_destination1 = 'nor-jboss-2'
>> >> >     heartbeat_destination_port1 = 9694
>> >> >     other_pgpool_hostname1 = 'nor-jboss-2'
>> >> >     other_pgpool_port1 = 5432
>> >> >     heartbeat_destination2 = 'nor-openfire-1'
>> >> >     heartbeat_destination_port2 = 9694
>> >> >     other_pgpool_hostname2 = 'nor-openfire-1'
>> >> >     other_pgpool_port2 = 5432
>> >> >     heartbeat_destination3 = 'nor-postgres-1'
>> >> >     heartbeat_destination_port3 = 9694
>> >> >     other_pgpool_hostname3 = 'nor-postgres-1'
>> >> >     other_pgpool_port3 = 5432
>> >> > nor-jboss-2:
>> >> >     backend_hostname1 = '10.100.1.11'
>> >> >     backend_port1 = 5433
>> >> >     backend_weight1 = 1
>> >> >     backend_data_directory1 = '/var/lib/postgresql/9.3'
>> >> >     backend_flag1 = 'ALLOW_TO_FAILOVER'
>> >> >     backend_hostname2 = '10.100.1.10'
>> >> >     backend_port2 = 5433
>> >> >     backend_weight2 = 1
>> >> >     backend_data_directory2 = '/var/lib/postgresql/9.3'
>> >> >     backend_flag2 = 'ALLOW_TO_FAILOVER'
>> >> >     fail_over_on_backend_error = on
>> >> >     heartbeat_destination0 = 'nor-postgres-2'
>> >> >     heartbeat_destination_port0 = 9694
>> >> >     other_pgpool_hostname0 = 'nor-postgres-2'
>> >> >     other_pgpool_port0 = 5432
>> >> >     heartbeat_destination1 = 'nor-openfire-1'
>> >> >     heartbeat_destination_port1 = 9694
>> >> >     other_pgpool_hostname1 = 'nor-openfire-1'
>> >> >     other_pgpool_port1 = 5432
>> >> >     heartbeat_destination2 = 'nor-postgres-1'
>> >> >     heartbeat_destination_port2 = 9694
>> >> >     other_pgpool_hostname2 = 'nor-postgres-1'
>> >> >     other_pgpool_port2 = 5432
>> >> >     heartbeat_destination3 = 'nor-jboss-1'
>> >> >     heartbeat_destination_port3 = 9694
>> >> >     other_pgpool_hostname3 = 'nor-jboss-1'
>> >> >     other_pgpool_port3 = 5432
>> >> >
>> >> > Other question:
>> >> >
>> >> > Our servers are connected via two networks. Is it possible to specify
>> a
>> >> > pool member with both IP interfaces?
>> >>
>> >> One other pgpool must be specified by single IP or hostname. You can use
>> >> either
>> >> of them. The exception is heartbeat destination; you can specify both IP
>> >> as heartbeat_destinationX.
>> >>
>> >> >
>> >> >
>> >>
>> ----------------------------------------------------------------------------------------
>> >> > Network #1
>> >> >              |                |                  |             |
>> >> >         |
>> >> >              S1           S2               S3          S4
>> >> S5
>> >> >              |               |                    |            |
>> >> >         |
>> >> >
>> >>
>> ----------------------------------------------------------------------------------------
>> >> > Network #2
>> >> >
>> >> > none of the networks are connected with failover connections, both use
>> >> > different IP subnets.
>> >> >
>> >> > Regards,
>> >> >
>> >> > Attila
>> >>
>> >>
>> >> --
>> >> Yugo Nagata <nagata at sraoss.co.jp>
>> >>
>>


More information about the pgpool-general mailing list