Re: pg_restore fails due to foreign key violation

Olga Vingurt <olga(dot)vingurt(at)gmail(dot)com> writes:
> The only question left is how we got into corrupted data state.
> In the event logs (PorstgeSQL is runnign on Wondows Server) we found error
> which looks relevant:

> ERROR: could not truncate file "base/12373/17254" to 19 blocks: Permission denied
> CONTEXT: automatic vacuum of table "postgres.public.<table_name>"

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. In that case the truncation failure effectively results in
those pages reverting to some prior state, possibly bringing dead tuples
"back to life". We've been poking at that problem off and on for awhile;
the most recent thread is here:

https://www.postgresql.org/message-id/flat/5BBC590AE8DF4ED1A170E4D48F1B53AC%40tunaPC

Assuming that that is the issue (which is unproven, but seems probable)
then your report makes the third report we've seen of this failure mode
in about a decade --- and it's been there, in principle, much longer than
that. Nobody's thought of a fix that isn't either very unpleasant for
performance (eg, "uselessly" writing out pages we're about to truncate
away) or bad for robustness (eg, PANIC'ing on truncate failure). So
while it's certainly bad news when it happens, I'm not sure that any
fix is going to get committed soon.

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.

regards, tom lane