AWS Redshift compatible PostgreSQL client
How to install a PostgreSQL client (psql) that is compatible with AWS Redshift
In order to use a version that is as much compatible as possible with Amazon Redshift you need version
You can build it in few minutes.
sudo apt-get install make gcc libreadline-dev zlib1g-dev -y
Choose the directory where you want to install it, I recommend using
Then download it and build it with the following commands
cd /tmp wget https://ftp.postgresql.org/pub/source/v8.0.2/postgresql-8.0.2.tar.gz tar xf postgresql-8.0.2.tar.gz cd postgresql-8.0.2 ./configure --prefix=$PGROOT make sudo make install
Do not forget to add
$PGROOT/bin to your
$PATH: it should be already
ok if you used the PGROOT recommended above.
Consider set your environment to point to your main database. For example I added to my ~/.bashrc something like
export PGHOST=mydb-instance.cd274s5bo4aq.eu-west-1.redshift.amazonaws.com export PGPORT=5439 export PGDATABASE=mydb export PGUSER=mydb_user
- Redshift hostname. You can find it in the AWS console, look for Cluster Endpoint in your Redshift instance Configuration tab: it is something like
- 5439, is default Redshift password.
- Your database name.
- Your database user name.
PGPASSWORDto set credentials, it is strongly recommended to use a pgpass file (read below).
It is a good choice to use a pgpass file to store passwords.
touch ~/.pgpass chmod 600 ~/.pgpass
and add lines in the following format
echo $PGHOST:$PGPORT:$PGDATABASE:$PGUSER:password >> ~/.pgpass
Then edit .pgpass and change password.
Now you can connect to the database just launching
To display also the database user in the prompt, put the following line in your ~/.psqlrc
\set PROMPT1 '%n@%/=%# '
See also psql prompting for more choices.