View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000753 | Pgpool-II | General | public | 2022-05-10 22:33 | 2022-06-03 22:16 |
| Reporter | garg1982@gmail.com | Assigned To | pengbo | ||
| Priority | normal | Severity | major | Reproducibility | have not tried |
| Status | closed | Resolution | open | ||
| OS | Redhat | OS Version | 7.6 | ||
| Product Version | 4.2.7 | ||||
| Summary | 0000753: Disconnection issue of pgpool server to database node | ||||
| Description | Hi Team, We faced disconnection issue of pgpool server to database node. The error message came "postmaster on DB node 0 was shutdown by administrative command" in pgpool log but we don't know who trigger this shutdown action. Need your help to identify the cause of it. Pgpool log =========== 2022-05-10 02:48:02: pid 74090: DETAIL: postmaster on DB node 0 was shutdown by administrative command 2022-05-10 02:48:02: pid 74090: LOG: received degenerate backend request for node_id: 0 from pid [74090] 2022-05-10 02:48:02: pid 74090: LOG: signal_user1_to_parent_with_reason(0) 2022-05-10 02:48:02: pid 35659: LOG: Pgpool-II parent process received SIGUSR1 2022-05-10 02:48:02: pid 35659: LOG: Pgpool-II parent process has received failover request 2022-05-10 02:48:02: pid 35659: LOG: starting degeneration. shutdown host 10.155.144.145(5432) 2022-05-10 02:48:02: pid 35659: WARNING: All the DB nodes are in down status and skip writing status file. 2022-05-10 02:48:02: pid 35659: LOG: failover: no valid backend node found 2022-05-10 02:48:02: pid 35659: LOG: Restart all children 2022-05-10 02:48:02: pid 35659: LOG: find_primary_node_repeatedly: all of the backends are down. Giving up finding primary node 2022-05-10 02:48:02: pid 35659: LOG: failover: set new primary node: -1 2022-05-10 02:48:02: pid 102776: FATAL: pgpool is not accepting any new connections Database log ============ 2022-05-10 02:48:02 IST [74459]: user=postgres,db=PIMDB,app=[unknown],client=10.155.144.199 FATAL: terminating connection due to administrator command Thanks | ||||
| Tags | No tags attached. | ||||
|
|
It is the correct behavior in Pgpool-II 4.2.x. > 2022-05-10 02:48:02 IST [74459]: user=postgres,db=PIMDB,app=[unknown],client=10.155.144.199 FATAL: terminating connection due to administrator command I think the PostgreSQL backend process above may be terminated by using SIGTERM signal or pg_terminate_backend. PostgreSQL returns the same error code in the following two cases: - backend node is shutdown by admin - backend process is killed by either a signal or by pg_terminate_backend() function Because Pgpool-II couldn't determine if it is a PostgreSQL failure or connection termination, in these cases Pgpool-II will perform failover. Since Pgpool-II 4.3, this issue can be avoided by setting "failover_on_backend_shutdown": https://www.pgpool.net/docs/latest/en/html/runtime-config-failover.html#GUC-FAILOVER-ON-BACKEND-SHUTDOWN |
|
|
Thank you for the update. I have tried to kill the pid of postgres backend server on test machine but it gives the " WARNING: PID 46109 is not a PostgreSQL server process". It means we can't kill postgres server pid from backend. And in my original production case, timestamp of both processes (postgres and pgpool dameon process) was older. It means it was not restarted [postgres@ANGDSIV000001 pgpool]$ ps -ef|grep "postgres -D" postgres 46109 1 0 May03 ? 00:07:14 /usr/local/pgsql/bin/postgres -D /data/pg-json12.3/data postgres 179699 170772 0 11:16 pts/3 00:00:00 grep --color=auto postgres -D postgres=# select pg_terminate_backend(46109); WARNING: PID 46109 is not a PostgreSQL server process pg_terminate_backend ---------------------- f (1 row) Please update if I am missing something here Thanks Gaurav |
|
|
Not the PostgreSQL server process. The child process of PostgreSQL was terminated. For example, in the log below the child process 74459 is terminated. --------- 2022-05-10 02:48:02 IST [74459]: user=postgres,db=PIMDB,app=[unknown],client=10.155.144.199 FATAL: terminating connection due to administrator command --------- Below are the steps to reproduce this issue: (1) Connect to Pgpool. $ /usr/pgsql-14/bin/psql -h 127.0.0.1 -p 11000 -U pengbo test test=# test=# show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------------- 0 | /tmp | 11002 | up | 0.500000 | primary | 0 | true | 0 | | | 2022-05-11 15:29:25 1 | /tmp | 11003 | up | 0.500000 | standby | 0 | false | 0 | streaming | async | 2022-05-11 15:29:25 (2 rows) test=# (2) Open a new terminal and check the process id: $ ps -ef | grep postgres | grep test pengbo 25952 25882 0 15:29 ? 00:00:00 postgres: pengbo test [local] idle pengbo 25953 25894 0 15:29 ? 00:00:00 postgres: pengbo test [local] idle (3) kill the process id 25952 which is connecting to the primary PostgreSQL $ kill -SIGTERM 25952 (4) Go back to the terminal in step (1): test=# show pool_nodes; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. test=# show pool_nodes; node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change ---------+----------+-------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+--------------------- 0 | /tmp | 11002 | up | 0.500000 | standby | 0 | false | 0 | streaming | async | 2022-05-11 15:30:17 1 | /tmp | 11003 | up | 0.500000 | primary | 0 | true | 0 | | | 2022-05-11 15:30:03 (2 rows) As you can see in the result above, failover is performed and node1 is promoted to primary. (5) Check the logs: ---- Pgpool-II log ---- 2022-05-11 15:30:01: psql pid 25927: DETAIL: postmaster on DB node 0 was shutdown by administrative command 2022-05-11 15:30:01: psql pid 25927: LOG: received degenerate backend request for node_id: 0 from pid [25927] 2022-05-11 15:30:01: psql pid 25927: LOG: signal_user1_to_parent_with_reason(0) 2022-05-11 15:30:01: main pid 25906: LOG: Pgpool-II parent process received SIGUSR1 2022-05-11 15:30:01: main pid 25906: LOG: Pgpool-II parent process has received failover request 2022-05-11 15:30:01: main pid 25906: LOG: starting degeneration. shutdown host /tmp(11002) 2022-05-11 15:30:01: main pid 25906: LOG: Restart all children 2022-05-11 15:30:01: main pid 25906: LOG: execute command: /home/pengbo/pgpoolsetup/etc/failover.sh 0 /tmp 11002 /home/pengbo/pgpoolsetup/data0 1 0 /tmp 0 11003 /home/pengbo/pgpoolsetup/data1 ----------------------------- ---- PostgreSQL log ---- 25952 2022-05-11 15:30:01 JST FATAL: terminating connection due to administrator command ----------------------------- |
|
|
Thank you for explaining in detail !! It means we can't kill any session from DB end so is it a bug or usual behavior. If it is a usual behavior so is any way to kill session from database end Thanks |
|
|
> It means we can't kill any session from DB end so is it a bug or usual behavior. > > If it is a usual behavior so is any way to kill session from database end Sorry for the late response. It is the correct behavior. The workaround is to run pg_terminate_backend() via Pgpool-II. Alternatively, you can upgrade to 4.3.x and set "failover_on_backend_shutdown" to "off": https://www.pgpool.net/docs/latest/en/html/runtime-config-failover.html#GUC-FAILOVER-ON-BACKEND-SHUTDOWN |
|
|
Thank you for your help !! Regards Gaurav |
|
|
One last question. can we disable failover in version below 4.3.x to overcome this behavior? If yes, can you please share what parameter need to be set for disable failover Regards Gaurav |
|
|
> One last question. can we disable failover in version below 4.3.x to overcome this behavior? > If yes, can you please share what parameter need to be set for disable failover If you set backend_flagX = 'DISALLOW_TO_FAILOVER', failover can be disabled. https://www.pgpool.net/docs/latest/en/html/runtime-config-backend-settings.html#GUC-BACKEND-FLAG Because 'DISALLOW_TO_FAILOVER' will disable failover even if the node is down, I do not recommend using this configuration. The 'DISALLOW_TO_FAILOVER' flag is actually added for PostgreSQL Aurora or Kubernetes platform. |
|
|
Have you resolved this issue? May I close this issue? |
|
|
Yes, Please close the ticket. Thank you all your support |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2022-05-10 22:33 | garg1982@gmail.com | New Issue | |
| 2022-05-11 10:45 | pengbo | Assigned To | => pengbo |
| 2022-05-11 10:45 | pengbo | Status | new => assigned |
| 2022-05-11 13:05 | pengbo | Note Added: 0004031 | |
| 2022-05-11 13:05 | pengbo | Status | assigned => feedback |
| 2022-05-11 14:54 | garg1982@gmail.com | Note Added: 0004032 | |
| 2022-05-11 14:54 | garg1982@gmail.com | Status | feedback => assigned |
| 2022-05-11 15:43 | pengbo | Note Added: 0004033 | |
| 2022-05-11 15:44 | pengbo | Status | assigned => feedback |
| 2022-05-11 21:02 | garg1982@gmail.com | Note Added: 0004034 | |
| 2022-05-11 21:02 | garg1982@gmail.com | Status | feedback => assigned |
| 2022-05-23 14:53 | pengbo | Note Added: 0004043 | |
| 2022-05-23 14:54 | pengbo | Note Edited: 0004043 | |
| 2022-05-23 14:55 | pengbo | Status | assigned => feedback |
| 2022-05-23 15:05 | garg1982@gmail.com | Note Added: 0004044 | |
| 2022-05-23 15:05 | garg1982@gmail.com | Status | feedback => assigned |
| 2022-05-23 16:06 | garg1982@gmail.com | Note Added: 0004045 | |
| 2022-05-24 14:35 | pengbo | Note Added: 0004047 | |
| 2022-05-24 14:35 | pengbo | Status | assigned => feedback |
| 2022-05-24 14:36 | pengbo | Note Edited: 0004047 | |
| 2022-06-02 13:50 | administrator | Note Added: 0004051 | |
| 2022-06-02 23:15 | garg1982@gmail.com | Note Added: 0004056 | |
| 2022-06-02 23:15 | garg1982@gmail.com | Status | feedback => assigned |
| 2022-06-03 22:16 | administrator | Status | assigned => closed |
