Thread: 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. ;-)==============================================
-- 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 %# '
==============================================
-- 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
On 25/04/2016 16:55, Cal Heldenbrand wrote:
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)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.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.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. ;-)
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.
/usr/local/pgsql/etc/psqlrcInline paste of the two files below. Replace the paths with your environment:==============================================
-- 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
Hi Cal: On Mon, Apr 25, 2016 at 3:55 PM, Cal Heldenbrand <cal@fbsdata.com> 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 ' <RED>db1</RED>folarte@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.
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
Hi: On Mon, Apr 25, 2016 at 4:04 PM, Achilleas Mantzios <achill@matrix.gatewaynet.com> 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.
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)1) garbled output in large queries
But again, I think the more elegant approach is to alter the %M logic.
On Mon, Apr 25, 2016 at 9:39 AM, Francisco Olarte <folarte@peoplecall.com> wrote:
Hi:
On Mon, Apr 25, 2016 at 4:04 PM, Achilleas Mantzios
<achill@matrix.gatewaynet.com> 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
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)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.
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
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
cal@fbsdata.com
On Tue, Apr 26, 2016 at 11:02 AM, Steve Crawford <scrawford@pinpointresearch.com> wrote:
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)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.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
Hi Cal: On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand <cal@fbsdata.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. 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.
On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte <folarte@peoplecall.com> wrote:
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%# '
Hi Cal:
On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand <cal@fbsdata.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.
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.
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=>
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
On Wed, Apr 27, 2016 at 12:09 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
> On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte <folarte@peoplecall.com>
> wrote:
>>
>> Hi Cal:
>>
>> On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand <cal@fbsdata.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.
>>
>>
>> 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_CONNECTION Identifies 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.
On Wed, Apr 27, 2016 at 12:09 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
> On Wed, Apr 27, 2016 at 1:29 AM, Francisco Olarte <folarte@peoplecall.com>
> wrote:
>>
>> Hi Cal:
>>
>> On Tue, Apr 26, 2016 at 5:20 PM, Cal Heldenbrand <cal@fbsdata.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.
>>
>>
>> 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_CONNECTION Identifies 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.
Hi Steve: On Wed, Apr 27, 2016 at 7:09 PM, Steve Crawford <scrawford@pinpointresearch.com> 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.
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 <folarte@peoplecall.com> wrote:
Hi Steve:
On Wed, Apr 27, 2016 at 7:09 PM, Steve Crawford
<scrawford@pinpointresearch.com> 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.