Re: Vacuum deadlocks?

Eric,

> Every so often (perhaps 1 time in 50) the vacuumdb -a --analyze
> command will deadlock the system, so that the clients show a status
> of either 'starting' or 'async-notify', or 'idle in transaction'.
> This happened roughly every other day when I had vacuum running from
> an hourly cron, once a week from the every 3 hour cron, and now it's
> happened a couple of times when I run it just after backups.
>
> What am I doing wrong here? Is this a known issue for older
> versions? Or should I be running the vacuums in a different manner?

I think there were some issues with VACUUM on 7.2.1. Were I you, the
first thing I would try is an upgrade to 7.2.3, which will also fix
some serious security issues and at least one backup/restore issue.
Maybe someone else on the list will speak to your specific issue, or
you can look at the release notes for 7.2.2 and 7.2.3.

You could also upgrade to 7.3.1, but that's a more serious undertaking,
as you may have some backward compatibility issues.

Second, were you aware that you have the option of running a regular
VACUUM or VACUUM <table-name> from a database connection without
locking users out of the database? In high-transaction environments,
I can run these every 5 -15 minutes. The vacuumdb commmand-line
utility, I believe, does a VACUUM FULL which exclusively locks the
database (and you do need to do periodically, just not frequently).

See the online docs on the VACUUM command for more information.

-Josh