pg_dump: error: schema with OID 514367 does not exist
pg_dumpall: error: pg_dump failed on database "postgres", exiting
So I hit this myself and this question set me on the path to a fix, but like @mikato alluded to in his comment, I could not delete the offending rows in pg_proc
.
I found them with the block of commands from @Brian
select * from pg_type where typnamespace=514367;
select * from pg_class where relnamespace = 514367;
select * from pg_operator where oprnamespace = 514367;
select * from pg_conversion where connamespace = 514367;
select * from pg_opclass where opcnamespace = 514367;
select * from pg_aggregate where aggfnoid = 514367 or aggtransfn = 514367 or aggfinalfn = 514367;
select * from pg_proc where pronamespace = 514367;
(I didn't save this tab in Datagrip, so I only have the screenshot.)
After discussion with a DBA friend, I was thinking I may have to export the other logical dbs and recreate the instance. This probably was a sane choice as I am running my DB on zolando/postgres-operator and it was the postgres
db, so I didn't need it. However that also allowed me to try a wildcard.
I vacuumed the DB, and reindexed it. Then a new error came up when I tried to check pg_proc
:
ERROR: missing chunk number 0 for toast value 514376 in pg_toast_1255
That error meant that definite corruption had occurred. This was likely when a full disk event hit the primary after a replica stopped following it and I didn't notice.
So I followed this genius little writeup on Github: https://gist.github.com/supix/80f9a6111dc954cf38ee99b9dedf187a
The boiled down version is, first reindex things:
REINDEX table pg_proc;
VACUUM analyze pg_proc;
Then, select chunks of data by offsets rather than data stored in any column. That way the database just grabs chunks of rows from the table until you find the spot that's corrupt.
select * from pg_proc order by oid limit 50 offset 0;
select * from pg_proc order by oid limit 50 offset 50;
select * from pg_proc order by oid limit 50 offset 100;
select * from pg_proc order by oid limit 50 offset 150;
select * from pg_proc order by oid limit 50 offset 200;
....
Once you find a block that is corrupt, narrow the search on that block, iterating down until you find the row(s) responsible.
Once I narrowed it down to two rows.
You then select only the id
(or in the case of pg_proc
, oid
) field, by again grabbing the row by the offset.
select oid from pg_proc order by oid limit 1 offset 3238;
select oid from pg_proc order by oid limit 1 offset 3239;
That yielded the two corrupt row ids, which I was then able to delete
delete from pg_proc where oid = 514371;
delete from pg_proc where oid = 514373;
The two rows that I needed to delete happened to be the two rows that I found with the commands.
Once they were finally gone, I was again able to backup the database with pg_dump
Hoping this helps save someone some pain and also document a possible cause of this issue for others.
select oid,nspname from pg_namespace
... is there a record of OID 7549789 (I'm assuming that there won't be).VACUUM FULL ANALYZE
whenever I do something major. I could perhaps do it again, tonight. But, it'll take a lot of time.