On Tue, 16 Oct 2018 at 18:58, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:
Please don't top post.
Joshua White wrote:
> On Mon, 15 Oct 2018 at 21:13, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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.
