[pgpool-general: 7652] Re: Can PGPool manage multiple Postgres Clusters on every node?

[Tatsuo Ishii]

Hi,

> Hi Tatsuo,
> in your proposed schema, how do you manage the failover for example of C1
> C2 C3 instances if you have only 1 instance on node2?

>> On node 2 pgpool3 is running and managing C1, C2, C3

Pgpool3 checks healthiness of C1, C2 and C3. If it notices C1
(primary) goes down, detach C1 and promotes C2 or C3.

> Thanks
> Luca
> 
> 
> On Tue, Jul 27, 2021 at 2:50 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> > Hi Tatsuo, thank you for your feedback.
>> >
>> > The main requirement is to separate the 4 PG instances on every node,
>> > mainly for performance and management reasons.
>> >
>> > So it seems that the only solution is to have 4 pgpool instances for
>> every
>> > node, right?
>>
>> No. For example, you could have following configuration. (I suppose
>> there are 3 physical servers = nodes). Here,
>> An (n=1, 2, 3) represents "prod",
>> Bn (n=1, 2, 3) represents "pre-production",
>> Cn (n=1, 2, 3) represents "test", and
>> Dn (n=1, 2, 3) represents "dev".
>>
>> On node 1 following PostgreSQL instances are running.
>> A1, B1, C1, D1
>>
>> On node 2 following PostgreSQL instances are running.
>> A2, B2, C2, D2
>>
>> On node 3 following PostgreSQL instances are running.
>> A3, B3, C3, D3
>>
>> On node 1 pgpool1 is running and managing A1, A2, A3
>> On node 1 pgpool2 is running and managing B1, B2, B3
>> On node 2 pgpool3 is running and managing C1, C2, C3
>> On node 3 pgpool4 is running and managing D1, D2, D3
>>
>> Please note that on node 1 there are 2 pgpool instances, while on node
>> 2 and 3 there is only 1 pgpool instance.
>>
>> > Thanks
>> > Best regards
>> > Luca
>> >
>> >
>> >
>> > On Mon, Jul 26, 2021 at 3:56 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> >
>> >> > Hello,
>> >> >
>> >> > we are setting up a 3 nodes clusters similar to this one:
>> >> >
>> >> > https://www.pgpool.net/docs/latest/en/html/example-cluster.html
>> >> >
>> >> > we are using CentOS 7.9 physical nodes with PGPool 4.2 and PostgreSQL
>> 12.
>> >> >
>> >> > We need to have on every node 4 PostgreSQL instances (1 for prod, 1
>> for
>> >> > pre-production, 1 for test and 1 for dev), all of them must be in
>> >> streaming
>> >> > replication with the other 2 nodes.
>> >> >
>> >> > Can PGPool manage such configuration? From the documentation we didn't
>> >> find
>> >> > any reference.
>> >> > We are thinking to create 4 different pgpool instances, each one with
>> its
>> >> > set of files and tcp port, is this a supported configuration?
>> >>
>> >> The short answer is no.
>> >>
>> >> Pgpool-II assumes that there's only one streaming replication primary
>> >> server exists among PostgreSQL instances. That means:
>> >>
>> >> Suppose you have following PostgreSQL instances:
>> >>
>> >> "A" group for "prod": A1, A2, A3
>> >>
>> >> Pgpool-II can manage A1, A2 and A3, assuming that one of A1, A2,
>> >> A3 is the primary server and rest of it are standby servers.
>> >>
>> >> Similary:
>> >> "B" group for "pre-production": B1, B2, B3
>> >>
>> >> Pgpool-II can manage B1, B2 and B3, assuming that one of B1, B2,
>> >> B3 is the primary server and rest of it are standby servers.
>> >>
>> >> *BUT* a Pgpool-II instance cannot manage A group and B group altogeter
>> >> at the same time.
>> >>
>> >> So it seems you have 4 groups of PostgreSQL instances (prod,
>> >> pre-production, test and dev), you can manage each group by assigning
>> >> 4 Pgpool-II instances to each. In this case each Pgpool-II instance is
>> >> completely separated and will not communicate each other. I am not
>> >> sure what you can achieve in this configuration.
>> >>
>> >> Best regards,
>> >> --
>> >> Tatsuo Ishii
>> >> SRA OSS, Inc. Japan
>> >> English: http://www.sraoss.co.jp/index_en.php
>> >> Japanese:http://www.sraoss.co.jp
>> >>
>>