Example: Parameters for Connecting to the Database in Different Scenarios

DR

A customer has two database clusters. Cluster A is the production cluster, and cluster B is the DR cluster. When the customer performs a DR switchover, cluster A becomes the DR cluster, and cluster B becomes the production cluster. In this case, to avoid application restart or re-release caused by configuration file modification, the customer can write clusters A and B to the connection string when initializing the configuration file. In this case, if the primary cluster cannot be connected, the driver attempts to connect to the DR cluster. Assume that cluster A is {node1,node2,node3} and cluster B is {node4,node5,node6}.

jdbc:postgresql://node1,node2,node3,node4,node5,node6/database

Load Balancing

A customer has a database cluster that contains the following nodes: {node1,node2,node3,node4,node5,node6,node7,node8,node9,node10,node11,node12}.

1. The customer establishes 120 persistent connections in application A and expects that the connections on application A can be evenly distributed on each node in the current cluster. The URL can be configured as follows:

   jdbc:postgresql://node1,node2,node3/database?autoBalance=true

2. The customer develops two applications B and C and wants the three applications to be evenly distributed on specified nodes. For example, the connections of application A are distributed on {node1,node2,node3,node4}. The connections of application B are distributed on {node5,node6,node7,node8}. The connections of application C are distributed on {node9,node10,node11,node12}. The URLs can be configured as follows:

   Application A: jdbc:postgresql://node1,node2,node3,node4,node5/database?autoBalance=priority4

   Application B: jdbc:postgresql://node5,node6,node7,node8,node9/database?autoBalance=priority4

   Application C: jdbc:postgresql://node9,node10,node11,node12,node1/database?autoBalance=priority4

3. The customer develops more applications, uses the same connection configuration string, and expects that the application connections can be evenly distributed on each node in the cluster. The URL can be configured as follows:

   jdbc:postgresql://node1,node2,node3,node4/database?autoBalance=shuffle

4. If the customer does not want to use the load balancing function, configure the URL as follows:

   jdbc:postgresql://node1/database

   Or

   jdbc:postgresql://node1/database?autoBalance=false

   

   When the autoBalance parameter is enabled, the interval for the JDBC to refresh the available CN list is 10s by default. You can use refreshCNIpListTime to set the interval:

   jdbc:postgresql://node1,node2,node3,node4/database?autoBalance=true&refreshCNIpListTime=3

Log Diagnosis

To locate faults, a customer can enable the trace log function for diagnosis. The URL can be configured as follows:

jdbc:postgresql://node1/database?loggerLevel=trace

High Performance

A customer may execute the same SQL statement for multiple times with different input parameters. To improve the execution efficiency, the prepareThreshold parameter can be enabled to avoid repeatedly generating execution plans. The URL can be configured as follows:

jdbc:postgresql://node1/database?prepareThreshold=5

A customer queries 10 million data records at a time. To prevent memory overflow caused by simultaneous return of the data records, the defaultRowFetchSize parameter can be used. The URL can be configured as follows:

jdbc:postgresql://node1/database?defaultRowFetchSize=50000

A customer needs to insert 10 million data records in batches. To improve efficiency, the batchMode parameter can be used. The URL can be configured as follows:

jdbc:postgresql://node1/database?batchMode=on