Thread: Troublesome handling of dropped connection
I had connected to a Postgresql database, running 7.1.3 on FreeBSD, machine through ssh/psql. My machine crashed. Upon restart of my machine I recconected to the database machine, again ssh to the machine and then run psql. The table I was loading data to at the time of the disconnection was unresponsive. I tried to "truncate" it, tried to "vacuum <table> and psql would just sit there for a long time. On the server log there were several "MoveOfflineLogs" entries, but nothing else. I tried to do a "smart" shutdown (pg_ctl stop) and that didn't seem to work. I ended up doing "pg_ctl stop -m fast". After the fast stop, the server came back up fine. Did a vacuum <table> and it was instantaneous. Any ideas what happened to the database prior to the shutdown? I didn't have anyone else connected so it was not a problem shutting down the server, however is there anything else I could have tried? In the coming weeks this machine will go into production and then I will have more users and a shutdown would not be convenient.
Francisco Reyes <lists@natserv.com> writes:
> I had connected to a Postgresql database, running 7.1.3 on FreeBSD,
> machine through ssh/psql. My machine crashed.
> Upon restart of my machine I recconected to the database machine, again
> ssh to the machine and then run psql.
Did you look to see whether your old session had disconnected or not?
> The table I was loading data to at the time of the disconnection was
> unresponsive.
It sounds like your new session was waiting around for the old session
to complete a transaction and release locks.
Unfortunately, if your old session was not in the midst of
sending/receiving data at the time of the crash, it may take quite a
while (an hour or two) before the TCP code notices that the connection's
been lost. The backend will just sit there patiently until the
connection times out. There is not a lot we can do about this; the
long timeout is mandated by the TCP specs, and the code involved is
kernel code not Postgres code anyway.
You could have zapped the backend more cleanly by sending it a SIGINT.
regards, tom lane
On Mon, 26 Nov 2001, Tom Lane wrote: > Francisco Reyes <lists@natserv.com> writes: > > I had connected to a Postgresql database, running 7.1.3 on FreeBSD, > > machine through ssh/psql. My machine crashed. > > > Upon restart of my machine I recconected to the database machine, again > > ssh to the machine and then run psql. > > Did you look to see whether your old session had disconnected or not? How would I see this from within psql? > > The table I was loading data to at the time of the disconnection was > > unresponsive. > > It sounds like your new session was waiting around for the old session > to complete a transaction and release locks. Is there anything I could have looked at to see this? Right now this machine is in testing stages, but I waiting authorization to start production. Once I go on production then I will have more people/connections. More importantly, right now I am the only one that goes in so I know exactly who is connected. When I go on production I won't know who/when other users connect. I can see how many postgresql sessions are running from top, but how would I see what users are connected from within psql? > You could have zapped the backend more cleanly by sending it a SIGINT. > regards, tom lane If there is a way to see who is connected on psql, is there a way to kill a connection other than SIGINT?
Francisco Reyes <lists@natserv.com> writes: >> Did you look to see whether your old session had disconnected or not? > How would I see this from within psql? Right now I think "ps" on the server machine is the only viable way. In 7.2 the pg_stat_activity table would give you a clue, at least. You might care to read http://candle.pha.pa.us/main/writings/pgsql/sgml/monitoring.html which is from 7.2 docs, but the info about using ps applies to 7.1. regards, tom lane
On Tue, 27 Nov 2001, Tom Lane wrote: > Francisco Reyes <lists@natserv.com> writes: > >> Did you look to see whether your old session had disconnected or not? > > > How would I see this from within psql? > > Right now I think "ps" on the server machine is the only viable way. I find that "top" works pretty good.