Using Client Drivers to Implement Failover and Read/Write Splitting

Since PostgreSQL 10 (libpq.so.5.10), libpq has been supporting failover and read/write splitting, and Java Database Connectivity (JDBC) has been supporting read/write splitting, failover, and load balancing.

PostgreSQL client drivers are backward compatible. Even RDS for PostgreSQL 9.5 and 9.6 instances can be connected through the libpq driver of the latest version to implement failover.

In this section, failover refers to the failover of read-only workloads.

libpq is a C application programming interface (API) to PostgreSQL. libpq is a set of library functions that allow client programs to pass queries to the PostgreSQL backend server and to receive the results of these queries.
JDBC is an API used in Java to define how client programs access databases. In PostgreSQL, JDBC supports failover and load balancing.

**Table 1** Functions supported by libpq and JDBC
Driver	Read/Write Splitting	Load Balancing	Failover
libpq	√	×	√
JDBC	√	√	√

Using libpq for Failover and Read/Write Splitting

You can use libpq functions to connect to multiple databases. If one database fails, workloads are automatically switched to another available database.

postgresql://[user[:password]@][netloc][:port][,...][/dbname][?param1=value1&...]

Example: Connect to one primary RDS for PostgreSQL instance and two read replicas. Read requests will not fail as long as there is at least one available instance.

postgres://10.6.10.194:5432,10.6.2.98:5432,10.6.9.43:5432/postgres?target_session_attrs=any

**Table 2** Parameter description
Parameter	Description	Example Value
target_session_attrs	Type of the database to be connected.	any (default): libpq can connect to any database. If the connection is interrupted due to a fault in the database, libpq will attempt to connect to another database to implement failover. read-write: libpq can only connect to a database that supports both read and write. libpq attempts a connection to the first database you specified. If this database supports only read or write operations, libpq disconnects from it and attempts to connect to the second one and so on until it connects to a database that supports both read and write. read-only: libpq can only connect to a read-only database. libpq attempts a connection to the first database you specified. If this database is not a read-only database, libpq disconnects from it and attempts to connect to the second one and so on until it connects to a read-only database. This value is not supported in RDS for PostgreSQL 13 (libpq.so.5.13) or earlier versions.

For details about libpq and related parameters, see Connection Strings.

You can use the pg_is_in_recovery() function in your application to determine whether the connected database is a primary instance (indicated by f) or a read replica to implement read/write splitting.

The following is an example of Python code (psycopg2 a wrapper for libpq):

import psycopg2  
conn = psycopg2.connect(database="postgres",host="10.6.10.194,10.6.2.98", user="root", password="******", port="5432,5432", target_session_attrs="read-write")  
cur = conn.cursor()  
cur.execute("select pg_is_in_recovery()")  
row = cur.fetchone()
print("recovery =", row[0])

Using JDBC for Failover and Read/Write Splitting

You can define multiple databases (hosts and ports) in the connection URL and separate them with commas (,). JDBC will attempt to connect to them in sequence until the connection is successful. If the connection fails, an error message is displayed.

jdbc:postgresql://node1,node2,node3/${database}?targetServerType=preferSecondary&loadBalanceHosts=true

Example:

jdbc:postgresql://10.6.10.194:5432,10.6.2.98:5432,10.6.9.43:5432/${database}?targetServerType=preferSecondary&loadBalanceHosts=true

For details about the Java code, see Connecting to an RDS for PostgreSQL Instance Through JDBC.

**Table 3** Parameter description
Parameter	Description	Example Value
targetServerType	Type of the database to be connected.	any: any database. primary: primary database (writable and readable). For versions earlier than JDBC 42.2.0, use the parameter value master. secondary: secondary database (readable). For versions earlier than JDBC 42.2.0, use the parameter value slave. preferSecondary: The secondary database is preferred. If no secondary database is available, the primary database is connected. For versions earlier than JDBC 42.2.0, use the parameter value preferSlave.
loadBalanceHosts	Sequence of databases to be connected.	False (default): Databases are connected in the sequence defined in the URL. True: Databases are randomly connected.

To distinguish between the primary and secondary databases, check whether data can be written to the database. If yes, it is a primary database. If no, it is a secondary database. You can use the pg_is_in_recovery() function to determine whether a database is a primary database. For details, see Using libpq for Failover and Read/Write Splitting.

To implement read/write splitting, you need to configure two data sources. For the first data source, set targetServerType to primary to process write requests. For the second data source:

If there is only one read replica, set targetServerType to preferSecondary to process read requests. Assume that the IP addresses of the primary instance and read replica are 10.1.1.1 and 10.1.1.2, respectively.
jdbc:postgresql://10.1.1.2:5432,10.1.1.1:5432/${database}?targetServerType=preferSecondary
If there are two read replicas, set targetServerType to any to process read requests. Assume that the IP addresses of the read replicas are 10.1.1.2 and 10.1.1.3, respectively.
jdbc:postgresql://10.1.1.2:5432,10.1.1.3:5432/${database}?targetServerType=any&loadBalanceHosts=true