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