On Fri, Apr 8, 2022 at 11:06 AM <friend.have_00@xxxxxxxxxx> 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 failureCurrent Issue: The file base/16509/17869 is zero bytes in size.Additional Information:Platform: Windows ServerPostGres 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 bytesThe 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 CASCADETABLE "currentapprovalstage" CONSTRAINT "currentapprovalst_fk1" FOREIGN KEY (stageid) REFERENCES approvalstagedefinition(stageid) ON DELETE CASCADETABLE "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.
As the file is zero bytes, there is no data to recover at the PostgreSQL level unless you have backups. Your filesystem lost the content of it, so any recovery must be done at the file system or storage level. PostgreSQL can do nothing about a zero bytes file.
The only real option here is to restore from backup, unless you have some operating system/storage expert at hand who can recover the file from the filesystem for you.
You can drop and recreate the table, but since your filesystem has already lost data what's to say it hasn't corrupted other parts of the database as well? And as you note, since this is underlying storage corruption PostgreSQL will not be able to do anything about foreign keys etc. You will have to verify all those manually. If you do trust the rest of the system (with some reason), drop the three foreign keys, drop and recreate the table, and then re-add the foreign keys. But having had this type of fundamental disk corruption, I personally wouldn't trust the rest of the contents.
If you end up not actually having any backups, I'd suggest:
1. Drop the table
2. pg_dump what's there
3. Re-initialize a new database from initdb (I would also say create a completely new filesystem underneath it since that's where the corruption is, if that's easily done)
4. Restore the pg_dump. At this point it will throw errors on any foreign keys that are "off", and you will have to clean that up manually.
You should also make sure to apply the latest patches for your PostgreSQL bringing it to version 10.20. But there are AFAIK no bugs in any of those minors that would cause this type of corruption, so not being properly updated is not the root cause of your issue.
--
