Install psql

In order to use a version that is as much compatible as possible with Amazon Redshift you need version v8.0.2.

You can build it in few minutes.

Install requirements

sudo apt-get install make gcc libreadline-dev zlib1g-dev -y

Choose the directory where you want to install it, I recommend using

export PGROOT=/usr/local/

Then download it and build it with the following commands

cd /tmp
tar xf postgresql-8.0.2.tar.gz
cd postgresql-8.0.2
./configure --prefix=$PGROOT
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

export PGUSER=mydb_user
export PGDATABASE=mydb
export PGPORT=5439


It is a good choice to use a pgpass file to store passwords.

Create it,

touch ~/.pgpass
chmod 600 ~/.pgpass

and add lines in the following format


For instance

export PASSWD=s3cret

Now you can connect to the database just launching psql.

Custom prompt

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.