Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog How to Set Up libpq Failover and Load Balancing for JDBC Driver Layer

How to Set Up libpq Failover and Load Balancing for JDBC Driver Layer

In this article, the author discusses libpq failover and load balancing JDBC driver layer in PostgreSQL.

By digoal

Background

Since PostgreSQL 10, the libpq driver layer has been supporting simple failover with simple setups. Similarly, the JDBC driver supports simple failover and load balancing, also with simple setups.

Such functions, with a few link configurations, are available for clients who use JDBC or libpq.

Failover — libpq Documents

It is possible to specify multiple host components, each with an optional port component, in a single URI. A URI of the form postgresql://host1:port1,host2:port2,host3:port3/ is equivalent to a connection string of the form host=host1,host2,host3 port=port1,port2,port3. Each host will be tried in turn until a connection is successfully established.

Specifying Multiple Hosts

It is possible to specify multiple hosts to connect to so that they are tried in the given order. In the Keyword/Value format, the host, hostaddr, and port options accept a comma-separated list of values. You must give the same number of elements in each option specified, such that, for example, the first hostaddr corresponds to the first hostname, the second hostaddr corresponds to the second hostname, and so forth. As an exception, if you specify only one port, it applies to all the hosts.

In the connection URI format, you can list multiple host:port pairs separated by commas in the host component of the URI.

In either format, a single hostname can translate to multiple network addresses. A typical example of this is a host that has both an IPv4 and an IPv6 address.

When you specify multiple hosts, or when a single hostname is translated to multiple addresses, all the hosts and addresses will be tried in order until one succeeds. The connection fails if none of the hosts can be reached. If a connection is established successfully but authentication fails, the remaining hosts in the list will not be tried.

If you use a password file, you can have different passwords for different hosts. All the other connection options are the same for every host in the list; it is impossible to specify different usernames for different hosts.

When using multi-host for configuration, you can only configure host:port pairs with multiple passwords. You can configure other parameters with only one password. All host:port pairs share the same password. If you configure different passwords on multiple hosts, it is necessary to point them out in the password file. Otherwise, a single shared password configured in URI will cause other connection errors.

Passfile

It specifies the name of the file used to store passwords (see Section 33.15). Defaults to ~/.pgpass, or %APPDATA%\postgresql\pgpass.conf on Microsoft Windows. No error is reported if this file does not exist.

target_session_attrs

If you set this parameter 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. The connection will be closed if it returns “on”.

If you specify 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.

connect_timeout

It specified the maximum wait for a connection in seconds (write as a decimal integer, e.g., 10). Zero, negative, or not specified means wait indefinitely. The minimum allowed timeout is 2 seconds; therefore, a value of 1 is interpreted as 2. This timeout applies separately to each hostname or IP address. For example, if you specify two hosts and connect_timeout is 5, each host will time out if no connection is made within 5 seconds, so the total time spent waiting for a connection might be up to 10 seconds.

Failover and Load Balance — JDBC Document

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.

targetServerType = String

It allows opening connections to only servers with the required state. The allowed values are any, master, slave, secondary, preferSlave, and preferSecondary. The master/slave distinction is currently done by observing if the server allows writes. The value preferSecondary tries to connect to secondary if any are available. Otherwise, it falls back to connect to the master.

jdbc:postgresql://node1,node2,node3/accounting?targetServerType=master.  
  
jdbc:postgresql://node1,node2,node3/accounting?targetServerType=preferSlave&loadBalanceHosts=true

Example: psql Command Connecting Multiple Hosts

psql uses libpq driver to link to the database and supports multi-master URI, as shown in these examples.

Environment:

192.168.70.10 is the master  
192.168.70.20 is the first standby  
192.168.70.30 is the second standby

View the address and port of the linked database through inet_server_addr() and inet_server_port(). You can use Pg_is_in_recovery() to see whether the database is in recovery, which database it is, what role it currently belongs to, and whether it is read-write or in recovery.

$psql -h 192.168.70.10  
Password for user postgres:  
psql (11.5)  
Type "help" for help.  
   
postgres=# select inet_server_addr() as "connected_to";  
connected_to  
---------------  
192.168.70.10   主库  
(1 row)  
   
postgres=# select client_addr, write_lag,flush_lag,replay_lag from pg_stat_replication;  
client_addr | write_lag | flush_lag | replay_lag  
---------------+-----------------+-----------------+-----------------  
192.168.70.20 | 00:00:00.058204 | 00:00:00.058617 | 00:00:00.058619  
192.168.70.30 | 00:00:00.03639 | 00:00:00.036689 | 00:00:00.036693  
(2 rows)

The following settings are connected to three databases. Specifying the target_session_attrs=read-write requires connection to a read-write library.

$ psql 'postgres://192.168.70.20:5432,192.168.70.10:5432,192.168.70.30:5432/postgres?target_session_attrs=read-write' -c "select inet_server_addr()"  
Password for user postgres:  
inet_server_addr  
------------------  
192.168.70.10  
(1 row)

Specify the target_session_attrs=ANY to link to any library selected at random from all configurations, as you can see by checking inet_server_addr:

$ psql 'postgres://192.168.70.20:5432,192.168.70.10:5432,192.168.70.30:5432/postgres?target_session_attrs=any' -c "select inet_server_addr()"  
inet_server_addr  
------------------  
192.168.70.20  
(1 row)

Or,

$ psql 'postgres://192.168.70.10:5432,192.168.70.20:5432,192.168.70.30:5432/postgres?target_session_attrs=any' -c "select inet_server_addr()"  
inet_server_addr  
------------------  
192.168.70.10  
(1 row)

Disable the main library. Specify target_session_attrs=ANY to link to any library, namely the second one randomly selected from all configurations.

-- On Server : 192.168.70.10  
  
$ pg_ctl -D $PGDATA stop -mf  
waiting for server to shut down.... done  
server stopped  
[postgres@pg1]$ psql 'postgres://192.168.70.10:5432,192.168.70.20:5432,192.168.70.30:5432/postgres?target_session_attrs=any' -c "select inet_server_addr()"  
inet_server_addr  
------------------  
192.168.70.20  
(1 row)

Example: Python Connecting Multiple Hosts

$ cat pg_conn.py  
import psycopg2  
conn = psycopg2.connect(database="postgres",host="192.168.70.10,192.168.70.20,192.168.70.30", user="postgres", password="secret", port="5432", target_session_attrs="read-write")  
cur = conn.cursor()  
cur.execute("select pg_is_in_recovery(), inet_server_addr()")  
row = cur.fetchone()  
print "recovery =",row[0]  
print "server =",row[1]  
  
$ python pg_conn.py  
recovery = False  
server = 192.168.70.10

Example: PHP Connecting Multiple Hosts

# cat pg_conn.php  
<?php  
$conn = pg_connect("host=192.168.70.10,192.168.70.20,192.168.70.30 port=5432 dbname=postgres user=postgres password=secret target_session_attrs=read-write") or die("Could not connect");  
$status = pg_connection_status($conn);  
if ($status === PGSQL_CONNECTION_OK) {  
print "Connection status ok\n";  
} else {  
print "Connection status bad\n";  
}  
$sql = pg_query($conn, "select pg_is_in_recovery()");  
while ($row = pg_fetch_row($sql)) {  
echo "Recovery-status: $row[0]\n";  
}  
?>  
  
$ php -f pg_conn.php  
Connection status ok  
Recovery-status: f  
Server: 192.168.70.10

Pay Attention to the Driver

Multi-host is supported since PostgreSQL 10, so be sure to use a driver above 10.

The libpq dependencies for the PHP or python driver are as follows. The result is based on libpq 11, meaning that this version supports multi-host.

# yum info python2-psycopg2-2.8.3-2.rhel7.x86_64 | grep repo  
From repo : pgdg11  
  
# rpm -q --requires python2-psycopg2-2.8.3-2.rhel7.x86_64 | grep libpq  
libpq.so.5()(64bit)  
  
# rpm -q --requires php-pgsql-5.4.16-46.el7 | grep libpq  
libpq.so.5()(64bit)  
  
# locate libpq.so.5  
/usr/pgsql-11/lib/libpq.so.5

References

PostgreSQL Database Load Balancing JDBC libpq Database Failover
0 0 0
Share on

Read previous post:

How to Write into a Database Using reWriteBatchedInserts Parameter

Read next post:

How to Use pg_receivewal with Synchronous Replication to Mirror Redo Logs

digoal

282 posts | 25 followers

You may also like

Comments

digoal

282 posts | 25 followers

Related Products

More Posts by digoal

See All