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

[J.L.]

Thanks a lot Bo, it really helps.
By default installing postgres will
generate /usr/lib/systemd/system/postgresql-12.service as:

...
ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-12/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
...

So maybe to start postgresql automatically I just need to change from
ExecStart=/usr/pgsql-12/bin/postmaster -D ${PGDATA}
to
ExecStart=/usr/pgsql-12/bin/postgresql -D ${PGDATA}

as http://saule1508.github.io/pgpool/ described (in case we use the default
data directory)

Then for pgpool we need to check and test, pick up a good number for
search_primary_node_timeout.
After that in most cases if we just rebooted those 3 servers, postgresql &
pgpool could start automatically.

Please correct me if I'm wrong.
Thanks a lot.

On Wed, Aug 12, 2020 at 9:53 AM Bo Peng <pengbo at sraoss.co.jp> wrote:

> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200812/5cc97b22/attachment.html>