search

pgadmin-org / pgadmin4

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.
https://www.pgadmin.org
Other
2.51k stars 651 forks source link

Target Session Attribute Primary or Target Session Attribute Read-Write Fail to Connect #7832

Closed prime-minister-of-fun closed 1 month ago

prime-minister-of-fun commented 2 months ago

Describe the bug Pgadmin4 is running in desktop mode, version 8.9.

Using a comma separated string of hosts and adding the "Target Session Attribute" parameter to either read-write or primary returns an error message. Pgadmin4 is unable to connect to the primary.
Error message: 2024-08-20 11:56:34,972: ERROR pgadmin: Could not connect to server(#3) - 'my-staging-dev'. Error: connection failed: connection to server at "10.3.10.71", port 5432 failed: server is in hot standby mode

To Reproduce

Steps to reproduce the behavior:

  1. Create a new connection.
  2. Add a comma separated list of host names in the Host name/address field
  3. Add "Target Session Attribute" to Connection Parameters.
  4. Use the drop-down menu to set the Value to either read-write or primary
  5. Save connection.
  6. Attempt to connect to the cluster.

Expected behavior

I expect to be connected to the primary node.

Error message

2024-08-20 11:56:34,972: ERROR pgadmin: Could not connect to server(#3) - 'my-staging-dev'. Error: connection failed: connection to server at "10.3.10.71", port 5432 failed: server is in hot standby mode

Desktop (please complete the following information):

Additional context In case it matters, this is a multi-user remote desktop session. The error appears if one or more users is on the VM, and using pgadmin. It's an ordeal to get a new version approved for installation. So, if there's a workaround, that would be great.

prime-minister-of-fun commented 2 months ago

I tried 8.10 and had the same problem.

I narrowed the problem down. The nature of the issue needs a little explaining.

Patroni is the orchestrator of the PostgreSQL cluster. One of the nodes had fallen behind too far and could not rejoin the cluster. What happens in this scenario is PostgreSQL server is listening, but not able to serve read-only queries. The PostgreSQL server responds with the "hot standby" error.

==========|---> host2, replication broken, server listening Host1: leader =| ==========|---> host3, replication working, server listening

It remains the case, I only want to connect to the primary.

Maybe this should be a feature request to ignore servers generating "hot standby" errors when using "Target Session Attribute": read-write or primary.

adityatoshniwal commented 2 months ago

@prime-minister-of-fun Are you able to connect using psql? The error is coming from PostgreSQL and may not be a pgAdmin issue. Secondly, pgAdmin doesn't intercept PostgreSQL errors. Errors can be in any language or may change with time.

yogeshmahajan-1903 commented 1 month ago

No response from author , hence closing the issue.