[pgpool-general: 8866] Re: Fwd: pgpool is not accepting connections

Bo Peng pengbo at sraoss.co.jp
Wed Jul 5 13:40:06 JST 2023


Hi,

There are several reasons:

1. Pgpool wasn't able to connect node0.
   Please check if any connection error occurs in logs.

2. Pgpool records the backend node status into status file (pgpool_status) and
   retrieve the status of backend nodes from status file at startup.
   Please check you log file if the following log message exists.

   "reading status file: 0 th backend is set to down status"

   If you can find the above log message,
   to prevent Pgpool-II from retrieving status from status file,
   you need to add "-D" start option.

   If you installed pgpool using the RPM package provided by Pgpool-II community,
   edit /etc/sysconfig/pgpool and add the following option:

   vi /etc/sysconfig/pgpool
   (add -D to OPTS)
   OPTS=" -D -n"

On Tue, 4 Jul 2023 18:42:29 +0800
Zhaoxun Yan <yan.zhaoxun at gmail.com> wrote:

> No, I always started postgres first, then pgpool.
> 
> On Tue, Jul 4, 2023 at 5:05 PM Bo Peng <pengbo at sraoss.co.jp> wrote:
> 
> > Hi,
> >
> > When you restart Pgpool-II, will pgpool not be able to detect this primary?
> >
> > On Mon, 3 Jul 2023 16:55:50 +0800
> > Zhaoxun Yan <yan.zhaoxun at gmail.com> wrote:
> >
> > > Hi Peng!
> > > As in the configuration:
> > > backend_hostname0 = '172.17.0.2'
> > > 172.17.0.2 is primary by default, or does pgpool think otherwise? How
> > can I
> > > check?
> > >
> > >
> > > On Mon, Jul 3, 2023 at 4:29 PM Bo Peng <pengbo at sraoss.co.jp> wrote:
> > >
> > > > Hi,
> > > >
> > > > > I have tested local postgresql, and it is ok for psql works:
> > > > > psql -h 127.0.0.1  -U checker -c '\l' template1
> > > > > But as I add in `-p 9999` the pgpool is down:
> > > > > psql -h 127.0.0.1 -p 9999 -U checker -c '\l' template1
> > > > > And I have found the latest log with these lines repeating:
> > > >
> > > > You need to make sure PostgreSQL primary is running before pgpool
> > starts.
> > > > If pgpool can't find any running PostgreSQL primary within
> > > > "search_primary_node_timeout",
> > > > Pgpool believes all backend nodes are down and will not accepting any
> > new
> > > > connections.
> > > >
> > > > On Thu, 29 Jun 2023 15:04:17 +0800
> > > > Zhaoxun Yan <yan.zhaoxun at gmail.com> wrote:
> > > >
> > > > > I have also tested connecting postgres directly via ip address
> > registered
> > > > > as first backend in pgpool.conf
> > > > > backend_hostname0 = '172.17.0.2'
> > > > >
> > > > > And it works:
> > > > > # psql -h 172.17.0.2 -p 5432 -U checker -c '\l' template1
> > > > > Password for user checker:
> > > > >                                   List of databases
> > > > >    Name    |  Owner   | Encoding |   Collate   |    Ctype    |
> >  Access
> > > > > privileges
> > > > >
> > > >
> > -----------+----------+----------+-------------+-------------+-----------------------
> > > > >  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> > > > >  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> > > > =c/postgres
> > > > >          +
> > > > >            |          |          |             |             |
> > > > > postgres=CTc/postgres
> > > > >  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> > > > =c/postgres
> > > > >          +
> > > > >            |          |          |             |             |
> > > > > postgres=CTc/postgres+
> > > > >            |          |          |             |             |
> > > > > checker=CTc/postgres
> > > > > (3 rows)
> > > > >
> > > > > while pgpool getsdozens of subprocess waiting for connection, it
> > still
> > > > > believes that all nodes are down:
> > > > >
> > > > > # ps aux|grep pgpool|grep -v grep
> > > > > root      100949  0.0 17.5 149080 137180 ?       Ss   Jun28   0:00
> > pgpool
> > > > > root      100950  0.0  0.3  15308  2932 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > PgpoolLogger
> > > > > root      100953  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100955  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100956  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100958  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100959  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100960  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100961  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100962  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100963  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100964  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100966  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100967  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100969  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100970  0.0  0.3 149080  2784 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100971  0.0  0.3 149080  2788 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100972  0.0  0.3 149080  2788 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100973  0.0  0.3 149080  2788 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100974  0.0  0.3 149080  2788 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100975  0.0  0.3 149080  2788 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100977  0.0  0.3 149080  2788 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100978  0.0  0.3 149080  2788 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100981  0.0  0.3 149080  2788 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100982  0.0  0.3 149080  2788 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      100983  0.0  0.3 149080  2788 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > wait for connection request
> > > > > root      101010  0.0  0.1 149084  1352 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > PCP: wait for connection request
> > > > > root      101011  0.0  0.1 149080  1352 ?        S    Jun28   0:00
> > > > pgpool:
> > > > > worker process
> > > > >
> > > > > ---------- Forwarded message ---------
> > > > > From: Zhaoxun Yan <yan.zhaoxun at gmail.com>
> > > > > Date: Wed, Jun 28, 2023 at 4:47 PM
> > > > > Subject: pgpool is not accepting connections
> > > > > To: <pgpool-general at pgpool.net>
> > > > >
> > > > >
> > > > > Hi guys!
> > > > >
> > > > > I have tested local postgresql, and it is ok for psql works:
> > > > > psql -h 127.0.0.1  -U checker -c '\l' template1
> > > > > But as I add in `-p 9999` the pgpool is down:
> > > > > psql -h 127.0.0.1 -p 9999 -U checker -c '\l' template1
> > > > > And I have found the latest log with these lines repeating:
> > > > >
> > > > > 2023-06-28 16:36:22.928: child pid 100980: FATAL:  pgpool is not
> > > > accepting
> > > > > any new connections
> > > > > 2023-06-28 16:36:22.928: child pid 100980: DETAIL:  all backend
> > nodes are
> > > > > down, pgpool requires at least one valid node
> > > > > 2023-06-28 16:36:22.928: child pid 100980: HINT:  repair the backend
> > > > nodes
> > > > > and restart pgpool
> > > > > 2023-06-28 16:36:22.928: main pid 100949: LOG:  reaper handler
> > > > > 2023-06-28 16:36:22.929: main pid 100949: LOG:  child process with
> > pid:
> > > > > 100980 exited with success and will not be restarted
> > > > > 2023-06-28 16:36:22.929: main pid 100949: LOG:  reaper handler:
> > exiting
> > > > > normally
> > > > > 2023-06-28 16:36:28.899: child pid 100976: FATAL:  pgpool is not
> > > > accepting
> > > > > any new connections
> > > > >
> > > > > Attached is the current pgpool configuration.
> > > >
> > > >
> > > > --
> > > > Bo Peng <pengbo at sraoss.co.jp>
> > > > SRA OSS LLC
> > > > TEL: 03-5979-2701 FAX: 03-5979-2702
> > > > URL: https://www.sraoss.co.jp/
> > > >
> >
> >
> > --
> > Bo Peng <pengbo at sraoss.co.jp>
> > SRA OSS LLC
> > TEL: 03-5979-2701 FAX: 03-5979-2702
> > URL: https://www.sraoss.co.jp/
> >


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/



More information about the pgpool-general mailing list