View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000828 | Pgpool-II | Bug | public | 2024-02-21 22:07 | 2024-02-27 20:13 |
| Reporter | amodkakade | Assigned To | t-ishii | ||
| Priority | high | Severity | major | Reproducibility | always |
| Status | assigned | Resolution | open | ||
| Product Version | 4.5.0 | ||||
| Summary | 0000828: PgPool doesnt perform load balancing of RW queries after switchover of postgreSQL database nodes | ||||
| Description | Post PostgreSQL database switchover(changing the replication role from master to slave), all the write transactions to database using PGPOOL port fail with error "ERROR: cannot execute INSERT in a read-only transaction". However all the connections to direct database port complete successful transaction. | ||||
| Steps To Reproduce | Issue reproduction steps 1. Deploy PostgreSQL with HA architecture using native postgreSQL streaming replication. Configure Pgpool and enable load balancing on it & enabling health checks 2. Test the insert/update/delete type of RW queries against PGPOOL port. They execute successful 3. Occasionally, when a master PostgreSQL node switches to slave and slave node to master due to a restart or any other reason, Pgpool continue to send all queries to the previous master, which is now acting as a slave. This results in the following error:ERROR: cannot execute INSERT in a read-only transaction This error occurs because Pgpool attempts to execute INSERT and UPDATE queries on a PostgreSQL instance that is in a read-only state. | ||||
| Tags | "ERROR: cannot execute INSERT in a read-only transaction". | ||||
|
|
> 3. Occasionally, when a master PostgreSQL node switches to slave and slave node to master due to a restart or any other reason, PostgreSQL shutdown must be noticed by pgpool's health check (or any other error detection mechanism built in pgpool). Moreover PostgreSQL database role changing (primary -> standby or standby -> primary) should be handled by pgpool through failover script and follow_primary_command (if any). Did you promote PostgreSQL by hand, or using other tool? |
|
|
Hi, This postgresql streaming replication is monitored via the pg_auto_failover package and the switchovers and failovers are also managed by pg_auto_failover. The promotion is executed using pg_autoctl perform promotion command. I have enabled the health checks using health_check_period parameter, however that is not helping here. I am not using the pgpool failover script or follow_primary command. Attached is the pgpool.conf for your reference. pgpool.conf (1,717 bytes)
listen_addresses = '*' backend_weight0 = 0 backend_flag0 = 'DISALLOW_TO_FAILOVER' backend_weight1 = 1 backend_flag1 = 'DISALLOW_TO_FAILOVER' log_hostname = on log_statement = on log_per_node_statement = on log_client_messages = on pid_file_name = 'pgpool.pid' sr_check_period = 0 sr_check_database = 'postgres' health_check_period = 1 health_check_database = 'postgres' hostname0 = '' allow_clear_text_frontend_auth = 'on' logging_collector = 'on' log_filename = 'btpgpool-%d_%H%M%S.log' client_min_messages = 'error' log_min_messages = 'FATAL' log_per_node_statement = 'on' log_client_messages = 'on' log_hostname = 'on' log_connections = 'on' log_disconnections = 'on' log_error_verbosity = 'verbose' client_idle_limit = 1800 connection_life_time = 0 max_pool = 8 log_truncate_on_rotation = 'on' ssl = 'on' enable_pool_hba = 'on' ssl_ca_cert = '/var/lib/pgsql/pgpool/cert/ca.crt' ssl_prefer_server_ciphers = on ssl_key = '/var/lib/pgsql/pgpool/cert/tls.key' ssl_cert = '/var/lib/pgsql/pgpool/cert/tls.crt' failover_on_backend_error = 'off' backend_data_directory0 = '/apps/pgdata01/15/51901' backend_data_directory1 = '/apps/pgdata01/15/51901' backend_data_directory2 = '/apps/pgdata01/15/51901' backend_application_name0= 'primary-vm-pg' backend_application_name1= 'stby-vm-pg' backend_application_name2= 'stby-vm-pg' backend_hostname0 = '1********1' backend_port0 = 5***1 backend_hostname1 = '1********5' backend_port1 = 5***1 backend_hostname2 = '1********6' backend_port2 = 5***1 port = 5***2 log_directory = '/var/lib/pgsql/pgpool/log' load_balance_mode = 'on' sr_check_user = 'postgres' sr_check_password = '***' health_check_user = 'postgres' health_check_password = '***' num_init_children = 100 |
|
|
Then that's an expected behavior. Pgpool-II is not designed to run with other failover control tools including pg_auto_failover. |
|
|
Ah i understand. Thanks. Currently the load balancing starts to work once I restart all the pgPool processes using pgpool stop and pgpool start command. Can you suggest any best possible workaround to handle this situation? |
|
|
I think restarting pgpool is the only option. |
|
|
All right. Thanks for clarification. Truly appreciated. I am happy to close this thread Amod |
|
|
Thank you for understanding. Maybe pgpool should provide a way to recognize status changing in backend (primary/standby change, up/down change) without restarting pgpool. I will think about the idea for feature release of pgpool. |
|
|
Yes that feature of "pgpool should provide a way to recognize status changing in backend (primary/standby change, up/down change) without restarting pgpool" would be very useful in situation where the pgPool is being used only as load balancer and Connection pooling agent. Thanks Amod |
|
|
Wait. Maybe you should be able to use pcp_promote_node command to let pgpool recognize the new primary. https://www.pgpool.net/docs/latest/en/html/pcp-promote-node.html |
|
|
I am not able to run that command. It errs out as shown [postgres@tp**3 conf]$ pcp_pool_status -p 5***2 -U postgres -h 1********1 ERROR: unable to read data from socket. [postgres@tp**3 conf]$ |
|
|
Probably wrong port number or IP. Can you run other pcp command such as pcp_node_info? |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2024-02-21 22:07 | amodkakade | New Issue | |
| 2024-02-21 22:07 | amodkakade | Tag Attached: "ERROR: cannot execute INSERT in a read-only transaction". | |
| 2024-02-22 08:31 | t-ishii | Assigned To | => t-ishii |
| 2024-02-22 08:31 | t-ishii | Status | new => assigned |
| 2024-02-22 08:38 | t-ishii | Note Added: 0004482 | |
| 2024-02-22 08:38 | t-ishii | Status | assigned => feedback |
| 2024-02-22 13:28 | amodkakade | Note Added: 0004483 | |
| 2024-02-22 13:28 | amodkakade | File Added: pgpool.conf | |
| 2024-02-22 13:28 | amodkakade | Status | feedback => assigned |
| 2024-02-22 13:34 | t-ishii | Note Added: 0004484 | |
| 2024-02-22 13:37 | amodkakade | Note Added: 0004485 | |
| 2024-02-22 13:50 | t-ishii | Note Added: 0004486 | |
| 2024-02-22 14:20 | amodkakade | Note Added: 0004487 | |
| 2024-02-22 15:13 | t-ishii | Note Added: 0004488 | |
| 2024-02-22 17:54 | amodkakade | Note Added: 0004489 | |
| 2024-02-27 13:02 | t-ishii | Note Added: 0004490 | |
| 2024-02-27 18:23 | amodkakade | Note Added: 0004491 | |
| 2024-02-27 20:13 | t-ishii | Note Added: 0004492 |
