Re: psql color hostname prompt

Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> · Mon, 25 Apr 2016 17:04:54 +0300



    On 25/04/2016 16:55, Cal Heldenbrand
      wrote:

    
                  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@xxxxxxxxxxx
        
      
    -- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt