I've been running a postgres database on an external hard drive and it appears it got corrupted after reconnecting it to a sleeping laptop that THOUGHT the server was still running. After running a bunch of reindex commands to fix some other errors I'm now getting the below error.
ERROR: missing chunk number 0 for toast value 12942 in pg_toast_2618
An example of a command that returns this error is:
select table_name, view_definition from INFORMATION_SCHEMA.views;
I've run the command "select 2618::regclass;" that gives you the problem table. However reindexing this doesn't seem to solve the problem. I see a lot of suggestions out there about finding the corrupted row and deleting it. However, the table that appears to have corruption in my instance is pg_rewrite and it appears to NOT be a corrupted row but a corrupted COLUMN.
I've run the following commands, but they aren't fixing the problem.
REINDEX table pg_toast.pg_toast_$$$$;
REINDEX table pg_catalog.pg_rewrite;
VACUUM ANALYZE pg_rewrite; -- just returns succeeded.
I can run the following SQL statement and it will return data.
SELECT oid, rulename, ev_class, ev_type, ev_enabled, is_instead, ev_qual FROM pg_rewrite;
However, if I add the ev_action column to the above query it throws a similar error of:
ERROR: missing chunk number 0 for toast value 11598 in pg_toast_2618
This error appears to affect all schema related queries to things like INFORMATION_SCHEMA tables. Luckily it seems as though all of my tables and data in my tables are fine but I cannot query the sql that generates those tables and any views I have created seem inaccessible (although I've noticed I can create new views).
I'm not familiar enough with Postgresql to know exactly what pg_rewrite is, but I'm guessing I can't just truncate the data in the table or set ev_action = null.
I'm not sure what to do next with the information I've gathered so far.
(At least) your pg_rewrite catalog has data corruption. This table contains the definition of all views, including system views that are necessary for the system to work.
The best thing to do is to restore a backup.
You won't be able to get the database back to work, the best you can do is to salvage as much of the data as you can.
Try a pg_dump. I don't know off-hand if that needs any views, but if it works, that is good. You will have to explicitly exclude all views from the dump, else it will most likely fail.
If that doesn't work, try to use COPY for each table to get at least the data out. The metadata will be more difficult.
If this is an important database, hire an expert.