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-22.214.171.124.126.96.36.199.3 ) which may or may not work depending on what the process ID actually is.
My questions are:
- Would the process ID or session ID be a suitable unique identifier for a connected socket?
- 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?
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
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();