Re: psql color hostname prompt

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)

Hello, have done that, looked really nice, but unfortunately this resulted in a lot of garbled output, in case of editing functions, huge queries, up arrows, etc...
You might want to test with those before submitting.

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
   Web Operations at FBS
   Creators of flexmls® and Spark Platform
   cal@fbsdata.com

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt