Preferring synchronous secondary database servers v42.5.4.2

The EDB Postgres Advanced Server JDBC Connector supports the preferSyncSecondary option for the targetServerType connection property.

The preferSyncSecondary option provides a preference for synchronous, standby servers for failover connection, thus ignoring asynchronous servers.

The specification of this capability in the connection URL is shown by the following syntax:

jdbc:edb://primary:port,secondary_1:port_1,secondary_2:port_2,.../
database?targetServerType=preferSyncSecondary

Parameters

primary:port

The IP address or a name assigned to the primary database server followed by its port number. If primary is a name, you must specify it with its IP address in the /etc/hosts file on the host running the Java program.

Note

You can specify the primary database server in any location in the list. It doesn't have to precede the secondary database servers.

secondary_n:port_n

The IP address or a name assigned to a standby, secondary database server followed by its port number. If secondary_n is a name, you must specify it with its IP address in the /etc/hosts file on the host running the Java program.

database

The name of the database to which to make the connection.

The following is an example of the connection URL:

String url = "jdbc:edb://primary:5300,secondary1:5400/edb?targetServerType=preferSyncSecondary";
con = DriverManager.getConnection(url, "enterprisedb", "edb");

The following characteristics apply to the preferSyncSecondary option:

  • You cam specify the primary database server in any location in the connection list.
  • Connection for accessing the database for use by the Java program is first attempted on a synchronous secondary. The secondary servers are available for read-only operations.
  • No connection attempt is made to any servers running in asynchronous mode.
  • The order in which connection attempts are made is determined by the loadBalanceHosts connection property. If disabled, which is the default setting, connection attempts are made in the left-to-right order specified in the connection list. If enabled, connection attempts are made randomly.
  • If connection can't be made to a synchronous secondary, then connection to the primary database server is used. If the primary database server isn't active, then the connection attempt fails.

The synchronous secondaries to use for the preferSyncSecondary option must be configured for hot standby usage.

Configuring primary and secondary database servers overview

The process for configuring a primary and secondary database servers is described in the PostgreSQL documentation.

For general information on hot standby usage, which is needed for the preferSyncSecondary option, see the PostgreSQL core documentation.

For information about creating a base backup for the secondary database server from the primary, see Section 25.3.2, Making a Base Backup (describes usage of the pg_basebackup utility program) or Section 25.3.3, Making a Base Backup Using the Low Level API in Section 25.3 Continuous Archiving and Point-in-Time Recovery (PITR) in The PostgreSQL Core Documentation.

For information on the configuration parameters to set for hot standby usage, see Section 19.6, Replication.

Example: Primary and secondary database servers

In the example that follows, the:

  • Primary database server resides on host 192.168.2.24, port 5444.
  • Secondary database server is named secondary1 and resides on host 192.168.2.22, port 5445.
  • Secondary database server is named secondary2 and resides on host 192.162.2.24, port 5446 (same host as the primary).

In the primary database server’s pg_hba.conf file, there must be a replication entry for each unique replication database USER/ADDRESS combination for all secondary database servers. In the following example, the database superuser enterprisedb is used as the replication database user for both the secondary1 database server on 192.168.2.22 and the secondary2 database server that is local relative to the primary.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    replication     enterprisedb    192.168.2.22/32         md5
host    replication     enterprisedb    127.0.0.1/32            md5

After the primary database server is configured in the postgresql.conf file along with its pg_hba.conf file, database server secondary1 is created by invoking the following command on host 192.168.2.22 for secondary1:

su – enterprisedb
Password:
-bash-4.1$ pg_basebackup -D /opt/secondary1 -h 192.168.2.24 -p 5444 -Fp -R -X stream -l 'Secondary1'

On the secondary database server, /opt/secondary1, a recovery.conf file is generated in the database cluster, which was edited in the following example by adding the application_name=secondary1 setting as part of the primary_conninfo string and removing some of the other unneeded options automatically generated by pg_basebackup. Also note the use of the standby_mode = 'on' parameter.

standby_mode = 'on'
primary_conninfo = 'user=enterprisedb password=password host=192.168.2.24 port=5444 application_name=secondary1'

The application name secondary1 must be included in the synchronous_standby_names parameter of the primary database server’s postgresql.conf file.

The secondary database server (secondary2) is created in an alternative manner on the same host used by the primary:

su - enterprisedb
Password:
-bash-4.1$ psql -d edb -c "SELECT pg_start_backup('Secondary2')"
Password:
 pg_start_backup
-----------------
 0/6000028
(1 row)

-bash-4.1$ cp -rp /var/lib/edb/as12/data/opt/secondary2
-bash-4.1$ psql -d edb -c "SELECT pg_stop_backup()"
Password:
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup
----------------
 0/6000130
(1 row)

On the secondary database server /opt/secondary2, create the recovery.conf file in the database cluster. The application_name=secondary2 setting is part of the primary_conninfo string as shown in the following example. Also be sure to include the standby_mode = 'on' parameter.

standby_mode = 'on'
primary_conninfo = 'user=enterprisedb password=password host=localhost port=5444 application_name=secondary2'

The application name secondary2 must be included in the synchronous_standby_names parameter of the primary database server’s postgresql.conf file.

You must ensure the configuration parameter settings in the postgresql.conf file of the secondary database servers are properly set (particularly hot_standby=on).

Note

As of EDB Postgres Advanced Server v12, the recovery.conf file is no longer valid. It's replaced by the standby.signal file. As a result, primary_conninfo is moved from the recovery.conf file to the postgresql.conf file. The presence of the standby.signal file signals the cluster to run in standby mode. Even if you try to create a recovery.conf file manually and keep it under the data directory, the server fails to start and reports an error.

The parameter standby_mode=on is also removed from EDB Postgres Advanced Server v12, and the trigger_file parameter name is changed to promote_trigger_file.

The following table lists the basic postgresql.conf configuration parameter settings of the primary database server as compared to the secondary database servers.

ParameterPrimarySecondaryDescription
archive_modeonoffCompleted WAL segments sent to archive storage
archive_commandcp %p /archive_dir/%fn/aArchive completed WAL segments
wal_level (10 or later)replicaminimalInformation written to WAL segment
max_wal_sendersn (positive integer)0Maximum concurrent connections from standby servers
wal_keep_segmentsn (positive integer)0Minimum number of past log segments to keep for standby servers
synchronous_standby_namesn(secondary1, secondary2,...)n/aList of standby servers for synchronous replication. Must be present to enable synchronous replication. These are obtained from the application_name option of the primary_conninfo parameter in the recovery.conf file of each standby server.
hot_standbyoffonClient application can connect and run queries on the secondary server in standby mode

The secondary database server (secondary1) is started:

-bash-4.1$ pg_ctl start -D /opt/secondary1 -l logfile -o "-p 5445"
server starting

The secondary database server (secondary2) is started:

-bash-4.1$ pg_ctl start -D /opt/secondary2/data -l logfile -o "-p 5446"
server starting

To ensure that the secondary database servers are properly set up in synchronous mode, use the following query on the primary database server. The sync_state column lists applications secondary1 and secondary2 as sync.

edb=# SELECT usename, application_name, client_addr, client_port, sync_state FROM pg_stat_replication;
Output
   usename    | application_name | client_addr  | client_port | sync_state
--------------+------------------+--------------+-------------+------------
 enterprisedb | secondary1       | 192.168.2.22 |       53525 | sync
 enterprisedb | secondary2       | 127.0.0.1    |       36214 | sync
(2 rows)

The connection URL is:

String url = "jdbc:edb://primary:5444,secondary1:5445,secondary2:5446/edb?targetServerType=preferSyncSecondary";
con = DriverManager.getConnection(url, "enterprisedb", "password");

The /etc/hosts file on the host running the Java program contains the following entries with the server names specified in the connection URL string:

192.168.2.24            localhost.localdomain primary
192.168.2.22            localhost.localdomain secondary1
192.168.2.24            localhost.localdomain secondary2

For this example, the preferred synchronous secondary connection option results in the first usage attempt made on secondary1, then on secondary2 if secondary1 is not active, and then on the primary if both secondary1 and secondary2 aren't active as shown by the following program. The program displays the IP address and port of the database server to which the connection is made.

import java.sql.*;
public class InetServer
{
  public static void main(String[] args)
  {
    try
    {
      String url =
 "jdbc:edb://primary:5444,secondary1:5445,secondary2:5446/edb?targetServerType=preferSyncSecondary";
      String user     = "enterprisedb";
      String password = "password";
      Connection con  = DriverManager.getConnection(url, user, password);

      ResultSet rs = con.createStatement().executeQuery("SELECT inet_server_addr() || ':' || inet_server_port()");
      rs.next();
      System.out.println(rs.getString(1));

      rs.close();
      con.close();
      System.out.println("Command successfully executed");
    }
    catch(ClassNotFoundException e)
    {
      System.out.println("Class Not Found : " + e.getMessage());
    }
    catch(SQLException exp)
    {
      System.out.println("SQL Exception: " + exp.getMessage());
      System.out.println("SQL State:     " + exp.getSQLState());
      System.out.println("Vendor Error:  " + exp.getErrorCode());
    }
  }
}

Case 1: When all database servers are active, connection is made to secondary1 on 192.168.2.22 port 5445.

$ java InetServer
192.168.2.22/32:5445
Command successfully executed

Case 2: When secondary1 is shut down, connection is made to secondary2 on 192.168.2.24 port 5446.

bash-4.1$ /usr/edb/as12/bin/pg_ctl stop -D /opt/secondary1
waiting for server to shut down.... done
server stopped

$ java InetServer
192.168.2.24/32:5446
Command successfully executed

Case 3: When secondary2 is also shut down, connection is made to the primary on 192.168.2.24 port 5444.

bash-4.1$ /usr/edb/as12/bin/pg_ctl stop -D /opt/secondary2/data
waiting for server to shut down.... done
server stopped

$ java InetServer
192.168.2.24/32:5444
Command successfully executed