Re: [GENERAL] psql color hostname prompt

[Steve Crawford]

BTW, I just noticed that as of 9.5 there is an optional GUC called
cluster_name. Unfortunately I don't see a way to reference it in the prompt
string. I'll suggest that as a feature. My earlier hack will work but in
9.5 use cluster_name instead of making up a fake extension variable.

Cheers,
Steve

On Thu, Apr 28, 2016 at 2:41 AM, Francisco Olarte 
wrote:

> Hi Steve:
>
> On Wed, Apr 27, 2016 at 7:09 PM, Steve Crawford
>  wrote:
> > The various hacks appear to not deal with the fact that there may be
> > multiple instances of postgresql running on different TCP ports or Unix
> > connections nor with the fact that the local connection may, in fact, be
> a
> > pooler and not a direct connection to the database.
>
> Because the problems is with the host, the port is solved trivially
> with %> and the local socket name is dependent on the listening port.
> And, regarding pgbouncer, psql just knows it's talking with someone
> who speaks the postgres protocol, it has no way to know what is being
> done with the socket.
>
> > As long as we're into hack-land, I'll offer one.
> > First, you abuse the custom variables feature in postgresql.conf and add
> > something like:
>
> That's a nice trick ( once you peel of the ; before \gset IIRC ) for
> the problem of knowing which of your server databases you are
> connected to. Low impact and if the database does not have the guc you
> can easily know it ( I think knowing the host is not the problem, it
> does not matter how many socket redirections, bouncers or other things
> you go through your solucion solves the problem ).
>
>
> > Next you update .psqlrc with something along the lines of:
>
> Just a problem, you need it somewhere where it can be re-executed on
> reconnects ( am I the only one who routinely uses \c ? ).
>
> > On the plus side, the custom GUC setting is available to any client, not
> > just psql. It also handles multiple PostgreSQL instances and connections
> > that are actually routed through a pooler.
>
> Yes, you do not know who you are connected to, but you know which
> server istance you end up in, which is nice. I think knowing the
> host/path+port is useful for some things, but for the originally
> stated problem this seems better.
>
> > On the down side, it is a hack. The method is not in any way guaranteed
> to
> > be future-proof. It leaves an ugly bit of server output at psql startup.
> It
> > requires ensuring that all servers have the variable set appropriately.
> You
> > need to avoid colliding with a custom GUC used by an extension. But
> perhaps
> > it is useful.
>
> Not this hacky, I'll use it in preference to changing the prompt with
> scripts ( I'll continue using %M and changing terminal titles, but I'm
> too used to it ).
>
> Well seen.
>
>
> Francisco Olarte.
>

Re: [GENERAL] psql color hostname prompt

[Francisco Olarte]

Hi Steve:

On Wed, Apr 27, 2016 at 7:09 PM, Steve Crawford
 wrote:
> The various hacks appear to not deal with the fact that there may be
> multiple instances of postgresql running on different TCP ports or Unix
> connections nor with the fact that the local connection may, in fact, be a
> pooler and not a direct connection to the database.

Because the problems is with the host, the port is solved trivially
with %> and the local socket name is dependent on the listening port.
And, regarding pgbouncer, psql just knows it's talking with someone
who speaks the postgres protocol, it has no way to know what is being
done with the socket.

> As long as we're into hack-land, I'll offer one.
> First, you abuse the custom variables feature in postgresql.conf and add
> something like:

That's a nice trick ( once you peel of the ; before \gset IIRC ) for
the problem of knowing which of your server databases you are
connected to. Low impact and if the database does not have the guc you
can easily know it ( I think knowing the host is not the problem, it
does not matter how many socket redirections, bouncers or other things
you go through your solucion solves the problem ).


> Next you update .psqlrc with something along the lines of:

Just a problem, you need it somewhere where it can be re-executed on
reconnects ( am I the only one who routinely uses \c ? ).

> On the plus side, the custom GUC setting is available to any client, not
> just psql. It also handles multiple PostgreSQL instances and connections
> that are actually routed through a pooler.

Yes, you do not know who you are connected to, but you know which
server istance you end up in, which is nice. I think knowing the
host/path+port is useful for some things, but for the originally
stated problem this seems better.

> On the down side, it is a hack. The method is not in any way guaranteed to
> be future-proof. It leaves an ugly bit of server output at psql startup. It
> requires ensuring that all servers have the variable set appropriately. You
> need to avoid colliding with a custom GUC used by an extension. But perhaps
> it is useful.

Not this hacky, I'll use it in preference to changing the prompt with
scripts ( I'll continue using %M and changing terminal titles, but I'm
too used to it ).

Well seen.


Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] psql color hostname prompt

[Cal Heldenbrand]

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

Re: [GENERAL] psql color hostname prompt

[dabicho]

On Wed, Apr 27, 2016 at 12:09 PM, Steve Crawford
 wrote:
> On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte 
> wrote:
>>
>> Hi Cal:
>>
>> On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand  wrote:
>> ...
>> > 2)  %M vs shell call
>> > %M on when connected to the local machine displays the string "[local]"
>> > which I didn't like.  I wanted a real hostname to show no matter which
>> > client/server pair I was using.  Zero chance for mistaken commands on
>> > the
>> > wrong host.  Many times we ssh to a remote server, then run psql
>> > locally.
>>
>>
>> That can be done with a named pipe ;->  ( or with an alias / function
>> using getopt to parse the options before forwarding them to psql ).
>> But, which just \sets $hostname in a var and uses it. )  Anyway, the
>> problem with this is that if you do \connect to another. You could do
>> something similar to this using only psql/psqlrc tricks:
>>
>> cdrs=> \set fecha `date`
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:23:22 CEST 2016
>>
>> Here you would use your script instead of fecha, and interpolate it
>> using %:fecha: in the prompt.
>>
>> And now the second step of the trick:
>> cdrs=> \set recalc '\\set fecha `date`'
>> cdrs=> \echo :recalc
>> \set fecha `date`
>> cdrs=> :recalc
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:24:07 CEST 2016
>> cdrs=> :recalc
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:24:16 CEST 2016
>>
>> Now you can use :recalc if you do connect to have the prompt updated.
>>
>> Anyway, TIMTOWTDI.
>>
>> > But again, I think the more elegant approach is to alter the %M logic.
>> > Any thoughts?
>>
>> At risk of being redundant, not altering %M, another %x better.
>>
>
> The various hacks appear to not deal with the fact that there may be
> multiple instances of postgresql running on different TCP ports or Unix
> connections nor with the fact that the local connection may, in fact, be a
> pooler and not a direct connection to the database.
>
> As long as we're into hack-land, I'll offer one.
>
> First, you abuse the custom variables feature in postgresql.conf and add
> something like:
> serverinfo.name = 'steve_test'
>
> Now you can read that info from any client:
> select current_setting('serverinfo.name');
> current_setting
> -
> steve_test
>
> Next you update .psqlrc with something along the lines of:
>
> select current_setting('serverid.name') as server_name;
> \gset
> \set PROMPT1 'You are connected to ' :server_name '/%/%R%# '
>
> Then when you start psql you will see:
>
>  server_name
> -
> steve_test
> (1 row)
>
> psql (9.4.7)
> Type "help" for help.
>
> You are connected to steve_test/steve=>
>
> On the plus side, the custom GUC setting is available to any client, not
> just psql. It also handles multiple PostgreSQL instances and connections
> that are actually routed through a pooler.
>
> On the down side, it is a hack. The method is not in any way guaranteed to
> be future-proof. It leaves an ugly bit of server output at psql startup. It
> requires ensuring that all servers have the variable set appropriately. You
> need to avoid colliding with a custom GUC used by an extension. But perhaps
> it is useful.
>
> -Steve
>
>
>
>

When you connect through ssh, aren't  a set of system environment variables set?
I think you could use them if available as a first cheap option.
(availability may depend on the ssh server/OS  or may not be
available if you change into another user environment after remote
login though).

I just checked, and from the info page for openssh
SSH_CONNECTIONIdentifies the client and server ends of the con‐
   nection.  The variable contains four space-sepa‐
   rated values: client IP address, client port num‐
   ber, server IP address, and server port number.

Just my 2 cents.

Sorry if the reply came in twice.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] psql color hostname prompt

[dabicho]

On Wed, Apr 27, 2016 at 12:09 PM, Steve Crawford
 wrote:
> On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte 
> wrote:
>>
>> Hi Cal:
>>
>> On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand  wrote:
>> ...
>> > 2)  %M vs shell call
>> > %M on when connected to the local machine displays the string "[local]"
>> > which I didn't like.  I wanted a real hostname to show no matter which
>> > client/server pair I was using.  Zero chance for mistaken commands on
>> > the
>> > wrong host.  Many times we ssh to a remote server, then run psql
>> > locally.
>>
>>
>> That can be done with a named pipe ;->  ( or with an alias / function
>> using getopt to parse the options before forwarding them to psql ).
>> But, which just \sets $hostname in a var and uses it. )  Anyway, the
>> problem with this is that if you do \connect to another. You could do
>> something similar to this using only psql/psqlrc tricks:
>>
>> cdrs=> \set fecha `date`
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:23:22 CEST 2016
>>
>> Here you would use your script instead of fecha, and interpolate it
>> using %:fecha: in the prompt.
>>
>> And now the second step of the trick:
>> cdrs=> \set recalc '\\set fecha `date`'
>> cdrs=> \echo :recalc
>> \set fecha `date`
>> cdrs=> :recalc
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:24:07 CEST 2016
>> cdrs=> :recalc
>> cdrs=> \echo :fecha
>> Wed Apr 27 10:24:16 CEST 2016
>>
>> Now you can use :recalc if you do connect to have the prompt updated.
>>
>> Anyway, TIMTOWTDI.
>>
>> > But again, I think the more elegant approach is to alter the %M logic.
>> > Any thoughts?
>>
>> At risk of being redundant, not altering %M, another %x better.
>>
>
> The various hacks appear to not deal with the fact that there may be
> multiple instances of postgresql running on different TCP ports or Unix
> connections nor with the fact that the local connection may, in fact, be a
> pooler and not a direct connection to the database.
>
> As long as we're into hack-land, I'll offer one.
>
> First, you abuse the custom variables feature in postgresql.conf and add
> something like:
> serverinfo.name = 'steve_test'
>
> Now you can read that info from any client:
> select current_setting('serverinfo.name');
> current_setting
> -
> steve_test
>
> Next you update .psqlrc with something along the lines of:
>
> select current_setting('serverid.name') as server_name;
> \gset
> \set PROMPT1 'You are connected to ' :server_name '/%/%R%# '
>
> Then when you start psql you will see:
>
>  server_name
> -
> steve_test
> (1 row)
>
> psql (9.4.7)
> Type "help" for help.
>
> You are connected to steve_test/steve=>
>
> On the plus side, the custom GUC setting is available to any client, not
> just psql. It also handles multiple PostgreSQL instances and connections
> that are actually routed through a pooler.
>
> On the down side, it is a hack. The method is not in any way guaranteed to
> be future-proof. It leaves an ugly bit of server output at psql startup. It
> requires ensuring that all servers have the variable set appropriately. You
> need to avoid colliding with a custom GUC used by an extension. But perhaps
> it is useful.
>
> -Steve
>
>
>
>

When you connect through ssh, aren't  a set of system environment variables set?
I think you could use them if available as a first cheap option.
(those variables may depend on the ssh server/OS  or would not be
available if you change into another user environment after remote
login though).

I just checked, and from the info page for openssh
SSH_CONNECTIONIdentifies the client and server ends of the con‐
   nection.  The variable contains four space-sepa‐
   rated values: client IP address, client port num‐
   ber, server IP address, and server port number.

Just my 2 cents.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] psql color hostname prompt

[Francisco Olarte]

Hi Cal:

On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand  wrote:
...
> 2)  %M vs shell call
> %M on when connected to the local machine displays the string "[local]"
> which I didn't like.  I wanted a real hostname to show no matter which
> client/server pair I was using.  Zero chance for mistaken commands on the
> wrong host.  Many times we ssh to a remote server, then run psql locally.

I do this (ssh'ing) too. What I do when it matters ( connecting to
many similar servers at a time ) is to use host connections for
everything ( so %M works, and the overhead of using local ip
connections vs unix domain sockets is nearly zero these days ).

> Perhaps the more elegant route here, is to change psql's behavior with %M
> when connected to the local machine?  (This would also solve point #3)

mmm, strong -1 for this. I would vote for another mechanism, but I
think it must reflect the real connection, after all I can typically
connect to [local], 127.0.0.1/localhost, $(hostname -i)/$(hostname)
and they are different things. A %nice_name would be ok for me, ( and
I think easy to do, just do 'if (local) expand hostname else expand
whatever %M does'. Also, you could precede it by something, or print
it like '[local=host.na.me]' without disturbing present %M usage.

> 3)  a forked process for every prompt
> While this also isn't very elegant, it seems to work fine.

Not an elegance concern, and forking is what shells do every time, so
fine for me.

> It would be nice if there was a way to do some kind of templating script
> with the psqlrc file.  Something that would dynamically generate the "\set
> PROMPT" commands on psql startup, rather than calling out to a shell every
> command.  (I'm thinking along the lines of ERB for Ruby, Django for Python,
> etc.)

That can be done with a named pipe ;->  ( or with an alias / function
using getopt to parse the options before forwarding them to psql ).
But, which just \sets $hostname in a var and uses it. )  Anyway, the
problem with this is that if you do \connect to another. You could do
something similar to this using only psql/psqlrc tricks:

cdrs=> \set fecha `date`
cdrs=> \echo :fecha
Wed Apr 27 10:23:22 CEST 2016

Here you would use your script instead of fecha, and interpolate it
using %:fecha: in the prompt.

And now the second step of the trick:
cdrs=> \set recalc '\\set fecha `date`'
cdrs=> \echo :recalc
\set fecha `date`
cdrs=> :recalc
cdrs=> \echo :fecha
Wed Apr 27 10:24:07 CEST 2016
cdrs=> :recalc
cdrs=> \echo :fecha
Wed Apr 27 10:24:16 CEST 2016

Now you can use :recalc if you do connect to have the prompt updated.

Anyway, TIMTOWTDI.

> But again, I think the more elegant approach is to alter the %M logic.
> Any thoughts?

At risk of being redundant, not altering %M, another %x better.

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] psql color hostname prompt

[Cal Heldenbrand]

Would it be reasonable to just take the simple approach with same algorithm
I used in the shell script?  Basically:  If the psql client uses a local
UNIX domain socket, or localhost TCP connection, use the string output by
"hostname" system command.  From the C perspective, this is just calling
the uname(2) and/or gethostname(2) system call.

All other remote connections would then fall back to the current %M
functionality.


---
Cal Heldenbrand
   Web Operations at FBS
   Creators of flexmls ® and Spark Platform

   c...@fbsdata.com

On Tue, Apr 26, 2016 at 11:02 AM, Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

> 2)  %M vs shell call
>>
>> %M on when connected to the local machine displays the string "[local]"
>> which I didn't like.  I wanted a real hostname to show no matter which
>> client/server pair I was using.  Zero chance for mistaken commands on the
>> wrong host.  Many times we ssh to a remote server, then run psql locally.
>>
>> Perhaps the more elegant route here, is to change psql's behavior with %M
>> when connected to the local machine?  (This would also solve point #3)
>>
>>
> There is a basic problem - what is the hostname?
>
> 1.2.3.4? db23.example.com? server42? 127.0.0.1? 192.168.54.23? Those
> could all be the same PostgreSQL instance, all different or some mix. And
> we haven't even considered local Unix connections, servers running on
> different ports and the fact that localhost is machine specific and
> non-fully-qualified names that depend on resolver search orders.
>
> One possible solution would be to essentially do the reverse of
> application_name. I.e. do something along the lines of adding a server-side
> parameter to specify the name of the server instance that would be
> exchanged in the startup handshake and made available to client processes.
>
> I could see some value in that but can't speak to the work and possible
> wire-protocol breakage that might be involved relative to the benefits.
>
> Cheers,
> Steve
>
>

Re: [GENERAL] psql color hostname prompt

[Steve Crawford]

>
> 2)  %M vs shell call
>
> %M on when connected to the local machine displays the string "[local]"
> which I didn't like.  I wanted a real hostname to show no matter which
> client/server pair I was using.  Zero chance for mistaken commands on the
> wrong host.  Many times we ssh to a remote server, then run psql locally.
>
> Perhaps the more elegant route here, is to change psql's behavior with %M
> when connected to the local machine?  (This would also solve point #3)
>
>
There is a basic problem - what is the hostname?

1.2.3.4? db23.example.com? server42? 127.0.0.1? 192.168.54.23? Those could
all be the same PostgreSQL instance, all different or some mix. And we
haven't even considered local Unix connections, servers running on
different ports and the fact that localhost is machine specific and
non-fully-qualified names that depend on resolver search orders.

One possible solution would be to essentially do the reverse of
application_name. I.e. do something along the lines of adding a server-side
parameter to specify the name of the server instance that would be
exchanged in the startup handshake and made available to client processes.

I could see some value in that but can't speak to the work and possible
wire-protocol breakage that might be involved relative to the benefits.

Cheers,
Steve

Re: [GENERAL] psql color hostname prompt

[Cal Heldenbrand]

Thanks for the input everyone.  I'll try to comment on each discussion
point:

1) garbled output in large queries

I messed around with a few things, and have not been able to recreate any
issues.  Can you provide a test case for this?  Also, any other interesting
things about your terminal, like your $TERM setting, locale, etc.  (I'm
using xterm with UTF-8, and KDE's Konsole app)


2)  %M vs shell call

%M on when connected to the local machine displays the string "[local]"
which I didn't like.  I wanted a real hostname to show no matter which
client/server pair I was using.  Zero chance for mistaken commands on the
wrong host.  Many times we ssh to a remote server, then run psql locally.

Perhaps the more elegant route here, is to change psql's behavior with %M
when connected to the local machine?  (This would also solve point #3)

3)  a forked process for every prompt

While this also isn't very elegant, it seems to work fine.  It's not
something to worry about these days when the server has 40 cores.  ;-)
But, I've noticed that different distros store lsof in different locations,
so per-environment tweaking might be necessary.

It would be nice if there was a way to do some kind of templating script
with the psqlrc file.  Something that would dynamically generate the "\set
PROMPT" commands on psql startup, rather than calling out to a shell every
command.  (I'm thinking along the lines of ERB for Ruby, Django for Python,
etc.)

But again, I think the more elegant approach is to alter the %M logic.

Any thoughts?

Thanks!

--Cal


On Mon, Apr 25, 2016 at 9:39 AM, Francisco Olarte 
wrote:

> Hi:
>
> On Mon, Apr 25, 2016 at 4:04 PM, Achilleas Mantzios
>  wrote:
> > 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...
>
> Did you  use %[ %] to delimit non-printing sequences as Cal did? I've
> tested his prompt ( substituting 'echo tarari' for his sh script ) and
> it works fine ( at least in xfce4-terminal / ubuntu / 6 terminal lines
> long input line ). I've seen a lot of color prompts worked by
> forgetting them ( even when the doc for them specially speaks of color
> changes ).
> Frnacisco Olarte.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] psql color hostname prompt

[Francisco Olarte]

Hi:

On Mon, Apr 25, 2016 at 4:04 PM, Achilleas Mantzios
 wrote:
> 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...

Did you  use %[ %] to delimit non-printing sequences as Cal did? I've
tested his prompt ( substituting 'echo tarari' for his sh script ) and
it works fine ( at least in xfce4-terminal / ubuntu / 6 terminal lines
long input line ). I've seen a lot of color prompts worked by
forgetting them ( even when the doc for them specially speaks of color
changes ).
Frnacisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] psql color hostname prompt

[hubert depesz lubaczewski]

On Mon, Apr 25, 2016 at 08:55:21AM -0500, Cal Heldenbrand wrote:
> 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.

Why would you need lsof to get hostname for remote connection, when you
can use %M ?

Best regards,

depesz



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] psql color hostname prompt

[Francisco Olarte]

Hi Cal:

On Mon, Apr 25, 2016 at 3:55 PM, Cal Heldenbrand  wrote:
> 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.

Color may be nice, but as previously pointed it can distort things.

> /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 '

And you are exec'ing in every round ( not too big, but I come from a
time of slow forks).

I think psql ( at least in 9.3 didn't bother looking behind ) gives you enough:

cdrs=> \set PROMPT1 %M:%>-%x-%:AUTOCOMMIT:-:PROMPT1
db1:5432--on:-cdrs=> begin;
BEGIN
db1:5432-*-on:-cdrs=> commit;
COMMIT
db1:5432--on:-cdrs=> \c apc -
psql (9.4.7, server 9.3.10)
You are now connected to database "apc" as user "folarte".
db1:5432--on:-apc=>

And you can color it:

db1:5432--on:-apc=> \set PROMPT1 '%[%033[1;31m%]%M%[%033[0m%]%n@%/%R%#%x '
db1folarte@apc=> -- not a fan of html mail.

lsof may give you longer or more acurate names, but I think std
escapes are enough.

Francisco Olarte.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] psql color hostname prompt

[Achilleas Mantzios]

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 

c...@fbsdata.com 



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

[GENERAL] psql color hostname prompt

[Cal Heldenbrand]

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
   Web Operations at FBS
   Creators of flexmls ® and Spark Platform

   c...@fbsdata.com