>> Now I've made a change and I find myself using the command "vacuumdb
>> -f foo" to perform a full vacuum. However, I've noticed that vacuum
>> full seems to return non-zero return values much more often.
>
>This is not a very useful statement. What would be useful is to see the
>error messages you are getting. (Look in the postmaster log, if your
>script is discarding stderr.)
Ok -- I redirected stdout/stderr for postmaster and vacuumdb, and the
problem finally re-occurred a few minutes ago.
This is what happens:
- database is started using "postmaster -D /foo >/tmp/dblog 2>&1 &"
- pg_dumpall is run which results in a return code of zero (Tom Lane
mentionned a few days ago that pg_dumpall might be a better "database
integrity check" than running vacuumdb)
- vacuumdb is started using "vacuumdb -f log >/tmp/vacuumdblog 2>&1"
- vacuumdb results in a return code of 1! (I'm testing how valid our
previous attempts at "integrity checks" might be)
The error received when we run "vacuumdb" is:
-> ERROR: Cannot insert a duplicate key into unique index
pg_class_oid_index
-> vacuumdb: vacuum log failed
The error logged by postmaster is exactly the same:
-> ERROR: Cannot insert a duplicate key into unique index
pg_class_oid_index
Now in the past, when vacuumdb returns non-zero return codes, we've
considered the databasebase to be hosed, and thus, would blow it away
and rebuild it. Many days of data would sometimes be lost. However,
pg_dumpall shows that we seem to still have access to the data.
Looking up "vacuum cannot insert a duplicate key into unique index" on
usenet returns quite a few postings, but no many suggestions or
solutions.
In our case, we are using 7.2 on a linux 2.2.14 kernel. Binaries were
downloaded via RPM directly from RedHat.
My questions now would be:
1) How serious is the vacuumdb error?
2) How do we fix it?
3) Is the database hosed?
Thanks in advance,
Stephane Charette
