Postgres Corruption: ERROR: could not open file "base/.../.."

vCenter crashed and down with errors like the following. At this time, no one has deleted anything manually from the base directory. It just happened out of the blue. This is an incident happened of vCenter Appliance with Postgresql and not uncommon. If indexes were the only corruption, they can be identified and recreated and perhaps reindex will reconstruct the corrupted indexes (I have not personally tried this, just a logical thinking). My guess is, this works similar if index pg_toast missing. If tables involved in the corruptions and disappeared, it is not worth the time and would likely have data integrity issues. Work on a plan to restore up to the point where corruptions no longer showing. There are chances that the backup may inherit the same corruption. This corruption might be detectable with vacuum full.

2015-05-14T10:40:00.011-04:00 warning vpxd[08940] [Originator@6876 sub=Default] [VdbStatement] Execution elapsed time: 4 ms

2015-05-14T10:40:00.011-04:00 warning vpxd[08940] [Originator@6876 sub=Default] [VdbStatement] Statement diagnostic data from driver is 58P01:0:7:ERROR: could not open file "base/16384/26465": No such file or directory;

--> Error while executing the query

2015-05-14T10:40:00.011-04:00 error vpxd[08940] [Originator@6876 sub=Default] [Vdb::IsRecoverableErrorCode] Unable to recover from 58P01:7

2015-05-14T10:40:00.011-04:00 error vpxd[08940] [Originator@6876 sub=Default] [VdbStatement] SQLError was thrown: "ODBC error: (58P01) - ERROR: could not open file "base/16384/26465": No such file or directory;

--> Error while executing the query" is returned when executing SQL statement "select rule_topn1_proc()"

2015-05-14T10:40:00.012-04:00 warning vpxd[08940] [Originator@6876 sub=OsLayer_win32] [VpxUnhandledException] Win32 Exception (3765269347) detected at 000007fefd90aaad

To confirm with missing objects, use can locate it with 'ls' command or the following query.

SELECT pg_catalog.pg_relation_filenode(c.oid) as "Object ID", relname as "Object Name",

case WHEN relkind='r' THEN 'Table' when relkind='m'

THEN 'Materialized View' when relkind='i'

THEN 'Index' when relkind='S'

THEN 'Sequence' when relkind='t'

THEN 'Toast' when relkind='v'

THEN 'View' when relkind='c'

THEN 'Composite' when relkind='f'

THEN 'Foreign_Table'

ELSE 'other' end as "Object Type", o.rolname as "Owner"

FROM pg_catalog.pg_class c

LEFT JOIN pg_catalog.pg_authid o ON o.oid=c.relowner

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),

pg_catalog.pg_tablespace t

WHERE pg_catalog.pg_relation_filenode(c.oid) = 26465