PostgreSQL database connections hang after client reboot
I had a PostgreSQL database hang a while ago under odd circumstances. A test run of a Python+psycopg2 script was inserting rows into the database when the client froze (or otherwise became non-responsive to anything I tried at the keyboard--I suspect the desktop manager was the cause) requiring a reboot. After the client had been rebooted, re-running the data load hung up. Again. A remote pqsql connection to the database I tried starting was non-responsive as well. The client system wasn't experiencing any further problems. An ssh connection to the database server allowed me to bounce the database and clear the condition--whatever it was--and I could restart the load script and it ran to completion. There's nothing in the PostgreSQL log that shows any problems other than a prior error about a non-escaped apostrophe (that my script had failed to catch and deal with) in a value that failed to load earlier.
Anyone seen something like this happen before and have any ideas what would have caused the database connections to become catatonic like this?
I'm not usually doing low-level monitoring of the network activity between two boxes when running scripts like this so I'm sure there's helpful information that wasn't collected. And I'm not certain I could reproduce the problem if I wanted to. BTW, manually aborting the script (^C) hasn't caused any problems like this in the past so I seriously doubt it would help for debugging this. If anyone has done this kind of monitoring of database connections, pointers on what tools you use for this--wireshark, etc.--would be most helpful.
Is there anything that can be done to avoid having to take the whole database down to clear connection problems? Fortunately, or unfortunately (depending on your point of view), there wasn't anything else that was trying to use the database at the time. If there had been, that might have helped determine whether this was only affecting the connection between those two hosts or whether the entire database was hung up.
Any pointers welcomed.
TIA...
|