psql color hostname prompt

[Cal Heldenbrand <cal.heldenbrand@xxxxxxxxx>]

Hi everyone,

The default psql prompt can be a little frustrating when managing many hosts.  Typing the wrong command on the wrong host can ruin your day.  ;-)

I whipped up a psqlrc and companion shell script to provide a colored prompt with the hostname of the machine you're connected to.  It works for both local sockets and remote connections too.

The only outside tool it requires is lsof to determine the hostname of the remote socket.  Otherwise it uses plain stuff like awk / sec and bash tools.

If everyone gives this a thumbs up, I'd like to submit this for inclusion in the official postgres source.  (Maybe as an additional psqlrc.sample.color_hostname file or similar)

Inline paste of the two files below.  Replace the paths with your environment:

/usr/local/pgsql/etc/psqlrc

==============================================
-- PROMPT1 is the primary prompt
\set PROMPT1 '%[%033[1;31m%]%`/usr/local/pgsql/etc/psql_hostname.sh`%[%033[0m%] %n@%/%R%#%x '

-- PROMPT2 is the secondary (query continue) prompt
\set PROMPT2 '%[%033[1;31m%]%`/usr/local/pgsql/etc/psql_hostname.sh`[%033[0m%] %n@%/%R %# '
==============================================

/usr/local/pgsql/etc/psql_hostname.sh

==============================================
#!/bin/bash
# Intelligently return local hostname, or remote server connection

# - list file descriptors of my parent PID (psql command)
# - include only FD #3, which is the postgres socket
# - print the NAME column
name=$(/usr/sbin/lsof -p $PPID -a -d 3 | tail -1 | awk '{print $9}')

if [[ "$name" == "socket" ]]; then
  # We're on the local socket
  hostname -f
else
  # Cut out the destination machine from the socket pair
  echo $( sed 's/.*->\(.*\):postgres/\1/' <<< $name )
fi
==============================================

Thank you!

------------------------------------------
Cal Heldenbrand