Re: Loss of table structure on 7.3.19

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Loss of table structure on 7.3.19
Дата
Msg-id 9966.1253195828@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Loss of table structure on 7.3.19  (Nigel Metheringham <nigel.metheringham@dev.intechnology.co.uk>)
Ответы Re: Loss of table structure on 7.3.19  (Nigel Metheringham <nigel.metheringham@dev.intechnology.co.uk>)
Re: Loss of table structure on 7.3.19  ("Paul B. Anderson" <paul.a@pnlassociates.com>)
Список pgsql-admin
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

В списке pgsql-admin по дате отправления:

Предыдущее
От: Johann Spies
Дата:
Сообщение: Re: PostgreSQL 8.3
Следующее
От: Nigel Metheringham
Дата:
Сообщение: Re: Loss of table structure on 7.3.19