How do I troubleshoot Amazon Redshift connection errors?

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

You recently resized or restored your Amazon Redshift cluster from a snapshot

If you recently resized your Amazon Redshift cluster or restored it from a snapshot, then check your cluster's subnet. You must launch your cluster in the same subnet from before the snapshot resize or restore.

If you didn't recently resize or restore your Amazon Redshift cluster, then verify your network configurations. The network configurations must be the same for both the old and new subnets.

Your Amazon Redshift cluster resides in a public subnet

If you're trying to connect to an Amazon Redshift cluster that's in a public subnet, then check that you set the cluster to Publicly Accessible. For more information about publicly accessible options, see Redshift resources in a virtual private cloud (VPC).

Confirm that you attached an internet gateway to your route table.

To test the connection to your Amazon Redshift cluster, run the following telnet command:

telnet cluster_endpoint cluster_port

Note: Replace cluster_endpoint with your cluster endpoint and cluster_port with your cluster point.

Your Amazon Redshift cluster resides in a private subnet

To connect to a Redshift cluster in a private subnet, your client must be within the same VPC as the cluster.

To verify DNS resolution of your cluster endpoint, run the following dig command:

dig cluster_endpoint

Note: Replace cluster_endpoint with your cluster endpoint.

To test the connection to your Amazon Redshift cluster, run the following telnet command:

telnet cluster_endpoint cluster_port

Note: Replace cluster_endpoint with your cluster endpoint and cluster_port with your cluster port.

Your Amazon Redshift cluster is still inaccessible

If the output from the telnet command shows that your Amazon Redshift cluster connection is unsuccessful, then check the following conditions:

• The security group's inbound rule allows the Amazon Redshift port 5439 of type TCP.
• The security group's inbound rule includes the CIDR range or IP address that you use to connect to the Amazon Redshift cluster.

If the output shows that your Amazon Redshift cluster connection is successful, but your cluster is still inaccessible, then check your network's firewall. Your firewall might block the Amazon Redshift port 5439.

Invalid operation connection error

If your Amazon Redshift cluster requires that you turn on SSL for any connection, then you might receive the following error message:

"Error: [Amazon](500310) Invalid operation: no pg_hba.conf entry for host "::ffff:205.xxx.xxx.xxx", user "username", database "dbname", SSL off;"

To check if you set the require_ssl parameter to true, complete the following steps:

1. Open the Amazon Redshift console.
2. In the navigation pane, choose Configurations.
3. Choose Workload Management.
4. Modify the parameter group that you associate with your Amazon Redshift cluster.
5. (Optional) To verify your SSL configuration, run the describe-cluster-parameters AWS CLI command:

   aws redshift describe-cluster-parameters --parameter-group-name exampleparametergroupname

   Note: Replace exampleparametergroupname with your parameter group name.

By default, Amazon Redshift sets your SSL configuration to true. You must update the require_ssl parameter to false. To change the SSL configuration, edit the parameter group that's associated with your Amazon Redshift cluster. You can edit the parameter group from the Parameters tab of the Amazon Redshift console. After you modify the parameter group, reboot the cluster for the changes to take effect.

If the SSL connection is required for your use case or environment, then make sure that your connection string includes the required SSL parameter.

For example:

jdbc:redshift://examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com:5439/dev?ssl=true 

Fatal connection error

If there are too many open connections in your Amazon Redshift cluster, then you might receive the following error message:

"Error: FATAL: connection limit [500]/[2000] exceeded for user"

For more information on quotas, see Quotas for Amazon Redshift objects.To check the number of open connections in your Amazon Redshift cluster, complete the following steps:

1. Open the Amazon CloudWatch console.

2. Choose the DatabaseConnections metric in your Amazon CloudWatch Logs entries.

3. Use the STL_CONNECTION_LOG table to check your open connections:

   select recordtime, username, dbname, remotehost, remoteportfrom stl_connection_log 
   where event = 'initiating session' 
   and pid not in 
   (select pid from stl_connection_log 
   where event = 'disconnecting session') 
   order by 1 desc; 

4. Use the STV_SESSIONS table to view information about the active user sessions for Amazon Redshift:

   select * from stv_sessions;

If you have a large number of open connections, then take one of the following actions:

• Use the PG_TERMINATE_BACKEND command to shut down idle sessions.
• Change the TCP/IP timeout settings on the client-side based on the operating system that you use to connect to your Amazon Redshift cluster. The update to your TCP/IP timeout settings can help prevent idle sessions from staying open.

Connection refused error

If your Amazon Redshift cluster fails to establish a connection, then you might receive the following Connection Refused error message:

"Error: amazon 500150 error setting/closing connection: operation timed out/ connection refused."

To resolve this connection error, take one of the following actions:

• If you created your cluster in an Amazon Virtual Private Cloud (Amazon VPC), then add your client CIDR/IP address to the VPC security group. For more information, see VPC security groups.
• If you created your cluster outside of a VPC, then add your client CIDR or IP address to the cluster security group. For more information, see Configuring security group communication settings for an Amazon Redshift cluster or an Amazon Redshift Serverless workgroup.

Related information

How can I make a private Amazon Redshift cluster publicly accessible?

Why can't I connect to my Amazon Redshift cluster?