Re: psql color hostname prompt

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(at)peoplecall(dot)com>
wrote:

> Hi Steve:
>
> On Wed, Apr 27, 2016 at 7:09 PM, Steve Crawford
> <scrawford(at)pinpointresearch(dot)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.
>