Finding the DBOS process ID associated with a Postgres PID or session SID

Answer

Sometimes as part of debugging an issue, you want to generate a stack trace on a DBOS process id. The problem is that the DBOS process ID is not listed in the output of nzsession or in the pg.log or dbos.log.

The following bits of information can be found in the following sources:

1) The pg.log contains the Postgres process ID.  This number appears in square brackets after the timestamp.

2) nzsession contains the session ID in the first column, the Postgres process ID in the fifth column, and the client IP address in the ninth column.

3) The dbos.log contains the client ID (cli ID =), session ID (cli SID =), client user ID (cli UID =), and the Postgres process ID (cli PID =).

The DBOS process ID is not listed in these sources.  If you want to find out which DBOS process ID is associated with a given session ID or Postgres process ID you can look in the output of the following command: 

ps -efwH | grep nz > ps-efwH.out

The following is an example of the process for making the association:

1.  Using the pg.log or the nzsession output, find the Postgres process ID.  It is highlighted in boldface below.

pg.log: 2008-06-25 17:27:36.872048 EDT [26784]  DEBUG:  QUERY: select * from t1;

nzsession: 10141 SQL  ADMIN 25-Jun-08, 17:27:07 EDT 26784 proddb1 Idle   normal   127.0.0.1      26775 select * from t1;

2.  In the dbos.log, use the Postgres process ID to find the cli ID, cli SID, and cli UID (as shown in boldface below).

2008-06-25 17:27:36.879607 EDT Info: plan queued: plan ID = 5, tx ID = 0x100e, cli ID = 140, cli SID = 10141, cli UID = 1140, cli PID = 26784

3.  In the output of the ps -efwH command, find the DBOS process ID associated with the cli ID, cli UID, and cli SID.  It will be the first number on the line with the cli ID, cli UID, and cli SID. In the example below, it is 26787 (highlighted in boldface).

nz       26787 30969  0 17:27 ?        00:00:00       dbos event C140,U1140,S10141