Menu
Amazon Redshift
Management Guide (API Version 2012-12-01)

Connect to Your Cluster by Using the psql Tool

After you create an Amazon Redshift cluster, you can use psql, a terminal-based front end from PostgreSQL, to query the data in your cluster. You can type the queries interactively or read them from a file. To connect from psql, you must specify the cluster endpoint, database, and port.

Note

Amazon Redshift does not provide the psql tool; it is installed with PostgreSQL. For information about using psql, go to http://www.postgresql.org/docs/8.4/static/app-psql.html. For information about installing the PostgreSQL client tools, select your operating system from the PostrgreSQL binary downloads page at http://www.postgresql.org/download/.

Connect by Using the psql Defaults

By default, psql does not validate the Amazon Redshift service; it makes an encrypted connection by using Secure Sockets Layer (SSL).

To connect by using psql defaults

  1. Sign in to the AWS Management Console and open the Amazon Redshift console at https://console.aws.amazon.com/redshift/.

  2. In the left navigation pane, click Clusters. Click your cluster to open it. Under Cluster Database Properties, record the values of Endpoint, Port, and Database Name.

  3. At a command prompt, specify the connection information by using either command line parameters or a connection information string. To use parameters:

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

    Where:

    • <endpoint> is the Endpoint you recorded in the previous step.

    • <userid> is a user ID with permissions to connect to the cluster.

    • <databasename> is the Database Name you recorded in the previous step.

    • <port> is the Port you recorded in the previous step.

    For example:

    Copy
    psql -h examplecluster.<XXXXXXXXXXXX>.us-west-2.redshift.amazonaws.com -U masteruser -d dev -p 5439

  4. At the psql password prompt, enter the password for the <userid> user.

You are connected to the cluster, and you can interactively enter commands.

Connect by Using a Certificate

To control whether psql authenticates the service using a certificate, you must use a connection information string to specify connection information, and specify the sslmode keyword. By default, psql operates with sslmode=prefer. To specify that psql opens an encrypted connection and uses an Amazon Redshift certificate to verify the service, download an Amazon Redshift certificate to your computer, specify sslmode=verify-ca, and specify sslrootcert with the location of the certificate. For more information about sslmode, see Configure Security Options for Connections.

For more information about connection information string parameters, see http://www.postgresql.org/docs/8.4/static/libpq-connect.html.

To connect by using a certificate

  1. Save the public key from https://s3.amazonaws.com/redshift-downloads/redshift-ssl-ca-cert.pem as a .pem file to your computer. If you do a File\Save as using Internet Explorer, specify the file type as Text file (*.txt) and delete the .txt extension. For example, save it as the file C:\MyDownloads\redshift-ssl-ca-cert.pem.

  2. In the Amazon Redshift console, select the cluster to display the Cluster Database Properties. Record the values displayed in the Endpoint, Port, and Database Name fields.

  3. At a command prompt, specify the connection information using a connection information string:

    Copy
    psql "host=<endpoint> user=<userid> dbname=<databasename> port=<port> sslmode=verify-ca sslrootcert=<certificate>"

    Where:

    • <endpoint> is the Endpoint you recorded in the previous step.

    • <userid> is a user ID with permissions to connect to the cluster.

    • <databasename> is the Database Name you recorded in the previous step.

    • <port> is the Port you recorded in the previous step.

    • <certificate> is the full path to the certificate file. On Windows systems, the certificate path must be specified using Linux-style / separators instead of the Windows \ separator.

    For example:

    Copy
    psql "host=examplecluster.<XXXXXXXXXXXX>.us-west-2.redshift.amazonaws.com user=masteruser dbname=dev port=5439 sslmode=verify-ca sslrootcert=C:/MyDownloads/redshift-ssl-ca-cert.pem"

  4. At the psql password prompt, enter the password for the <userid> user.

You are connected to the cluster, and you can interactively enter commands.