How to Connect to Amazon Redshift from Terminal or GUI in PostgreSQL

How do I connect to Amazon Redshift from a terminal or GUI client?

Connects your local SQL client (psql or GUI) to an Amazon Redshift cluster so you can run queries.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What connection details do I need from Redshift?

You only need four values: cluster endpoint (host), port (default 5439), database name, and a user/password with CONNECT privilege. Find them in the AWS console under “Clusters → Properties → Endpoint” and “Database”.

How do I connect to Redshift with psql?

Install the PostgreSQL client, then run the psql command shown in the Syntax section. The -h flag takes the Redshift endpoint; -p the port; -d the database; -U the user.Enter the password when prompted.

How do I connect with a GUI like Galaxy, DBeaver, or DataGrip?

Create a new PostgreSQL connection, paste the same host, port, database, and credentials, and set SSL mode to “require.” GUIs handle the JDBC URL behind the scenes, so clicking “Test” should return “Success.”

Why does Redshift accept PostgreSQL drivers?

Amazon Redshift exposes a PostgreSQL-compatible wire protocol, so any client that speaks Postgres (psql, JDBC, ODBC) can connect.Advanced Postgres features (e.g., extensions) remain unsupported, but core SQL works.

Can I use environment variables for automation?

Yes. Export PGHOST, PGPORT, PGDATABASE, PGUSER, and PGPASSWORD before running psql. This keeps credentials out of bash history and CI logs.

Best practice: enable SSL

Redshift requires SSL by default. Pass sslmode=require in psql or check the “Use SSL” box in your GUI.Without SSL you will receive FATAL 08001 errors.

Best practice: restrict user privileges

Create a read-only role for analysts: CREATE USER analyst PASSWORD '✱'; GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;. Connect using that user instead of the superuser to minimize risk.

What if the connection hangs?

Verify the VPC security group allows inbound traffic on port 5439 from your IP. In corporate networks, open proxy/SSH tunnel or whitelist the office egress IP.

Why How to Connect to Amazon Redshift from Terminal or GUI in PostgreSQL is important

How to Connect to Amazon Redshift from Terminal or GUI in PostgreSQL Example Usage


-- After connecting, find top-spending customers
SELECT c.id, c.name, SUM(o.total_amount) AS lifetime_value
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY lifetime_value DESC
LIMIT 5;

How to Connect to Amazon Redshift from Terminal or GUI in PostgreSQL Syntax


psql -h redshift-cluster.abc123.us-east-1.redshift.amazonaws.com \
     -p 5439 \
     -d ecommerce \
     -U analyst \
     -c "SELECT customer_id, total_amount FROM Orders LIMIT 10;"

# JDBC/GUI URL
jdbc:redshift://redshift-cluster.abc123.us-east-1.redshift.amazonaws.com:5439/ecommerce?ssl=true

Common Mistakes

Using the Postgres default port 5432 instead of Redshift’s 5439. Fix by specifying -p 5439 or editing the GUI port field.
Leaving SSL disabled, which triggers connection failures. Always add sslmode=require or check the GUI’s SSL box.