Connecting a Client Application to a CDS Cluster

To connect to a CDS hosted cluster, provide your client application with the IP address and port of the server, and the credentials associated with the role defined when the server cluster was created.

Cluster details tab

The Details panel on the Clusters tab.

If you have defined a cluster with two or more servers, client applications should always connect to the load balancing port of the master server (the first Address name listed in the Details panel). This will ensure that read requests are distributed efficiently across the cluster replicas to maximize performance, while write requests are directed only to the cluster master.

  • The ADDRESS column displays the address of the node; a connecting client should use this address when connecting to a specific server.
  • The LBPORT column displays the port number to which a client application should connect to utilize load balancing. Since only the master node of a multi-server cluster operates in read/write mode, all write queries will be directed to the master node, while any read-only queries may be directed to a replica node.

Use the authentication information (Master User and Master Password) provided on the Create a New Server Cluster dialog to establish the initial connection to the cluster as the database superuser. Please note that connecting with this identity grants you superuser privileges on the server; you should not share this connection information with un-trusted users.

Connecting with the psql Client

To use the psql client to connect to an instance, navigate into the directory in which the psql client is installed on your local system and enter:

./psql -h <instance_address> -p 9999 -U <superuser_name> -d <database_name>

Where

instance_address is the address of the master node of the cluster.

superuser_name is the name of the database superuser.

database_name is the name of the database to which you will connect.

When prompted, provide the password associated with the CDS instance.

For example:

$ ./psql -h ec2-3-93-214-74.compute-1.amazonaws.com -p 9999 -U
enterprisedb
-d edb
Password for user enterprisedb:
psql.bin (11.0, server 11.1.7)
SSL connection (protocol: TLSv1.2, cipher: AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
edb=#

Connecting with a JDBC Client

If connecting with a JDBC client, you must configure the client connection to use SSL. For example, specify the following properties when configuring the client JDBC driver:

String url = "jdbc:postgresql://localhost/test";
Properties props = new Properties();
props.setProperty("user","user_name");
props.setProperty("password","secret");
props.setProperty("ssl","true");
//accept self-signed certificate
Connection conn = DriverManager.getConnection(url, props);

For more information about configuring a JDBC client, please see the JDBC Connector Guide

Load Balancing

CDS uses pgPool functionality to implement automatic load balancing. Load balancing increases system performance by distributing client queries to replica nodes, while routing database modifications to the master node. Any modifications to the master node are subsequently propagated to each replica using Postgres streaming replication.

CDS monitors the health of the load balancer to ensure that service is not interrupted. If the load balancer (pgpool) should fail while monitoring is enabled, PgPool will be automatically restarted. If the load balancer cannot be automatically restarted, CDS will display a warning sign next to the cluster name on the Details panel and send a notification email to the cluster user.

To utilize load balancing, you should direct client applications to connect to the load balancing port (port 9999). The load balancing port number is displayed in the LBPORT column on the Details pane of the Clusters tab of the console.

pgPool may direct the following statement types to either a primary or a standby node:

  • SELECT statements (not listed below)
  • COPY TO
  • DECLARE
  • FETCH
  • CLOSE
  • SHOW
  • SET
  • DISCARD
  • DEALLOCATE ALL

When deciding which node a query should be routed to, pgPool checks the transaction log number; if the transaction log number on the standby server is lower than the log number on the master, pgPool routes the statement to the master node. This helps to ensure that the data returned by the query is the most recent available.

In some cases, specific clauses within a query statement will signal pgPool to direct a statement to the master node. In other cases, the transaction type, or order of commands within a transaction can direct a statement to the master node. By default, the following transaction types will always be executed on the master node:

  • SELECT INTO, SELECT FOR UPDATE or SELECT FOR SHARE statements
  • SELECT statements within SERIALIZABLE transactions
  • SELECT statements that follow an INSERT statement
  • SET SESSION CHARACTERISTICS AS TRANSACTION… READ WRITE statements
  • SET transaction_read_only = off statements
  • EXPLAIN and EXPLAIN ANALYZE SELECT statements
  • START TRANSACTION… READ WRITE statements
  • LOCK commands that are stricter than ROW EXCLUSIVE MODE
  • Transactions that start with a BEGIN statement
  • The nextval() and setval() sequence functions
  • Large objects creation commands

Please Note: If your application uses JDBC, and the autocommit option is set to false, the JDBC driver will include a BEGIN and COMMIT statement with each SELECT statement. To enable load balancing when using the JDBC driver, your application must include a call to setAutoCommit(true).

pgPool directs the following non-query statement types to the master node only:

  • INSERT
  • UPDATE
  • DELETE
  • COPY FROM
  • TRUNCATE
  • CREATE
  • DROP
  • ALTER
  • COMMENT
  • PREPARE TRANSACTION
  • COMMIT PREPARED
  • ROLLBACK PREPARED
  • LISTEN
  • UNLISTEN
  • NOTIFY
  • VACUUM