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
client_hostname columns in
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?