Configure automatic failover and read/write splitting for PostgreSQL using libpq and JDBC - ApsaraDB RDS

ApsaraDB RDS for PostgreSQL lets you implement automatic failover and read/write splitting at the driver layer — no changes to your application logic required. Both libpq (PostgreSQL's C client library) and Java Database Connectivity (JDBC) support this from PostgreSQL 10 onward.

Quick reference: choose your driver

Driver	Language	Failover	Load balancing
libpq	C, Python, PHP, and any language using the C library	Yes	No
JDBC	Java	Yes	Yes (via `loadBalanceHosts`)

Use libpq for automatic failover and read/write splitting

libpq accepts a comma-separated list of hosts in the connection string. If a host becomes unavailable, the driver automatically connects to the next available host — no reconnection logic needed in your application.

Connection string format

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

Example

The following connection string connects a client to one primary instance and two read-only instances. As long as at least one instance is available, read requests succeed.

postgres://pgm-bpxxx1.pg.rds.aliyuncs.com:3433,pgm-bpxxx2.pg.rds.aliyuncs.com:3433,pgm-bpxxx3.pg.rds.aliyuncs.com:3433/postgres?target_session_attrs=any

target_session_attrs parameter

The target_session_attrs parameter controls which instance type the client connects to.

Value	Behavior
`any` (default)	Connects to any available instance. If the connection is interrupted, the driver reconnects to another instance in the list.
`read-write`	Connects only to an instance that accepts read and write operations. The driver tries each host in order and skips instances that do not support read and write operations.

For the full parameter reference, see Connection Strings.

Determine whether a connection is to a primary or read-only instance

Call pg_is_in_recovery() in your application to check whether the connected instance is a primary instance (false) or a read-only instance (true). Use this to route write and read traffic to the correct instance.

Python example

import psycopg2

conn = psycopg2.connect(
    database="postgres",
    host="pgm-bpxxx1.pg.rds.aliyuncs.com,pgm-bpxxx2.pg.rds.aliyuncs.com,pgm-bpxxx3.pg.rds.aliyuncs.com",
    user="testxxx",
    password="xxxxxx",
    port="3433",
    target_session_attrs="read-write"
)
cur = conn.cursor()
cur.execute("select pg_is_in_recovery(), pg_postmaster_start_time()")
row = cur.fetchone()
print("recovery =", row[0])
print("time =", row[1])

Expected output:

recovery = False
time = 2020-07-09 15:33:57.79001+08

Note

The example uses Python 3 print syntax. For Python 2, replace print("recovery =", row[0]) with print "recovery =",row[0].

PHP example

<?php
$conn = pg_connect("host=pgm-bpxxx1.pg.rds.aliyuncs.com,pgm-bpxxx2.pg.rds.aliyuncs.com,pgm-bpxxx3.pg.rds.aliyuncs.com port=3433 dbname=postgres user=testxxx password=xxxxxx 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";
}
?>

Expected output:

Connection status ok
Recovery-status: f
Server: xxx.xxx.xx.xx

Use JDBC for automatic failover and read/write splitting

The PostgreSQL JDBC driver accepts a comma-separated list of hosts. It tries each host in sequence until a connection succeeds. If all hosts are unavailable, it returns an error.

Connection string format

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

Example

jdbc:postgresql://pgm-bpxxx1.pg.rds.aliyuncs.com:3433,pgm-bpxxx2.pg.rds.aliyuncs.com:3433,pgm-bpxxx3.pg.rds.aliyuncs.com:3433/accounting?targetServerType=preferSlave&loadBalanceHosts=true

JDBC connection parameters

targetServerType

Specifies which instance type the JDBC driver connects to.

Value	Behavior
`any`	Connects to any available instance.
`master`	Connects only to the primary instance, which supports write operations.
`slave`	Connects only to a secondary instance. Secondary instances do not support write operations.
`preferSlave`	Connects to a secondary instance if available; falls back to the primary instance if no secondary is available.

loadBalanceHosts

Specifies the connection order when multiple hosts are listed.

Value	Behavior
`false` (default)	Connects in the order listed in the connection string.
`true`	Connects in random order, distributing load across instances.

Implement read/write splitting with two connection pools

Configure separate connection strings for write traffic and read traffic. All examples below list the same three hosts; the targetServerType parameter determines which instance type each pool connects to.

Write pool — connects only to the primary instance:

jdbc:postgresql://pgm-bpxxx1.pg.rds.aliyuncs.com:3433,pgm-bpxxx2.pg.rds.aliyuncs.com:3433,pgm-bpxxx3.pg.rds.aliyuncs.com:3433/accounting?targetServerType=master

Read pool — connects to secondary instances, with load balancing and primary fallback:

jdbc:postgresql://pgm-bpxxx1.pg.rds.aliyuncs.com:3433,pgm-bpxxx2.pg.rds.aliyuncs.com:3433,pgm-bpxxx3.pg.rds.aliyuncs.com:3433/accounting?targetServerType=preferSlave&loadBalanceHosts=true

Route write operations through the write pool and read operations through the read pool. To verify which type of instance a connection is on, call pg_is_in_recovery() — it returns false for primary instances and true for secondary or read-only instances.

Usage notes

Read pool during failover: During a failover window, a read pool configured with targetServerType=preferSlave may briefly connect to the newly promoted primary if no secondary is yet available. This is expected behavior and resolves automatically once a secondary comes online.
Write vs. read instances: Primary instances support write operations. Secondary and read-only instances do not support write operations — use targetServerType=master for any connection that performs writes.