For more details, you can refer https://www.postgresql.org/docs/devel/external-interfaces.html
Why configure multiple backends?
For example, the backend features the multi-master architecture and the master-slave architecture.
Which database node should be chosen when a client requests for connection?
If the connection response of some nodes times out, will the client continue to connect to other backends or report an error?
What backend should be configured when the business logic requires read/write separation or a connection request to connect to certain nodes preferentially?
JDBC and libpq support multi-backend configurations. But, JDBC supports more backends.
PgJDBC uses three parameters (since 2014):
1) targetServerType=(any | master | secondary | preferSecondary). Default is "any". When set to "master", it will look for a "read-write" server. If set to "preferSecondary" it would search for "read-only" server first, then fall back to master, and so on.
2) loadBalanceHosts=(true | false). PgJDBC enables load-balancing across servers provided in the connection URL. When set to "false", PgJDBC tries connections in order. Otherwise, it shuffles the connections.
3) hostRecheckSeconds=int. PgJDBC caches the "read/write" status of a host:port combination, so it doesn’t re-check the status if multiple connections are created within hostRecheckSeconds timeframe.
Allow opening connections to servers only with required state, and the allowed values are any, master, slave, secondary, preferSlave and preferSecondary. The distinction between master and slave is currently done by observing if the server allows writes. The value preferSecondary tries to connect to secondary if any are available, otherwise allows falls back to connect to the master.
Control how long in seconds the knowledge about a host state is cached in JVM-wide global cache. The default value is 10 seconds.
In default mode (disabled), hosts are connected in the given order. If enabled, hosts are chosen randomly from the set of suitable candidates.
libpq also supports multi-backend configuration but only supports any and read-write modes for backend selection. For more details, you can refer https://www.postgresql.org/docs/devel/libpq-connect.html
If target_session_attrs is set to "any", any available backend can be used.
If it is set to "read-write", the master node (transaction_read_only = false) is used.
If this parameter is set to read-write, only a connection in which read-write transactions are accepted by default is considered acceptable. The query SHOW transaction_read_only will be sent upon any successful connection; if it returns “on”, the connection will be closed. If you specified multiple hosts in the connection string, any remaining servers will be tried just as if the connection attempt had failed. The default value of this parameter, any, regards all connections as acceptable.
transaction_read_only: A session status parameter that decides whether to allow sending write SQL requests.
postgres=# show default_transaction_read_only ; default_transaction_read_only ------------------------------- off (1 row) postgres=# show transaction_read_only ; transaction_read_only ----------------------- on (1 row) postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row)
Primary and master return:
postgres=# show default_transaction_read_only ; default_transaction_read_only ------------------------------- off (1 row) postgres=# show transaction_read_only ; transaction_read_only ----------------------- off (1 row) postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row)
libpq does not support the preferential configuration of read-only nodes. This patch supports the configuration of read-only, prefer-read, or prefer-write.
read-only: Only read-only backend is allowed.
prefer-read: The read-only backend is preferred.
prefer-write: The writable backend is preferred.
The patch applies, builds, and passes "make check-world".
The "prefer-read" functionality is desirable: It is exactly what you need if you want to use replication for load balancing and your application supports different database connections for reading and writing queries.
"read-only" does not have a clear use case, in our opinion.
With the patch, PostgreSQL behaves as expected if you have a primary and a standby and run:
psql "host=/tmp,/tmp port=5433,5434 target_session_attrs=prefer-read"
But if you stop the standby (port 5434), libpq goes into an endless loop.
Concerning the code, the documentation needs some attention.
Suggestion: If this parameter is set to
<literal>prefer-read</literal>, connections where
<literal>SHOW transaction_read_only</literal> returns “off” are preferred. If no such connection can be found, a connection that allows read-write transactions will be accepted.
The construction with "read_write_host_index" makes the code even more complicated than it already is.
What about keeping the first successful connection open and storing it in a variable if we are in "prefer-read" mode? If we get the read-only connection we desire, we close that cached connection. Otherwise, we use it.
digoal - May 27, 2021
Alibaba Clouder - June 10, 2019
Alibaba Clouder - November 18, 2019
digoal - April 21, 2021
Alibaba Clouder - December 20, 2019
zhuodao - July 30, 2020
Fully managed and less trouble database servicesLearn More
Leverage cloud-native database solutions dedicated for FinTech.Learn More
Migrate your legacy Oracle databases to Alibaba Cloud to save on long-term costs and take advantage of improved scalability, reliability, robust security, high performance, and cloud-native features.Learn More
Migrating to fully managed cloud databases brings a host of benefits including scalability, reliability, and cost efficiency.Learn More
More Posts by digoal