Re: pg_restore fails due to foreign key violation

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Hm. In theory, that truncation failure in itself shouldn't have caused a
> problem --- autovacuum is just trying to remove some empty pages, and if
> they don't get removed, they'd still be empty. However, there's a problem
> if the pages are empty because we just deleted some recently-dead tuples,
> because the state of the pages on-disk might be different from what it
> is in-memory.

It indeed looks like that was exactly the issue.
The error we saw in the event log happened only once and mentioned the
specific table we had issues with.
We had rows in the table which should have been deleted due to foreign key
constraint (ON DELETE CASCADE configured for the foreign key) and when I
tried to select one of the rows by using the column with the foreign key
nothing returned in the query so I guess the matching index was missing the
rows.

In the short term, what you need to do is figure out what caused the
> permission failure. The general belief among pgsql-hackers is that
> shoddy antivirus products tend to cause this, but I don't know details.
>

There is no antivirus on the Windows server. As it happened only once (in a
few years we installed on the server) and we don't have any additional info
why PostgreSQL got "Permission denied" error we will hope for the best i.e.
that we won't get into this situation again.
Thanks a lot for the help!

Regards,
Olga