About 25% of the time while running my nightly maintenance script, which
deletes and the copies in a large amount of new data, then vacuums and
recreates indexes, the vacuum command will hang. When I wake up in the
morning, everything is hosed and I have 6000 complaint emails. boohoo. :)
This behavior started a few weeks ago when I upgraded from 7.0 to 7.1.3.
Looking at the process list (ps -x on Readhat Linux 6.2) shows the
following,
4168 ? S 1:11 postgres: postgres ucc 207.132.206.57 idle in
transaction
5754 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5755 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5756 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5757 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5758 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5759 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5791 ? S 0:01 postgres: postgres ucc 207.132.206.57 VACUUM
waiting
5794 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5796 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5797 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5798 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5799 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5800 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5801 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5802 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
5803 ? S 0:00 postgres: postgres ucc 207.132.206.65 idle
waiting
etc...
If I kill pid 4168, the idle in transaction process, then the vacuum and all
the other waiting processes will proceed fine. Any clues to what could be
hanging or how I could get more information from postmaster about what
transaction is hanging?
Thanks,
Rich