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.

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://<instance_ip>:<instance_port>,<instance_ip>:<instance_port>,<instance_ip>:<instance_port>/<database_name>?target_session_attrs=any

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):

// There will be security risks if the username and password used for authentication are directly written into code. Store the username and password in ciphertext in the configuration file or environment variables.
// In this example, the username and password are stored in the environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV as needed.

import psycopg2
import os

username = os.getenv("EXAMPLE_USERNAME_ENV")
password = os.getenv("EXAMPLE_PASSWORD_ENV")
conn = psycopg2.connect(database=<database_name>,host=<instance_ip>, user=username, password=password, port=<instance_port>, target_session_attrs="read-write")
cur = conn.cursor()
cur.execute("select pg_is_in_recovery()")
row = cur.fetchone()
print("recovery =", row[0])

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

Example:

jdbc:postgresql://<instance_ip>:<instance_port>,<instance_ip>:<instance_port>,<instance_ip>:<instance_port>/<database_name>?targetServerType=preferSecondary&loadBalanceHosts=true

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

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