Case Study: Couldn’t dump postgresql database , access attempted several different ways, all failed.
Old database had to be dumped to transfer to a new application instance.
ERROR ON LEGACY SERVER WHEN DUMPING
WARNING: invalid page in block 59888 of relation base/16384/16488; zeroing out page
WARNING: invalid page in block 63671 of relation base/16384/16488; zeroing out page
WARNING: invalid page in block 63927 of relation base/16384/16488; zeroing out page
Access via remote replication failed with :
Caused by: org.postgresql.util.PSQLException: ERROR: could not open file “base/16384/16906.1” (target block 942550576): No such file or directory
To mitigate or work around, I tried to identify where the issue was before shutting down the PSQL instance and replacing the data file referenced before trying to salvage the table, somehow.
The logs indicated where the error was.
” base/16384/16906.1 “
The next step was to find a good block and copy into the corrupted table.
I navigated to the psql base folder, and found another file of 8096 kb size, and did
cp base/16384/16499 base/16384/16488
This did cause loss of the affected table, but overall gained the usability of the database enough to dump to text and transfer to a new instance.
Following up, I discovered that a separate, seemingly unrelated table was affected and had to be dumped and transferred separately. For this i used
pg_dump -U postgres database_name -t database_name.table –inserts -f ~admin/table.sql
