You can use libpq or Java Database Connectivity (JDBC) in PostgreSQL to configure automatic failover and read/write splitting.

Background information

In PostgreSQL 10 and later versions, libpq supports failover and JDBC supports failover and load balancing at the driver layer.

  • libpq is a C 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 a Java API to define how client programs access databases. In PostgreSQL, JDBC supports failover and load balancing.

Use libpq to implement automatic failover and read/write splitting

You can use libpq functions to connect to multiple databases. If one database becomes faulty, services are automatically switched to other available databases.

Command:

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

Example:

In the following example, libpq is connected to a primary ApsaraDB RDS for PostgreSQL database and two of its read-only databases. If at least one database is available, read requests do not fail.

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

Parameters:

target_session_attrs: specifies the type of databases to which libpq connects. Valid values:

  • any: libpq randomly connects to a database. This is the default value. If the connection is interrupted because the database is faulty, libpq connects to another database to implement failover.
  • read-write: libpq only connects to a database that supports both read and write. Specifically, libpq connects to the databases in sequence. If a database does not support read and write, libpq disconnects from it and connects to the next database and so on until libpq connects to a database that supports read and write.

For more information about how to use libpq and configure required parameters, see Connection Strings.

You can use the pg_is_in_recovery() function in your application to determine whether the connected database is primary or read-only. This allows you to achieve failover and read/write splitting. Examples:

  • Python
    $ cat pg_conn.py  
    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]  
    
    $ python pg_conn.py  
    recovery = False  
    time = 2020-07-09 15:33:57.79001+08  
  • PHP
    # cat pg_conn.php  
    <? 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";  
    }  
    ? >  
    
    $ php -f pg_conn.php  
    Connection status ok  
    Recovery-status: f  
    Server: xxx.xxx.xx.xx  

Use JDBC to implement automatic failover and read/write splitting

You can specify multiple databases separated with commas (,) in the connection URL. The JDBC driver attempts to connect to the databases in sequence until the connection is successful. If all connection attempts fail, an error message is returned.

Command:

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  

Parameters:

  • targetServerType: specifies the type of databases to which the JDBC driver connects. Valid values:
    • any: The JDBC driver connects to any database.
    • master: The JDBC driver only connects to the primary database.
    • slave: The JDBC driver only connects to the secondary database.
    • preferSlave: The JDBC driver connects to the secondary database in priority. If no secondary database is available, the JDBC driver connects to the primary database.
    Note A primary database supports write operations, and a secondary database does not.
  • loadBalanceHosts: specifies whether to randomly connect to the databases. Valid values:
    • False: The databases are connected in the sequence specified in the command. This is the default value.
    • True: The databases are randomly connected.

To implement read/write splitting, you can configure two data sources. Set targetServerType to master for the first data source and set targetServerType to preferSlave for the second. Then, specify that write operations are performed on the first data source and read operations on the second. If you want to determine whether a connected database is primary or secondary (read-only), use the pg_is_in_recovery() function. This allows you to achieve failover and read/write splitting.