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

[Tatsuo Ishii]

> 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
>>