From: | friend(dot)have_00(at)icloud(dot)com |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Could not read block 0 in file |
Date: | 2022-04-08 07:01:57 |
Message-ID: | 5D839689-C2C9-437E-AD6B-CD744AC3F489@icloud.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
Thank you
From | Date | Subject | |
---|---|---|---|
Next Message | PALAYRET Jacques | 2022-04-08 08:29:06 | PostgreSQL : error hint for LATERAL join |
Previous Message | Nikolay Samokhvalov | 2022-04-08 05:57:18 | Re: What have I done!?!?!? :-) |