Re: Problem creating a database - Mailing list pgsql-general

Please don't top post.

Joshua White wrote:
> On Mon, 15 Oct 2018 at 21:13, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > Joshua White wrote:
> > > I'm hoping someone can point me in the right direction. I've got a PostgreSQL 10 server
> > > instance on CentOS 6, which I set up and manage. I have full admin rights on this machine,
> > > so I can access logs, etc.
> > >
> > > 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
> > >
> > > It has been some time since I set up the database, so I don't know how long ago this
> > > became an issue. I can't seem to find any other instances of this problem online either.
> > > The logs are not helpful - even on the highest debug setting, I only see the
> > > "connection authorized" then the fatal "could not open file" error.
> > >
> > > The data directory is on a separate disk array to the OS. Recently checked it and
> > > there are no disk errors.
> > >
> > > Any thoughts or ideas would be much appreciated.
> >
> > 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.
That may of yourse change if you rewrite the table.

Try the following as OS user "postgres":
   oid2name -d <your database> -f 2610
Then you can see which table is associated to that file.

Anyway, your database seems to be quite wrecked, and you'd probably need
an expert to save what can be saved.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com