View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000459 | Pgpool-II | Bug | public | 2019-01-26 03:07 | 2019-01-27 17:04 |
| Reporter | adityakamana | Assigned To | |||
| Priority | urgent | Severity | major | Reproducibility | always |
| Status | closed | Resolution | open | ||
| Product Version | 4.0.2 | ||||
| Summary | 0000459: pgpool sending deletes to standby | ||||
| Description | Hello, We have pgpool 4.0.2 installed with one master and one standby server. "load_balance_mode" is on with lb_weight as 0.5 for each host. This is the setting I have for the transactions, disable_load_balance_on_write = 'trans_transaction'. I still see deletes and updates coming to the standby server: postgresql-20190124-22.log:2019-01-24 22:19:02 GMT [11267]: [39-1] user=hdap_user,db=hdap 10.62.237.121 CONTEXT: SQL statement "UPDATE device.device_kits postgresql-20190124-22.log:2019-01-24 22:19:18 GMT [11293]: [138-1] user=hdap_user,db=hdap 10.62.237.121 ERROR: cannot execute UPDATE in a read-only transaction postgresql-20190124-22.log:2019-01-24 22:19:18 GMT [11293]: [139-1] user=hdap_user,db=hdap 10.62.237.121 CONTEXT: SQL statement "UPDATE device.device_kits postgresql-20190124-22.log:2019-01-24 22:23:55 GMT [12223]: [31-1] user=hdap_user,db=hdap 10.62.237.121 ERROR: cannot execute UPDATE in a read-only transaction postgresql-20190124-22.log:2019-01-24 22:23:55 GMT [12223]: [32-1] user=hdap_user,db=hdap 10.62.237.121 CONTEXT: SQL statement "UPDATE device.device_kits postgresql-20190124-22.log:2019-01-24 22:23:55 GMT [12225]: [31-1] user=hdap_user,db=hdap 10.62.237.45 ERROR: cannot execute UPDATE in a read-only transaction postgresql-20190124-22.log:2019-01-24 22:23:55 GMT [12225]: [32-1] user=hdap_user,db=hdap 10.62.237.45 CONTEXT: SQL statement "UPDATE device.device_kits DELETES: postgresql-20190124-22.log:2019-01-24 22:17:19 GMT [10866]: [83-1] user=hdap_user,db=hdap 10.62.237.45 ERROR: cannot execute UPDATE in a read-only transaction postgresql-20190124-22.log:2019-01-24 22:17:19 GMT [10866]: [84-1] user=hdap_user,db=hdap 10.62.237.45 CONTEXT: SQL statement "UPDATE users.users SET User_Status_ID = userStatusId, Login_Name =loginName, First_Name = firstName,Last_Name = lastName, Email_Address = useremail, postgresql-20190124-22.log:2019-01-24 22:19:02 GMT [11267]: [38-1] user=hdap_user,db=hdap 10.62.237.121 ERROR: cannot execute UPDATE in a read-only transaction postgresql-20190124-22.log:2019-01-24 22:19:02 GMT [11267]: [39-1] user=hdap_user,db=hdap 10.62.237.121 CONTEXT: SQL statement "UPDATE device.device_kits postgresql-20190124-22.log:2019-01-24 22:19:18 GMT [11293]: [138-1] user=hdap_user,db=hdap 10.62.237.121 ERROR: cannot execute UPDATE in a read-only transaction postgresql-20190124-22.log:2019-01-24 22:19:18 GMT [11293]: [139-1] user=hdap_user,db=hdap 10.62.237.121 CONTEXT: SQL statement "UPDATE device.device_kits postgresql-20190124-22.log:2019-01-24 22:23:55 GMT [12223]: [31-1] user=hdap_user,db=hdap 10.62.237.121 ERROR: cannot execute UPDATE in a read-only transaction postgresql-20190124-22.log:2019-01-24 22:23:55 GMT [12223]: [32-1] user=hdap_user,db=hdap 10.62.237.121 CONTEXT: SQL statement "UPDATE device.device_kits postgresql-20190124-22.log:2019-01-24 22:23:55 GMT [12225]: [31-1] user=hdap_user,db=hdap 10.62.237.45 ERROR: cannot execute UPDATE in a read-only transaction postgresql-20190124-22.log:2019-01-24 22:23:55 GMT [12225]: [32-1] user=hdap_user,db=hdap 10.62.237.45 CONTEXT: SQL statement "UPDATE device.device_kits Not sure what is wrong in my configuration. Please let me know how I can fix this. | ||||
| Tags | No tags attached. | ||||
|
|
|
|
|
My guess is Pgpool-II fails to find the primary node. To find the cause, can you share the pgpool.log? |
|
|
I have attached the log file. I saw this issue when I set the "disable_load_balance_on_write" to "trans_transaction" or "transaction". When it is set to "always" looks like this is not happening much. I also don't see an issue with pgpool identifying primary and standby. node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change ---------+----------------------------+------+--------+-----------+---------+------------+-------------------+-------------------+--------------------- 0 | qa1.com | 6543 | up | 0.500000 | primary | 157828 | false | 0 | 2019-01-25 18:45:53 1 | qa2.com | 6543 | up | 0.500000 | standby | 0 | true | 0 | 2019-01-25 18:45:53 |
|
|
We have pgpool installed on 3 nodes sending traffic to the database hosts. |
|
|
Close this issue by reporter's request. See issue 460 instead. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2019-01-26 03:07 | adityakamana | New Issue | |
| 2019-01-26 06:47 | adityakamana | File Added: pgpool.conf | |
| 2019-01-27 03:04 | adityakamana | Issue cloned: 0000460 | |
| 2019-01-27 09:09 | t-ishii | Note Added: 0002335 | |
| 2019-01-27 09:26 | adityakamana | File Added: pgpool.log | |
| 2019-01-27 09:26 | adityakamana | Note Added: 0002336 | |
| 2019-01-27 09:27 | adityakamana | Note Added: 0002337 | |
| 2019-01-27 17:04 | t-ishii | Note Added: 0002344 | |
| 2019-01-27 17:04 | t-ishii | Status | new => closed |
