Amazon Redshift rsql is a powerful command line utility that is a fork of PostgreSQL psql. It has many of the same features as psql but it uses ODBC rather than libpq to connect to the database. It also has additional features which helps in the migration of Teradata BTEQ scripts.

PostgreSQL psql is limited to using database username/password for authentication with Amazon Redshift while Amazon Redshift rsql also allows for IAM and federated logins with Active Directory, OKTA, and others. For that reason, Amazon Redshift rsql is the tool of choice to use with Amazon Redshift.

If you need to use both Amazon Redshift rsql and PostgreSQL psql on the same Linux host to connect to both PostgreSQL and Redshift, you may run into issues. This article will walk you through the steps needed to install and configure both utilities on the same EC2 instance.

Getting Started

To get started, you need to first launch an EC2 instance. Covered here is the latest Amazon Linux version 2023 as well as the previous version Amazon Linux 2. There are slight differences in the configuration.

Amazon Linux 2023

Launch a t2.micro EC2 instance with Amazon Linux 2023.

Connect to the EC2 instance with ssh as ec2-user using your ssh key you provided in the previous step.

Install the PostgreSQL psql client with yum. Note: Amazon Linux 2023 comes with PostgreSQL version 15.

sudo yum install postgresql15.x86_64 -y

Install ODBC and OpenSSL with yum.

sudo yum install unixODBC openssl -y

Install the Redshift ODBC driver. At the time of this article, the latest version is 1.5.7 but be sure to check here for newer versions.

sudo yum install -y https://s3.amazonaws.com/redshift-downloads/drivers/odbc/1.5.7.1007/AmazonRedshiftODBC-64-bit-1.5.7.1007-1.x86_64.rpm
cp /opt/amazon/redshiftodbc/Setup/odbc.ini ~/.odbc.ini

Next, install rsql. At the time of this article, that latest version is 1.0.8 but be sure to check here for newer versions.

sudo yum install -y https://s3.amazonaws.com/redshift-downloads/amazon-redshift-rsql/1.0.8/AmazonRedshiftRsql-1.0.8.x86_64.rpm

Fix a version issue with the readline library.

sudo ln -s /usr/lib64/libreadline.so.8 /usr/lib64/libreadline.so.6

Install the OpenSSL compatibility version.

sudo yum install -y https://rpmfind.net/linux/centos/8-stream/AppStream/x86_64/os/Packages/compat-openssl10-1.0.2o-4.el8.x86_64.rpm

The last step is to change the libpq version that was configured with Amazon Redshift rsql.

sudo rm /usr/lib64/libpq.so.5
sudo ln -s /usr/lib64/libpq.so.5.5 /usr/lib64/libpq.so.5

 

Amazon Linux 2

Launch a t2.micro EC2 instance with Amazon Linux 2.

Connect to the EC2 instance with ssh as ec2-user using your ssh key you provided in the previous step.

Install the PostgreSQL psql client with yum. Note: Amazon Linux 2 comes with PostgreSQL version 9.2.

sudo yum install postgresql.x86_64 -y

Install ODBC and OpenSSL with yum.

sudo yum install unixODBC openssl -y

Install the Redshift ODBC driver. At the time of this article, the latest version is 1.5.7 but be sure to check here for newer versions.

sudo yum install -y https://s3.amazonaws.com/redshift-downloads/drivers/odbc/1.5.7.1007/AmazonRedshiftODBC-64-bit-1.5.7.1007-1.x86_64.rpm
cp /opt/amazon/redshiftodbc/Setup/odbc.ini ~/.odbc.ini

Next, install rsql. At the time of this article, that latest version is 1.0.8 but be sure to check here for newer versions.

sudo yum install -y https://s3.amazonaws.com/redshift-downloads/amazon-redshift-rsql/1.0.8/AmazonRedshiftRsql-1.0.8.x86_64.rpm

The last step is to change the libpq version that was configured with Amazon Redshift rsql.

sudo rm /usr/lib64/libpq.so.5
sudo ln -s /usr/lib64/libpq.so.5.5 /usr/lib64/libpq.so.5

Configuration

Now that you have your Amazon Linux 2023 or Amazon Linux 2 EC2 instance deployed and all software installed, you now need to configure it. These following steps are the same for both versions of Linux.

.bashrc file

Add the following to your .bashrc file and be sure to change the PGUSER, PGHOST, and PGDATABASE variables for your Redshift environment.

# for rsql
export ODBCINI=~/.odbc.ini
export ODBCSYSINI=/opt/amazon/redshiftodbc/Setup
export AMAZONREDSHIFTODBCINI=/opt/amazon/redshiftodbc/lib/64/amazon.redshiftodbc.ini

# for psql
export PGUSER="<user>"
export PGHOST="redshift-cluster-1.<account>.<region>.redshift.amazonaws.com"
export PGPORT="5439"
export PGDATABASE="<dbname>"

Source the .bashrc file so these variables go into effect.

source .bashrc

.pgpass file for PostgreSQL psql

Make an entry to your .pgpass file for your Redshift cluster. This file will typically be used for your PostgreSQL connections but you will be able to also use psql with Redshift. Be sure to update the endpoint the correction information.

redshift-cluster-1.<account>.<region>.redshift.amazonaws.com:5439:*:<user>:<pass>

The .pgpass file requires a chmod to 600 so that the passwords stored in clear text are secure.

chmod 600 .pgpass

.odbc.ini file for Amazon Redshift rsql

Add an entry for your database connection to the end of the .odbc.ini file you copied to your home directory in a previous step. Provide a meaningful alias as the value for your DSN. Below, an example "dev" is used. Also, be sure to update the Host, Database, UID, and PWD values.

[dev]
Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so
SSLMode=verify-ca
Min_TLS=1.2
boolsaschar=0
Host=redshift-cluster-1.<account>.<region>.redshift.amazonaws.com
Port=5439
Database=<dbname>
UID=<user>
PWD=<pass>
sslmode=prefer

Connecting

Connecting with either tool is now possible.

Note: Before launching psql, you need to unset the LD_LIBRARY_PATH environment variable. This will ensure psql will use the PostgreSQL version of libpq.

export LD_LIBRARY_PATH=
psql 

Note: Before launching rsql, you need to set the LD_LIBRARY_PATH environment variable to use the pglib version provided with Amazon Redshift rsql.

export LD_LIBRARY_PATH=/usr/share/aws/rsql/lib/
rsql -D dev