Preferring Synchronous Secondary Database Servers¶

The Advanced Server JDBC Connector supports the preferSyncSecondary option for the targetServerType connection property as noted in Table 5-2.

Note

The targetServerType values slave, preferSlave, and preferSyncSlave all provide the same corresponding set of functionality as the secondary, preferSecondary, and preferSyncSecondary values; the slave values have been deprecated. We advise you to use the secondary values.

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

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

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

Parameters

master:port

The IP address or a name assigned to the master database server followed by its port number. If master is a name, it must be specified with its IP address in the /etc/hosts file on the host running the Java program. Note: The master database server can be specified in any location in the list. It does not 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, it must be specified with its IP address in the /etc/hosts file on the host running the Java program.

database

The name of the database to which the connection is to be made.

The following is an example of the connection URL:

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

The following characteristics apply to the preferSyncSecondary option:

The master database server may be specified in any location in the connection list.
Connection for accessing the database for usage 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 as described in Table 5‑2. 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 cannot be made to a synchronous secondary, then connection to the master database server is used. If the master database server is not active, then the connection attempt fails.

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

The following section provides a brief overview of setting up the master and secondary database servers for hot standby, synchronous replication.

Configuring Master and Secondary Database Servers Overview¶

The process for configuring a master and secondary database servers are described in the PostgreSQL documentation.

For general information on hot standby usage, which is needed for the preferSyncSecondary option, see Section 26.5, Hot Standby.

For information about creating a base backup for the secondary database server from the master, 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 within Section 25.3 Continuous Archiving and Point-in-Time Recovery (PITR) in The PostgreSQL Core Documentation.

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

The following section provides a basic example of setting up the master and secondary database servers.

Example: Master and Secondary Database Servers¶

In the example that follows, the:

Master 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 master)

In the master 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 master.

# 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 master database server has been 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 has been edited in the following example by adding the application_name=secondary1 setting as part of the primary_conninfo string and removal of 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 master database server’s postgresql.conf file.

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

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. Note the application_name=secondary2 setting as 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 master 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 is 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 standby.signal file signals the cluster to run in standby mode. Please note that even if you try to create a recovery.conf file manually and keep it under the data directory, the server will fail to start and throw 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 master database server as compared to the secondary database servers:

Table - Master/Secondary Configuration Parameters

Parameter	Master	Secondary	Description
archive_mode	on	off	Completed WAL segments sent to archive storage
archive_command	cp %p /archive_dir/%f	n/a	Archive completed WAL segments
wal_level	hot_standby (9.5 or earlier), replica (9.6 or later)	minimal	Information written to WAL segment
max_wal_senders	n (positive integer)	0	Maximum concurrent connections from standby servers
wal_keep_segments	n (positive integer)	0	Minimum number of past log segments to keep for standby servers
synchronous_standby_names	n(secondary1, secondary2,…)	n/a	List 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_standby	off	on	Client 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 master database server. Note that 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;
   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://master: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:

168.2.24            localhost.localdomain master
168.2.22            localhost.localdomain secondary1
168.2.24            localhost.localdomain secondary2

Note

Though the connection URL names secondary1 and secondary2 happen to match the application names given in the primary_conninfo and synchronous_standby_names parameters, this is not a requirement. The connection URL names are independent of the application names specified by the primary_conninfo and synchronous_standby_names parameters.

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, then on the master if both secondary1 and secondary2 are not active as demonstrated by the following program that 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
    {
      Class.forName("com.edb.Driver");
      String url =
 "jdbc:edb://master: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 master 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

Preferring Synchronous Secondary Database Servers¶

Configuring Master and Secondary Database Servers Overview¶

Example: Master and Secondary Database Servers¶

Previous topic

Next topic

Table Of Contents