I deal with lots of production.  When you’re dealing with multiple production machines, it’s important to know many things:

  1. Who you are
  2. What you’re connected to
  3. Where you are
  4. Why you are connected
  5. When you are connected

Most prompts don’t give you any of this detail.  If you’re familiar with bash, you probably have heard of PS1, it’s an environment variable that lets you set what your prompt looks like.  psql has something very similar.  The question becomes, how do I get a useful prompt that is compact and doesn’t cause all of my commands to wrap off of the screen?

Let’s start with bash, most people are familiar with the default linux prompt (debian and redhat differ slightly in their defaults) of:

[root@ansible-test-1 ~]#

One day, I realized that wasn’t enough.  I had a lot of tabs open in my terminal emulator and I was ssh’d three ways from Sunday.  Many of those tabs were executing long running commands, and I couldn’t quickly look back to see how long it had been running for (unless I remembered to execute the date command prior to starting my job).  That’s when I came up with this prompt:

BashPrompt

This tells me most of what I’d like to know.  I have:

[user@host : Full Path to current directory: Time the prompt was generated]
$ <your command gets entered here>

 

You can accomplish this with:

export PS1=[\u@\h : \[\e[4;33m\]\w\[\e[0m\] : \[\e[0;94m\]\t\[\e[0m\]]\n$

I did have one issue, in order to see the full path, I had to put the next command on the line below.  I’ve actually come to appreciate it quite a bit.  Essentially, I have a line that tells me: Who @ What: Where : When.  (I guess why I’m there is something I should probably already know : -)

Now, I decided to apply the same design to my psql prompt so that I knew what was going on.  psql prompt

You can accomplish this by putting the following in your .bashrc:

alias psql='psql -v PROMPT1=[`hostname -s`'\''@%m:%>]
%n@%/%=%# '\'''

It’s important to note, the line wrap, you have to actually enter that or this won’t work.  Essentially, I have to have bash set the ‘hostname’ in the prompt (which is why this isn’t in .psqlrc).

Why do I think this prompt is better than the default?

This prompt tells me:

  1.  scottsmac : What host psql is running from
  2. [local]         : What host psql is connected to
  3. 9520            : What port psql is connected to
  4. newline
  5. postgres      : What user psql is connected as
  6. @postgres  : What database psql is connected to

This, again gives me ‘who, what, where, why and when’ … okay, not the ‘why’ but that’s really up to you.