ApsaraDB RDS:Implement automatic failover and read/write splitting

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 over to other available databases.

Command

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

Example

In the following example, a client is connected to a primary ApsaraDB RDS for PostgreSQL instance and two of its read-only RDS instances. If at least one of these RDS instances 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

target_session_attrs: specifies the status of the RDS instance that is connected. Valid values:

• any: The client randomly connects to an RDS instance in your database system. If the connection is interrupted because the RDS instance is faulty, the client connects to other RDS instances in the database system to implement failover. This is the default value.

• read-write: The client connects only to an RDS instance that supports read and write operations in your database system. The client connects to the RDS instances in the database system in sequence. If an RDS instance does not support read and write operations, the client disconnects from the RDS instance and connects to the next RDS instance until the client connects to the RDS instance that supports read and write operations.

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

You can call the pg_is_in_recovery() function in your application to determine whether the connected RDS instance is a primary RDS instance or a read-only RDS instance. This helps implement automatic 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

  Note

  The print syntax in the preceding example applies only to Python 2. If you use Python 3, replace the print syntax with the following content:

  print("recovery =", row[0])
  print("time =", row[1])

• 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 RDS instances and separate them with commas (,) in the connection URL. The JDBC driver attempts to connect to the instances 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  

The following list describes the parameters in the returned result:

• targetServerType: specifies the RDS instance to which the JDBC driver connects. Valid values:

  • any: The JDBC driver connects to any RDS instance.

  • master: The JDBC driver connects only to the primary RDS instance.

  • slave: The JDBC driver connects only to the secondary RDS instance.

  • preferSlave: The JDBC driver preferentially connects to the secondary RDS instance. If no secondary RDS instances are available, the JDBC driver connects to the primary RDS instance.

  Note

  A primary RDS instance supports write operations, but a secondary RDS instance does not support write operations.

• loadBalanceHosts specifies the sequence of attempts to connect to RDS instances. Valid values:

  • False: The RDS instances are connected in the sequence that is specified in the command. This is the default value.

  • True: The RDS instances are randomly connected.

To implement read/write splitting, you must configure a primary RDS instance and a secondary RDS instance when you configure the JDBC driver. Set targetServerType to master for the primary RDS instance and targetServerType to preferSlave for the secondary RDS instance. Then, specify that write operations are performed on the primary RDS instance and read operations are performed on the secondary RDS instance. If you want to determine whether a connected RDS instance is a primary or a secondary or read-only RDS instance, you can call the pg_is_in_recovery() function. This helps implement automatic failover and read/write splitting.