Could not read block 0 in file - Mailing list pgsql-general
| From | friend.have_00@icloud.com |
|---|---|
| Subject | Could not read block 0 in file |
| Date | |
| Msg-id | 5D839689-C2C9-437E-AD6B-CD744AC3F489@icloud.com Whole thread Raw |
| Responses |
Re: Could not read block 0 in file
(Vijaykumar Jain <vijaykumarjain.github@gmail.com>)
Re: Could not read block 0 in file (Magnus Hagander <magnus@hagander.net>) |
| 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 CASCADEDesired 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
pgsql-general by date: