Re: Could not read block 0 in file

On Fri, Apr 8, 2022 at 11:06 AM <friend(dot)have_00(at)icloud(dot)com> wrote:

> Hi,
>
> While we are looking for a suitable backup to recover from, I hope this
> community may have some other advice on forward steps in case we cannot
> restore.
>
> RCA: Unexpected shutdown due to critical power failure
>
> Current Issue: The file base/16509/17869 is zero bytes in size.
>
> Additional Information:
> Platform: Windows Server
> PostGres Version: 10.16 (64-bit)
>
> The database does start, and is otherwise functioning and working aside
> from a particular application feature that relies on the lookup of the
> values in the table that was held in the currently zero-bytes data file.
>
> The non-functioning table (ApprovalStageDefinition) is a relatively simple
> table with 5 rows of static data. The contents can easily be recovered with
> a query such as the following for each of the 5 records:
> insert into ApprovalStageDefinition values (1, 'Stage One', 'Stage One');
>
> The error message when running this query is:
> ERROR: could not read block 0 in file "base/16509/17869": read only 0 of
> 8192 bytes
>
> The file does exist on the file system, with zero bytes, as do the
> associated fsm and vm files.
>
> PostGres does allow us to describe the table:
> \d ApprovalStageDefinition;
> Table "public.approvalstagedefinition"
> Column | Type | Collation | Nullable | Default
> -------------------+--------+-----------+----------+---------
> stageid | bigint | | not null |
> stagename | citext | | not null |
> internalstagename | citext | | not null |
> Indexes:
> "approvalstagedef_pk" PRIMARY KEY, btree (stageid)
> "approvalstagedefinition_uk1" UNIQUE CONSTRAINT, btree (stagename)
> "approvalstagedefinition_uk2" UNIQUE CONSTRAINT, btree
> (internalstagename)
> Check constraints:
> "approvalstagedefinition_internalstagename_c" CHECK
> (length(internalstagename::text) <= 100)
> "approvalstagedefinition_stagename_c" CHECK (length(stagename::text)
> <= 100)
> Referenced by:
> TABLE "approvaldetails" CONSTRAINT "approvaldetails_fk5" FOREIGN KEY
> (stageid) REFERENCES approvalstagedefinition(stageid) ON DELETE CASCADE
> TABLE "currentapprovalstage" CONSTRAINT "currentapprovalst_fk1"
> FOREIGN KEY (stageid) REFERENCES approvalstagedefinition(stageid) ON DELETE
> CASCADE
> TABLE "serviceapprovermapping" CONSTRAINT "serviceapprovermapping_fk4"
> FOREIGN KEY (stageid) REFERENCES approvalstagedefinition(stageid) ON DELETE
> CASCADE
>
> Desired Solution:
> A way to recreate the data file based on the existing schema so that we
> can then insert the required records.
>
> Challenges/Apprehensions:
> I am a PostGres novice, and reluctant to try dropping the table and
> recreating it based on the existing schema as I don’t know what else it may
> break, especially with regards to foreign keys and references.
>
> Any constructive advice would be appreciated.
>
>
As the file is zero bytes, there is no data to recover at the PostgreSQL
level unless you have backups. Your filesystem lost the content of it, so
any recovery must be done at the file system or storage level. PostgreSQL
can do nothing about a zero bytes file.

The only real option here is to restore from backup, unless you have some
operating system/storage expert at hand who can recover the file from the
filesystem for you.

You can drop and recreate the table, but since your filesystem has already
lost data what's to say it hasn't corrupted other parts of the database as
well? And as you note, since this is underlying storage corruption
PostgreSQL will not be able to do anything about foreign keys etc. You will
have to verify all those manually. If you do trust the rest of the system
(with some reason), drop the three foreign keys, drop and recreate the
table, and then re-add the foreign keys. But having had this type of
fundamental disk corruption, I personally wouldn't trust the rest of the
contents.

If you end up not actually having any backups, I'd suggest:
1. Drop the table
2. pg_dump what's there
3. Re-initialize a new database from initdb (I would also say create a
completely new filesystem underneath it since that's where the corruption
is, if that's easily done)
4. Restore the pg_dump. At this point it will throw errors on any foreign
keys that are "off", and you will have to clean that up manually.

You should also make sure to apply the latest patches for your PostgreSQL
bringing it to version 10.20. But there are AFAIK no bugs in any of those
minors that would cause this type of corruption, so not being properly
updated is not the root cause of your issue.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>