Re: Problem creating a database

On Tue, 16 Oct 2018 at 18:58, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> Please don't top post.
>
> Joshua White wrote:
> > On Mon, 15 Oct 2018 at 21:13, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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
>
>
Sorry about the top-posting - default behaviour of my email client.

./oid2name -d postgres -f 2610 returns no tables.

Four of my six custom databases within the cluster return "pg_index" as the
table. The other two return nothing.

I guess this is fast becoming a situation where I'd be better off to start
over and restore the entire cluster from backups. Not ideal, but might have
to be done. At least I can take the opportunity to replace CentOS 6 with 7
if I do that.

Kind Regards,
Joshua.