First thanks to all for your immediate help!
2006/9/3, Tom Lane <tgl@sss.pgh.pa.us>:
> "Peter Bauer" <peter.m.bauer@gmail.com> writes:
> > - OS: Debian Sarge with postgresql-7.4.7-6sarge1
>
> You do know that we're up to 7.4.13 in that branch? There were some
> pretty serious bugs found in 7.4 since 7.4.7, so I would strongly advise
> an update.
Thank you for that, i have to change some minds to be able to upgrade
and your statement will support me.
> > 4. Once, the following error prevents creation of some tables (postgresql.log):
>
> > 2006-08-30 17:50:39 [11189] ERROR: 23505: duplicate key violates
> > unique constraint "pg_class_oid_index"
>
> That sounds like you've wrapped around the OID counter and are suffering
> occasional OID collisions in generating new pg_class entries --- though
> I'm doubtful that this explains any of the other failures.
Could this be another effect of the VACUUM FULL? How could this be
prevented in the future?
> The "missing attributes" complaints might possibly be due to corrupt
> indexes on pg_attribute --- try reindexing it.
I put the same questions here. Should we also regularly vacuum pg_attribute?
> The other behaviors you mention don't ring a bell, but I do note that
> some of the post-7.4.7 fixes are for bugs in vacuum full, so your habit
> of doing a vacuum full every ten minutes may be contributing to the
> system's instability. Constant vacuum-full is not considered good
> practice these days anyway; have you looked into using plain vacuums and
> making sure your FSM settings are high enough?
>
> regards, tom lane
>
The problems appeared nearly at the same time we introduced the
regular vacuum runs due to the decreasing performance of the database
although pg_autovacuum was installed and running. So it really could
be the reason for all that, which would be great since the solution
is pretty easy to do.
So my next steps will be to
1. change the vacuum configuration to remove the --full
2. vacuum pg_attribute like this?
http://archives.postgresql.org/pgsql-admin/2004-08/msg00281.php
3. dump and restore the database
4. upgrade to version 7.4.13 using a backport package for Sarge
Is there anything else we could do, just to be sure to be on the safe side?
thx,
Peter