Re: Loss of table structure on 7.3.19

Nigel Metheringham <nigel(dot)metheringham(at)dev(dot)intechnology(dot)co(dot)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