PostgreSQL: How libpq Helps Configure Multi-Backend Connection Preferences

In this article, the author explains how libpq can support configuring multiple backend connection preferences, including read-only, prefer-read, and prefer-write.

Background

PostgreSQL client drivers include C, ECPG, Perl, Java, C++, JavaScript, .NET, Tcl, Go, ODBC, and Python, among others. Some of these drivers support the configuration of multiple backends.

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.

targetServerType = String

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.

hostRecheckSeconds = int

Control how long in seconds the knowledge about a host state is cached in JVM-wide global cache. The default value is 10 seconds.

loadBalanceHosts = boolean

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.

target_session_attrs

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.

Example:

postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp

transaction_read_only: A session status parameter that decides whether to allow sending write SQL requests.

Standby returns:

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 read-only, prefer-read, or prefer-write Patch

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.

Community

PostgreSQL: How libpq Helps Configure Multi-Backend Connection Preferences

Background

libpq read-only, prefer-read, or prefer-write Patch

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Database for FinTech Solution

Oracle Database Migration Solution

Database Migration Solution