[pgpool-general: 8942] pgpool 4.4.4: reading status file: 1 th backend is set to down status
Camarena Daniel
Daniel.Camarena at azo.com
Wed Oct 11 20:57:22 JST 2023
Hi,
I've a cluster with 3 nodes. Every node runs bitnami/pgpool:4.4.4 as proxy and bitnami/postgresql-repmgr:15.4.0 as server. A PostgreSQL connection to all services (pg0, pg1, pg2, pgpool0, pgpool1, pgpool2) can be established.
In the QA system I see that pgpool of node 1 is not running properly. It is always in state "starting" and never "healthy". Therefore orchestration is terminating and restarting the container.
Having a look at the log of pgpool1 and comparing it with the other pgpool instances there is one difference:
main pid 1: LOG: reading status file: 1 th backend is set to down status
Therefore my questions:
1. Is there a file which buffers pg states?
2. How did the system get into this state?
First try to solve it was to remove the pgpool2 container and recreated it. Hopefully the status file is gone and everything works like expected. ==> This was the solution, but anyway, I want to prevent this state. So, the questions remain. As some pg servers reported "FATAL: remaining connection slots are reserved for non-replication superuser connections" and the max_connections was still set to the default of 100 I increased it to 200. Hope this helps.
Here are some results of queries:
Query
Pg0
Pgpool0
Pg1
Pgpool1
Pg2
Pgpool2
SELECT pg_is_in_recovery();
TRUE
False
False
TRUE
TRUE
False
SELECT sum(numbackends) FROM pg_stat_database;
87
92
92
93
87
92
Show pool_nodes; of pgpool 0:
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+-----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 10.0.10.7 | 5432 | up | up | 0.333333 | standby | standby | 70 | true | 0 | | | 2023-10-10 07:08:18
1 | 10.0.10.8 | 5432 | up | up | 0.333333 | primary | primary | 45 | false | 0 | | | 2023-10-10 07:08:18
2 | 10.0.10.9 | 5432 | up | up | 0.333333 | standby | standby | 64 | false | 0 | | | 2023-10-10 07:08:18
(3 rows)
Show pool_nodes; of pgpool 1:
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+-----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 10.0.10.7 | 5432 | up | up | 0.333333 | standby | standby | 0 | false | 0 | | | 2023-10-10 08:20:57
1 | 10.0.10.8 | 5432 | down | up | 0.333333 | standby | primary | 0 | false | 0 | | | 2023-10-10 08:20:57
2 | 10.0.10.9 | 5432 | up | up | 0.333333 | standby | standby | 1 | true | 0 | | | 2023-10-10 08:20:57
(3 rows)
show pool_nodes; of pgpool 2:
node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
---------+-----------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | 10.0.10.7 | 5432 | up | up | 0.333333 | standby | standby | 71 | false | 0 | | | 2023-10-10 07:07:16
1 | 10.0.10.8 | 5432 | up | up | 0.333333 | primary | primary | 43 | false | 0 | | | 2023-10-10 07:07:16
2 | 10.0.10.9 | 5432 | up | up | 0.333333 | standby | standby | 74 | true | 0 | | | 2023-10-10 07:07:16
(3 rows)
pgpool docker compose:
pgpool:
image: bitnami/pgpool:4.4.4
ports:
- "5000:5432"
environment:
- PGPOOL_BACKEND_NODES=${PGPOOL_BACKEND_NODES}
- PGPOOL_SR_CHECK_USER=${POSTGRES_USER}
- PGPOOL_SR_CHECK_PASSWORD=${POSTGRES_PASSWORD}
- PGPOOL_ENABLE_LDAP=no
- PGPOOL_POSTGRES_USERNAME=${POSTGRES_USER}
- PGPOOL_POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
- PGPOOL_ADMIN_USERNAME=${POSTGRES_USER}
- PGPOOL_ADMIN_PASSWORD=${POSTGRES_PASSWORD}
healthcheck:
test: ["CMD", "/opt/bitnami/scripts/pgpool/healthcheck.sh"]
interval: 10s
timeout: 5s
retries: 5
networks:
network_backend:
aliases:
- pgpool-instance
network_frontend:
pg docker compose:
image: bitnami/postgresql-repmgr:15.4.0
user: "1000:0"
restart: unless-stopped
ports:
- "5432:5432"
volumes:
- "/etc/timezone:/etc/timezone:ro"
- "/etc/localtime:/etc/localtime:ro"
- ../postgresql-volume/data:/bitnami/postgresql
- ../postgresql-volume/init:/docker-entrypoint-initdb.d
#- ../postgresql-volume/logs:/opt/bitnami/postgresql/logs
environment:
TZ: ${TZ}
POSTGRESQL_POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
POSTGRESQL_USERNAME: ${POSTGRES_USER}
POSTGRESQL_PASSWORD: ${POSTGRES_PASSWORD}
POSTGRESQL_DATABASE: ***
REPMGR_PASSWORD: ${POSTGRES_PASSWORD}
REPMGR_PRIMARY_HOST: ${REPMGR_PRIMARY_HOST}
REPMGT_PRIMARY_PORT: 5432
REPMGR_PARTNER_NODES: ${REPMGR_PARTNER_NODES}
REPMGR_NODE_NAME: pg-${SEQ}
REPMGR_NODE_NETWORK_NAME: ${IP}
REPMGR_PORT_NUMBER: 5432
networks:
network_backend:
network_frontend:
pgpool env:
Node 0
Node 1
Node 2
APP_VERSION
4.4.4
BITNAMI_APP_NAME
pgpool
HOME
/
LD_LIBRARY_PATH
/opt/bitnami/common/lib:/opt/bitnami/common/lib64:
OS_ARCH
amd64
OS_FLAVOUR
debian-11
OS_NAME
linux
PATH
/opt/bitnami/postgresql/bin:/opt/bitnami/common/bin:
/opt/bitnami/common/sbin:/opt/bitnami/pgpool/bin:
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
PGPOOL_ADMIN_PASSWORD
***
PGPOOL_ADMIN_USERNAME
***
PGPOOL_BACKEND_NODES
0:10.0.10.7:5432,1:10.0.10.8:5432,2:10.0.10.9:5432
PGPOOL_ENABLE_LDAP
No
PGPOOL_POSTGRES_PASSWORD
***
PGPOOL_POSTGRES_USERNAME
***
PGPOOL_SR_CHECK_PASSWORD
***
PGPOOL_SR_CHECK_USER
***
pg env (joined columns or empty where same content):
Node 0
Node 1
Node 2
APP_VERSION
15.4.0
BITNAMI_APP_NAME
postgresql-repmgr
HOME
/
LANG
en_US.UTF-8
LANGUAGE
en_US:en
NSS_WRAPPER_LIB
/opt/bitnami/common/lib/libnss_wrapper.so
OS_ARCH
amd64
OS_FLAVOUR
debian-11
OS_NAME
linux
PATH
/opt/bitnami/postgresql/bin:/opt/bitnami/repmgr/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
POSTGRESQL_DATABASE
***
POSTGRESQL_PASSWORD
***
POSTGRESQL_POSTGRES_PASSWORD
***
POSTGRESQL_USERNAME
***
REPMGR_NODE_NAME
pg-0
pg-1
pg-2
REPMGR_NODE_NETWORK_NAME
10.0.10.7
10.0.10.8
10.0.10.9
REPMGR_PARTNER_NODES
10.0.10.7:5432,10.0.10.8:5432,10.0.10.9:5432
REPMGR_PASSWORD
***
REPMGR_PORT_NUMBER
5432
REPMGR_PRIMARY_HOST
10.0.10.7
REPMGT_PRIMARY_PORT
5432
TZ
Europe/Amsterdam
AZO GmbH & Co. KG
Rosenberger Str. 28
D-74706
Osterburken
Tel.: +49 6291 92-6449
Mob.: +49 162 9919448
Fax: +49 6291 9290449
Mail: Daniel.Camarena at azo.com
Web: www.azo.com
AZO. We Love Ingredients.
KG: Sitz Osterburken, Register-Gericht Mannheim HRA 450086, Persönlich haftende Gesellschafterin: AZO Beteiligungs GmbH, Sitz Osterburken, Register-Gericht Mannheim HRB 450261
Geschäftsführer: Rainer Zimmermann | Daniel Auerhammer | Dr. Matthias Fechner | Jan-Wilko Helms | Dennis Künkel
Diese E-Mail einschließlich ihrer Anhänge ist vertraulich. Wir bitten Sie, eine fehlgeleitete E-Mail zu löschen und uns eine Nachricht zukommen zu lassen. Wir haben die E-Mail vor dem Versenden auf Virenfreiheit geprüft. Eine Haftung für Virenfreiheit schließen wir jedoch aus.
This e-mail and its attachments are confidential. If you are not the intended recipient of this e-mail message, please delete it and inform us accordingly. This e-mail was checked for viruses when sent, however we are not liable for any virus contamination.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20231011/b8f50281/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image840031.png
Type: image/png
Size: 760 bytes
Desc: image840031.png
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20231011/b8f50281/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image329387.png
Type: image/png
Size: 1048 bytes
Desc: image329387.png
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20231011/b8f50281/attachment-0001.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image877105.png
Type: image/png
Size: 1321 bytes
Desc: image877105.png
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20231011/b8f50281/attachment-0002.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image779108.png
Type: image/png
Size: 1362 bytes
Desc: image779108.png
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20231011/b8f50281/attachment-0003.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image883433.png
Type: image/png
Size: 795 bytes
Desc: image883433.png
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20231011/b8f50281/attachment-0004.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image616454.png
Type: image/png
Size: 1755 bytes
Desc: image616454.png
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20231011/b8f50281/attachment-0005.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image450857.png
Type: image/png
Size: 39998 bytes
Desc: image450857.png
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20231011/b8f50281/attachment-0006.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image112759.png
Type: image/png
Size: 22075 bytes
Desc: image112759.png
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20231011/b8f50281/attachment-0007.png>
More information about the pgpool-general
mailing list