Обсуждение: Accidental removal of a file causing various problems

Pavan Deolasee <pavan.deolasee@gmail.com> writes:
> 1. The user soon found out that they can no longer connect to any database
> in the cluster. Not just the one to which the affected table belonged, but
> no other database in the cluster. The affected table is a regular user
> table (actually a toast table).

Please define "can no longer connect".  What happened *exactly*?
How long did it take to start failing like that (was this perhaps a
shutdown-because-of-impending-wraparound situation)?

> 2. So they restarted the database server. While that fixed the connection
> problem, they started seeing toast errors on the table to which the missing
> file belonged to. The missing file was recreated at the database restart,
> but of course it was filled in with all zeroes, causing data corruption.

Doesn't seem exactly surprising, if some toast data went missing.

> 3. To make things worse, the corruption then got propagated to the standbys
> too. We don't know if the original file removal was replicated to the
> standby, but it seems unlikely.

This is certainly unsurprising.

> I've a test case that reproduce all of these effects if a backend file is
> forcefully removed,

Let's see it.

Note that this:

> WARNING:  could not write block 27094010 of base/56972584/56980980
> DETAIL:  Multiple failures --- write error might be permanent.
> ERROR:  could not open file "base/56972584/56980980.69" (target block
> 27094010): previous segment is only 12641 blocks
> CONTEXT:  writing block 27094010 of relation base/56972584/56980980

does not say that the .69 file is missing.  It says that .68 (or, maybe,
some even-earlier segment) was smaller than 1GB, which is a different
matter.  Still data corruption, but I don't think I believe it was a
stray "rm".

Oh, and what PG version are we talking about?

            regards, tom lane

On 2018-Aug-25, Pavan Deolasee wrote:

> The errors were simply about about the missing file. See attached
> reproduction script that I created while studying this complaint. It will
> throw errors such as:
> 
> psql: FATAL:  could not open file "base/12669/16387": No such file or
> directory
> CONTEXT:  writing block 207724 of relation base/12669/16387
> 
> Now of course, the file is really missing. But the user was quite surprised
> that they couldn't connect to any database, even though mishap happened to
> a user table in one of their reporting databases.

Hmm, that sounds like there's a bunch of dirty pages waiting to be
written to that nonexistant file, and the error prevents the starting
backend from acquiring a free page on which to read something from disk
for another relation.

> To add to their misery, while restart fixed this error and opened
> their other databases for regular operation, it caused the toast
> corruption.

Sounds like the right data was in shared buffers, but it was lost in the
shutdown.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hi,

On 2018-08-25 00:56:17 +0530, Pavan Deolasee wrote:
> > Oh, and what PG version are we talking about?
> >
> 
> I think this is reproducible on all versions I have tested so far,
> including master.

The point is not where you can cause trouble by explicitly deleting
files - that'll always screw up things - but where you encountered this
in the wild.

Greetings,

Andres Freund

Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2018-Aug-25, Pavan Deolasee wrote:
>> Now of course, the file is really missing. But the user was quite surprised
>> that they couldn't connect to any database, even though mishap happened to
>> a user table in one of their reporting databases.

> Hmm, that sounds like there's a bunch of dirty pages waiting to be
> written to that nonexistant file, and the error prevents the starting
> backend from acquiring a free page on which to read something from disk
> for another relation.

Perhaps so --- but wouldn't this require that every buffer in shared
buffers now belong to the corrupted file?  Or have we broken the
allocation algorithm such that the same buffer keeps getting handed
out to every request?

I'm starting to wonder if this type of scenario needs to be considered
alongside the truncation corruption issues we're discussing nearby.
What do you do given a persistent failure to write a dirty block?
It's hard to see how you get to an answer that doesn't result in
(a) corrupted data or (b) a stuck database, neither of which is
pleasant.  But I think right now our behavior will lead to (b),
which is what this is reporting --- until you do stop -m immediate,
and then likely you've got (a).

            regards, tom lane

Andres Freund <andres@anarazel.de> writes:
> The point is not where you can cause trouble by explicitly deleting
> files - that'll always screw up things - but where you encountered this
> in the wild.

Actually, I think the main point is given that we've somehow got into
a situation like that, how do we get out again?

            regards, tom lane