Sample Header Ad - 728x90

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