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
