Thread: Kill specific connection

What's a safe way to kill a specific connection to the database?  I'm
testing some code that reconnects if a connection has timed out or
gone bad and I need to simulate a connection that has gone away.

Chris

snacktime <snacktime@gmail.com> writes:
> What's a safe way to kill a specific connection to the database?  I'm
> testing some code that reconnects if a connection has timed out or
> gone bad and I need to simulate a connection that has gone away.

Disconnecting a network cable might be the easiest test.  Anything
involving a process kill is not a realistic test, because the kernel
will report connection closure to the other end, which is a luxury
you don't get in the real-world cases where this is an issue.

            regards, tom lane

snacktime wrote:
> What's a safe way to kill a specific connection to the database?  I'm
> testing some code that reconnects if a connection has timed out or
> gone bad and I need to simulate a connection that has gone away.

There are various ways. The two easiest are to use iptables to block
network access or to kill the backend process.

If you only have one connection from the client machine then identifying
the backend is easy. On recent versions of PG you can run "select * from
pg_stat_activity" to to find the PID of the backend associated with any
given TCP/IP address/port pair. If you are sorting out multiple
connections or running an older version of PG just use "lsof -P -i
:5432" on the client and server machines to figure out which backend to
kill.

But one thing that is slightly harder to test but probably more
important is when the backend isn't responding but isn't gone and hasn't
closed the connection. I've done partial simulations of this using
"netcat -l -p 5432". The network connection will open successfully but
the "server" will just sit and send no data.

We've found that for our purposes (where a failure to respond within 5
seconds requires the client to continue processing and store the data
locally for recovery later) the combination of using a timeout parameter
in the connection string and "set statement_timeout to 50000" works very
reliably.

Cheers,
Steve