View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000763 | Pgpool-II | General | public | 2022-08-04 01:09 | 2022-08-19 21:51 |
| Reporter | henri | Assigned To | pengbo | ||
| Priority | high | Severity | major | Reproducibility | always |
| Status | assigned | Resolution | open | ||
| Product Version | 4.2.9 | ||||
| Summary | 0000763: help troubleshooting verify_backend_node_status: primary 0 owns only 0 standbys out of 2 | ||||
| Description | Looking for help how to troubleshoot the error below . Is the error below of concern? 2022-08-03 11:08:51: pid 867771: LOG: new IPC connection received 2022-08-03 11:08:51: pid 868230: LOG: verify_backend_node_status: primary 0 does not connect to standby 1 2022-08-03 11:08:51: pid 868230: LOG: verify_backend_node_status: primary 0 does not connect to standby 2 2022-08-03 11:08:51: pid 868230: LOG: verify_backend_node_status: primary 0 owns only 0 standbys out of 2 2022-08-03 11:09:01: pid 867771: LOG: new IPC connection received Any help would be appreciated ... | ||||
| Tags | No tags attached. | ||||
|
|
It seems the primary can't connect to standby nodes. Please check the configuration of streaming replication between primary and standby servers. |
|
|
Is the "streaming replication between primary and standby servers" reference related to PostgreSQL replication via the WAL from the primary (W/R) the the standby PostgreSQL systems (R) in a 3 nodes cluster config select * from pg_replication_slots; on the primary PostgreSQL system SELECT * FROM pg_stat_wal_receiver; on the 2 standby PostgreSQL systems I guess my question is there a pgpool CLI command or PostgreSQL select query we could execute that would help us identify where the issue could be ? What other error string should we be looking for in pgpool.log or in postgresql.log |
|
|
The error messages show that primary can't connect to standby servers. You can connect to PostgreSQL primary directly and check the connectivity using the following command: select * from pg_stat_replication; You can also connect to pgpool and run "show pool_nodes;" to check the backend servers status. https://www.pgpool.net/docs/latest/en/html/sql-show-pool-nodes.html |
|
|
I spent a bit of time with our DBA today, and confirmed PostgreSQL replication is being performed and everything looks normal. We created a test database with some data and everything get replicated as we can see the data on the 2 standby PostgreSQL systems . We tool a look at the replication stats and they seem normal. We could not see any obvious errors in the progresql.log file either. show pool_nodes looks normal pcp_watchdog_info -p 9898 -U pgpool -v looks normal. What else can we check ? Is there a table / fields in PostgreSQL we could further check for something? We stopped pgpool (standby 2, standby 1 and primary) and PostgreSQL services and restarted pgpool services in the reverse a few times. Same error keeps popping up. Trying to prevent having to enable debug mode in pgpool.conf file. Any addition thoughts ? |
|
|
Could you share your pgpool.conf and pgpool log? |
|
|
Given this instance of this cluster reside within a customer environment, We have not receive the ok to share any config information yet. Hence why I asking for internal troubleshooting tips , if any. |
|
|
> Hence why I asking for internal troubleshooting tips , if any. Which version of PostgreSQL are you using? Pgpool checks the connectivity between primary and standby by using "pg_stat_wal_receiver" which requires PostgreSQL 9.6 or later. If your PostgreSQL is 9.5 or earlier, Pgpool-II can not find any standby servers. Could you check the result of "show pool_nodes" command? You should check if "role", "replication_delay" and "replication_state" columns are displayed in "standby", "0" and "streaming". test=# show pool_nodes; 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 | localhost | 11002 | up | up | 0.500000 | primary | primary | 0 | false | 0 | | | 2022- 08-16 16:36:12 1 | localhost | 11003 | up | up | 0.500000 | standby | standby | 0 | true | 0 | streaming | async | 2022- 08-16 16:36:12 (2 rows) |
|
|
PostgreSQL version 12 is being utilize, We may have found the problem, Doing more test on a test cluster. Will advise . |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2022-08-04 01:09 | henri | New Issue | |
| 2022-08-04 10:28 | pengbo | Note Added: 0004105 | |
| 2022-08-04 10:29 | pengbo | Assigned To | => pengbo |
| 2022-08-04 10:29 | pengbo | Status | new => feedback |
| 2022-08-04 23:58 | henri | Note Added: 0004106 | |
| 2022-08-04 23:58 | henri | Status | feedback => assigned |
| 2022-08-05 01:10 | pengbo | Note Added: 0004107 | |
| 2022-08-05 01:10 | pengbo | Status | assigned => feedback |
| 2022-08-05 08:52 | henri | Note Added: 0004108 | |
| 2022-08-05 08:52 | henri | Status | feedback => assigned |
| 2022-08-08 10:51 | pengbo | Note Added: 0004109 | |
| 2022-08-08 10:51 | pengbo | Status | assigned => feedback |
| 2022-08-13 03:37 | henri | Note Added: 0004110 | |
| 2022-08-13 03:37 | henri | Status | feedback => assigned |
| 2022-08-16 16:41 | pengbo | Note Added: 0004113 | |
| 2022-08-16 16:42 | pengbo | Status | assigned => feedback |
| 2022-08-19 21:51 | henri | Note Added: 0004114 | |
| 2022-08-19 21:51 | henri | Status | feedback => assigned |
