[pgpool-general: 7195] Re: The best way to enable PgPool and PostgreSQL server start automatically in the Pgpool + WatchDog Mode

[Bo Peng]

Hi,

On Wed, 12 Aug 2020 00:28:54 +0800
"J.L." <wantdrink at gmail.com> wrote:

> Hi Bo,
> In fact the I only start pgpool service in all servers with
> systemctl start pgpool
> 
> And only start the postgres on the primary with
> systemctl start postgresql-12
> 
> The postgres service started on server 2 & 3 as replication is to execute
> pcp_recovery_node command with specify the VIP as the host the same as the
> official guidance:
> 8.3.7.1. Set up PostgreSQL standby server
> 
>     # pcp_recovery_node -h 192.168.137.150 -p 9898 -U pgpool -n 1
>     Password:
>     pcp_recovery_node -- Command Successful
> 
>     # pcp_recovery_node -h 192.168.137.150 -p 9898 -U pgpool -n 2
>     Password:
>     pcp_recovery_node -- Command Successful
> 

You are using "pcp_recovery_node" to create standby servers.
Thank you for your explanation.

I don't recommend automatic starting pgpool at boot.
Pgpool will try to find backend servers at startup,
If all the backend servers have not been started, 
pgpool may trigger failover and don't accept any 
new connections.

Therefore, you need to make sure that at least one backend server is running
before pgpool is started.

If your backend server takes time to startup,
the workaround is to set "search_primary_node_timeout" parameter a little longer.
Default is 300 seconds.
It means pgpool will keep try to search for the primary node for 300 seconds at startup.

https://www.pgpool.net/docs/latest/en/html/runtime-config-failover.html#GUC-SEARCH-PRIMARY-NODE-TIMEOUT

> The configuration of pgpool is nearly the same with
> https://www.pgpool.net/docs/latest/en/html/example-cluster.html#EXAMPLE-CLUSTER-PRE-SETUP
> 
> Thanks.
> .
> 
> On Tue, Aug 11, 2020 at 10:47 PM Bo Peng <pengbo at sraoss.co.jp> wrote:
> 
> > Hi,
> >
> > On Sun, 9 Aug 2020 17:05:01 +0800
> > "J.L." <wantdrink at gmail.com> wrote:
> >
> > > Hi there,
> > >
> > > I've installed pgpool 4.1.2 and postgresql 12.3 with watchdog based on
> > > https://www.pgpool.net/docs/latest/en/html/example-cluster.html.
> > > That is 3 machines with a VIP, pgpool service + watchdog service +
> > > postgresql service running on each of them, 1 primary + 2 standby
> > > postgresql.
> > > The question is: is that possible to enable those services as auto
> > started
> > > even if I reboot all of them?
> > >
> > > I checked running services on them:
> > >
> > > [root at primary ~]# systemctl status postgresql-12
> > > > postgresql-12.service - PostgreSQL 12 database server
> > > >    Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service;
> > > > disabled; vendor preset: disabled)
> > > >    Active: active (running) since Thu 2020-07-02 22:17:35 CDT; 1
> > months 6
> > > > days ago
> > > >
> > > > [root at secondary1 ~]# systemctl status postgresql-12
> > > > postgresql-12.service - PostgreSQL 12 database server
> > > >    Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service;
> > > > disabled; vendor preset: disabled)
> > > >    Active: inactive (dead)
> > > >
> > > > [root at secondary2 ~]# systemctl status postgresql-12
> > > > postgresql-12.service - PostgreSQL 12 database server
> > > >    Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service;
> > > > disabled; vendor preset: disabled)
> > > >    Active: inactive (dead)
> > >
> > >
> > > So the postgresql service on secondary 1 & 2 is not started by postgresql
> > > service, they are triggered by pgpool with command like:
> > >
> > > > /usr/pgsql-12/bin/postgres -D /var/lib/pgsql/12/data
> >
> > Sorry, I could not understand the configuration.
> > May I ask how you configure pgpool to execute command above?
> >
> > > And on the primary the ps aux shows
> > >
> > > > /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
> > >
> > >
> > > Which means I can enable pgpool service on all 3 machines:
> > >
> > > > systemctl enable pgpool
> > >
> > >
> > > But shouldn't do the same for postgresql service like systemctl enable
> > > postgresql-12.
> > > Also I'm not sure if enabling services to start automatically will lead
> > to
> > > any issue.
> > >
> > > Any suggestion or guidance about this is appreciated. I just want to make
> > > the DB system recover even after rebooting.
> > >
> > > Thanks.
> >
> >
> > --
> > Bo Peng <pengbo at sraoss.co.jp>
> > SRA OSS, Inc. Japan
> >


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan