Re: Could not read block 0 in file

On 4/8/22 04:54, Magnus Hagander wrote:
>
>
> On Fri, Apr 8, 2022 at 11:06 AM <friend(dot)have_00(at)icloud(dot)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.
>
>
> 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.

Could OP drop the constraints, drop the table and then recreate the table,
indices and constraints?

>
> 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.

--
Angular momentum makes the world go 'round.