On Fri, 8 Apr 2022 at 14:36, <friend.have...@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> 2) PostgreSQL: Documentation: 14: F.2. amcheck <https://www.postgresql.org/docs/current/amcheck.html> 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> 4) Physical recovery with pg_filedump (alexey-n-chernyshov.github.io) <https://alexey-n-chernyshov.github.io/blog/physical-recovery-with-pg_filedump.html> (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> (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/>
