Re: Loss of table structure on 7.3.19

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(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
>
>