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@xxxxxxxxxxxxxx> wrote:
Hi Steve:
On Wed, Apr 27, 2016 at 7:09 PM, Steve Crawford
<scrawford@xxxxxxxxxxxxxxxxxxxx> 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.
