Connecting to Amazon Redshift using Command Line (psql)

PSQL is a command line front-end to PostgreSQL. It enables you to query the data in an Amazon Redshift data warehouse (serverless or provisioned cluster) interactively. In this recipe, we will see how to install psql and run interactive queries.

Getting ready

To complete this recipe, you will need:

• Install psql (comes with PostgreSQL). To learn more about using psql, you can refer to https://www.postgresql.org/docs/8.4/static/app-psql.html. Based on your operating system, you can download the corresponding PostgreSQL binary from https://www.postgresql.org/download/.
• If you are using Windows, set the PGCLIENTENCODING environment variable to UTF-8 using the following command using the Windows command-line interface:

  set PGCLIENTENCODING=UTF8
  

• Capture the Amazon Redshift login credentials.
• Modify the security group attached to the Amazon Redshift cluster to allow connection from the server or client running the psql application, which will allow access to execute the psql code.

How to do it…

The following steps will let you connect to Amazon Redshift through a command-line interface:

1. Open the command-line interface and type psql to make sure it is installed.
2. Provide the connection credentials as shown in the following command line to connect to Amazon Redshift:

   C:\Program Files\PostgreSQL\10\bin> .\psql -h cookbookcluster-2ee55abd.cvqfeilxsadl.eu-west-1.redshift.amazonaws.com -d dev -p 5439 -U dbuser
   Password for user dbuser:
   Type "help" for help.
   dev=# help
   You are using psql, the command-line interface to PostgreSQL.
   Type:  \copyright for distribution terms
           \h for help with SQL commands
           \? for help with psql commands
          \g or terminate with semicolon to execute query
          \q to quit
   

To connect to Amazon Redshift using the psql command line, you will need the clusters endpoint, the database username, and the port. You can use the following command to connect to the Redshift data warehouse:

psql -h <clusterendpoint> -U <dbuser> -d <databasename> -p <port>

3. To check the database connection, you can use a sample query as specified in the following command:

   dev=# select sysdate;
   

You are now successfully connected to the Amazon Redshift data warehouse and ready to run the SQL queries!