PostgreSQL: Suitable ID to uniquely identify client socket connection



  • I'm working on an application which needs to track the connection status of TCP sockets to a PostgreSQL database instance. I'm looking for a suitable ID which can uniquely identify a particular socket connection amongst all the others. Ideally this ID will have the following properties:

    • The ID will not change while the particular socket is connected
    • Different sockets from different hosts to the PostgreSQL server will have different IDs
    • Multiple sockets from the same host and username connected to the same database (e.g. a connection pool) will each have different IDs
    • If a socket disconnects, then after a while reconnects from the same host and username to the same database, it will have a different ID

    Looking at the PostgreSQL documentation, I see that there is a notion of a "process ID", which looks to be different for each socket connection to a single database instance. However, I'm a little confused as to whether this is the actual OS process ID of the PostgreSQL server itself, or whether this is a PostgreSQL construct identifying a particular socket. I see there is also a "session ID" which looks to be derived from the "process start time and the process ID" (according to the https://www.postgresql.org/docs/current/runtime-config-logging.html#id-1.6.6.11.5.2.10.1.3 ) which may or may not work depending on what the process ID actually is.

    My questions are:

    1. Would the process ID or session ID be a suitable unique identifier for a connected socket?
    2. If not, is there another ID that I could use that can act as a unique identifier, or would I need to derive one from the remote host and port?

    Thanks.



  • Every PostgreSQL database connection has a backend process on the database server, which processes the SQL statements for that connection. The process ID of that backend process can therefore uniquely identify a certain database session, and that is what is used as identifier for a client connection in PostgreSQL. You can get the process ID for the current session with the pg_backend_pid() function.

    Note that over the course of time, process IDs can be reused. So if you are looking for a globally unique identifier for a database session, you may want to add the timestamp at which the connection was established. You can use the following query to get both for the current database session:

    SELECT pid, backend_start
    FROM pg_stat_activity
    WHERE pid = pg_backend_pid();
    

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2