After the driver has loaded and registered itself with the DriverManager, the ListEmployees class can attempt to connect to the database server, as shown in the following code fragment:All JDBC connections start with the DriverManager. The DriverManager class offers a static method called getConnection() that is responsible for creating a connection to the database. When you call the getConnection() method, the DriverManager must decide which JDBC driver to use to connect to the database; that decision is based on a URL (Uniform Resource Locator) that you pass to getConnection().The first component in a JDBC URL is always jdbc. When using the Advanced Server JDBC Connector, the second component (the driver) is edb.jdbc:edb:<database>jdbc:edb://<host>/<database>jdbc:edb://<host>:<port>/<database>
The host name of the server. Defaults to localhost. In addition to the standard connection parameters, the Advanced Server JDBC driver supports connection properties that control behavior specific to EnterpriseDB. You can specify these properties in the connection URL or as a Properties object parameter passed to DriverManager.getConnection(). Listing 1.2 demonstrates how to use a Properties object to specify additional connection properties:To specify additional connection properties in the URL, add a question mark and an ampersand-separated list of keyword-value pairs:
The value of loglevel determines the amount of detail printed to the DriverManager’s current value for LogStream or LogWriter. It currently supports values of:Set the value of loglevel to INFO to include sparse log information or to DEBUG to produce significant detail. The value of charSet determines the character set used for data sent to or received from the database. The value of prepareThreshold determines the number of PreparedStatement executions required before switching to server side prepared statements. The default is five. Allows opening connections to only servers with the required state. The allowed values are any, master, slave, preferSlave, and preferSyncSlave. The master/slave distinction is currently done by observing if the server allows writes. The value preferSlave tries to connect to slaves if any are available, otherwise allows connecting to the master. The Advanced Server JDBC Connector supports preferSyncSlave, which permits connection to only synchronous slaves or the master if there are no active synchronous slaves. See Section 4.2.2 for information on preferSyncSlave. When set to true, column names from the RETURNING clause are not quoted. This eliminates a case-sensitive comparison of the column name. When set to false (the default setting), column names are quoted.The Advanced Server JDBC Connector supports option preferSyncSlave for the targetServerType connection property as listed in Table 4‑2.The preferSyncSlave option provides a preference for synchronous, standby servers for failover connection, and thus ignoring asynchronous servers.jdbc:edb://master:port,slave_1:port_1,slave_2:port_2,.../
database?targetServerType=preferSyncSlavemaster: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 slave database servers.slave_n:port_nThe IP address or a name assigned to a standby, slave database server followed by its port number. If slave_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 preferSyncSlave option:
• The order in which connection attempts are made is determined by the loadBalanceHosts connection property as described in Table 4‑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 slaves to be used for the preferSyncSlave option must be configured for hot standby usage.The following section provides a brief overview of setting up the master and slave database servers for hot standby, synchronous replication.The process for configuring a master and slave database servers are described in the PostgreSQL documentation.For general information on hot standby usage, which is needed for the preferSyncSlave option, see The PostgreSQL Core Documentation available at:For information on creating a base backup for the slave 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:
• Slave database server named slave1 on host 192.168.2.22, port 5445
• Slave database server named slave2 on host 18.104.22.168, 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 slave database servers. In the following example, superuser enterprisedb is used as the replication database user for both the slave1 database server on 192.168.2.22 and the slave2 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 slave1 is created by invoking the following command on host 192.168.2.22 for slave1:On the slave database server, /opt/slave1, a recovery.conf file is generated in the database cluster, which has been edited in the following example by adding the application_name=slave1 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=slave1'The application name slave1 must be included in the synchronous_standby_names parameter of the master database server’s postgresql.conf file.Slave database server slave2 is created in an alternative manner on the same host used by the master:On the slave database server /opt/slave2, create the recovery.conf file in the database cluster. Note the application_name=slave2 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=slave2'The application name slave2 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 slave 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 slave database servers:
cp %p /archive_dir/%f hot_standby (9.5 or earlier), replica (9.6) n (positive integer) n (positive integer) n(slave1,slave2,...) 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. Slave database server slave1 is started:Slave database server slave2 is started:To ensure that the slave 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 slave1 and slave2 as sync.edb=# SELECT usename, application_name, client_addr, client_port, sync_state FROM pg_stat_replication;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 slave1 and slave2 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 slave connection option results in the first usage attempt made on slave1, then on slave2 if slave1 is not active, then on the master if both slave1 and slave2 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 slave1 on 192.168.2.22 port 5445.Case 2: When slave1 is shut down, connection is made to slave2 on 192.168.2.24 port 5446.Case 3: When slave2 is also shut down, connection is made to the master on 192.168.2.24 port 5444.