Обсуждение: Data integrity problem !!!

Hi,

we run PG database successfully for 5 month. There were no problem until
now. I set night maintenance script 14 days ago. It does only this:

VACUUM ANALYZE

I've tried restore database today and strange error come:

Cannot insert a duplicate key .......

I've started investigate a cause and I've found that there are really
duplicated PK in table and some data in the same rows are corrupted. I
I've checked all log files and database backups and found that this
problem arose probably after run VACUUM ANALYZE command which is executed
every night. Everning DB backup was ok and morning backup contained these
errors. There were no user activity between these 2 backups except VACUUM
ANALYZE command. I've inspected another database backups after this event
and I've found that there are some additional minor data curruption after
night execution of VACUUM ANALYZE.
We run PG 7.2. There are no errors in log files and whole system looks ok,
except this :(.

Regards,

Vaclav

Vaclav Kulakovsky <vaclav.kulakovsky@definity.cz> writes:
> I've started investigate a cause and I've found that there are really
> duplicated PK in table and some data in the same rows are corrupted. I
> I've checked all log files and database backups and found that this
> problem arose probably after run VACUUM ANALYZE command which is executed
> every night. Everning DB backup was ok and morning backup contained these
> errors. There were no user activity between these 2 backups except VACUUM
> ANALYZE command.

Hmm.  Can you reproduce the problem from a standing start?  (Ie, initdb,
load your evening backup, vacuum analyze, look for duplicates.)  This
is a worrisome report but I really see nothing we can do without more
information.  A test case would be great.

            regards, tom lane

I test it, but I can't reproduce on clean database cluster (after init).
Problem is that it was on our production database so I can't experiment
too much on it. So last night I backed up whole pgsql tree and I instaled
clean 7.2.1 instalation to our production environment. Now I will be able
to perform some deeper.
I remember only one event. There was HW crash on our server 2 month ago.
I transfered whole pgsql tree to a different computer. But there were no
problems until now. So I don't know if some file which could be corrupted
at that time could have influence to this data corruption.

Regards,

Vaclav

On Tue, 30 Apr 2002, Tom Lane wrote:

> Vaclav Kulakovsky <vaclav.kulakovsky@definity.cz> writes:
> > I've started investigate a cause and I've found that there are really
> > duplicated PK in table and some data in the same rows are corrupted. I
> > I've checked all log files and database backups and found that this
> > problem arose probably after run VACUUM ANALYZE command which is executed
> > every night. Everning DB backup was ok and morning backup contained these
> > errors. There were no user activity between these 2 backups except VACUUM
> > ANALYZE command.
>
> Hmm.  Can you reproduce the problem from a standing start?  (Ie, initdb,
> load your evening backup, vacuum analyze, look for duplicates.)  This
> is a worrisome report but I really see nothing we can do without more
> information.  A test case would be great.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Tom Lane wrote:
> Vaclav Kulakovsky <vaclav.kulakovsky@definity.cz> writes:
> > I've started investigate a cause and I've found that there are really
> > duplicated PK in table and some data in the same rows are corrupted. I
> > I've checked all log files and database backups and found that this
> > problem arose probably after run VACUUM ANALYZE command which is executed
> > every night. Everning DB backup was ok and morning backup contained these
> > errors. There were no user activity between these 2 backups except VACUUM
> > ANALYZE command.
>
> Hmm.  Can you reproduce the problem from a standing start?  (Ie, initdb,
> load your evening backup, vacuum analyze, look for duplicates.)  This
> is a worrisome report but I really see nothing we can do without more
> information.  A test case would be great.

The user reported they were running 7.2.  I assume they got bitten by
the sequence counter bug we fixed in 7.2.1.  They just upgraded to 7.2.1
so I assume the problem will not happen again.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026