Is there a way to get the IP address of a client virtual machine from pg_stat_activity? I have several C# applications where I have to ensure that there is only one instance of the application running against a given schema.

I am migrating from Oracle to PostgreSQL 10.

In Oracle, I would run the following query:

select sid, username, machine, program, logon_time
  from sys.v_$session v
 where status not in ('KILLED')
   and upper('\' || machine) not like upper('%\' || :MACHINE)
   and upper(username) = upper(:USERNAME)
   and upper(program) = upper(:PROGRAM)


I use Environment.MachineName in my applications to get the value for the MACHINE parameter.

I have rewritten the query for PostgreSQL as follows:

select pid, user, client_hostname, application_name, backend_start
  from pg_stat_activity
 where upper(state) not in ('KILLED')
   and upper('\\' || client_hostname) not like upper('%\\' || :MACHINE)
   and upper(user) = upper(:USERNAME)
   and upper(application_name) = upper(:APPNAME)


I have added the ApplicationName to my connection string and enabled the log_hostname setting on my database to populate the application_name and client_hostname columns in pg_stat_activity, respectively.

The problem that I have run into is that, when running the application on a virtual machine, the client_address column shows the IP address of the physical machine that is hosting the VM, not the IP address of the virtual machine, and therefore the client_hostname is wrong also.

Am I doing something wrong?  Is there a way to get my application to push the correct information to PostgreSQL when connecting?
I have added the ApplicationName to my connection string


I believe that the machine name on Oracle is voluntarily reported by the client to the server, which is why it works even behind NAT.

PostgreSQL only has one self-reported field, so I think that what you will have to do is stuff both "program name" and "machine name" into the ApplicationName field with some kind of delimiter between them.