Trying to drop a database, this morning I ran into the not so unusual
error:
dropdb: database removal failed: ERROR: database "test_seg1" is
being accessed by other users
however, when I did "select * from pg_stat_activity" on the pg
server, it showed no connection to that db. Then I looked at the
processes:
tmp0% ps ax | grep test_seg1
10317 ? D 0:36 postgres: autovacuum process test_seg1
I imagine this doesn't show up because there is no connection, per
se, the autovacuum process is a bon-a-fide backend process in its own
right. It's just a bit confusing in this circumstance.
I guess this is more of a heads up than a question.
Another circumstance where this has bitten me is in doing a slony
SUBSCRIBE_SET. At least in 1.1.5 the autovac daemon can deadlock with
slony and cause the subscriber operation to fail. When this happened
to me it was somewhat baffling at first because I had altered
pg_hba.conf to forbid all non-superuser connections and there were no
other connections visible at the time (in pg_stat_activity). Turning
off autovacuum during the subscribe fixed this, but not without a
little head-scratching (and maybe some luck).
No way comes to my mind to improve the visiblity of autovac
"connections" but I thought I would throw this out here in case
anyone had any thoughts on the matter.
-Casey