How do I troubleshoot Amazon Redshift connection errors?

Resolution

The Amazon Redshift cluster was recently resized or restored from a snapshot

If your Amazon Redshift cluster was recently resized or restored from a snapshot, then check your cluster's subnet. The cluster must be launched in the same subnet from before the snapshot resize or restore.

If your Amazon Redshift cluster wasn't resized or restored recently, then verify your network configurations. The network configurations must be the same for both the old and new subnets.

The Amazon Redshift cluster resides in a public subnet

If you're trying to connect to an Amazon Redshift cluster that resides in a public subnet, then check the following:

• Confirm that the Amazon Redshift cluster is set to "Publicly Accessible." For more information about publicly accessible options, see Managing clusters in a VPC.
• Confirm that you have an internet gateway attached to your route table.

To test connectivity, use telnet to test the connection to your Amazon Redshift cluster:

telnet <cluster endpoint> <cluster port>

The Amazon Redshift cluster resides in a private subnet

If your Amazon Redshift cluster resides in a private subnet, then check the following:

• Be sure to attach the NAT Gateway to the public subnet’s route table. The NAT Gateway allows clusters in a private subnet to connect to the internet.
• Be sure that your cluster isn't set to Publicly Accessible.

To confirm that your client can reach the private IP address of the Amazon Redshift cluster's leader node, use the dig command:

dig <cluster endpoint>

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

telnet <cluster endpoint><cluster port>

Telnet is unsuccessful or Amazon Redshift cluster remains inaccessible

If the telnet command indicates that your Amazon Redshift cluster connection is unsuccessful, then verify that the following conditions are true:

• The Amazon Redshift port (default 5439) of type TCP is allowed in the Security Group’s inbound rule.
• The CIDR range or IP you are connecting to the Amazon Redshift cluster from is added in the Security Group’s ingress rule.

If the telnet command indicates that your Amazon Redshift cluster connection is successful but your cluster remains inaccessible, then check your network's firewall. Your firewall might be blocking the Amazon Redshift port (the default port is 5439).

Invalid operation connection error

If your Amazon Redshift cluster requires SSL to be enabled for any connection, then you might receive the following connection error:

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

To confirm whether the require_ssl parameter is set to true, perform the following steps:

1.    Open the Amazon Redshift console.

2.    Choose the Config tab.

3.    Modify the parameter group that's associated with your Amazon Redshift cluster.

4.    (Optional) Verify your SSL configuration using the AWS Command Line Interface (AWS CLI):

describe-cluster-parameters

If your SSL configuration is set to true, then consider the following approaches:

• Update the require_ssl parameter back to false, which is default parameter value. You can change the SSL configuration by editing the parameter group that's associated with your Amazon Redshift cluster. The parameter group can be edited from the Parameters tab of the Amazon Redshift console.
• If the SSL connection is a requirement for your use case or environment, 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

When there are too many open connections in your Amazon Redshift cluster, you receive the following error:

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

As the error message indicates, 500 is the max connection limit for dc2.large and ds2.xlarge node types in Amazon Redshift. For ds2.8xlarge, dc2.8xlarge, ra3.xlplus, ra3.4xlarge and ra3.16xlarge nodes, the max connection limit is 2,000.

To verify the number of open connections in your Amazon Redshift cluster, perform the following steps:

1.    Open the Amazon CloudWatch console.

2.    Look for 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, remoteport
from 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, consider the following approaches:

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

Connection refused error

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

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

This error can indicate a permissions issue with accessing your Amazon Redshift cluster. To resolve this connection error, consider the following approaches:

• 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 about configuring VPC security groups for your cluster, see Managing clusters in a VPC.
• 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 about configuring cluster security groups, see Amazon Redshift cluster security groups.

---

Related information

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

I can't connect to my Amazon Redshift cluster