Re: Could not read block 0 in file - Mailing list pgsql-general

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.