Horton

display hot standby state in psql prompt

Jim Jones <jim.jones@uni-muenster.de>

View thread
From: Jim Jones <jim.jones@uni-muenster.de>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-06-25T08:01:40Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. psql: Add %i prompt escape to indicate hot standby status.

  2. Mark search_path as GUC_REPORT

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