Could not read block 0 in file

On Fri, 8 Apr 2022 at 14:36, <friend.have_00@icloud.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.

Thank you

in the order of steps

1) Corruption - PostgreSQL wiki
<https://wiki.postgresql.org/wiki/Corruption&gt;
2) PostgreSQL: Documentation: 14: F.2. amcheck
<https://www.postgresql.org/docs/current/amcheck.html&gt;
3) df7cb/pg_filedump: pg_filedump provides facilities for low-level
examination of PostgreSQL tables and indexes (github.com)
<https://github.com/df7cb/pg_filedump&gt;
4) Physical recovery with pg_filedump (alexey-n-chernyshov.github.io)
<https://alexey-n-chernyshov.github.io/blog/physical-recovery-with-pg_filedump.html&gt;
(example usage)
5) Pgopen-Recovery_damaged_cluster(1).pdf (postgresql.org)
<https://wiki.postgresql.org/images/3/3f/Pgopen-Recovery_damaged_cluster%281%29.pdf&gt;
(using zero_damaged_pages option to skip/zero error pages and move on)

Although I never really dealt with disk corruption, so i am not hands on
with the scenarios, I have tried to replicate some scenarios by injecting
disk faults using dmsetup local disk.
which may/may not be the same the power failure/ RAID controller problems
especially on windows. but the above would be helpful to atleast get the
data (if possible) from the corrupt pages and also scan through the entire
db to find out more problems.

--
Thanks,
Vijay
LinkedIn - Vijaykumar Jain <https://www.linkedin.com/in/vijaykumarjain/&gt;

On Fri, Apr 8, 2022 at 11:06 AM <friend.have_00@icloud.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/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

On Fri, 8 Apr 2022 at 15:24, Magnus Hagander <magnus@hagander.net> wrote:

On Fri, Apr 8, 2022 at 11:06 AM <friend.have_00@icloud.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.

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

i guess it is maybe page header/ metadata not the whole file is zero bytes.
the data can be recovered then from the blogs?
i may be corrected :)

On 4/8/22 04:54, Magnus Hagander wrote:

On Fri, Apr 8, 2022 at 11:06 AM <friend.have_00@icloud.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.

Could OP drop the constraints, drop the table and then recreate the table,
indices and constraints?

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.

--
Angular momentum makes the world go 'round.

On Fri, 8 Apr 2022 at 15:31, Vijaykumar Jain <
vijaykumarjain.github@gmail.com> wrote:

On Fri, 8 Apr 2022 at 15:24, Magnus Hagander <magnus@hagander.net> wrote:

On Fri, Apr 8, 2022 at 11:06 AM <friend.have_00@icloud.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.

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

i guess it is maybe page header/ metadata not the whole file is zero
bytes. the data can be recovered then from the blogs?
i may be corrected :)

My bad, sorry did not read the email properly.

*The file does exist on the file system, with zero bytes, as do the
associated fsm and vm files.*

As Magnus suggests, then.

--
Thanks,
Vijay
LinkedIn - Vijaykumar Jain
<https://www.linkedin.com/in/vijaykumarjain/&gt;