> > > Recently I attempted to create a new database in this cluster. The command succeeds,
> > > but when I try to connect to the new database, I get a "could not open file" error:
> > >
> > > psql: FATAL: could not open file "base/618720/2610": No such file or directory
> > >
> >
> > Looks like the file backing the "pg_index" table is gone.
> >
> > Can you check if the file exists in the data directory or not?
>
> Thanks for the tip. I've checked and the on-disk file behind "pg_index" still exists.
>
> I have existing databases in this cluster that I'd prefer not to drop and recreate if possible.
>
> I've tried dropping and recreating the new database I want to use, but each time get the same type of error.
"pg_index" initially uses file 2610.
Anyway, your database seems to be quite wrecked, and you'd probably need
an expert to save what can be saved.
So you're right about it being seriously corrupted somehow. All my custom databases seem to work fine. I have data checksums and amcheck enabled and haven't found any issues using the query below.
SELECT bt_index_check(index => c.oid), c.relname, c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree'
-- Don't check temp tables, which may be from another session:
AND c.relpersistence != 't'
AND i.indisready AND i.indisvalid
ORDER BY c.relpages DESC;
However, when I went to connect to template1, it failed:
# \connect template1
FATAL: could not open file "base/1/2610": No such file or directory
Previous connection kept
If template1 is corrupt, that would explain why I am unable to create new databases.
Guess it's time to rebuild.
