使用PostgreSQL的libpq或JDBC,您可以通过简单的配置实现自动故障转移(failover)和读写分离。

背景信息

从PostgreSQL 10开始,libpq驱动层开始支持简单的故障转移,JDBC驱动层则支持简单的故障转移和负载均衡。

  • libpq是PostgreSQL的C应用程序接口,包含一组库函数,允许客户端程序将查询请求发送给PostgreSQL后端服务器并接收这些查询的结果。
  • JDBC(Java Database Connectivity)是Java语言中用来规范客户端程序如何访问数据库的应用程序接口,在PostgreSQL中JDBC支持故障转移和负载平衡(Load Balance)。

libpq实现自动故障转移和读写分离

通过libpq函数连接多个数据库,当出现故障时会自动切换到可用的数据库。

命令

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

示例

如下示例为连接1个RDS PostgreSQL主实例数据库和对应的2个只读实例数据库,只要确保至少有一个数据库可用,读请求就不会失败。

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:允许连接到指定状态的数据库,取值:

  • any:默认值,表示允许连接到任意数据库,会从所有配置的数据库中随机选择一个尝试连接,如果连接的数据库出现故障导致连接断开,会尝试连接其他数据库,从而实现故障转移。
  • read-write:只会连接到支持读写的数据库,即从第一个数据库开始尝试连接,如果连接后发现不支持读写,则会断开连接,然后尝试连接第二个数据库,以此类推,直至连接到支持读写的数据库。

更多libpq的使用方法和参数说明请参见Connection Strings

您可以在应用程序中结合pg_is_in_recovery()函数,判断连接的数据库是主实例数据库的还是只读实例数据库,最终实现读写分离和故障转移,示例如下:

  • 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  

JDBC实现读写分离和自动故障转移

您可以在连接URL中定义多个数据库,并用逗号分隔,驱动程序将尝试按顺序连接到它们中的每一个,直到连接成功。如果没有成功,会返回连接异常报错。

命令

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

示例

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  

参数说明如下:

  • targetServerType:允许连接到指定状态的数据库,取值:
    • any:任何数据库。
    • master:主数据库。
    • slave:从数据库。
    • preferSlave:优先从数据库,如果没有从数据库才连接到主数据库。
    说明 区别数据库主从的方式是通过查询数据库是否允许写入,允许写入的判断为主数据库,不允许写入的判断为从数据库。
  • loadBalanceHosts:尝试连接数据库的顺序,取值:
    • False:默认值,按命令内顺序连接数据库。
    • True:随机连接数据库。

为实现读写分离,您需要在配置JDBC时配置2个数据源,1个设置targetServerType=master,一个设置targetServerType=preferSlave。需要写操作时,指定master的数据源,需要读操作时,指定preferSlave的数据源。如果需要判断数据源类型,您可以结合pg_is_in_recovery()函数,判断连接的数据库是主实例数据库的还是只读实例数据库,最终实现读写分离和故障转移。