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.
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.
ADDRESScolumn displays the address of the node; a connecting client should use this address when connecting to a specific server.
LBPORTcolumn 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
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
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>
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.
$ ./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
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
- 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
pgPool directs the following non-query statement types to the master node only:
- COPY FROM
- PREPARE TRANSACTION
- COMMIT PREPARED
- ROLLBACK PREPARED