Thread: Server name in psql prompt

Is there any way of getting psql to display the name of the
currently-connected server in its prompt, and perhaps a custom string
identifying e.g. a disc set, without having to create a psqlrc file on
every client system that's got a precompiled psql installed?

I've just come close to dropping a table that would have been
embarrassing because I couldn't see which server an instance of psql was
talking to. Now obviously that's due to lackwittedness on my part and it
could be cured by installing psqlrc files- but this might not be a
viable option since it means chasing down every psql binary that's been
installed on the LAN in an attempt to protect users from self-harm: far
nicer if the default psql prompt could be loaded from the server.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

--- On Fri, 8/1/10, Mark Morgan Lloyd <markMLl.pgsql-
> Is there any way of getting psql to
> display the name of the currently-connected server in its
> prompt, and perhaps a custom string identifying e.g. a disc
> set, without having to create a psqlrc file on every client
> system that's got a precompiled psql installed?

You could use the psql -v option to set the PROMPT variables (or set them as ENV)

see:

http://www.postgresql.org/docs/8.3/static/app-psql.html
http://www.postgresql.org/docs/8.3/static/app-psql.html#APP-PSQL-PROMPTING

On Fri, Jan 08, 2010 at 11:20:36AM +0000, Mark Morgan Lloyd wrote:
> Is there any way of getting psql to display the name of the
> currently-connected server in its prompt, and perhaps a custom string
> identifying e.g. a disc set, without having to create a psqlrc file on
> every client system that's got a precompiled psql installed?

what exactly is the problem with distributing your own .psqlrc?

for me it's one of the first things that I do - I setup environment.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

On Fri, 2010-01-08 at 11:20 +0000, Mark Morgan Lloyd wrote:

> Is there any way of getting psql to display the name of the
> currently-connected server in its prompt, and perhaps a custom string
> identifying e.g. a disc set, without having to create a psqlrc file on
> every client system that's got a precompiled psql installed?

It's a reasonable request but PostgreSQL databases don't have specific
names. You have to set up the logic yourself.

--
 Simon Riggs           www.2ndQuadrant.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


> Is there any way of getting psql to display the name of the
> currently-connected server in its prompt, and perhaps a custom string
> identifying e.g. a disc set, without having to create a psqlrc file on
> every client system that's got a precompiled psql installed?

Sure, use backticks to get what you want into there. For example,
here's one of my common prompts:

\set PROMPT1 '%n@%`hostname`:%>%R%#%x%x%x '

I once had a client that needed something more than that, so I
wrote a quick shell script that outputted the info on a single
line and then called the script inside the backticks.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201001080924
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAktHQIcACgkQvJuQZxSWSsgo6QCg5/4Rtx5Jnoso+i9P6+cph+1e
do8AoIVqlXg8u8Eb8NtPWm+Y2y+sYyfB
=gmZS
-----END PGP SIGNATURE-----

hubert depesz lubaczewski wrote:
> On Fri, Jan 08, 2010 at 11:20:36AM +0000, Mark Morgan Lloyd wrote:
>> Is there any way of getting psql to display the name of the
>> currently-connected server in its prompt, and perhaps a custom string
>> identifying e.g. a disc set, without having to create a psqlrc file on
>> every client system that's got a precompiled psql installed?
>
> what exactly is the problem with distributing your own .psqlrc?
>
> for me it's one of the first things that I do - I setup environment.

Thanks everybody for the comments. The problem is that in an environment
where the end-users generally have enough nous (or are sufficiently
assertive) to run their own systems (e.g. an engineering department)
there is still a requirement to protect shared resources like a
database. It's not really feasible for the overall sysadmin to work his
way around all possible machines, work out which distro each is running,
and install a suitable psqlrc in the place expected by that distro's
psql. It's even less feasible to install a shim that forces default
command-line parameters.

When I referred to a disc set I wasn't thinking about something in the
context of PostgreSQL, I was thinking about a group of discs in
removable (Compaq) caddies that might be transferred to one of a number
of chassis. At present I've got one chassis here into which I'm putting
one of two disc sets, both of which are the 8.4 upgrade target: I'd like
to be able to confirm from the client which set is in the chassis.

After playing some more I think there are actually three issues:

i)   Getting psql to take its initial defaults, i.e. if there isn't a
psqlrc file, from the server (e.g. for the prompt).

ii)  Getting the prompt to display the actual hostname of the server,
rather than what was put on the command line which might be an alias or
dotted-quad address.

iii) Getting the prompt to display some other identifier from the server
to identify the disc set.

I don't think anybody else thinks this is an issue so I guess all I can
say is thanks for listening :-)

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

On 01/08/2010 08:59 AM, Mark Morgan Lloyd wrote:
> hubert depesz lubaczewski wrote:
>> On Fri, Jan 08, 2010 at 11:20:36AM +0000, Mark Morgan Lloyd wrote:
>>> Is there any way of getting psql to display the name of the
>>> currently-connected server in its prompt, and perhaps a custom string
>>> identifying e.g. a disc set, without having to create a psqlrc file
>>> on every client system that's got a precompiled psql installed?
>>
>> what exactly is the problem with distributing your own .psqlrc?
>>
>> for me it's one of the first things that I do - I setup environment.
>
> Thanks everybody for the comments. The problem is that in an environment
> where the end-users generally have enough nous (or are sufficiently
> assertive) to run their own systems (e.g. an engineering department)
> there is still a requirement to protect shared resources like a
> database. It's not really feasible for the overall sysadmin to work his
> way around all possible machines, work out which distro each is running,
> and install a suitable psqlrc in the place expected by that distro's
> psql. It's even less feasible to install a shim that forces default
> command-line parameters.
>
> When I referred to a disc set I wasn't thinking about something in the
> context of PostgreSQL, I was thinking about a group of discs in
> removable (Compaq) caddies that might be transferred to one of a number
> of chassis. At present I've got one chassis here into which I'm putting
> one of two disc sets, both of which are the 8.4 upgrade target: I'd like
> to be able to confirm from the client which set is in the chassis.
>
> After playing some more I think there are actually three issues:
>
> i) Getting psql to take its initial defaults, i.e. if there isn't a
> psqlrc file, from the server (e.g. for the prompt).

In the case you describe the below might work:
http://www.postgresql.org/docs/8.4/interactive/app-psql.html
"Before starting up, psql attempts to read and execute commands from the
system-wide psqlrc file and the user's ~/.psqlrc file. (On Windows, the
user's startup file is named %APPDATA%\postgresql\psqlrc.conf.) See
PREFIX/share/psqlrc.sample for information on setting up the system-wide
file. It could be used to set up the client or the server to taste
(using the \set  and SET commands). "

Set up a system psqlrc. I have done this when working with multiple
versions/multiple database clusters of Postgres on one machine to keep
track.

>
> ii) Getting the prompt to display the actual hostname of the server,
> rather than what was put on the command line which might be an alias or
> dotted-quad address.


Same link as above.
%M

     The full host name (with domain name) of the database server, or
[local] if the connection is over a Unix domain socket, or
[local:/dir/name], if the Unix domain socket is not at the compiled in
default location.

>
> iii) Getting the prompt to display some other identifier from the server
> to identify the disc set


Make either one of these different for each server.

%:name:

     The value of the psql variable name. See the section Variables for
details.

%[ ... %]

     Prompts can contain terminal control characters which, for example,
change the color, background, or style of the prompt text, or change the
title of the terminal window. In order for the line editing features of
Readline to work properly, these non-printing control characters must be
designated as invisible by surrounding them with %[ and %]. Multiple
pairs of these can occur within the prompt. For example:

     testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '

     results in a boldfaced (1;) yellow-on-black (33;40) prompt on
VT100-compatible, color-capable terminals.

>
> I don't think anybody else thinks this is an issue so I guess all I can
> say is thanks for listening :-)
>


--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver wrote:

> In the case you describe the below might work:
> http://www.postgresql.org/docs/8.4/interactive/app-psql.html
> "Before starting up, psql attempts to read and execute commands from the
> system-wide psqlrc file and the user's ~/.psqlrc file. (On Windows, the
> user's startup file is named %APPDATA%\postgresql\psqlrc.conf.) See
> PREFIX/share/psqlrc.sample for information on setting up the system-wide
> file. It could be used to set up the client or the server to taste
> (using the \set  and SET commands). "

Thanks Adrian, link noted.

> Set up a system psqlrc. I have done this when working with multiple
> versions/multiple database clusters of Postgres on one machine to keep
> track.

I've gone round a number of machines setting up a basic psqlrc file, and
will mail the most obnox^H^H^H^H^H demanding users warning them of the
issues.

>> ii) Getting the prompt to display the actual hostname of the server,
>> rather than what was put on the command line which might be an alias or
>> dotted-quad address.
>
>
> Same link as above.
> %M
>
>     The full host name (with domain name) of the database server, or
> [local] if the connection is over a Unix domain socket, or
> [local:/dir/name], if the Unix domain socket is not at the compiled in
> default location.

I've just checked that and if I do  psql -h postgres  where postgres is
a DNS alias to postgres1 then the expansion of %M is "postgres" not
"postgres1".

>> iii) Getting the prompt to display some other identifier from the server
>> to identify the disc set
>
>
> Make either one of these different for each server.
>
> %:name:
>
>     The value of the psql variable name. See the section Variables for
> details.
>
> %[ ... %]

OK but if I understand you (and the docs) correctly I'd still need to
find a way to set the variable on the client rather than having
something fetched from the server.

I thought earlier that I could use finger as a hack for querying the
server, i.e. I could put e.g. a disc set name in /home/postgres/.plan.
However I then realised that I'd need %M to be expanded before %`, so
that I could do something like

\set PROMPT1 '`finger postgres@%M|filter`: %/%R%# '

where filter only returned the bit that was needed. I've not tried this
due to the ordering issue.

>     Prompts can contain terminal control characters which, for example,
> change the color, background, or style of the prompt text, or change the
> title of the terminal window. In order for the line editing features of
> Readline to work properly, these non-printing control characters must be
> designated as invisible by surrounding them with %[ and %]. Multiple
> pairs of these can occur within the prompt. For example:
>
>     testdb=> \set PROMPT1 '%[%033[1;33;40m%]%n@%/%R%[%033[0m%]%# '
>
>     results in a boldfaced (1;) yellow-on-black (33;40) prompt on
> VT100-compatible, color-capable terminals.

Thanks, noted.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

Mark Morgan Lloyd wrote:
> I thought earlier that I could use finger as a hack for querying the
> server, i.e. I could put e.g. a disc set name in /home/postgres/.plan.
> However I then realised that I'd need %M to be expanded before %`, so
> that I could do something like
>
> \set PROMPT1 '`finger postgres@%M|filter`: %/%R%# '
>
> where filter only returned the bit that was needed. I've not tried this
> due to the ordering issue.

I've just been playing with this. I can finger a fixed server for its
"true name" using e.g.

postgres: markMLl=>
\set PROMPT1 '%`finger postgres@postgres|head -1|cut -d \[ -f 2|cut -d .
-f 1`: %/%R%# '
postgres1: markMLl=>

but if I try to replace the fixed server name in that

\set PROMPT1 '%`finger postgres@%:HOST:|head...

the nested variable doesn't get expanded.

That's obviously useful if there's only one server or the servers are
pooled since it means that the current disk set or whatever can be
identified, but less so if there are multiple servers.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]