Steps to Connect to Redshift cluster using PostgreSQL – psql

PostgreSQL (psql) is interactive terminal, you can type the queries and get output on terminal. After you create an Amazon Redshift cluster, you can access it using a terminal-based front end from PostgreSQL, psql, to query the data in your Redshift database.

Steps to Connect to Redshift cluster using PostgreSQL – psql

Below are the steps you can follow to connect to Redshift cluster using PostgreSQL – psql:

Install PostgreSQL on Ubuntu – psql

If you are using Ubuntu, you can use apt-get to install PostgreSQL. Below is the command:

vithal@vithal-Inspiron-3558:~$ sudo apt-get install postgresql postgresql-contrib
[sudo] password for vithal: 
Reading package lists... Done
Building dependency tree 
...
 * Starting PostgreSQL 9.3 database server [ OK ] 
Setting up postgresql (9.3+154ubuntu1.1) ...
Setting up postgresql-contrib-9.3 (9.3.20-0ubuntu0.14.04) ...
Setting up postgresql-contrib (9.3+154ubuntu1.1) ...
Processing triggers for libc-bin (2.19-0ubuntu6.13) ...

Installation created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, you need to log into that account.

Use below command:

sudo -i -u postgres

You need to provide shell user sudo password in order to login.

You will get Postgres prompt immediately if you type psql in the terminal:

postgres@vithal-Inspiron-3558:~$ psql
psql (9.3.20)
Type "help" for help.

postgres=#

Connect to Redshift cluster using PostgreSQL – psql

To connect to Redshift from psql, you must specify the cluster endpoint (your cluster name), database, port and provide password at the run-time. At a command prompt, specify the connection information by using either command line parameters or a connection information string.

psql -h <endpoint> -U <userid> -d <databasename> -p <port>

Where:

• <endpoint> It is nothing but Redshift cluster name.
• <userid> is a user ID with permissions to connect to the cluster.
• <databasename> is the Database name to which you want to connect.
• <port> is port name. Default port is, 5439

Now, you are all set to connect to Redshift from psql. Go ahead and provide all require parameter values.

Related Reading:

• Redshift SET ON_ERROR_STOP using psql and Examples
• Run Redshift SQL Script File using psql Variable Substitution
• Access Redshift using psql without Password Prompt – Use psql System Variables

postgres@vithal-Inspiron-3558:~$ psql -h vithalredshift.xxxxxxxxx.us-xxxxx.redshift.amazonaws.com -U vithal -d training -p 5439
Password for user vithal: 
psql (9.3.20, server 8.0.2)
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

training=#

Now test the connection by querying table:

training=# select * from tr_test;
 id | name 
----+------
 2 | bcd
 3 | CDE
 5 | EFG
 1 | ABC
 4 | def
(5 rows)

training=#

Related Articles

• Psql Windows Exit Status Codes – Explanation
• Redshift Show and Describe Table Command Alternative
• Best Amazon Redshift Query Tools – SQL Editors
• Psql Invalid Value for Parameter client_encoding – Redshift
• Redshift RSQL Control Statements – IF-ELSE-GOTO-LABEL