Psql meta-command conninfo+ - Mailing list pgsql-hackers

From Maiquel Grassi
Subject Psql meta-command conninfo+
Date
Msg-id CP8P284MB24965CB63DAC00FC0EA4A475EC462@CP8P284MB2496.BRAP284.PROD.OUTLOOK.COM
Whole thread Raw
Responses Re: Psql meta-command conninfo+
List pgsql-hackers

Hi,

I'm seeking to improve the \conninfo meta-command in psql. Currently, it provides limited information about the current connection. I believe that expanding it using the concept of "plus" [+] could ease the work of DBAs, SysAdmins, DevOps, etc., who manage a large volume of databases and/or multiple PostgreSQL servers. The objective of this enhancement is to obtain quick information about the current connection (session). I believe that for a PostgreSQL administrator, it is not feasible to write a plpgsql function and apply it to all databases (for example, imagine managing over 200 databases). I have an example on GitHub https://github.com/maiquelgrassi/DBA-toolkit/blob/main/cluster/dba_whoami_function.sql of a plpgsql function demonstrating exactly what I believe is impractical for the daily routine of a PostgreSQL professional. I see psql's meta-commands as significant allies in daily work in productive environments.


Note: As this is a prototype, I will adjust the rest (documentation, tests, etc.) once an agreement is reached.


Use cases for both the current and improved command bellow.

Connection 1 ("remote server"):

[postgres@localhost bin]$ ./psql -h 192.168.0.5 -p 5433 -U postgres -d postgres

psql (17devel, server 16.1)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "192.168.0.5" at port "5433".
postgres=# \conninfo+
 Current Connection Information
   Attribute    |     Value
----------------+----------------
 Database       | postgres
 User           | postgres
 Server Version | 16.1
 Server Address | 192.168.0.5/32
 Server Port    | 5433
 Client Address | 192.168.0.5/32
 Client Port    | 52716
 Session PID    | 21624
(8 rows)

Connection 2 (socket):

[postgres@localhost bin]$ ./psql

psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
postgres=# \conninfo+
      Current Connection Information
    Attribute     |         Value
------------------+-----------------------
 Info             | Connected via socket!
 Database         | postgres
 User             | postgres
 Socket Directory | /tmp
 Server Version   | 17devel
 Server Port      | 5432
 Session PID      | 27586
(7 rows)

Connection 3 (localhost):
[postgres@localhost bin]$ ./psql -h localhost
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "localhost" (address "::1") at port "5432".
postgres=# \conninfo+
Current Connection Information
   Attribute    |   Value
----------------+-----------
 Database       | postgres
 User           | postgres
 Host           | localhost
 Server Version | 17devel
 Server Address | ::1/128
 Server Port    | 5432
 Client Address | ::1/128
 Client Port    | 46824
 Session PID    | 27598
(9 rows)

Connection 4 (127.0.0.1):
[postgres@localhost bin]$ ./psql -h 127.0.0.1
psql (17devel)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" on host "127.0.0.1" at port "5432".
postgres=# \conninfo+
Current Connection Information
   Attribute    |    Value
----------------+--------------
 Database       | postgres
 User           | postgres
 Server Version | 17devel
 Server Address | 127.0.0.1/32
 Server Port    | 5432
 Client Address | 127.0.0.1/32
 Client Port    | 34876
 Session PID    | 27624
(8 rows)

Regards,
Maiquel O. Grassi.
Attachment

pgsql-hackers by date:

Previous
From: Nathan Bossart
Date:
Subject: Re: Remove Start* macros from postmaster.c to ease understanding of code
Next
From: Tom Lane
Date:
Subject: Re: Set log_lock_waits=on by default