Data Corruption - ERROR: could not open segment 2 of relation
2
votes
0
answers
1793
views
I am getting the below error for a production database. I am new to PostgreSQL databases. I need help to get this fixed.
2015-10-28 10:25:04,154 [monitor-thread-pool1-thread-6] ERROR [org.hibernate.util.JDBCExceptionReporter] ERROR: could not open segment 2 of relation 1663/98913/98947 (target block 427542): No such file or
directory
server1=> select oid::regclass from pg_class where relfilenode =98947;
oid
------------------------
alert_instance_history
(1 row)
server1=> select oid::regclass from pg_class where relfilenode =98913;
oid
-----
(0 rows)
server1=> select * from pg_database where oid=1663;
datname | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datvacuumxid | datfrozenxid | dattablespace | datconfig | datacl
---------+--------+----------+---------------+--------------+--------------+---------------+--------------+--------------+---------------+-----------+--------
(0 rows)
1. server1=> select version();
version
--------------------------------------------------------------------------------------------
PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13)
(1 row)
1. Error says 2 relations but I can only find 98947. How can I check the other segment ?
2. I just did a select on this table alert_instance_history, I can see records from this table. Do I need to check anything else?
server1=> select min(eventtimestamp) from alert_instance_history;
WARNING: could not write block 427542 of 1663/98913/98947
DETAIL: Multiple failures --- write error may be permanent.
ERROR: could not open segment 2 of relation 1663/98913/98947 (target block 427542): No such file or directory
CONTEXT: writing block 427542 of relation 1663/98913/98947
server1=>
server1=> reindex table alert_instance_history;
WARNING: could not write block 427542 of 1663/98913/98947
DETAIL: Multiple failures --- write error may be permanent.
ERROR: could not open segment 2 of relation 1663/98913/98947 (target block 427542): No such file or directory
CONTEXT: writing block 427542 of relation 1663/98913/98947
3. What's the possible options I have to restore, Can I restore just one table?
Backup of this database is done using pg_dump utility.
4. I verified random tables all of them are producing the same error.
cmproddb=> select min(CREATEDATE) from checkpointdata;
WARNING: could not write block 427542 of 1663/98913/98947
DETAIL: Multiple failures --- write error may be permanent.
ERROR: could not open segment 2 of relation 1663/98913/98947 (target block 427542): No such file or directory
CONTEXT: writing block 427542 of relation 1663/98913/98947
Asked by user5499459
(21 rep)
Oct 28, 2015, 06:39 PM
Last activity: Oct 28, 2015, 08:00 PM
Last activity: Oct 28, 2015, 08:00 PM