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

[Luca Maranzano]

Hi, just a follow-up to say that we implemented the configuration with 4
instances of pgpool-II on every node.

So for every pgpool instance on every node we have to differentiate the
following set of files:
    1. /etc/pgpool-II/pgpool_00X.conf
    2. /etc/pgpool-II/failover_00X.sh
    3. /etc/pgpool-II/follow_primary_00X.sh
    4. /etc/pgpool-II/escalation_00X.conf
    5. /etc/pgpool-II/pool_passwd_00X

The only file that cannot be differentiated is the pool_hba.conf which must
be the same for all of the instances, there is no configuration option for
it.
One side effect is that there is a significant sprawl of processes on the
servers. If you consider pgpool and postgres processes, we are at about
1200 processes on every node, but they are physical servers with a lot of
cores.

The benefit of this articulated configuration is that every pgpool cluster
is managed by 3 nodes so there are less chances of split brain and you can
leverage the fact that the Primary pgpool process can be spread on
different nodes.

Thanks
Luca





On Wed, Jul 28, 2021 at 12:14 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> 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
> >> >>
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20211025/7e4bfb2a/attachment-0001.htm>