Version: PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC
I was testing what would happen to a connection on the server when a client disconnects from the database via a lost network / internet connection.
To test this scenario out I connected with a Java application and then simply unplugged my network cable. I then shut down my application before reconnecting the network cable.
I then viewed the postgres processes running on the database server, which revealed the following:
postgres 12304 0.0 1.0 7416 1300 ? S Jan20 0:00 /usr/bin/postmaster -p 5432
postgres 12307 0.0 0.0 8280 52 ? S Jan20 0:00 postgres: stats buffer process
postgres 12309 0.0 0.1 7332 232 ? S Jan20 0:00 postgres: stats collector process
postgres 822 0.0 2.5 8100 3292 ? S 18:31 0:00 postgres: test_user test_data 192.168.100.22 idle
As you can see there still existed the connection to the database that I had established from the Java app (from my computer 192.168.100.22).
My questions are:
1) How as an administrator can I tell which connections are lost and which are not so I can shut down the appropriate processes?
2) Is there a way of connecting to the server with client applications that can avoid this situation. That is can the client software do something extra when connecting such as additional parameters, keep alive socket options... to avoid this scenario?
Thanks in advance
Donald Fraser