I am migrating from Oracle to Postgres and in oracle we are logging some DB information and looking for the corresponding syntax in Postgres.
The Oracle query is
SELECT sid, serial#, username, machine, osuser
FROM v$session
where sid=SYS_CONTEXT('USERENV','SID')
I can find the pid and the username in postgres:
Note: I am using Postgres 11.
The closest is the following:
select pid, usename, client_addr, client_hostname
from pg_stat_activity
where pid = pg_backend_pid();
client_hostname will only contain a valid hostname if log_hostname is enabled.
There is no equivalent for osuser as Postgres does not send this information to the server when connecting (and it's a bad idea to rely on that in Oracle as it can be changed to whatever the client application likes).
You can query the pg_stat_activity view. From the documentation:
The pg_stat_activity view will have one row per server process, showing information related to the current activity of that process.
Interesting columns include:
pid: process ID of this backendusename: Name of the user logged into this backendclient_hostname: Host name of the connected client (when log_hostname is enabled)client_addr: IP address of the client connecteAs far as concerns you cannot determine the client's machine in Postgres (see this thread for your reference)