The Advanced Server JDBC Connector supports option preferSyncSecondary for the targetServerType connection property as listed in Table 5‑2.Note: Use of the targetServerType values slave, preferSlave, and preferSyncSlave all provide the same corresponding set of functionality as values secondary, preferSecondary, and preferSyncSecondary, however the “slave” values have been deprecated. It is advised to use the “secondary” values.The preferSyncSecondary option provides a preference for synchronous, standby servers for failover connection, and thus ignoring asynchronous servers.jdbc:edb://master:port,secondary_1:port_1,secondary_2:port_2,.../
database?targetServerType=preferSyncSecondarymaster:portThe 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_nThe 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.The following characteristics apply to the preferSyncSecondary option:
• 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.
• 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.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 The PostgreSQL Core Documentation available at:For information on 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 available at:For information on the configuration parameters that must be set for hot standby usage, see The PostgreSQL Core Documentation available at:The following section provides a basic example of setting up the master and secondary database servers.
• Secondary database server named secondary1 on host 192.168.2.22, port 5445
• Secondary database server named secondary2 on host 184.108.40.206, port 5446 (same host as the master)On 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, 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.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:-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.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.Secondary database server secondary2 is created in an alternative manner on the same host used by the master: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.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.Make sure the configuration parameter settings in the postgresql.conf file of the secondary database servers are properly set, particularly with hot_standby=on.The following table lists the basic postgresql.conf configuration parameter settings of the master database server as compared to the secondary database servers:
cp %p /archive_dir/%f hot_standby (9.5 or earlier), replica (9.6 or later) n (positive integer) n (positive integer) n(secondary1,secondary2,...) 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. Secondary database server secondary1 is started:Secondary database server secondary2 is started: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;String url = "jdbc:edb://master:5444,secondary1:5445,secondary2:5446/edb?targetServerType=preferSyncSecondary";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: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.ResultSet rs = con.createStatement().executeQuery("SELECT inet_server_addr() || ':' || inet_server_port()");Case 1: When all database servers are active, connection is made to secondary1 on 192.168.2.22 port 5445.Case 2: When secondary1 is shut down, connection is made to secondary2 on 192.168.2.24 port 5446.Case 3: When secondary2 is also shut down, connection is made to the master on 192.168.2.24 port 5444.