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 <fola...@peoplecall.com> wrote: > Hi Steve: > > On Wed, Apr 27, 2016 at 7:09 PM, Steve Crawford > <scrawf...@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. >
