Addressing Table Corruption Issues Arising from Abrupt Server Shutdown During PostgreSQL 10.20 to 14.7 Migration

PostgreSQL
1

During the PostgreSQL migration from version 10.21 to 14.7, we encountered an issue caused by corruption in a specific table. Upon checking the logs, we noticed numerous instances of the server abruptly shutting down. After each server restart, when attempting to access the table, we encountered the following exceptions:

"unrecognized win32 error code: 1392
could not read block 2395 in file ""base/16509/3818424"": Invalid argument"

On checking the ‘relkind’ for ‘relfilenode,’ the type appeared to be an ordinary table. We were able to regain access to the table by truncating it.

ProdDB=> select count(*) from prrhtab;
ERROR:  could not read block 2394 in file "base/16509/3818424": read only 0 of 8192 bytes
ProdDB=> SELECT relname, relkind FROM pg_Class WHERE relfilenode=3818424;
        relname        | relkind
-----------------------+---------
 prrhtab | r
(1 row)


ProdDB=> truncate table prrhtab;
TRUNCATE TABLE
ProdDB=> select count(*) from prrhtab;
 count
-------
     0
(1 row)


ProdDB=>

However, since there is no available backup, we are uncertain about the presence of any data and whether any other recovery options are possible.

Please advise if there are alternative methods to recover the data and if there are options to prevent this issue from occurring even in the event of an abrupt server shutdown.

2

Hello,

Please refer to my answer to a later posting that you made.

1 Like