Thread: display hot standby state in psql prompt

Hi,

Some weeks ago we briefly discussed in the discord channel the
possibility of introducing a psql prompt display option to identify if
the connected database is in hot standby mode, which can be useful when
using multiple hosts in the connection string. Right now, it's using the
in_hot_standby value in prompt.c to determine the database state:

case 'i':
    if (pset.db && PQparameterStatus(pset.db, "in_hot_standby"))
    {
        const char *hs = PQparameterStatus(pset.db, "in_hot_standby");
        if (hs && strcmp(hs, "on") == 0)
            strlcpy(buf, "standby", sizeof(buf));
        else
            strlcpy(buf, "primary", sizeof(buf));

.. which could be used like this:

psql (18beta1)
Type "help" for help.

postgres=# \set PROMPT1 '[%i] # '
[standby] # SELECT pg_promote();
 pg_promote
------------
 t
(1 row)

[primary] #


The hardcoded "standby" and "primary" strings are not very flexible, but
I am not sure how to make these strings customisable just yet.

Any thoughts on this feature?

Best regards, Jim

Hi Greg

On 25.06.25 17:17, Greg Sabino Mullane wrote:
> Seems transaction_read_only might be a more useful thing to examine?
> That's the side-effect, if you will, that people really care about
> when in hot standby mode (and of course, we can get into TRO other ways).

Good point. But wouldn't it mean that I would need to execute a query
every time the prompt is refreshed? Since I cannot get the value of
transaction_read_only via PQparameterStatus. I like the idea, but I'm
concerned about the overhead.

Best, Jim