Folks,
I've a 7.2.4 report-generation database that has been growing for some time,
resuting in the nightly VACUUM FULL ANALYZE taking longer and longer. (most
of the data is copied nightly from other systems, so use of FSM is not very
effective).
The problem is that the nightly admin scripts are programmed to check for a
locked up nightly maintainence, and to "pg_ctl -m fast stop" it. As the
VACUUM FULL now takes over an hour, it falsely detected a lockup and shutdown
the database in the middle of VACUUM.
On restarting the database, I manually VACUUM FULLed it, and the VACUUM would
speed through until hitting the spot where the database was shutdown, at
which point the VACUUM process went "D", and apparently locked up for 10
minutes. No error messages were written to the logs. Unfortunately, I could
not give it longer to see if it recovered because this is a production system
and I had to get it up and running from backup by 9am.
Does this sound like a crash during VACUUM, or just like it needed more time?
If anyone wants to analyze, I have a complete backup of the post-problem
PGDATA directory. The host system is RH Linux 8.0.
--
Josh Berkus
Aglio Database Solutions
San Francisco