To clarify what a plain "vacuum" is, if you have an initdb data area, <a1> and the database listening on port <p1>,
Is this sufficient,
export PGDATA=<a1>
export PGPORT=<p1>
vacuumdb --all --analyze
running as user postgres (on Linux)?
Thanks.
Paul Anderson
Tom Lane wrote:
Nigel Metheringham <nigel.metheringham@dev.intechnology.co.uk> writes:
I have a database thats been running in production use since 2006 on a
Centos 4.7 (originally an earlier 4 release, updated incrementally).
The pg version is somewhat ancient as we have stuck with the system
postgres - currently postgresql-7.4.19-1.el4_6.1.
Yesterday it all fell apart with all queries/updates into it having
issues. A check showed that many of the tables had lost their
definitions - for example the task_log table now consisted on a single
timestamp field rather than the selection of fields that would
normally be there.
You hit transaction ID wraparound. There are automatic defenses against
this in 8.1 and up, but in 7.4 it's all on the DBA's head to vacuum
everything often enough. See
http://www.postgresql.org/docs/7.4/static/maintenance.html#VACUUM-FOR-WRAPAROUND
However we do have a regular vacuuming process - every day each table
is VACUUM ANALYZE-ed (as well as an index rebuild).
The symptoms indicate pretty strongly that you forgot about vacuuming
the system catalogs. A plain "VACUUM" executed in every database, by
a superuser, is sufficient for this. Trying to be smart by vacuuming
only what you think needs vacuumed is not sufficient.
regards, tom lane