I have a PostreSQL cluster where I have a master and a slave. I have seen that pgx/pgxpool supports simultaneous connection to multiple hosts, but I do not understand how requests will be executed then. Can pgx understand where the master is? (I didn't find that he could do that.) And will pgx work at all if I have a master and a slave, and only readonly requests can be executed on the slave.
Please help me figure out the issue
I had an idea that the requests would be scattered round robin, but I did not find confirmation. And I don't know what will happen if the write request gets to the slave.
I understand roughly how you can write a wrapper around a pool so that it supports such an architecture, but I don't understand whether it is necessary or not
regarding multiple hosts: pgx reimplements the same rules on connection string attributes as postgresql's implementation.
PostgreSQL doc mentions a round robin until one connection matches the criteria (connection is established, and target_session_attrs is matched)
link to postgres doc:
It is possible to specify multiple hosts to connect to, so that they are tried in the given order.
target_session_attrs=primary or target_session_attrs=read-write)This option determines whether the session must have certain properties to be acceptable. It's typically used in combination with multiple host names to select the first acceptable alternative among several hosts.
On the pgx side of things:
The doc pgx.ParseConfig will lead you (2 links) to pgconn.ParseConfig
The documentation explicitly mentions multiple hosts support, the support for target_session_attrs is less explicit -- it is implied by the fact that PGTARGETSESSIONATTRS is supported, and by the link to connection string attributes -- but it is supported.
In pgx code, the validation for target_session_attrs at connection goes through one of the ValidateConnectTargetSessionAttrs* functions